__Time Series Modeling Process__

The goal of time series modeling is to find the internal structure within a sequence of data points in time order. When that structure is known, the temporal measurement can be extrapolated in the forecast horizon. To build a time series model, SAP Predictive Analytics decomposes the signal (observed series) into additive components, as follows:

Signal = Trend + Cycles + Fluctuation + Residual

The tool evaluates automatically various combinations of trends, cycles and fluctuations. Eventually it selects the combination that gives the best forecast accuracy. The overall process is represented in the following diagram:

The user can customize the process by disabling some of the components if needed. By default, all the components are enabled.

After a model has been generated, the winning components are displayed in the debrief:

This model works with monthly ozone rates data. It detected a linear trend and an auto-regressive component of 37 months. No cycles were found. The user can view the signal (the green line on the chart) and the trend component (the blue line), as follows:

He can also display the detrended signal (in green below) and the auto-regressive component (in blue):

As part of the modeling process, the tool derives attributes automatically from the date-time field.

Here is the list of these derived attributes: secondOfMinute, minuteOfHour, hourOfDay, dayOfWeek, dayOfMonth, dayOfYear, weekOfMonth, weekOfYear, halfMonthOfYear and monthOfYear.

In the following model, built against a sub-hourly dataset, a cycle was detected that uses the minuteOfHour derived attribute:

Let’s look at another example with a monthly series. The following model captured a lag 1 trend, a cycle that uses the monthOfYear derived attribute, and an auto-regressive component of 44 points:

A time series model can detect multiple cycles. The following model captured a lag 2 trend and two cycles based on the dayOfWeek and the dayOfYear derived attributes:

__Extra Predictors__

In the diagram given at the beginning, you may have noticed the term, “extra-predictable”. What does that mean? The term refers to variables that the user can provide to improve the accuracy of the forecast. They are in fact candidate predictors that come in addition to the date-time field and its derived attributes. In the case of a daily series the user may be able to get data from external sources in order to build columns such as: Is National Holiday (Yes/No), Is School Holiday (Yes/No), Is Sporting Event (Yes/No), Weather Temperature, and Weather Rain. Or else, the user can perform calendar-based feature engineering.

Let’s look at an example where we prepared extra predictors on a daily series. Seven columns, one per weekday, were added. Each column is a continuous type and contains the number of days in the month regarding a given weekday. The debrief of the model shows a cycle based on the total days in the month for weekday 2, as follows:

Let’s look at a second example of extra predictors, still on a daily series. Using Data Manager, we calculated two flags of nominal type: In First 4 days of Month and, In Last 4 days of Month, as well as seven ranks of ordinal type: Rank Day Of Week 1, Rank Day Of Week 2, … , [up to] Rank Day Of Week 7.

The following is a data preview of the features built on the time dimension table:

The model debrief says that the feature Rank Day Of Week 1 allowed the detection of a cycle pattern in the data, as follows:

Note that the cycle component handles extra-predictors of ordinal type and continuous, not nominal. The trend component handles extra-predictors of ordinal type, continuous and nominal.

Note also that the data for the candidate predictors must be provided both for the past and future periods of the time series. So, for example, if you want to feed the model with weather information, it must include the weather forecast data.

__Forecast Accuracy__

The accuracy of an automated time series model in SAP Predictive Analytics is based upon the Mean Average Percentage Error (MAPE), as shown here:

In the model debrief, the user sees a global indicator that tells how well the model performs. This is called the Horizon Wide MAPE:

The statistical reports menu gives the details behind this performance indicator. In the example below that has a forecast horizon of 6 periods, we see 6 MAPE values calculated on the Validation dataset. The Horizon Wide MAPE is simply the average of these MAPE values.

The time series dataset was split into two partitions automatically. The first partition, called Estimation, contains 75% of the data. The second partition, called Validation, contains the other 25%; it has the most recent data points. This partitioning scheme corresponds to the default configuration of a time series model. Here is the default data split for the ozone rates data.

Note that a third partition can be added; in that case the split becomes: 60% for Estimation, 20% for Validation and 20% for Test.

To go beyond the MAPE indicator, a residual analysis can be done to check if the model managed to find all the patterns in the data. We will perform a residual analysis in the use case described in the next section.

__Use Case: Milk Production Forecast__

The SAP HANA Automated Predictive Library (APL) allows a data scientist or a SQL developer to invoke the automated time series modeling engine directly from a SQL script. This section illustrates how, within HANA Studio, a data scientist can use the Forecast function of APL.

To begin, we look at the data in a line chart, a 14-year history of monthly milk production:

We generate multiple lagged series from the original series to see if there is auto-correlation in the data:

