Piecewise Linear Trend with Automated Time Series Forecasting (APL)

In version 2113 the Automated Predictive Library introduces an additional fitting method called Piecewise Linear that can detect breakpoints in your series. You don’t have to do anything new to take advantage of this functionality, the trend is detected automatically as shown in the example below.

This article presents two ways of using APL: i) Python notebook, ii) SQL script.

Let’s start with Python. First, we define a HANA dataframe on top of a monthly series.

from hana_ml import dataframe as hd
conn = hd.ConnectionContext(userkey='MLMDA_KEY')
series_in = conn.table('SALES', schema='APL_SAMPLES')

We preview the data by putting a few rows in a Pandas dataframe.

series_in.tail(6).collect()

We ask APL to extrapolate three months ahead:

from hana_ml.algorithms.apl.time_series import AutoTimeSeries
apl_model = AutoTimeSeries(time_column_name= 'Month', target= 'Amount', horizon= 3)
series_out = apl_model.fit_predict(data = series_in, build_report=True)

The output shows a series extended with three more rows:

df = series_out.select(series_out.columns[0:5]).collect()
dict = {'ACTUAL': 'Actual', 
        'PREDICTED_1': 'Forecast', 
        'LOWER_INT_95PCT': 'Lower Limit', 
        'UPPER_INT_95PCT': 'Upper Limit' }
df.rename(columns=dict, inplace=True)
df.tail(6)

Since HANA ML 2.16 you can generate a report to see in a bar chart the components found by APL.

apl_model.generate_notebook_iframe_report()

The breakdown view shows two breakpoints (dotted vertical line).

We are done with our Python notebook. We said we will run the same example using SQL. Here is the sample SQL code to build the forecasting model and query some of the output tables.

-- Input Series sorted over time
create view TS_SORTED as select * from APL_SAMPLES.SALES order by "Month" asc;

--- Output Tables
create  table FORECAST_OUT (
	"Month" 	DATE,
	"Amount" DOUBLE,
	"kts_1" DOUBLE,
	"kts_1Trend" DOUBLE,
	"kts_1Cycles" DOUBLE,
    "kts_1_lowerlimit_95%" DOUBLE,
    "kts_1_upperlimit_95%" DOUBLE,
	"kts_1ExtraPreds" DOUBLE,
	"kts_1Fluctuations" DOUBLE,
	"kts_1Residues" DOUBLE
);

create table OP_LOG like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_LOG";
create table SUMMARY like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.SUMMARY";
create table INDICATORS like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.INDICATORS";
create table DEBRIEF_METRIC like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.DEBRIEF_METRIC_OID";
create table DEBRIEF_PROPERTY like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.DEBRIEF_PROPERTY_OID";

DO BEGIN
    declare header "SAP_PA_APL"."sap.pa.apl.base::BASE.T.FUNCTION_HEADER";
    declare config "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_CONFIG_DETAILED";
	declare var_desc "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_DESC_OID";   
    declare var_role "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_ROLES_WITH_COMPOSITES_OID";    
    declare apl_log "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_LOG";      
    declare apl_sum "SAP_PA_APL"."sap.pa.apl.base::BASE.T.SUMMARY";   
    declare apl_indic "SAP_PA_APL"."sap.pa.apl.base::BASE.T.INDICATORS";   	
    declare apl_metr "SAP_PA_APL"."sap.pa.apl.base::BASE.T.DEBRIEF_METRIC_OID";
    declare apl_prop "SAP_PA_APL"."sap.pa.apl.base::BASE.T.DEBRIEF_PROPERTY_OID";      

    :header.insert(('Oid', 'Monthly Sales'));

    :config.insert(('APL/Horizon', '3',null));
    :config.insert(('APL/TimePointColumnName', 'Month',null));
    :config.insert(('APL/LastTrainingTimePoint', '2018-08-01 00:00:00',null));
    :config.insert(('APL/DecomposeInfluencers', 'true',null));
    :config.insert(('APL/ApplyExtraMode', 'First Forecast with Stable Components and Residues and Error Bars',null));
	
    :var_role.insert(('Month', 'input', null, null, null));
    :var_role.insert(('Amount', 'target', null, null, null));
		
    "SAP_PA_APL"."sap.pa.apl.base::FORECAST_AND_DEBRIEF"(
	:header, :config, :var_desc, :var_role, 
	'USER_APL','TS_SORTED', 
	'USER_APL', 'FORECAST_OUT', apl_log, apl_sum, apl_indic, apl_metr, apl_prop);

	insert into  OP_LOG     select * from :apl_log;
	insert into  SUMMARY    select * from :apl_sum;
	insert into  INDICATORS    select * from :apl_indic;
	insert into  DEBRIEF_METRIC    select * from :apl_metr;
	insert into  DEBRIEF_PROPERTY  select * from :apl_prop;
END;

create view DECOMPOSED_SERIES 
("Time","Actual","Forecast","Trend","Cycles","Lower_Limit","Upper_Limit",
 "Influencers","Fluctuations","Residuals") as
SELECT * FROM FORECAST_OUT ORDER BY 1;

SELECT "Time",
 round("Actual",2) as "Actual",
 round("Forecast",2) as "Forecast",
 round("Trend",2) as "Trend",
 round("Cycles",2) as "Cycles",
 round("Influencers",2) as "Influencers",
 round("Fluctuations",2) as "AR",
 round("Residuals",2) as "Residuals"
FROM DECOMPOSED_SERIES ORDER BY 1;
 
select * from "SAP_PA_APL"."sap.pa.apl.debrief.report::TimeSeries_ModelOverview"
(USER_APL.DEBRIEF_PROPERTY, USER_APL.DEBRIEF_METRIC);

select * from "SAP_PA_APL"."sap.pa.apl.debrief.report::TimeSeries_Performance"
(USER_APL.DEBRIEF_PROPERTY, USER_APL.DEBRIEF_METRIC)
where "Partition" = 'Validation';

select * from "SAP_PA_APL"."sap.pa.apl.debrief.report::TimeSeries_Components"
(USER_APL.DEBRIEF_PROPERTY, USER_APL.DEBRIEF_METRIC);

select * from "SAP_PA_APL"."sap.pa.apl.debrief.report::TimeSeries_Decomposition"
(USER_APL.DEBRIEF_PROPERTY, USER_APL.DEBRIEF_METRIC)
order by 1, 2;

To know more about APL