Time-Series Modeling and Analysis using SAP HANA Predictive Analysis Library(PAL) through Python Machine Learning Client for SAP HANA

In this blog post, we will show users how to perform time-series modeling and analysis using SAP HANA Predictive Analysis Library(PAL). Different from the original SQL interface, here we call PAL procedures through the Python machine learning client for SAP HANA(hana_ml). Python is often much more welcomed for today’s users that are most familier with Python, especially data analysts. Thanks to hana_ml, now by wrtting lines of Python code, we can call various PAL procedures with easy.

Besides Python, we also assume that readers of this blog post have some basic knowledge on time-series like trend and seasonality. If not, please refer to the Appendix section of this blog post for a quick comprehension.

The rest of this blog post is organized as follows: firstly we have a brief introduction to the time-series data of interest, with some basic analysis using run-sequence plot and seasonal decomposition, then the time-series data are further analyzed by exponential smoothing and ARIMA models, finally the main content of this blog post is summarized.

Introduction to the Dataset, with Basic Analysis

The data used for demonstration in this blog post is the “AirPassengers” dataset, which reflects the number of passengers(in thousands) transmitted monthly by an airline company between Jan. 1949 and Dec. 1960. This is a classical dataset that was introduced by Box and Jenkins in the reknowned textbook Time Series Analysis, Forecasting and Control(3rd edition). We assume that the dataset is stored in a local .csv file(AirPassengers.csv), and it can be loaded to the Python client using the pandas libary.

import pandas as pd
air_passengers = pd.read_csv(PATH_TO_LOCAL_CSV,#replacing the actual path the the local .csv file
                             parse_dates=[0])#the 0th column contains month info, if not specified, values in this columns shall be parse as strings

air_passengers

air_passengers.dtypes

Month          datetime64[ns]
#Passengers             int64
dtype: object

import matplotlib.pyplot as plt
plt.plot(air_passengers['Month'], air_passengers['#Passengers'])
plt.vlines(x=pd.to_datetime(['{}-01-01 00:00:00'.format(str(1949+i)) for i in range(12)]),
           ymin=100, ymax=620, colors='r', linestyles='dashed')
plt.show()

Every dashed red line in the above figure indicates the start of a new year. We can see from this figure that the number of airline passengers keeps growing year-by-year in general, forming an upward trend. Besides, in each year the monthly numbers have ups-and-downs in a similar fashion: low in winter season and high in summer season. Furthermore, the magnitude of local oscillations grows with the upward trend, which is a clear evidence for multiplicative seasonality.

import hana_ml
from hana_ml.dataframe import create_dataframe_from_pandas, ConnectionContext
cc = ConnectionContext('xxxxxxx.xxx.xxx.xxx', 30x15, 'XXXXXX', 'XXXxxx')#account info hidden away
ap_name = 'AIR_PASSENGERS_TBL'
ap_df = create_dataframe_from_pandas(cc, pandas_df = air_passengers,
                                     table_name = ap_name, force = True)

Now ‘AIR_PASSENGERS_TBL’ is a concrete table that holds the time-series data in SAP HANA, and ap_df is the corresponding hana_ml DataFrame that are associated with this table in the Python client.  The data stored in SAP HANA can also be collected to the Python client, illustrated as follows:

ap_df.collect()

144 rows × 2 columns

Column data types of the table can be fetched as follows:

ap_df.dtypes()

[('Month', 'TIMESTAMP', 27, 27, 27, 0), ('#Passengers', 'INT', 10, 10, 10, 0)]

Since the seasonal decomposition procedure in PAL requires the input time-series data to have a timestamp column of integer type, which is inconsistent with the given hana_ml DataFrame, so we need to add an additional integer columns for representing the order of the time-series data. This could be be realized calling the add_id() function of hana_ml DataFrame, illustrated as follows:

ap_df_wid = ap_df.add_id(id_col="ID", ref_col='Month') # generate the integer ID column based on the order of Month
ap_df_wid.collect()

from hana_ml.algorithms.pal.tsa.seasonal_decompose import seasonal_decompose
stats, decomposed = seasonal_decompose(data = ap_df_wid, key = 'ID',
                                       endog = '#Passengers')
stats.collect()

decompose_data = decomposed.collect()
import matplotlib.pyplot as plt
figure, axe = plt.subplots(3)
axe[0].plot(decompose_data.TREND, 'r-.')
axe[0].set_title("Trend Component")
axe[1].plot(decompose_data.SEASONAL, 'b-.')
axe[1].set_title("Seasonal Component")
axe[2].plot(decompose_data.RANDOM, 'k-.')
axe[2].set_title("Residual")
plt.show()

