Header/Detail Fact Granularity

View previous topic View next topic Go down

Header/Detail Fact Granularity

Post  ozisamur on 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.


ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Granularity

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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