TM1 Scenario or Version Dim Design

[slu]

It is probably a simple question to you. We have two general ledger cube, one is GL_ input cube for budgeting/forecasting purpose, another is GL cube for reporting purpose. the design of two cubes are similar, the GL reporting cube has more dimension from transactional GL data such as GL source, GL product etc. But two cubes share scenario, version, month, year dimension.

Dimensions of the input cube are:

Market
Cost center
GL Account
Scenario
Version
Month
Year

In Scenario dimension, we have actual, budget, forecast, actual/forecast, Prior Year actual. Version is Submit1, 2, 3...12 (principal element name) with specific alias we want to show in the report upper management is used to, for instance, submit1 is used for budget, alias can be FTL12092015 which is 2015 final budget. For 2016, it is likely to have Budget_FTL12092016...mixed with 2015 forecast versions (3-4 version for 2015) using submit 2-5 as FTL06112015, FTL09152015...

The purpose with Scenario and Version as two separate dimensions is to allow multiple versions of budget and forecast stored in TM1, we do need to have multiple reports prepared comparing version to version variances during budget periods, so is the forecast version vs. version variances across the year.

The confusion now is we also have 2015 actual/forecast (8m act+4m fcst. ) as the baseline for 2016 run rate calculation and we also have multiple versions of 2015 forecast. When actual/forecast in the input cube is selected to do a variance comparison between 2015 actual/forecast vs. 2015 budget or 2016 budget vs. 2015 forecast, we confuse which version should be combined with correct scenario.

Now we consider combining Scenario and Version dim as one dim Budget_FTL_12092015, Forecast_FTL_06112015, Forecast_FTL_09152015... We have a concern is that this Scenario dimension will expand with lots of elements as year goes (at lease one lockup budget, 6-7 forecast version), it is also not as clean as use generic element like budget, forecast, actual/forecast. Also, how can you deal with multiple year scenario (I need to store at least 5 year budget) and multiple version forecast each year.

I consider several options 1) using different version dimension for GL input cube and GL reporting cube; 2) break down forecast to another cube; budget is once per year activity; forecast is recurring; I like to hear your suggestion. Thanks in advance.

[babytiger]

As a simple solution, I am wondering if grouping the combined scenario/version into years and scenarios, for example:

2015
Actual_2015
Budget_2015
Budget_FTL_ddmmyyy
Budget_FTL_ddmmyyy
Forecast_2015
Forecast_FTL_ddmmyyyy
2016
Actual_2016
Budget_2016


etc.

[slu]

Hi babytiger,

This is what we plan to implement right now. 2014 Budget, 2015 Budget..... As to forecast, we use Jan Forecast, Feb forecast, Jan forecast means 1+11, Feb forecast means 2+10... Thanks