FAQ

+ What is TM1py ?

TM1py is a Python package that wraps the TM1 REST API in a simple to use library. That makes it easy to build stuff with TM1 and python.

+ What is Python ?

Python is a widely-used general-purpose programming language that lets you work quickly and integrate systems more effectively.

+ Why you should use TM1py ?

  • Use Pandas for Data Analysis and Statistics on top of your TM1 model.
  • Load data (e.g. FX, stock data) from web services into TM1 such as Quandl financial.
  • Build Machine Learning and Forecasting Algorithms with Python and scikit-learn based on your TM1 data.

+ TM1py prerequisites ?

TM1 server should be on version 10.2.2 FP5 as minimum.

+ Do I need a license to use TM1py ?

TM1py is a free and open source (MIT license) and there are no limitations of any kind. All of the code, documents and information in TM1py are to be deemed without warranty.

+ What TM1 security does TM1py support ?

TM1py supports TM1 Security mode 1, 2, 4 and 5. TM1py supports CAM authentication which allows any LDAP directory to be used including the Active Directory.

+ How can I contribute?

The source code is hosted at github.com/cubewise-code/tm1py. If you find a bug or feel like you can contribute please fork the repository, update the code and then create a pull request so we can merge in the changes.

+ How do I get started ?

Just follow this Quick startup guide and you will be able to run your first Python script in less than 5 min.

Set up jupyter notebook

Jupyter is an open-source web application that

The main difference with pycharm is that jupyter allows you to execute your python scripts sections by sections.

Why you should use it:

  • Jupyter notebook is a web app, that allows us to write code in a more narrative, interactive way.

  • You can share notebooks through mails or host them at a shared platform.

  • Jupyter Notebooks are a great, new way to tell a story with your TM1 Data.

Download Jupyter

Run jupyter notebook

Run in a command line:

  • jupyter notebook

Jupyter is running by default on the 8888 port. To access the notebook, go to the following url:

  • http://localhost:8888

 

Check Connectivity with TM1

In order to check if TM1py can connect to your TM1 instance, navigate to the TM1py-samples folder that you downloaded from Github, in this example the samples are located in the following folder:

  • C:\TM1py\TM1py-samples-master

Hold shift and right-click in the folder. Then select "Open command window here". This should open the command-line (or PowerShell depending on your Windows version). Now type "python check.py" into the console to execute the check.py script.

  • python check.py