```
create view TS_LAGGED as
With
S2 as (
select 1 as id from dummy union
select 2 from dummy
),
LAGS as (
select row_number() over() as LAG
from S2, S2, S2, S2, S2, S2
)
Select
LAG,
A.MONTH_START as THE_DATE,
A.MILK_QTY as Y,
L.MILK_QTY as Y_LAGGED
From
APL_SAMPLES.MILK_PROD A, APL_SAMPLES.MILK_PROD L, LAGS
Where A.MONTH_START = add_months(L.MONTH_START, -LAG)
order by
1 asc;
```

We check for patterns visually at different lags, using a lag plot. In the screen below we selected the lag 12. There is clearly a pattern.

We compute the correlation coefficient, for each lag, to get a summary view.

```
select
LAG,
round(corr(Y, Y_LAGGED),3) as PEARSON_COEFFICIENT
from
TS_LAGGED
where
lag <= 36
group by
LAG
order by
1 asc;
```

The Pearson coefficient is a standard statistic that ranges from -1 to 1. A value of 1 means that there is a perfect positive correlation. A value of -1 means that there is a perfect negative correlation. Zero means that there is no correlation. In the case of the milk quantity we observe strong positive correlation at different lags.

Now that we are more familiar with the dataset, we move to time series modeling.

We create a SQL view where we transform the monthly quantity into an average quantity per day to remove the variation due to the fact that months don’t have the same number of days, and sort the series over time, as follows:

```
create view TS_SORTED as
select
MONTH_START as THE_DATE,
to_double( MILK_QTY / DAYOFMONTH(LAST_DAY(MONTH_START))) as AVERAGE_PER_DAY
from
APL_SAMPLES.MILK_PROD
order by
1 asc;
```

We create the table type that describes the structure of the forecast output:

```
create type FORECAST_OUT_T as table (
THE_DATE DATE,
AVERAGE_PER_DAY DOUBLE,
"kts_1" DOUBLE
);
```

We create the requested tables for the function to work, and ask for a 12-month forecast horizon:

```
create table FUNC_HEADER like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.FUNCTION_HEADER";
insert into FUNC_HEADER values ('Oid', 'MLK_PRD');
insert into FUNC_HEADER values ('LogLevel', '8');
create table FORECAST_CONFIG like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_CONFIG_DETAILED";
insert into FORECAST_CONFIG values ('APL/Horizon', '12', null);
insert into FORECAST_CONFIG values ('APL/TimePointColumnName', 'THE_DATE', null);
create table VARIABLE_DESC like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_DESC_OID";
create table VARIABLE_ROLES like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_ROLES_WITH_COMPOSITES_OID";
insert into VARIABLE_ROLES values ('THE_DATE', 'input', NULL, NULL, 'MLK_PRD');
insert into VARIABLE_ROLES values ('AVERAGE_PER_DAY','target', NULL, NULL, 'MLK_PRD');
create table FORECAST_OUT like FORECAST_OUT_T;
create table OPERATION_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";
```

We call the APL Forecast function:

```
call "SAP_PA_APL"."sap.pa.apl.base::FORECAST"(
FUNC_HEADER, FORECAST_CONFIG, VARIABLE_DESC, VARIABLE_ROLES, -- Inputs
'USER_APL','TS_SORTED', -- Business Data
'USER_APL','FORECAST_OUT', -- Forecasted Values
OPERATION_LOG, SUMMARY, INDICATORS -- Outputs
) with overview;
```

We query the output tables to obtain a debrief of the trained model:

```
select ltrim(ltrim(key,'Model'),'Time') as Item, value
from SUMMARY
where KEY like 'ModelTimeSeries%' or KEY like 'ModelRecord%'
UNION
select KEY , cast(VALUE as VARCHAR)
from INDICATORS
where KEY in ('Trend','Cycles','Fluctuations');
```

To assess if the model did a good job at capturing all the patterns in the time series data, we will run a few tests on the residuals.

First, we compute the mean of the residuals series, with the expected value being zero:

```
drop view ts_residuals;
create view ts_residuals as
select
THE_DATE,
AVERAGE_PER_DAY as OBSERVED_VALUE,
"kts_1" as MODEL_FORECAST, AVERAGE_PER_DAY - "kts_1" as MODEL_RESIDUALS
from
FORECAST_OUT
where
AVERAGE_PER_DAY is not null and "kts_1" is not null
order by
1 asc;
select
avg(MODEL_RESIDUALS) as "Mean value of residuals"
from
ts_residuals;
```

Then we generate multiple lagged series from the residuals series:

