Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear,
I've a classic problem that is getting me crazy. (even if it seems easy to solve...)
Scenario :
1) I've Invoice Header table and Invoice Position table
2) I've Sales Order Header table and Sales Order Position table
3) I've some customer/item/Calendar master table
What we need it's just to have per day/area the invoice and Order
I've problem 'cause to relate the master table to the data (fact) table (Invoice and Order) I always have problem with the cycle join loop as in the below image. (I just included some of the table to give u an example)
Which is the best practice in this situation ?
1) Creating a single dimension table with all the customer/item/Calendar data ?! Not so easy
2) Creating one single Fact table ? I don't think so...
Please let me know your suggestion
Thanks
Sergio
I would suggest to concatenate the Header tables (Invoice and Orders) and adding a field DocumentType (1 for invoive and 2 for Order).
Then concatenate the detailed tables together.
The link between Header and Details would be done by their numbers (Check that there is no redundant numbers between Orders and invoices)
Then the calendar can be linked to the Header table !
Thanks spastor,
Unfortunately I've same problem on position level too, 'cause I've some other fields that need main master (as item master, so group sales data by item group and so on)
It means that I need to concatenate all these data too .
Do u think I've only this solution or u have in mind something else ?
Sergio
Well you can also use qualify to separate orders and invoices.
In this case your loop will disappear, but you will need to double table.
Rgds,
Sébastien
Yes,
But I won't be able to have linked data to my dimension tables. Right ?
So I think that the concatenate should be the best solution
Sergio
Olá Sergio!
I think the link table is the best solution, even if it is too complex in the script...
best regards,
Fernando
Do u mean a single link table ?