Hello TM1 aficionados,

In my current multi-year TM1 project I am facilitating the creation of extended forecasts: they run for the current year and the next 3 years after, at the monthly level. The full P&L and balance sheet will be part of the exercise.

The cube structures is such that we have:

• 1 dimension containing the name of the forecast, followed by an indication of when it is created. For instance, FCST 2025 10 E1. 2025 10 obviously stands for October 2025. E1 stands for Estimate 1: it is in fact a version and a scenario combined.
• 1 dimension containing year/period combinations at the monthly level, including all sorts of rollups for YTD, YTG, HTD, averages, yearly input values, you name it.

The user is allowed to copy version "FCST 2025 10 E1" to "FCST 2025 10 E2", for instance. This will impact several cubes and all data points in the versions.

When we want to create views in PAW for data input or reporting or reconciliations, versions will be output against their respective periods. It does not make sense to show 10 years of data against 1 forecast when that forecast only holds values in 3 years.

MDX views in PAW are powerful. How would we set up this dependency between versions and periods? The user might select a different version from a selector and the periods should update accordingly. Note that we need several public subsets in the Period dimension:

• a subset containing all 36+ periods
• a subset containing 3 or 4 yearly totals
• a subset containing monthly values for the current year
• a subset containing YTD values for the current year
• etc.

This article shows my solution. It is not creating a beast of an MDX (or multiple such beasts) whereby the period selection is derived from the version selection. You know, WITH MEMBER, WITH SET and a number of ugly string computations (which probably cost some performance too).

Instead I chose to create 1 simple TI process to create static subsets (look at the 4 examples above). I create the subsets for all versions. Version parameters are conveniently stored in a simple lookup cube, such that we can infer - for a given version - what is year 1 ? what is year 2 ? what is year 3 ? etc. Based on these parameter values we can create and maintain the subsets. All forecast versions starting within the same year will contain the same elements so in the end, we do not have hundreds of subsets.

Last step is to change the view MDX to use the static subset that it needs. This could be something like:

SELECT

   {[Measure].[Measure].[Revenue]} ON 0,

   {TM1SubsetToSet([Period].[Period],

        [Prm_Version].([Prm_Version_Msr].[Prm_Version_Msr].[Subset prefix]) + "Year Input (4)", "public")} ON 1

FROM [Sales] WHERE (

   [Version].[Version].[FCST 2025 10 E1],

   [Company].[Company].[IBM])

Here we have the version (chosen from a selector) in the titles. That version is part of the 2D cube called "Prm_Version":

we store parameters like the "Subset prefix" by version. That prefix is concatenated with "Year Input (4)" and forms 1 of my static subsets. It is the subset with 4 yearly input elements (1 for each of the year of the given forecast version).

The end product is a simple yet elegant way to manage dozens of subsets that are applied in many dynamic views.