TM1 vs Data Science - Forecast

Financial Performance Management (FPM) applications mostly have forecasting capabilities. In TM1 (Planning Analytics) Forecasting often is based on historical month’s actual data with some business logic. In a parallel universe, we have excessive amount of machine learning libraries in python which are focused on time series forecasting. These algorithms are extensively used by data scientist to train the model and predict forecast. Although the new Planning Analytics Workspace (PAW) version comes with inbuilt time series forecasting powered by SPSS engine, it is confined to specific libraries in SPSS. Nevertheless, this blog will focus on exposing forecast number from a live tm1 model to compare against a machine learning algorithm: ARIMA (Auto Regressive Integrated Moving Average) model. Also, it will list out the important points for a TM1 developer to consider when they are connecting their TM1 data with data science libraries. 

In the latest version of TM1py we have Single Sign On (SSO) enabled. So, we can save our config file and use that in our code. It just requires gateway details and doesn’t require username and password.

Config.ini file looks something like this:

We can have all our TM1 servers in this file based on the HTTP port number and reference them in our python code (JupyterLab). The below snip, we can see that the MDX is structured in a way; I have one dimension in the rows (month) and couple of measure elements in columns. This produces the data in tabular format, and I am using powerBI class of TM1py as its outputs pandas data frame using MDX. 

From the above step we have extracted data from TM1 model which will look as below and We need to drop the columns which are not required for example: Version, Productlist, source and currency.

Dropping unwanted columns can be completed as per below and we will have month and measure in a tabular format with three columns (Month, Measure 1 and Measure 2)

Once we have got our TM1 data in tabular format we need to complete three important steps before we can blend this data with data science library

1. DateTime format

2. DateTime frequency

3. convert measure column to numeric type

Usually, Tm1 period dimension will be months rolling to Quarter -> Half Year -> Year. In this example, leaf level is Month and we need to set the format in python before we can use the data with machine learning algorithm. However, based on the level of extract, appropriate datetime format and frequency should be considered. In pandas documentation we can check time series/ date functionality -> offset alias section to understand various options to set based on TM1 period dimension.

TM1 month format – 2019M01. As this is the leaf level, we are converting it to start date of the month format in python i.e 2019-01-01 and setting it as frequency (MS – Month Start). Convert measures to numeric type and plot measure 1 using matplotlib’s pyplot.

Time Series Forecasting using ARIMA

There is enough online content on ARIMA and how to use this algorithm for time series forecasting. First initializing the model requires the below code: where p,q,d is denoted for seasonality, trend and noise in the data.

Below code is required to find the lowest Akaike Information Criterion (AIC) using a grid search with TM1 data:

Based on the search, we need to fit the model as per below:

We could then compare TM1 Forecast vs Forecast prediction from the ARIMA model by stating the start of forecast month. In the below example, I have specified November 2020.

The below output shows TM1 forecast performs versus ARIMA forecast prediction.

And the model can be validated by finding the mean squared error (MSE). The lower the MSE, the closer it is to find the line of best fit.

The MSE above is higher which means the model may be overfit/underfit. There are multiple ways to make it best fit but training the model with uncorrelated data (just Actuals) which is out of scope for this article.

Therefore, we can overlay tm1 data with data science libraries to understand the forecast and if we are happy we can push this back to TM1 using Rest API as one of the scenarios (version) to compare our forecast data.

Credits: TM1Py, IBM RestAPI, Cubewise, Book: Introduction to Time series forecasting with python (Jason Browniee), Kaggle, KDNuggets, Ben Wilson (Senior Portfolio Management Specialist - Suncorp)