17 June 2015

IBM Cognos TM1 Essentials

IBM Cognos TM1 is an enterprise planning software platform that can transform enterprise planning cycle, from budgeting to analysis, forecasting and even scorecarding. TM1 is available as an on-premise or on-cloud solution, it enables enterprises to collaborate on plans, budgets or forecasts.

TM1 streamlines the planning process, using the familiar Excel interface but reducing errors common to linked spreadsheets. Built using components of the TM1 business intelligence product set TM1 provides a structure in which organizations can create plans, assign responsibility and monitor the progress of activities through the planning cycle.
TM1 Key Advantages:

  • 64 bit architecture and in memory OLAP cubes;

  • Quickly load and merge large data volumes;

  • What-if scenario analysis;

  • Write back capability;

  • Supports simple and weighted aggregations, complex calculations and data spreading;

  • Includes easily modified data model, workflow actions and statuses;

  • Eliminates prolonged calculation and aggregation times;

  • Operates from within Excel, leveraging workforce expertise;

  • On the fly calculations and changes to hierarchies;

  • Load data from multiple sources (ODBC, SAP, CSV, Excel, Flat files);

  • Can accommodate advanced calculations through rules & Turbo Integrator processes.

IBM Cognos BI users and groups integration.

TM1 Architecture

IBM Cognos TM1 is an enterprise planning software platform that can transform enterprise planning cycle, from budgeting to analysis, forecasting and even scorecarding. TM1 is available as an on-premise or on-cloud solution, it enables enterprises to collaborate on plans, budgets or forecasts.

TM1 Case Study

Goal: A customer asked us to build a solution that allowed them to build a commercial budget nationwide to all the commercial hierarchy levels, starting in the Chief Commercial Officer and stopping at the insurance salesman. That solution had to be done in two steps, first it would be a top down budget and only after that a bottom up budget with approval hierarchy. After the budget is done the data must be exported to Cognos BI for analysis purposes.

Solution: We would build a model in TM1 10.2.2 On-Cloud Solution using the needed dimensions and cubes. That solution was split in three different applications, first application would only be available for the CCO to enter budget values for all regions in Portugal.

The second application would receive values from the first and regional directors would have to distribute the value they received for each region office. The third one would be a bottom up approval application starting at the office chiefs distributing the value they received for their office to all insurance salesman beneath that office.

The trickiest parts in that solution were that at any time at budgeting cycle some insurance salesman could change from one office to another and the budget value that he had must go with him to the office that he moves for.
The same way an office could change from a central office to another, which means that the security applied to the Commercial Hierarchy dimension had to be dynamic in the TM1 dimension and also in all deployed TM1 applications.

The big issue was that approval hierarchies from TM1 applications are not dynamically updated when the dimension is update, which means that we need to update it manually every time the dimension is updated.
To do that we need an external command and application batch to force the hierarchy update each time the dimension is updated.

Step 1 - We build Turbo Integrator processes to create and update dimensions. After that we build all needed cubes and views to deploy in the TM1 excel perspectives add-on. There were created all needed TI processes to update data in all cubes.

Step 2 - Created all groups and users and applied dimension security based on that groups.
Created all needed excel perspectives views using TM1 cube views and application deployment using Performance Modeler.

Step 3 - There were created application maintenance processes to update approval hierarchies and take some application offline or reset application state, which means resetting the application workflow.

Update TM1 Application Approval Hierarchies

 

app_maintenance.bat -serviceurl https://<TM1URL>/pmpsvc -username admin -pwd <password> -op refreshrights -app “<Aplication Name>”
We also created the processes that would allow users to externally execute any TM1 Turbo integrator process, this tool is external to TM1 and it can allow users to even execute TI processes from any external system.

TIExecute.exe.config example:

<configuration>

<appSettings>

<add key=”TM1APIPath” value=”C:\tm1\bin” />

<!–

TM1 SSL Settings

–>

<add key=”UseSSL” value=”True” />

<add key=”SSLCertificateAuthority” value=”C:\tm1\bin\ssl\applixca.pem” />

<add key=”SSLCertificateID” value=”tm1adminserver” />

<add key=”SSLCertificateRevocationList” value=”” />

<add key=”SSLUseCertificateStore” value=”False” />

<add key=”SSLExportCertificateID” value=”” />

<add key=”ClientSettingsProvider.ServiceUri” value=”” />

</appSettings>

</configuration>

Process Execution example:

“C:\FICHEIROS_TM1\TM1_BATCH\0.Aplicacao_TM1\TIExecute\TIExecute.exe” “data” “tm1” “TM1;admin;<password>” “RunProcess” “MASTER Update Dim Estrutura Comercial”

Final Result

Performance Modeler General Solution View – Dimensions, cubes, chores and applications.

Chief Commercial Officer Budget view (Top down Budget)

Regional Directors Budget view (Top down Budget)

Chief Commercial Officer Budget View (Bottom Up with approval hierarchy)

Approval hierarchy and submission status on the left side

Office Manager Budget View

.
.
.
.
.
.
.
Marcos Fernandes
       Consultant