Which date to be used for rolling up the data into monthly aggregate fact ?

Hi,

Our data model has one base fact table which is " Snapshot Fact table" storing complete life cycle of business process in one record. And in doing so it captures lot of date milestones such as - Case reported date, Case completed date, Case lock date etc. From this fact table we are deriving lots of metrics such as Count of Cases, days interval between various dates combination etc.

To avoid performance overhead we are planning to create an Monthly Aggregate fact which will be populated from this (above mentioned Snapshot Fact table) base fact able. This aggregate will be storing all the counts and days interval measures by month.

Problem :

As we are having multiple date columns in base fact table we are confused which date should be considered for rolling up the data. As earlier mentioned we have dates like Case reported date, Case completed date, Case lock date etc. For the time being we got a go ahead to use Case completed date as the base date on which the data can be rolled up.

But what happen if customer decides that they want to see Monthly data based on some other date column say - Case completed date. In this scenario my aggregate fact is unusable as Monthly Aggregate does not have data being rolled up for Case completed date.

Should we suggest customer to create another monthly aggregate fact and treat base date as Case completed date or hit the base fact for all such adhoc queries. Please provide your suggestions to improve the design.

Another option is to create an abstract design (not a big fan of this !) for monthly aggregate fact with a "Discriminator column". And store the data rolled up based on various dates which is being segregated based on discriminator column.

Regards,
Abhiraizada

Rollup using snapshot date. All counts are simply reported by calendar month. So all if you have 3 cases completed in January, the case completed count is 3.

It sounds like you will need a unique "key/combo" using the dates of your businss process. I tried to illustrate here:

Code:


Detail Fact                  
Case # (degen)   Case Amt   Created Date   Closed Date   # of Days To Close   Locked Date   # of Days to Lock
A   50   6/30/2011   7/10/2011   10   7/12/2011   2
B   100   7/1/2011   7/13/2011   12   7/16/2011   3
C   250   7/4/2011   7/28/2011   24   7/31/2011   3
D   100   8/2/2011   9/16/2011   45   9/17/2011   1
E   60   8/20/2011   9/1/2011   12   9/6/2011   5
F   400   8/26/2011   9/1/2011   6   9/5/2011   4
                  
                  
                  
Summary Fact                  
Case Count   Case Amt   Month Create Date   Month Close Date   # of Days To Close   Month Lock Date   # of Days to Lock
1   50   6/1/2011   7/1/2011   10   7/1/2011   2
2   350   7/1/2011   7/1/2011   26   7/1/2011   6
3   560   8/1/2011   9/1/2011   63   9/1/2011   10

*sorry for the inline code, first time poster and the forum would not let me post a screenshot

The "unique" rows of the aggregate are grouped by the Month Begin dates for each of the three dates used in the detail fact. I'm not sure if this will reduce your fact down to a reasonable size for performance reasons however. You can run a few queries to determine the ratio of detail-2-summary before you get started.

And like BL mentioned, this would be also grouped by Snapshot Date