The reasons for this is that there are many header level items like (discounts,
taxes..) are calculated as a sum for all the line items.
A good dimensional table design would look for calculating the header items
like discounts, taxes, transportation etc and allocate them to the individual
items. This may involve some allocation rule, which may not be thoroughly scientific,
but they will help in making better analysis.
For example, if I have an invoice containing 6 different items, I will have
six records in the fact table, with each item having the freight (calculated
for overall invoice) allocated to individual line items as per the sale value
of the line item, and tax calculated as per the tax rules for the given product
in the line item. Ideally the allocation rules should be kept simple or else
you will get caught into hundreds of different rules related to these kind
of calculations (for example free shipping for a product if bought within
Christmas seasons..).
One has to make sure that the total rolled-up amount will be equal to the
header amount in the invoice. |
