August 3, 2007 at 9:59 am
Hi,
I am trying to design a data mart that will server as a data source for an Analysis cube. I would like to know the best practice for handling order headers and order lines. Do I need 2 fact tables in the data mart, and does there need to be a foreign key relationship between them.
The header table will contain information like whether a delivery was made on time, which depot delivered the order, how many times the delivery was attempted, total distribution charge, whereas the line table will contain product related information with quantities ordered / delivered, and the cost per line
Thanks
August 7, 2007 at 11:52 pm
Hello Andy,
My recommendation is to de-normalize the two order tables (header and line) into one factOrder table in your data mart. Not knowing the details such as data size, how you will be slicing/dicing, etc... I'd initially suggest keeping all of the details of the line rather than aggregating them up to the header. This of course will depend on what the business users are really after as well as size limitations.
Hope this helps.
Marc BeacomManaging Partner DatalereTAP the Power of Data(tm)
August 9, 2007 at 5:44 am
The issue will be, you mentioned some facts (total delivery time in hours/days, total delivery charge) which don't fit the "grain" (level of detail) of the order-line fact.
You'll need to either:
- Have two fact tables
- Allocate things like Delivery Time and Delivery Charge across all of the detail order rows, which can give odd results if you do partial queries
- "Cheat" and put some of these facts in your Order Header Dimension.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply