Getting Data from TM1 with Python

TM1py makes all the goodness of the Python ecosystem available to your IBM TM1 and Planning analytics cubes data. Python is very popular for Data Science and for a lot of other stuff. To use a Python library with your cubes data, you will need to get your data out of your cubes, do the magic and send it back to TM1. In this article we focus on the first step, getting data out of TM1.

There are different ways to achieve this, over time the TM1py community made available lots of functions to cater for different scenario (getting the data into a Pandas frame, into a CSV format…). All these functions are available in the cubes.cells object.

In your Python code, you need first to declare a TM1 service object as below:

#Connect to the TM1 instancetm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, ssl=SSL)

The functions to get data starts with execute_ , to see them you will need to type first tm1.cubes.cells. and then if you are using Jupyter Notebook or Pycharm, pressing the TAB key will show the list of available functions:

There are two ways to get data from a TM1 cube, either from an existing cube view or from a MDX statement:

• execute_mdx…: getting data from a MDX query
• execute_view…: getting data from a cube view

In the example below we use execute_view to get the data from a cube view with the cells coordinates and values:

private=False means that the view is public. If it was a private view, we had to use private=True

execute_view_csv will retrieve the value as a CSV format, this is very useful if you want to extract data from a cube view into a csv file:

execute_view_dataframe will retrieve the data as a Pandas dataframe. Once your values are stored into a Pandas dataframe, you can start using all popular Pandas functions, this is very common in the Data Science world:

With a Pandas dataframe, you can export cube data into a csv file with two simple lines of code:

# Get the datadf = tm1.cubes.cells.execute_view_dataframe(cube_name=cube_name, view_name=view_name, private=False)# Send data into csvdf.to_csv("data.csv")

All functions described above exists as well for MDX. For example to get data from a MDX, we use execute_mdx function:

More examples are available in the reading_data Jupyter Notebook from the TM1py samples data folder:

• https://github.com/cubewise-code/tm1py-samples/tree/master/Data

The easiest way to test it is to download the all TM1py Samples repository.