Title: Loading Data to IBM Planning Analytics using TM1Py and Pandas Part 3
Author(s): Paul Caggiano
Application: Planning Analytics, Python
Software Version: Planning Analytics Version 11.8.01300.1; Python 3.9.12
Keywords: TM1Py, Python, Planning Analytics, TM1, Pandas

 

This article is Part 3 of the series exploring how to integrate TM1Py with your IBM Planning Analytics (PA) instance to improve efficiency when loading data generated by a Python script.

Part 1 of this series covers how to create a connection between TM1Py and your PA instance.
Part 2 of the series covers how to prepare your Pandas dataframe for upload to PA.

 

Preparing your Cube for a Data Load

It is important to zero out the targeted intersection when loading data into a cube. This can be accomplished using the TM1py function clear() under the CellService functions.  This function takes one required argument: <cube>, as well as keyword arguments for each cube dimension. To define an intersection, use MDX for each dimension to choose the elements to target and zero out. The example below shows a static method of doing this; however, utilizing Python code this can be made to be dynamic based on the data in your dataframe.

Note that to call the clear function, you must reference your tm1 object – the tm1 variable defined in Part 1 of this series – followed by referencing cell services, a built-in function from the TM1Py library; shown above as cells. The result is tm1.cells.clear(). Dimension names should be exactly the same as in PA, except spaces should be eliminated from dimension names (e.g., Financial Measures => FinancialMeasures).

Sending Data to PA Using TM1Py

The data is now ready to be to be sent to the cube.  The function also falls under CellService functions: write_dataframe(). The following code will allow the data to be sent to the cube directly from the dataframe.

Required arguments are:

  1. Cube_name – the cube to load to
  2. Data – The variable that contains your dataframe object

Note that an optional argument increment = False was passed into the function. This is an important option, as it would increment values already in the cube. This does not, however, work the same as a CELLINCREMENTN as it will not increment two values with the same intersection in the source data. Setting increment = True will only increment to data already in the cube, not including multiple lines in the source data.

Now, looking into the Finance cube, we can see that the data has been loaded in as expected.

Closing Remarks

As this example suggests, using TM1Py can make an automated forecast more efficient. Eliminating the need for an ODBC connection that comes with longer loading times or managing many different output files and TI processes yields a more streamlined business process. The example above can be expanded upon using Python code. For example, the MDX queries for the zero-out step could be automated to include any specific element found in the dataframe dynamically. Further, TM1Py can be used within these scripts to look for elements that do not already exist in the dimension and create them with little human intervention. TM1Py is a powerful tool to integrate PA with Python-generated data. More information on the functionality of TM1Py can be found here.