The script will ask you for:

  • TM1 User (Name of the TM1 User)
  • Password (The user's password. The command-line reads your input silently)
  • CAM Namespace:
  • Port (The HTTPPortNumber as specified in the TM1s.cfg)
  • address (Address of the TM1 instance. 'localhost' or '' if you run the TM1 instance locally)
  • ssl (True or False, as stated in the TM1s.cfg)

TM1py will then try to connect to your TM1 instance.

It will print out the name of the TM1 instance. If this works without Errors you should be able run any of the samples.
All the samples are based on the Planning Sample TM1 model, that comes with the installation of TM1. The samples potentially contain hard coded references to TM1 objects (e.g. cube names). Make sure to adjust those references if you are not testing against the Planning Sample!

Set up your development environment

To use TM1py samples, you do not have to have a development environment, you can just edit the script in a text editor or in jupyter and then run the script. But if you want to dig deeper into the Python language, having a development environment will make your life easier.

Why do you need a development environment?

  • If you want to explore the TM1py code.
  • See the TM1py scripts in multiple windows.
  • Edit and run your scripts in the same interface.
  • Code-completion and syntax highlightning.

PyCharm

PyCharm is likely the best IDE for Python. It offers intelligent code completion, on-the-fly error checking and heaps of other features. It allows you to save time and be more productive. IntelliJ offers a free Community Edition of PyCharm:

https://www.jetbrains.com/pycharm/

Once downloaded and installed with the default settings, you just need to locate the folder where you have downloaded the TM1py samples:

pycharm2.png

You are now good to go!

The following video will give you a quick tour to the PyCharm main features:

Run TM1py script from TM1 process

This article explains the steps to run a TM1py script from TM1 process.

1. Locate python.exe

First you need to locate python.exe file. The first locations to look at are:

  • C:\Python36
  • C:\Users\(Your logged in User)\AppData\Local\Programs\Python\Python36

If you can't find your python.exe location, you can just type in a command line:

  • where python

In this example python.exe is located in C:\Python36\python.exe.

2. Locate the Python script

The script we want to run is 

  • C:\TM1py\TM1py-samples-master\Load Data\fx rates to cube.py

3. Create TM1 process

To run a python script from a TM1 process, we are going to use the ExecuteCommand function of the TM1process:

  • ExecuteCommand(CommandLine, Wait);

The ExecuteCommand function has two parameters:

  • CommandLine will be the path of the python.exe and the script.
  • Set Wait to 1 if you want the TM1 process to wait for the script to finish Wait to 0, if you want the process to go straight to the next line.

Create a new TM1 process and add the following code:

pyExe = '"C:\Python36\python.exe"' ;

pyFile = '"C:\TM1py\TM1py-samples-master\Load Data\fx rates to cube daily.py"' ;

EXECUTECOMMAND(pyExe |' '| pyFile,1);

Because there are spaces in the script name, we need to add double quotes "" inside the single quotes.

Save the process and run it.

If there is an error in the command line, you won't be able to see it from the TM1 process. Even if the TM1 process runs successfully, the command line could fail. You can check your command line in the command window:

  • "C:\Python36\python.exe" "C:\TM1py\TM1py-samples-master\Load Data\fx rates to cube daily.py"

If you are planning to run multiple scripts, it is a good practice to store the file locations in a TM1 cube, so you can easily update the file locations if you need to.

You can access the code of the script used in this example, fx rates to cube daily.py on Github.

Create TM1 objects for TM1py samples

All the TM1py scripts in the Load Data folder requires some TM1 objects. These scripts load data from different datasources into TM1 cubes. If you want to test these scripts without changing the target cubes, you will need to create these TM1 objects.
To create these objects, you can run the script sample setup.py which is in the Load Data folder:

1. Update TM1 instance settings

Before running the script you open the sample setup.py script and update the TM1 instance connection settings:

2. Run the script

To run the script, just type the following command line:

  • python "sample setup.py"

If the command line shows errors it could be either because you are not running the script in the right folder or tm1py can't connect to the TM1 instance. Check if the connection with TM1 works.

3. Check the new TM1 objects

if the script ran successfully, you will then be able to see the following three cubes in your TM1 instance:

Generate MDX Queries form Cubeview

MDX is a fast and powerful way to query data from TM1 Cubes. It can play a key role when you combine your TM1 models with other technologies (e.g. Canvas, R, Python).

Example of MDX Query:

SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS, 
  NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS  
  FROM [General Ledger] 
WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

How do we write MDX Queries? TM1py can do this for you!

TM1py can generate MDX Queries from existing cube views in your TM1 model. So, you don’t have to write them yourself.

This can be a great way to get started with MDX or to save time and be more productive.

This article explains the steps to create MDX query from an existing cubeview. For this example we will use the Gross Margin by periods view of the General Ledger cube:

The TM1py script which generates the MDX is called 

If you haven't downloaded the samples, you can download the script here.

2. Update the script

You need to update the TM1 connection parameters and then the cube and view name:

3. Run the script

Before running the script, you should make sure that TM1py is installed and that TM1py can connect to your TM1 instance.

To run the script open a command line, go to the script folder, in this example C:\TM1py\TM1py-samples-master\Other and then type:

  • python "generate mdx from native view.py"

TM1py is going to print in the command line the MDX query:

SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS, 
  NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS  
  FROM [General Ledger] 
WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

4. Print the MDX query into a file

To print the MDX query in a file you can replace the following code:

print(nv.MDX)

with

with open('output.csv','w') as file:
        print(dim, file=file)

The script should look like this:

If you run the script again in the command line:

printmdx2.png

A file containing the MDX query will be created in the same folder where the script is located:

Find unused dimensions

Ever wondered which of the dimensions in your TM1 Model are not used in cubes?

TM1py can help to answer this questions with 8 Lines of code!

If you have previously downloaded all samples folder, the script "find unused dimensions.py" is located in the Other folder:

If you want to only download this script, you can find the code on github.

1. Check connection with TM1

Before running the script you should make sure that the connection with your TM1 instance is working:

2. Update the script

Open the python script with a text editor and update the TM1 connection information (address, port, user, password, ssl):

3. Run the script

Open a command line from where the script is located and then type:

  • python "find unused dimensions.py"

By default it is going to print the list of all dimensions in the command line.

4. Print the list of dimensions into a file

To print this list of dimension in a file you can replace the following code:

    print(unused_dimensions)

with

with open('output.csv','w') as file:
    for dim in unused_dimensions:
        print(dim, file=file)

Your code should look like this:

Save the script and if you run the same command line again:

FindUnusedDimension4.png

The script will create the file in the same location where the script is located:

Open the file and you will see the list of all dimensions:

Be careful before deleting a dimension

This script will give you the list of all dimensions which are not used in a cube. But unused in a cube does not mean that the dimension is not used by something else. For example the dimension could be used in a TM1 process or TM1 rule.

To find all unused dimensions using a TM1 process, you can find the steps in this IBM guide:

Cleanup your TM1 application

Sometimes our TM1 models (especially non-production environments) become messed up with temporary and unwanted objects, like

  •  subsets and views that were used in TI processes, but not properly deleted
  • temporary processes that were used to test stuff
  • cubes and dimensions for prototyping

In many cases we do use certain naming conventions when we create temporary objects, like we prefix all the processes with ‘temp_’ or ‘test’. It can be a tedious task though, to walk through your model object by object and delete stuff that matches a certain naming convention.

Writing a TM1 process which loops through TM1 objects

Writing a TI process on the other Hand can be cody and might rely on the data directory (which is not a sustainable approach anymore, considering recent developments in the TM1 server, such as Encryption for Data at Rest)

Neither option is convenient. TM1py can help!

Through TM1py you can Read, Update, Create and Delete all kind of TM1 objects.
When we combine this with the power of Regular Expressions we can write scripts that clean our models from all temporary or unwanted objects with the ease of python and regular expressions.

This article explains how to run the TM1py scrip cleanup model.py. If you have downloaded the TM1py samples, you can find this script in the folder called Other:

The objective is to delete all TM1 objects starting with TM1py in the Canvas Sample TM1 instance:

1. Update TM1 settings and the regular expression

Open the script with any text editor or using PyCharm, update the TM1 connection settings (address, port, user, password, ssl). Before running the script you should make sure that TM1py can connect to TM1 instance.

To delete all TM1 objects starting with TM1py we are using the following regular expression:

  • regex_list = ['^TM1py*']

Your script should look like this:

2. Run the script

In a command line, navigate to the folder where the script is located and then type:

  • python "cleanup model.py"

If you check now your TM1 instance, all TM1 objects starting with TM1py should have been deleted.

Upload Exchange rate from a Webservice

Your TM1 models can get great benefits from external information, e.g., FX rates, weather forecast, data on commodity prices or consumer confidence. Webservices like Yahoo Finance, Bloomberg or FRED, offer a range of financial and non-financial information. Many of them are Free to use.

How do we bring this data into our TM1 model? TM1py can help us to do this.

What TM1py does:

The load process generally consist of 3 steps

  1. Load raw data from a webservice (e.g. Yahoo Finance, Quandl)
  2. Transform the data
  3. Push the transformed data into TM1

 This article explains the steps to load FX rates from the FRED to a TM1 cube.

What is the FRED?

The Federal Reserve Bank of St.Louis is a financial institution which offers lots of financial data such as all exchange rates. In this example we are interested to the exchange rate from USD to JPY:

Prerequisites

This sample uses pandas to get the data from the FRED, before running the script you need to install pandas python module. In a command line type:

  • pip install pandas

Once pandas has been installed, you need to install the pandas_reader module. To install it, run in a command line:

  • pip install pandas_datareader

1. Update TM1 instance settings

Before running the Load Data\fx rates to cube daily.py script you need to update the TM1 credentials:

This script is going to load the echange rates to TM1py FX Rates cube, to create this cube you need to run the sample setup.py script.

2. Run the script

After checking the connectivity with your TM1 instance, open a command line inside the Load Data folder and type the following command:

  • python "fx rates to cube daily.py"

3. Check TM1py FX Rates

This script will load daily exchange rates from 1990/01/01 to 2041/01/01in the TM1py FX Rates cube:

4. Load other Exchange rates

If you are interesting about other exchange rate, you can see all available Exchange rate in this link:

For example if you want to load the the Exchange rate USD To Euro, you need to get the index DEXUSEU:

To run this script from a TM1 process you should check the following help article:

Install TM1py

TM1py is a Python package that wraps the TM1 REST API in a simple to use library. That makes it easy to build stuff with TM1 and python.

Requirements:

  • TM1 10.2.2 FP5 as minimum

It is very easy to set up TM1py, you just need to follow these three steps:

  1. Install Python
  2. Enable the TM1 REST API
  3. Install TM1py package

All these steps are defined in the TM1py-sample github page: