Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Header/Detail Fact Granularity

2 posters

Go down

Header/Detail Fact Granularity Empty Header/Detail Fact Granularity

Post  ozisamur Tue Dec 16, 2014 12:37 pm

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.


Posts : 30
Join date : 2014-10-27

Back to top Go down

Header/Detail Fact Granularity Empty Re: Header/Detail Fact Granularity

Post  ngalemmo Tue Dec 16, 2014 7:02 pm

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.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum