<jupyter_start><jupyter_text>Lag 1 And Cycles with SAP HANA APL Forecasting  **Initialize the connection**<jupyter_code>import sqlalchemy, os
from sqlalchemy import create_engine

import pandas as pd
import matplotlib
import matplotlib.pyplot as plt

%matplotlib inline

%reload_ext sql
%config SqlMagic.displaylimit = 5
%config SqlMagic.feedback = False
%config SqlMagic.autopandas = True

hxe_connection = 'hana://ML_USER:Welcome18@hxehost:39015';

%sql $hxe_connection

pd.options.display.max_rows = 1000
pd.options.display.max_colwidth = 1000<jupyter_output><empty_output><jupyter_text>**Cleanup Tables and Views**<jupyter_code>%%sql
-- --------------------------------------------------------------------------
-- Cleanup SAPL objects
-- --------------------------------------------------------------------------
call sap_pa_apl."sap.pa.apl.base::CLEANUP"(1,?);
-- --------------------------------------------------------------------------
-- Drop function in/out tables, helper tables and views
-- --------------------------------------------------------------------------
drop table apl_l1c_function_header;
drop table apl_l1c_operation_config;
drop table apl_l1c_variable_desc;
drop table apl_l1c_variable_roles;
drop table apl_l1c_operation_log;
drop table apl_l1c_summary;
drop table apl_l1c_indicators;
drop table apl_l1c_result;<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Create Tables and Views**<jupyter_code>%%sql
-- --------------------------------------------------------------------------
-- Create generic tables
-- --------------------------------------------------------------------------
create column table apl_l1c_function_header   like sap_pa_apl."sap.pa.apl.base::BASE.T.FUNCTION_HEADER";
create column table apl_l1c_operation_config  like sap_pa_apl."sap.pa.apl.base::BASE.T.OPERATION_CONFIG_DETAILED";
create column table apl_l1c_variable_desc     like sap_pa_apl."sap.pa.apl.base::BASE.T.VARIABLE_DESC_OID";
create column table apl_l1c_variable_roles    like sap_pa_apl."sap.pa.apl.base::BASE.T.VARIABLE_ROLES_WITH_COMPOSITES_OID";
create column table apl_l1c_operation_log     like sap_pa_apl."sap.pa.apl.base::BASE.T.OPERATION_LOG";
create column table apl_l1c_summary           like sap_pa_apl."sap.pa.apl.base::BASE.T.SUMMARY";
create column table apl_l1c_indicators        like sap_pa_apl."sap.pa.apl.base::BASE.T.INDICATORS";
-- --------------------------------------------------------------------------
-- Create result table
-- --------------------------------------------------------------------------
create column table apl_l1c_result (
   time    daydate
  ,signal  double
  ,kts_1   double
  ,"kts_1_lowerlimit_95%" double
  ,"kts_1_upperlimit_95%" double    
);<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Set the algorithm parameters**<jupyter_code>%%sql
-- --------------------------------------------------------------------------
-- Configuration
-- --------------------------------------------------------------------------
truncate table apl_l1c_function_header;
insert into apl_l1c_function_header values ('Oid', '#1');
insert into apl_l1c_function_header values ('LogLevel', '8');

truncate table apl_l1c_operation_config;
insert into apl_l1c_operation_config values ('APL/TimePointColumnName'   , 'TIME'                     , null);
insert into apl_l1c_operation_config values ('APL/ApplyExtraMode'        , 'Forecasts and Error Bars' , null); 
insert into apl_l1c_operation_config values ('APL/SmoothingCycleLength'  , '4' , null); 

truncate table apl_l1c_variable_desc;
insert into apl_l1c_variable_desc values (0, 'TIME'   , 'datetime' , 'continuous', 1, 1, null, null, null, null);
insert into apl_l1c_variable_desc values (1, 'SIGNAL' , 'number'   , 'continuous', 0, 0, null, null, null, null);

truncate table apl_l1c_variable_roles;
insert into apl_l1c_variable_roles values ('TIME'   , 'input' , NULL, NULL, '#1');
insert into apl_l1c_variable_roles values ('SIGNAL' , 'target', NULL, NULL, '#1');
result_last_date = %sql select max(time) as last_date  from forecast_lag_1_and_cycles where signal is not null;

last_date = result_last_date.last_date[0]

%sql insert into apl_l1c_operation_config values ('APL/LastTrainingTimePoint' , '$last_date', null);
%sql insert into apl_l1c_operation_config values ('APL/Horizon'               , 16, null);
%sql select * from apl_l1c_operation_config;<jupyter_output>* hana://ML_USER:***@hxehost:39015
 * hana://ML_USER:***@hxehost:39015
 * hana://ML_USER:***@hxehost:39015
 * hana://ML_USER:***@hxehost:39015<jupyter_text>**Run the algorithm**<jupyter_code>%%sql