```
drop view TS_LAGGED;
create view TS_LAGGED as
With
S2 as (
select 1 as id from dummy union
select 2 from dummy
),
LAGS as (
select row_number() over() as LAG
from S2, S2, S2, S2, S2, S2
)
select
LAG,
A.THE_DATE ,
A.MODEL_RESIDUALS as Y,
L.MODEL_RESIDUALS as Y_LAGGED
from
ts_residuals A, ts_residuals L, LAGS
where A.THE_DATE = add_months(L.THE_DATE, LAG)
order by
1, 2 asc;
```

And we compute the correlation coefficient, for each lag, with the same SQL we used earlier:

It seems there is no auto-correlation left in the residuals.

Finally, we check that the residuals look like white noise, as expected in case of a good forecasting model. For that, we will use the HANA PAL function: White Noise Test. This PAL function performs a Ljung-Box test. If the series is white noise, the test returns a value of 1. If not, a value of 0 is returned.

We must put the residuals data in the format requested by the PAL function:

```
CREATE COLUMN TABLE PAL_WHITENOISETEST_DATA_TBL LIKE PAL_WHITENOISETEST_DATA_T;
INSERT INTO PAL_WHITENOISETEST_DATA_TBL
select
to_integer(to_char(the_date,'YYYYMMDD')), MODEL_RESIDUALS
from
USER_APL.ts_residuals
;
```

We want to run the Ljung-Box test from lag 1 up to lag 24, with probability set to 95%:

```
CREATE COLUMN TABLE PAL_CONTROL_TBL(
"NAME" VARCHAR (50),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100)
);
INSERT INTO PAL_CONTROL_TBL VALUES ('LAG', 24, NULL, NULL);
INSERT INTO PAL_CONTROL_TBL VALUES ('THREAD_NUMBER', 8, NULL, NULL);
INSERT INTO PAL_CONTROL_TBL VALUES ('PROBABILITY', NULL, 0.95, NULL);
```

We call the PAL function:

```
CREATE COLUMN TABLE PAL_WHITENOISETEST_RESULT_TBL LIKE PAL_WHITENOISETEST_RESULT_T;
CALL USER_APL.PAL_WHITENOISETEST_PROC(
PAL_WHITENOISETEST_DATA_TBL,
PAL_CONTROL_TBL, PAL_WHITENOISETEST_RESULT_TBL
) WITH OVERVIEW;
```

We display the result of the test:

```
select
Case "VALUE"
When 1 Then 'Yes' When 0 Then 'No' Else Null
End as "Is White Noise ?"
from
PAL_WHITENOISETEST_RESULT_TBL;
```

To use this PAL function, don’t forget to create first the required table types and its stored procedure, as we did with the following script:

```
CREATE TYPE PAL_WHITENOISETEST_DATA_T AS TABLE(
"IDCOL" INTEGER,
"DATACOL" DOUBLE
);
CREATE TYPE PAL_CONTROL_T AS TABLE(
"NAME" VARCHAR (50),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100)
);
CREATE TYPE PAL_WHITENOISETEST_RESULT_T AS TABLE(
"NAME" VARCHAR (100),
"VALUE" INT
);
CREATE COLUMN TABLE PAL_WHITENOISETEST_SIGNATURE(
"POSITION" INT, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7)
);
INSERT INTO PAL_WHITENOISETEST_SIGNATURE VALUES (1,'USER_APL', 'PAL_WHITENOISETEST_DATA_T','IN');
INSERT INTO PAL_WHITENOISETEST_SIGNATURE VALUES (2,'USER_APL', 'PAL_CONTROL_T','IN');
INSERT INTO PAL_WHITENOISETEST_SIGNATURE VALUES (3,'USER_APL', 'PAL_WHITENOISETEST_RESULT_T','OUT');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE(
'AFLPAL', 'WHITENOISETEST', 'USER_APL', 'PAL_WHITENOISETEST_PROC',PAL_WHITENOISETEST_SIGNATURE);
```

We are finished with our residual analysis tests. We know we have a good forecasting model. Let’s produce the forecasted values.

We transform the forecasted average quantity per day into a monthly quantity:

```
select
F.THE_DATE, A.MILK_QTY,
round(F."kts_1" * DAYOFMONTH(LAST_DAY(F.THE_DATE)),2) as FORECAST_QTY
from
USER_APL.FORECAST_OUT F left outer join APL_SAMPLES.MILK_PROD A
on
F.THE_DATE = A.MONTH_START
order by
1 asc;
```

We show below the results of this query; the last 12 rows, corresponding to the 12-month forecast, were automatically generated by APL:

Really interesting content! Thanks for taking the time to write this down!

I concur – great stuff Marc!

This is very helpful. Great content and well explained!