how to handle mutiple level granularity in retail domain dimensional model

View previous topic View next topic Go down

how to handle mutiple level granularity in retail domain dimensional model

Post  kuldeepchitrakar on Tue Apr 05, 2011 6:26 am

Hi

in retail domain, I have following scenario

An order can have multiple receipts and receipt can have multiple products.

I am creating fact table at product level transaction (per item in receipt) however Discounts are offered on total receipt amount and some time on product

So if i keep discount measure in fact table its granularity is conflicting as fact table granularity is at per product sale.

What would be recommended approach. Should I create 3 different fact table Order, Receipt and Item_fact


kuldeepchitrakar

Posts : 17
Join date : 2010-04-21
Age : 34
Location : India

View user profile http://www.bidwbooks.com

Back to top Go down

Re: how to handle mutiple level granularity in retail domain dimensional model

Post  Ramtin on Wed Apr 13, 2011 9:26 pm

I am fairly new to dimensional modelling however, I see two choices
1. You can calculate % of discount on the reciept/ Order and apply it to each item to calculate discount value at item level. When you agreggate up your fact you should get the same amount of discount from items.

2. if you do not need to see the discount at item level and only need to see at Reciept level or order level you can create an this level of data as enhanced Aggregate level that has the discount in addition to item level aggregated facts.

I personally prefere the 1st option but it envolves a little more ETL

Ramtin

Posts : 12
Join date : 2011-03-10

View user profile

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