Just wondering if someone can help me to understand dimensional model.
am building model/report for Sales. it has deals, orders, pricebook, contract, account and other standard dimensions.
Deals: have deal amount and other textual attributes such as name, dates, deal statuses.
Deal can be split into multiple deals based on product. in the source ‘Deal Product’.
& this Deal_Product can be further divided into ‘deal_product_seller’ this is mainly for agent commissions. every ‘Deal_product’ will have a record in ‘deal_product_seller’ table. if its only 1 sales rep then percent is 100%.
all 3 levels have amounts and downstream reports based on these.
Deal
Deal_product
Deal_product_seller
so how do I design dimensions & facts ?
we may not have all 3 levels defined on the day-1. once Deal is started, it may take weeks to get Deal_product record and after 2-3 weeks we might get seller record in table #3.
Hi @Datapassion - Based on the data shared, above model seems correct.
However, we ideally go for Star Schema where we have only one Fact table surrounded by dimension tables.
For above, you can merge your Deal/Deal Product/Rep tables into a single fact table either at Source (preferred) or in Power query. Once done, then can have One to many Relationship b/w Dimensions i.e. Party/Product etc with the single Fact table.
For missing data, there is no specific solution and depends on how one want to handle in reports.
Thank you, Ankit.
the above model is OLTP/source model. now we want convert this into Dimensional.
It is confusing because as per the Kimbal Fact tables must have only one same grain. but here Level 1,2,3 are different grains.
Hi @Datapassion - I believe Kimball Fact tables talk about having the Facts i.e. KPI’s at the lowest grain level. Don’t think it applies to dimensions as Deal/Product/Sales representatives are all dimensions and not Facts.
This is my understanding and how I build my Models.
