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
Depends on the analysis that needs to be supported, both by the data mart and the cube. For example, the FactInternetSales and FactResellerSales tables in the sample Adventure Works DW database are at the line level. But from your description, it sounde like there are relevant measures at both the header and line item level, so 2 fact tables (with 2 mesure groups) may be preferable. The detail table would then have a foreign key for the parent header table.
In terms of dimensions, those which directly relate to the detail measure group can be configured with a "many-many" relation to the header measure group (using the detail measure group and header fact dimension as intermediates). And those dimensions which directly relate to the header measure group can be configured as "referenced" (via the header fact dimension) for the detail measure group.
This article argues for a single detail-level fact table, but the AS 2005 modelling features may mitigate some of these issues:
Managing Your Parents
Be mindful of reporting needs when designing parent and child fact tables
By Ralph Kimball
...