Given that Actual data is provided monthly, forecast data is provided quarterly for months up to next October, Plan data is provided annually for current October to next October. Sample below
It is requested to report for current quarter, current fiscal year and current fiscal year to date
Approach
Assume that Historical reports is not needed to reproduce.
Report authors don't need to create measure on the fly, instead, the logic will be embedded in data mart.
Report data should be designed as same granularity and addable.
Solution
The idea is to wrap the logic into ETL as below
Combined actual: move forecast data from future periods into actual to report actual data for aggregated periods such as quarter, FY.
Combine forecast: move forecast data from previous quarters to align to current quarter to report forecast data for aggregated periods such as quarter, FY and FYTD
Combined plan: move plan data from previous year to align to current year to report plan data for aggregated periods such as quarter, FY and FYTD.
Dataflow
Step 1: Load combined plan data
Step 2: Normalize forecast data
Step 3: Load actual data
Step 4: load combined forecast data
Step 6: load combined actual data
No comments:
Post a Comment