Is there any resource anywhere that kind of lays out how a data model (primarily the fact tables) should be structured if we want to report actuals, forecast and plan especially if there can be multiple forecast version and multiple plan versions.
Further, I want the ability to setup scenarios that will consist of either actuals only or plan only or actuals + forecast combined and provide ability to compare scenarios.
I've seen the following:-
1. All 3 are in different fact tables (Limitation with this is that you can't provide a drillthrough to transactions)
2. All 3 are just listed as separate columns on each transaction (works if there is only one forecast/plan version)
3. All transactions within a scenario are pre-built (transactions will repeat in the fact table) at ETL level.
4. Everything is in a single table with column that differentiates between actuals and the different versions of forecast/plan and measures handle the various scenarios.
Which option is better?
Would be great if someone can point to a sample that kind of does this stuff or something close to it.
