Header/Detail Fact Granularity

Hi guys,

I have an ORDER and ORDER_LINE table in OLTP system but in dimensional model I have just one fact table. I put the CUSTOMER_SK and some other header attributes into the detail fact. But if there is a measure which belong to the Header table something like Shipping price how should I handle that ? Shipping price is header level data not item level data. But granularity of fact table is detail. Can I put the shipping price to the detail fact for all items like CUSTOMER_SK? So if end user wants to get average shipping price of orders he should group by of the order and shipping price etc.

Yes, one way to do it is to treat it as an item and create a line for shipping charges. It is a fairly common approach.

Alternately, you can add it as a separate measure and either create one line with no product or allocate the charges to each product. Both approaches have issues which may or may not make users happy. And when you start talking 'allocation' with the business, it opens up a can of worms that best be left alone (do you allocate by value, or price, or weight, or unit count, or some other method???). I would seriously consider it if shipping charges are in fact calculated at the item level and the order amount is a simple sum of those charges. But if it is a order charge based on other criteria, creating an item, as you suggest, is the easiest way to go.