Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

3 Answers 3

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.

share|improve this answer
    
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 '12 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 '12 at 5:24
    
+1 This will work. You can even get rid of the Shipping Amount column. –  brian Aug 24 '12 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 '12 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 '12 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:

--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 
share|improve this answer
    
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 '12 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? –  Leigh Riffel Aug 24 '12 at 18:55

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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