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 scenarios (getting the data into a Pandas frame, into a CSV format, etc.). All these functions are available in the cubes.cells object.
In your Python code, first declare a TM1 service object:
The functions to get data start with execute_. Type tm1.cubes.cells. and press TAB in Jupyter Notebook or PyCharm to see all available functions:
There are two ways to get data from a TM1 cube:
execute_mdx...— getting data from an MDX queryexecute_view...— getting data from a cube view
Getting values with coordinates
Use execute_view to get the data from a cube view with the cells coordinates and values. private=False means the view is public:
Getting data as CSV format
execute_view_csv will retrieve the value as CSV format — very useful for extracting data from a cube view into a csv file:
Getting data as a Pandas dataframe
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:
Getting data from MDX
All functions described above also exist for MDX. For example, to get data from MDX, use the execute_mdx function:
More examples are available in the reading_data Jupyter Notebook from the TM1py samples data folder on GitHub.