20 February 2018

TM1 Rolling Periods (generate years and transfer data in time dimension)

1. IBM COGNOS TM1 – WHAT IS IT?

IBM Cognos TM1 is a planning software that can transform the entire planning cycle from budget to report, scorecarding, analysis and forecasting.

2. TM1 - TURBOINTEGRATOR PROCESSES

The TurboIntegrator (TI) process allows data and metadata manipulation with functions in the Prolog, Metadata, Data, and Epilog process sub functions. These sub functions include statements based on the settings and options selected when defining the TI process.

3. MODEL DEVELOPMENT

3.1. Building Time dimension

As the time dimension is fixed, for example, current year and next 10 years, in the year change it is important to update the dimension for the new current year, that is, the current year goes to the next one, the last year is added and the values assigned to the correct years. To do this, you start by creating the Time dimension with codes in the elements and the "name" of the years as Alias attribute. The NextPeriod attribute is defined by the user and indicates the next code/period.

 

3.2. Building the cubes

In order to simplify and standardize the construction of all cubes, we chose to assign the Time dimension to the second position, regardless the number of dimensions in the cubes.

 

4. TI PROCESSES

4.1. Building the TI process for year calculation

Creating a process without datasource and only containing one parameter (string format) with a prompt question.

 

 

In Prolog, you begin by reading the current year defined in the Time dimension, and if the parameter defined in the prompt question is different from the current year of the dimension, the next years, for example 10, are calculated based on that year converted to the string format. After the years calculation, we need to execute the TI processes separated by cube size * (must be executed as many as the number of cubes we want to update) where a parameter is passed with the name of the cube to update.

 

* It is necessary to build a TI process for which cube size in the model, i.e. if there are cubes of 2, 4, 5 and 6 dimensions it is necessary to build 4 processes, one for the 2 dimensions, another one for the 4, another for the 5 and another for the 6.

 

4.2. Construction of the TI process for cubes of different dimensions

Example for 4 dimensions cubes - Starts by choosing the IBM Cognos TM1 Cube View datasource and chooses a cube with that number of dimensions. On the Parameters tab, create a new parameter called pCubo (parameter that will receive the value of the execution of the year calculation process).

4.2.1. Variables

The name of the dimensions/variables (in order to make the process dynamic) is changed to V1, V2, V3, V4 and Value with content type Other.

4.2.2. Advanced - Parameters

Create a new parameter called pCubo (parameter that will receive the value of the execution of the year calculation process).

4.2.3. Advanced - Prolog

New variables (TABx) are defined, as many as the cube dimensions and these dimensions are read out of the cube using the TABDIM function.

 

4.2.4. Advanced - Metadata (independent of the number of dimensions)

Empty.

4.2.5. Advanced - Data

In this sub function, it is verified if there is index in the dimension TABx of the element Vx of the cube, ending up creating new variables (FinalVx).

There are also four variables essential for the execution of the process conditions. The variables are (nrec = 1, selectedElement = '', selectedAttribute = '' and nValue = Value).

In the example, the third and fourth dimensions only have one element and, thus, the process is limited to these elements.

WHILE (nrec <= 27) - for the process to run only in the 4 years before, in the 12 months and in the following 10 years.

 

4.2.6. Advanced - Epilog (independent of the number of dimensions)

CUBESETLOGCHANGES (pCubo, OldCubeLogChanges) – log records active.

5. Final Result

 

Blog