how to handle mutiple level granularity in retail domain dimensional model
2 posters
Page 1 of 1
how to handle mutiple level granularity in retail domain dimensional model
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
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
Re: how to handle mutiple level granularity in retail domain dimensional model
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
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
Similar topics
» Dimensional Model with different granularity to Cube
» Transaction level granularity
» Why we use Dimensional Model over De-normalized relational Model ?
» How to model comparible store sales for retail data
» Granularity In two different Dimensional Models
» Transaction level granularity
» Why we use Dimensional Model over De-normalized relational Model ?
» How to model comparible store sales for retail data
» Granularity In two different Dimensional Models
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum