Designing a Fact for Sales DW
2 posters
Page 1 of 1
Designing a Fact for Sales DW
I'm designing a Sales Data Mart. I have SCD Type2 dimensions, and the main fact as Forecasted Revenue.
My basic business requirement is as below:
Considering 'Winning Probability%' in Opportunity Dimension as SCD Type 2, I will have to report snapshots of the Forecast at different Probability% (historical data) recorded at multiple time periods (ex:daily/weekly). Whereas for certain reports i need the effective fact as of date, with the current probability of the Opportunity.
1. Do i follow SCD type 2 for my fact? Is this a good approach?
2. In this scenario what is the best way I refresh/update historical data in my Forecast Fact?
I'm not looking at everyday snapshot, since the fact table will grow enormously. I need to capture the fact only when there is a change in the probability or in the fact itself.
3. If there is no change in the fact value (Forecast), how do i capture the fact for the changed probability% at my staging database load itself?
Thank you.
My basic business requirement is as below:
Considering 'Winning Probability%' in Opportunity Dimension as SCD Type 2, I will have to report snapshots of the Forecast at different Probability% (historical data) recorded at multiple time periods (ex:daily/weekly). Whereas for certain reports i need the effective fact as of date, with the current probability of the Opportunity.
1. Do i follow SCD type 2 for my fact? Is this a good approach?
2. In this scenario what is the best way I refresh/update historical data in my Forecast Fact?
I'm not looking at everyday snapshot, since the fact table will grow enormously. I need to capture the fact only when there is a change in the probability or in the fact itself.
3. If there is no change in the fact value (Forecast), how do i capture the fact for the changed probability% at my staging database load itself?
Thank you.
alpsdev- Posts : 2
Join date : 2012-02-20
Re: Designing a Fact for Sales DW
How about a design like
Dim Opportunities
|Name|...|Current Probability|
Fact Forecasted Revenue
|Time_Id|Opportunity_id|...|Revenue|Probability %|
- you record you historical % in fact tables (and make most of the reports on these fields)
- you update a field in dimension for quicker “as-of-now” reports
- if only probability changes, you just record previous forecast and an updated percentage
Dim Opportunities
|Name|...|Current Probability|
Fact Forecasted Revenue
|Time_Id|Opportunity_id|...|Revenue|Probability %|
- you record you historical % in fact tables (and make most of the reports on these fields)
- you update a field in dimension for quicker “as-of-now” reports
- if only probability changes, you just record previous forecast and an updated percentage
Similar topics
» Designing Sales Promotion for Packed Products
» Sales Rep <--> Customer relationship with Sales Fact Table
» Need help in Fact table designing
» Problem while Designing Fact table
» Designing huge incremental factless fact
» Sales Rep <--> Customer relationship with Sales Fact Table
» Need help in Fact table designing
» Problem while Designing Fact table
» Designing huge incremental factless fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum