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:

enter image description here

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?

4 Answers 4


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.

  • Would the "magic" Shipping product be a placeholder so that a line item with 0 quantity and 0 order amount could exist with the shipping amount? Does this mean you would then zero out the Shipping amount on the other line items?
    – 8kb
    Aug 24, 2012 at 4:33
  • Yes - zeros in order amount & quantity (so they can be added as before), and zero out the shipping amount on the other line items (unless they have special shipping charges particular to the line item).
    – Jamie
    Aug 24, 2012 at 5:24
  • +1 This will work. You can even get rid of the Shipping Amount column.
    – brian
    Aug 24, 2012 at 14:09
  • @Jamie: But if I want to do a browse query on the Product dimension (say, all Products who fall under a certain brand), this design means I'll need to somehow filter out all my "magic" products. Is that also correct? It seems like the problem can be solved by having two fact tables: one for header level and one for line-item level. But nobody seems to recommend this.
    – 8kb
    Aug 24, 2012 at 15:36
  • I guess if you're generating a list of Product.Brand you may want to filter out the Shipping products, but adding a flag to the product dimension doesn't seem like a big imposition.
    – Jamie
    Aug 27, 2012 at 5:40

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:

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);

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;

----------- ---------------- -------------------
         27            62.97                   3 
         39          1321.99                 132 
         19          1361.98                 138 
  • The query makes sense. But does this mean that the roll up can only occur within a view of some sort? If you were to take the fact table and build a cube from it, it seems like the customer wouldn't be able to do these roll ups in a pivot table.
    – 8kb
    Aug 24, 2012 at 18:30
  • I think you just stepped outside my current realm of knowledge. Perhaps someone else can help. I did have another thought though. Could you just assign the shipping to the first line item and leave the other line items in the order as zero? Aug 24, 2012 at 18:55

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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.