Changing grain over time for consolidated fact

[AndrewMater]

I have a scenario where I am not sure how to model and was hoping for some opinions. I am aware of the reasons why you want to keep a consistent grain in a fact table, and am hoping that I can keep this

The scenario is ...
-I have a consolidated fact that contains both actual and budget amounts for Hospital Seperations.
-The data spans multiple years.
-The level of detail for the budgets is different across years.
-The lowest level of detail is available for actuals for every year
-For the years that the budget does not have the lowest detail, there are no allocation rules available to split the budget into the lower levels of detail to match the actuals
-The business wants to report the budget v actuals variance for the level that the budget was done for each year, and have the ability to choose different years
Eg Fact Row Data
SKEY Year Month Meas Act Bud Care Group
1 2012 July Sep 5 0 SNAP
2 2012 July Sep 3 0 ACUTE
3 2012 July Sep 0 10 TOTAL Care Group/???? – add a default row to the dimension
4 2013 July Sep 12 15 SNAP
5 2013 July Sep 8 15 ACUTE

My options so far ....
a) Create an additional row in the Care Group dimension, that represents a 'TOTAL' care group.
This would require multiple lines in the fact as the actuals would go to the detailed Care group(Row 1 & 2 above), and the budget (Row 3 above) would go to the ‘TOTAL’ care group. I am not keen on this idea, as then the Care Group Dimension contains a hierarchy within the same column. Some are individual care groups and some are total care groups. The TOTAL is not really a care group.

b) Create 2 different fact tables, and depending on the year, source the data from the different table.
This is not really feasible, as I have only shown a single example of the differences for a single measure. There are multiple measures that have multiple differences of budget granularity over time. To do this, I would have to create a lot of fact tables and then implement a stack of report logic to choose the correct table for each measure, and then merge all the results into a single report.

c) Create a measure hierarchy.
Within the Activity Measure Dimension, Create a Hierarchy.
Eg
SKEY Measure Level 1 Measure Level 2
1 Separation Separation SNAP
2 Separation Separation ACTUE
3 Separation Separation -need this to cater for fact row 3 above.

Not keen on this, as we already have a Care Group Dimension that is used on our fact table that contains actuals that I would like to reuse.

Any suggestions would be appreciated.
Thanks in Advance
Andrew

[BoxesAndLines]

How about create an aggregate fact and then add the budget amount at the appropriate grain?

[AndrewMater]

Thanks for the reply. How is that different to option a) specificed above?

[BoxesAndLines]

So your aggregating your fact table to match the level of your budget amounts. There's no additional rows. The budget and actuals are now grain consistent. Everything is now additive. Your budget metrics are in the fact and on the same row as your aggregated metrics. If that is what option a is, then that's a great idea! ;-)

[AndrewMater]

Thanks again .... The problem is that the budgets only match the grain of the actuals for a subset of the years. Year 1 may match and Years 2 & 3 may not match. In the years where they dont match, the actual and the budget wont be at the same grain.

[Sponsored content]