How do you roll up header-level values on a fact table with line-item granularity?

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:

Designing Dimensional Models for Parent-Child Applications

Dimensional Modeling Basics

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?

I'd introduce a magic Shipping product, and put the shipping value against this only (leaving open the possibility to have multiple shipping products too - Heavy / Overnight / etc). I'd probably still keep the ShippingAmount separate from the ProductAmount column though, so you can easily add up product sales only.

StackExchange Database Administrators Q#23056, answer score: 4