| Title: | Loading Data to IBM Planning Analytics using TM1Py and Pandas Part 1 |
| 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 |
Python is no doubt one of the fastest growing programming languages in terms of its use for data science. This popularity comes from Python’s powerful ability to automate many tasks, such as data wrangling, manipulation, and visualization with help from many different integrated libraries that can be easily implemented by a developer of any experience level.
Many organizations are switching to Python for data modeling, taking advantage of the ability to handle different machine learning algorithms such as linear regression, logistic regression, and many others. This can be extremely useful for FP&A departments, as Python can allow for large quantities of data to be calculated and automated, boosting efficiency in areas such as financial forecasting.
A Use Case for TM1Py
When it comes to integrating forecasts from Python to IBM Planning Analytics (PA), this can become overwhelming as there are many different options to choose from – many of which come with a cost of performance and time. TM1Py is a Python library that solves this problem. It allows for direct integration of data between a Python script and Planning Analytics without having to use TI processes to read from csv files or creating time-consuming ODBC connections. With the use of the Pandas library and TM1Py, uploading data directly from Python can become an efficient process.
Many machine learning libraries within Python allow for a direct output to a dataframe from the Pandas library, and TM1Py allows for a direct integration between PA a Pandas dataframe. This can be useful in scenarios such as automating Python scripts to run a forecast and write directly to a dataframe. Instead of writing the data to a text file or to a SQL database and creating a TI process from reading an output file, modeling and uploading can be done directly from your Python script.
In this series, we will explore how to connect TM1Py to your PA instance, prepare a Pandas dataframe in Python for a load to PA, and prepare your PA cube and load data. The example discussed in this series shows a dataframe that was output by an automated forecasting algorithm in Python. Using only a Python script, the program will connect to PA, zero out the target intersection and allow for the upload of the forecasted data. Assumed in this series is a baseline understanding of Python and the Pandas library, as well as having Pandas and TM1Py installed in your Python environment.
Creating a Connection to Planning Analytics via Python
To import the library into your Python script, use the following line of code:
The library is now successfully implemented in the script, and connection to a Planning Analytics instance can be created. The first step is to create a TM1Service object. The following arguments are needed for the TM1Service object:
- Address – IP Address of the PA instance
- Port – HTTPPortNumber found in the tm1s.cfg file of the PA instance
- User – The User ID that will be used to control changes to the data
- Password – The corresponding password for the User ID
- SSL – If SSL is used, set equal to True.
The TM1Service object should show as follows:
The object “tm1” will be referenced any time the program interacts with the PA instance.
The next step is to prepare your dataframe for a load to PA, which is covered in Part 2 of this series.