This article will explain you how to set up CubeCalc, a super calculator for IBM TM1 and Planning Analytics

CubeCalc enables you to execute complex finance calculations which are not available natively in IBM Planning Analytics (TM1) such as IRR (Internal Rate of Return) and NPV (Net Present Value).

CubeCalc installation steps

• TM1 10.2 FP5 as minimum
• TM1py v1.0.3 as minimum

CubeCalc is a Python script, to run it, you will first need to install Python. Steps to install Python can be found here.

CubeCalc uses the free Python package TM1py, so the next step is to install the TM1py package. Open a command prompt and then type:

pip install TM1py

CubeCalc is an open-source Python script which you can download from its github page:

In this example, the CubeCalc folder has been copied to the C:TM1pycubecalc folder:

In the config file example, you will find two instances but you can add as many as you want, to add a TM1 instance just add the following information:

• [instanceName]
• address: IP address of the physical server where the TM1 instance is located
• port: HttpPortNumber of the instance (tm1s.cfg)
• namespace: (only required for CAM Security)
• user: TM1 user
• password: TM1 user password
• ssl: True/False (depending useSSL setting in tm1s.cfg)

To check the connectivity with your TM1 instance, you can follow the steps described here:

• code.cubewise.com/tm1py-help-content/check-connectivity-with-tm1

Execute your first CubeCalc calculation

The following examples used TM1 objects which can be created using the “setup sample.py” script located in the cubecalc folder.

To execute the script, open a command prompt, go to the cubecalc folder using cd C:TM1pycubecalc and then to execute the script use the command python “setup sample.py”. If the scrip ran successfully, the prompt should go to the next line:

In your TM1 instance, you should now see two new cubes (Py Project Planning and Py Project Summary):

The internal rate of return (IRR) is a metric used in capital budgeting to estimate the profitability of potential investments. This is a pretty common function in Excel, now thanks to CubeCalc, with one command line, you will be able to calculate the IRR in IBM TM1 and Planning Analytics.

In this example, we want to calculate the IRR for Project 1 for the next 12 quarters. The first view on the left is our data and the view on the right is the cell where CubeCalc will send the result of the IRR:

In this example we are using cubeview as data source so in our command line the paramters: cube_source “Py Project Planning”, view_source “Project1” and cube_target “Py Project Summary” and view_target “Project1 IRR” the command line is as below:

python CubeCalc.py --method "IRR" --tm1_source "tm1srv01" --tm1_target "tm1srv01" --cube_source "Py Project Planning" --cube_target "Py Project Summary" --view_source "Project1" --view_target "Project1 IRR" --tidy False

The tidy parameter enables you to delete the source and target views after they have been used. To keep the views, set the value to False, to delete the views set it to True.

Copy the above command line and paste it in the command promtp and press enter:

If you don’t get any messages, it means the CubeCalc ran successfully, you just need to recalculate the view Project1 IRR to see the result 0.0126231328:

If you compare in Excel, you will get the same value:

Since release v1.0, CubeCalc can perform the IRR calculation for multiple projects in one execution.

Just pass three additional arguments to the execution: Dimension, Hierarchy, Subset, and make sure the dimension is placed in the titles (on both views).

python cubecalc.py --method "IRR" --tm1_source "tm1srv01" --tm1_target "tm1srv01" --cube_source "Py Project Planning" --cube_target "Py Project Summary" --view_source "Project1" --view_target "Project1 IRR" --dimension "Py Project" --hierarchy "Py Project" --subset "All Projects" --rate 0.1

Cubecalc will run the calculation for every element in the subset.

The IRR will be calculated for each project:

To run CubeCalc from a TM1 process, you just need to use the ExecuteCommand function either in the Prolog or Epilog tab:

sCommand = 'python C:TM1pycubecalcCubeCalc.py --method "IRR" --tm1_source "tm1srv01" --tm1_target "tm1srv01" --cube_source "Py Project Planning" --cube_target "Py Project Summary" --view_source "Project1" --view_target "Project1 IRR" --tidy True';ExecuteCommand(sCommand,1);

The second parameter is either 1 if you want to wait for the command to finish or 0 to terminate the process straight away.

If CubeCalc returns an error, the best place to start looking at, is the log file. CubeCalc will create a CubeCalc.log file in the same folder location as the CubeCalc.py.

The most common errors are the following:

1. CubeCalc can’t connect to TM1 instance so you should check first the settings in the config.ini (Check connectivity with TM1).
2. TM1py version should be v1.0.3 as minimum (Upgrade TM1py).