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 TM1 and Planning Analytics 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
1. Install Python
CubeCalc is a Python script, to run it, you will first need to install Python. Steps to install Python can be found here.
2. Install TM1py
pip install TM1py
3. Download CubeCalc folder
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:\TM1py\cubecalc folder:
4. Update config.ini file
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:
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:
Execute your first CubeCalc calculation
Setup samples TM1 cubes
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:\TM1py\cubecalc 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):
Example: Calculate IRR
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 the 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:
Run CubeCalc from a TM1 process
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:\TM1py\cubecalc\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 True'; ExecuteCommand(sCommand,1);
The second parameter is either 1 if you want to wait for the command to finish or 0 to termiate 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: