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

Question

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?

1. Is the shipping amount considered a non-additive fact for this table?
2. Is some kind of special handling required?
3. Is there any reason not to simply have two (nearly-identical) fact tables? One for orders and one for order line-items?

Answer 1

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.

Answer 2

How do you avoid this doubling of the value?

You could do this by grouping by the OrderNumber first and then grouping by the CustomerKey. Here is an example:

--Setup.
DROP TABLE OrderLineItems;
CREATE TABLE OrderLineItems AS (
   SELECT 27 CustomerKey, 7867 OrderNumber, 49.98 OrderAmount
      , 3 ShippingAmount FROM dual);
INSERT INTO OrderLineItems VALUES (27,7867,12.99,3);
INSERT INTO OrderLineItems VALUES (39,7868,1321.99,132);
INSERT INTO OrderLineItems VALUES (19,7869,39.99,6);
INSERT INTO OrderLineItems VALUES (19,7870,1321.99,132);
COMMIT;

--Query.
SELECT CustomerKey, sum(OrderAmount), sum(ShippingAmount) FROM (
   SELECT CustomerKey, OrderNumber, sum(OrderAmount) OrderAmount
      , MIN(ShippingAmount) ShippingAmount 
   FROM OrderLineItems 
   GROUP BY CustomerKey, OrderNumber
)
GROUP BY CustomerKey;

CUSTOMERKEY SUM(ORDERAMOUNT) SUM(SHIPPINGAMOUNT)
----------- ---------------- -------------------
         27            62.97                   3 
         39          1321.99                 132 
         19          1361.98                 138 

Answer 3

You could divide the Shipping cost equally among the order lines. This will allow you to roll up by customer.

Answer 4

If at all possible, I would allocate the shipping cost to line items. This would let you assess the actual cost and profit for individual products as accurately as possible.

If this is not possible, I second @Jamie's idea of making Shipping a special type of "product."

This is discussed by Kimball in The Data Warehouse Toolkit (third edition), page 185, though he makes a slightly different recommendation:

If the shipping charges and other header-level facts cannot be successfully allocated, they must be presented in an aggregate table for the overall order. We clearly prefer the allocation approach, if possible, because the separate higher-level fact table has some inherent usability issues. Without allocation, you cannot explore header facts by product because the product isn't identified in a header-grain fact table.