From what I've read, the recommended granularity for a fact table based on an order header / line item structure is at the line item level. In the process, any additive values that apply only to the order header are repeated for each line item:
Below (from the second link) is an example of such a design:
In this example, the shipping amount, which applies to the order as a whole, repeats for each line item.
But if you were to roll all the shipping amounts under a specific customer, you would have double the actual value. How do you avoid this doubling of the value?
- Is the shipping amount considered a non-additive fact for this table?
- Is some kind of special handling required?
- Is there any reason not to simply have two (nearly-identical) fact tables? One for orders and one for order line-items?