-- --------------------------------------------------------------------------
-- Clean result tables
-- --------------------------------------------------------------------------
truncate table apl_l1c_result;
truncate table apl_l1c_operation_log;
truncate table apl_l1c_summary;
truncate table apl_l1c_indicators;
-- --------------------------------------------------------------------------
-- Execute the APL function to train the model with the minimal dataset
-- --------------------------------------------------------------------------
call sap_pa_apl."sap.pa.apl.base::FORECAST" (
    apl_l1c_function_header
  , apl_l1c_operation_config
  , apl_l1c_variable_desc
  , apl_l1c_variable_roles
  , current_schema, 'FORECAST_LAG_1_AND_CYCLES'
  , current_schema, 'APL_L1C_RESULT'
  , apl_l1c_operation_log
  , apl_l1c_summary
  , apl_l1c_indicators
) with overview;<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Check the output**<jupyter_code>%sql select * from apl_l1c_operation_log;
%sql select * from apl_l1c_operation_log where message like 'Model%' or message like 'The model %';
%sql select * from apl_l1c_operation_log where message like '%outlier%';
%sql select * from apl_l1c_summary;
%sql select * from apl_l1c_indicators;
%%sql
select oid, key, max(to_nvarchar(value)) as value
from apl_l1c_indicators
where key in ('Trend', 'Fluctuations', 'Cycles', 'MAPE')
group by oid, key
order by key;<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Check the results**<jupyter_code>%%sql
select top 100
      f.time
    , c.signal 
    , kts_1                  as forecast
    , "kts_1_lowerlimit_95%" as lower_limit
    , "kts_1_upperlimit_95%" as upper_limit
from 
     apl_l1c_result f
left outer join forecast_lag_1_and_cycles c
on   c.time = f.time
order by f.time desc;
forecast = _
time         = matplotlib.dates.date2num(forecast.time)

fig, ax = plt.subplots()
ax.plot(time, forecast.signal     , 'ro-', markersize=2, color='green')
ax.plot(time, forecast.forecast   , 'ro-', markersize=2, color='blue')
ax.plot(time, forecast.lower_limit, 'ro-', markersize=2, color='red')
ax.plot(time, forecast.upper_limit, 'ro-', markersize=2, color='red')
ax.xaxis_date()

fig.autofmt_xdate()
fig.set_size_inches(20, 20)
plt.show()<jupyter_output><empty_output><jupyter_text>**Run the algorithm with White Noise**<jupyter_code>%%sql
-- --------------------------------------------------------------------------
-- Clean result tables
-- --------------------------------------------------------------------------
truncate table apl_l1c_result;
truncate table apl_l1c_operation_log;
truncate table apl_l1c_summary;
truncate table apl_l1c_indicators;
-- --------------------------------------------------------------------------
-- Execute the APL function to train the model with the minimal dataset
-- --------------------------------------------------------------------------
call sap_pa_apl."sap.pa.apl.base::FORECAST" (
    apl_l1c_function_header
  , apl_l1c_operation_config
  , apl_l1c_variable_desc
  , apl_l1c_variable_roles
  , current_schema, 'FORECAST_LAG_1_AND_CYCLES_AND_WN'
  , current_schema, 'APL_L1C_RESULT'
  , apl_l1c_operation_log
  , apl_l1c_summary
  , apl_l1c_indicators
) with overview;<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Check the output**<jupyter_code>%sql select * from apl_l1c_operation_log;
%sql select * from apl_l1c_operation_log where message like 'Model%' or message like 'The model %';
%sql select * from apl_l1c_operation_log where message like '%outlier%';
%sql select * from apl_l1c_summary;
%sql select * from apl_l1c_indicators;
%%sql
select oid, key, max(to_nvarchar(value)) as value
from apl_l1c_indicators
where key in ('Trend', 'Fluctuations', 'Cycles', 'MAPE')
group by oid, key
order by key;<jupyter_output>* hana://ML_USER:***@hxehost:39015<jupyter_text>**Check the results**<jupyter_code>%%sql
select  top 100
      f.time
    , c.signal 
    , kts_1                  as forecast
    , "kts_1_lowerlimit_95%" as lower_limit
    , "kts_1_upperlimit_95%" as upper_limit
from 
     apl_l1c_result f
left outer join forecast_lag_1_and_cycles_and_wn c
on   c.time = f.time
order by f.time desc;
forecast = _
time         = matplotlib.dates.date2num(forecast.time)

fig, ax = plt.subplots()
ax.plot(time, forecast.signal     , 'ro-', markersize=2, color='green')
ax.plot(time, forecast.forecast   , 'ro-', markersize=2, color='blue')
ax.plot(time, forecast.lower_limit, 'ro-', markersize=2, color='red')
ax.plot(time, forecast.upper_limit, 'ro-', markersize=2, color='red')
ax.xaxis_date()

fig.autofmt_xdate()
fig.set_size_inches(20, 20)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.24311531841652323, 'fraction_numerical': 0.021729776247848536, 'mean_word_length': 2.614696734059098, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '363075', 'license': 'Apache-2.0', 'n_tokens_mistral': 3234, 'n_tokens_neox': 3104, 'n_words': 627, 'path': 'tutorials/hxe-aa-forecast-sql-04/hxe-aa-forecast-sql-04-Lag1AndCycles.ipynb', 'repo_name': 'maxta2002/Tutorials', 'size': '9296'}