Designing a Fact for Sales DW

View previous topic View next topic Go down

Designing a Fact for Sales DW

Post  alpsdev on Mon Feb 20, 2012 10:42 am

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.

alpsdev

Posts : 2
Join date : 2012-02-20

View user profile

Back to top Go down

Re: Designing a Fact for Sales DW

Post  ykud on Tue Feb 21, 2012 12:00 am

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
avatar
ykud

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.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