Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
marc_daniau
Advisor
Advisor
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
1 Comment