So the multiplicative resudals are all close to 1, implying that the multiplication of trend and seasonal components gives a time-series nearly the same as the original one, so seasonal decomposition can well explain the given time-series data.

Data Partition

Lastly in this section, we split the time-series into two parts, where the 1st part is from 1949 to 1959, used for model training; and the 2nd part is the data in 1960, used for testing. This step is for time-series modeling and forecasts in the subsequent sections.

ap_df_train = cc.sql("select * from ({}) where \"Month\" < '1960-01-01'".format(ap_df_wid.select_statement))
ap_df_test = cc.sql("select * from ({}) where \"Month\" >= '1960-01-01'".format(ap_df_wid.select_statement))

We may check the training part of this dataset as follows:

ap_df_train.collect()

from hana_ml.algorithms.pal.tsa.exponential_smoothing import AutoExponentialSmoothing
auto_eps = AutoExponentialSmoothing(model_selection = True,
                                    forecast_model_name = "TESM",
                                    seasonal='multiplicative',
                                    optimizer_time_budget=10,
                                    max_iter=500,
                                    forecast_num=12)

auto_eps.fit_predict(data = ap_df_train, key = "ID", endog = '#Passengers')

auto_eps.stats_.collect()

So auto exponential smoothing also finds out that the period of seasonality for the given time-series is 12(see the CYCLE value in the above stats table), which is consistent the result of seasonal decomposition.

The fitted & forecasted values are contained in the forecast_ attribute of auto_eps, and since we have assigned forecast_num the value of 12 when initializing the auto exponential smoothing class, the last 12 records in the forecast_ attribute should correspond to the forecasted values based on the trained triple exponential smoothing model. We can examine those values by collecting them to the Python client, illustrated as follows:

auto_eps.forecast_.select(["TIMESTAMP", "VALUE"]).collect().tail(12)

merged = ap_df_test.join(auto_eps.forecast_.select(['TIMESTAMP', 'VALUE']),
                         'TIMESTAMP = ID')
merged.collect()

from hana_ml.algorithms.pal.tsa.accuracy_measure import accuracy_measure
res = accuracy_measure(data = merged.select(['#Passengers', 'VALUE']),
                       evaluation_metric = ['rmse', 'mape', 'mad'])
res.collect()

from hana_ml.algorithms.pal.tsa.auto_arima import AutoARIMA
auto_arima = AutoARIMA()
auto_arima.fit(data = ap_df_train, key = 'ID',
               endog = '#Passengers')

auto_arima.model_.collect()

forecast_res = auto_arima.predict(forecast_method='formula_forecast',
                                  forecast_length=12)
fc_res_reduced = cc.sql('SELECT TIMESTAMP, FORECAST from ({})'.format(forecast_res.select_statement))
arima_merged = ap_df_test.join(fc_res_reduced,
                               'TIMESTAMP + {} = ID'.format(ap_df_test.collect()['ID'][0]))
arima_merged.collect()

from hana_ml.algorithms.pal.tsa.accuracy_measure import accuracy_measure
acc_measures = accuracy_measure(data = arima_merged.select(['#Passengers', 'FORECAST']),
                                evaluation_metric = ['rmse', 'mape', 'mad'])
acc_measures.collect()

ap_df_train_log = cc.sql('SELECT "ID", "Month", LN("#Passengers") FROM ({})'.format(ap_df_train.select_statement)) # LN is logarithm transformation
ap_df_train_log.collect()

from hana_ml.algorithms.pal.tsa.seasonal_decompose import seasonal_decompose
stats, _ = seasonal_decompose(data = ap_df_train_log,
                              key = 'ID',
                              endog = 'LN(#Passengers)')
stats.collect()

auto_arima.fit(data = ap_df_train_log, key = 'ID', endog = 'LN(#Passengers)')
auto_arima.model_.collect()

forecast_res2 = auto_arima.predict(forecast_method='formula_forecast',
                                   forecast_length=12)
fc_res2_reduced = cc.sql('select TIMESTAMP, EXP(FORECAST) AS FORECAST from ({})'.format(forecast_res2.select_statement))
arima_merged2 = ap_df_test.join(fc_res2_reduced,
                              'TIMESTAMP + {} = ID'.format(ap_df_test.collect()['ID'][0]))
arima_merged2.collect()

acc_measures2 = accuracy_measure(data = arima_merged2.select(['#Passengers', 'FORECAST']), 
                                 evaluation_metric = ['rmse', 'mape', 'mad'])
acc_measures2.collect()