In our case the ECN is named ecn1. It has been provisioned via HANA Cloud Central. For information on ECN provisioning see https://community.sap.com/t5/technology-blog-posts-by-sap/harnessing-dynamic-elasticity-elastic-compute-node-for-smarter-scaling-in/ba-p/14016836

To allow the execution of APL functions by the ECN, we configure as admin user a workload class using this line of SQL:

create WORKLOAD CLASS WC4 SET ‘ROUTING LOCATION HINT’ = ‘ecn1’;

This is just one way among many to create a workload class in HANA Cloud.

Working with the APL Python interface

As admin user …

from hana_ml import dataframe as hd
conn = hd.ConnectionContext(
address = ‘Host_String’, port = 443,
user = ‘DBADMIN’, password = ‘Password_String’,
encrypt = ‘true’, sslValidateCertificate = ‘false’ )
conn.connection.isconnected()

… it is possible to check if the ECN is active with this query:

sql_cmd = ‘select volume_id, host, port, service_name FROM M_VOLUMES order by 1’
hd.DataFrame(conn, sql_cmd).collect()

Before calling the APL function, we clear the SQL cache:

import pandas as pd
from hdbcli import dbapi
def clear_sql_cache():
with conn.connection.cursor() as cur:
cur.execute(“ALTER SYSTEM CLEAR SQL PLAN CACHE”)clear_sql_cache()

Let’s jump now to a second notebook where we run, as APL user this time, a forecasting task:

from hana_ml import dataframe as hd
conn = hd.ConnectionContext(
address = ‘Host_String’, port = 443,
user = ‘USER_APL’, password = ‘Password_String’,
encrypt = ‘true’, sslValidateCertificate = ‘false’ )
conn.connection.isconnected()

We define a dataframe for the input series …

series_in = conn.table(‘OZONE_RATE_LA’, schema=’APL_SAMPLES’)

… and run the forecast after specyfying the workload class WC4 for the routing to happen:

from hana_ml.algorithms.apl.time_series import AutoTimeSeries
apl_model = AutoTimeSeries(time_column_name= ‘Date’, target= ‘OzoneRateLA’, horizon= 12)
apl_model.set_scale_out(workload_class=”WC4″)
## apl_model.set_scale_out(route_to=1025) # Alternative option
series_out = apl_model.fit_predict(data = series_in, build_report=True)

Last, we go back to our first notebook, and verify that the APL functions ran indeed on the ECN:

Working with the APL SQL interface

As APL user, to ensure that the forecast is executed by the ECN, we put the APL script inside a stored procedure, say MDA_APL_FORECAST, and then we call that procedure using a hint as follows:

call MDA_APL_FORECAST with HINT(WORKLOAD_CLASS(WC4));

As admin user, we check that the routing worked with the code below:

select HOST, VOLUME_ID, APPLICATION_NAME, STATEMENT_STRING, LAST_EXECUTION_TIMESTAMP
from M_SQL_PLAN_CACHE
where USER_NAME= ‘USER_APL’ and LAST_EXECUTION_TIMESTAMP is not null
order by LAST_EXECUTION_TIMESTAMP;

Here is the sample code we used to create the stored procedure:

create type FORECAST_OUT_T as table (
“Date” DATE,
“OzoneRateLA” 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 FORECAST_OUT like FORECAST_OUT_T;

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 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”;

create procedure “MDA_APL_FORECAST”
as BEGIN
declare out_forecast FORECAST_OUT_T;
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 Ozone Rate’));

:config.insert((‘APL/Horizon’, ’12’,null));
:config.insert((‘APL/TimePointColumnName’, ‘Date’,null));
:config.insert((‘APL/LastTrainingTimePoint’, ‘1971-12-28 00:00:00’,null));
:config.insert((‘APL/ForcePositiveForecast’, ‘true’,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((‘Date’, ‘input’, null, null, null));
:var_role.insert((‘OzoneRateLA’, ‘target’, null, null, null));

dataset = select * from APL_SAMPLES.OZONE_RATE_LA order by “Date” asc;

“_SYS_AFL”.”APL_FORECAST__OVERLOAD_5_6″ (
:header, :config, :var_desc, :var_role, :dataset,
out_forecast, apl_log, apl_sum, apl_indic, apl_metr, apl_prop );

Note that for this basic sample we stored the full debrief tables. However, if your predictive use case involves a segmented APL model with many segments, it is preferable to extract only the information needed by the end-users, so that the amount of output data is reduced. Here is an example on how to obtain a couple of accuracy indicators by segment:

insert into “USER_APL”.”MDA_FORECAST_ACCURACY”
select “Oid” as “Segment”, “MAE”, “MAPE”
from “SAP_PA_APL”.”sap.pa.apl.debrief.report::TimeSeries_Performance”
(:apl_prop, :apl_metr)
where “Partition” = ‘Validation’;

To know more about APL

In this blog you will see how APL (Automated Predictive Library) addresses compute-intensive peak workloads by leveraging ECNs (Elastic Compute Nodes). The following example will walk you through the steps required to route an APL task to an ECN running on HANA Cloud.In our case the ECN is named ecn1. It has been provisioned via HANA Cloud Central. For information on ECN provisioning see https://community.sap.com/t5/technology-blog-posts-by-sap/harnessing-dynamic-elasticity-elastic-compute-node-for-smarter-scaling-in/ba-p/14016836To allow the execution of APL functions by the ECN, we configure as admin user a workload class using this line of SQL:create WORKLOAD CLASS WC4 SET ‘ROUTING LOCATION HINT’ = ‘ecn1’;This is just one way among many to create a workload class in HANA Cloud. Working with the APL Python interface As admin user …from hana_ml import dataframe as hd
conn = hd.ConnectionContext(
address = ‘Host_String’, port = 443,
user = ‘DBADMIN’, password = ‘Password_String’,
encrypt = ‘true’, sslValidateCertificate = ‘false’ )
conn.connection.isconnected()… it is possible to check if the ECN is active with this query:sql_cmd = ‘select volume_id, host, port, service_name FROM M_VOLUMES order by 1’
hd.DataFrame(conn, sql_cmd).collect()  Before calling the APL function, we clear the SQL cache:import pandas as pd
from hdbcli import dbapi
def clear_sql_cache():
with conn.connection.cursor() as cur:
cur.execute(“ALTER SYSTEM CLEAR SQL PLAN CACHE”)clear_sql_cache()Let’s jump now to a second notebook where we run, as APL user this time, a forecasting task:from hana_ml import dataframe as hd
conn = hd.ConnectionContext(
address = ‘Host_String’, port = 443,
user = ‘USER_APL’, password = ‘Password_String’,
encrypt = ‘true’, sslValidateCertificate = ‘false’ )
conn.connection.isconnected()We define a dataframe for the input series …series_in = conn.table(‘OZONE_RATE_LA’, schema=’APL_SAMPLES’)… and run the forecast after specyfying the workload class WC4 for the routing to happen:from hana_ml.algorithms.apl.time_series import AutoTimeSeries
apl_model = AutoTimeSeries(time_column_name= ‘Date’, target= ‘OzoneRateLA’, horizon= 12)
apl_model.set_scale_out(workload_class=”WC4″)
## apl_model.set_scale_out(route_to=1025) # Alternative option
series_out = apl_model.fit_predict(data = series_in, build_report=True)Last, we go back to our first notebook, and verify that the APL functions ran indeed on the ECN:      Working with the APL SQL interface As APL user, to ensure that the forecast is executed by the ECN, we put the APL script inside a stored procedure, say MDA_APL_FORECAST, and then we call that procedure using a hint as follows:call MDA_APL_FORECAST with HINT(WORKLOAD_CLASS(WC4));As admin user, we check that the routing worked with the code below:select HOST, VOLUME_ID, APPLICATION_NAME, STATEMENT_STRING, LAST_EXECUTION_TIMESTAMP
from M_SQL_PLAN_CACHE
where USER_NAME= ‘USER_APL’ and LAST_EXECUTION_TIMESTAMP is not null
order by LAST_EXECUTION_TIMESTAMP;    Here is the sample code we used to create the stored procedure:create type FORECAST_OUT_T as table (
“Date” DATE,
“OzoneRateLA” 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 FORECAST_OUT like FORECAST_OUT_T;

:header.insert((‘Oid’, ‘Monthly Ozone Rate’));

:var_role.insert((‘Date’, ‘input’, null, null, null));
:var_role.insert((‘OzoneRateLA’, ‘target’, null, null, null));

dataset = select * from APL_SAMPLES.OZONE_RATE_LA order by “Date” asc;

“_SYS_AFL”.”APL_FORECAST__OVERLOAD_5_6″ (
:header, :config, :var_desc, :var_role, :dataset,
out_forecast, apl_log, apl_sum, apl_indic, apl_metr, apl_prop );

insert into FORECAST_OUT select * from :out_forecast;
insert into OP_LOG select * from :apl_log;
insert into SUMMARY select * from :apl_sum;
insert into DEBRIEF_METRIC select * from :apl_metr;
insert into DEBRIEF_PROPERTY select * from :apl_prop;
END;Note that for this basic sample we stored the full debrief tables. However, if your predictive use case involves a segmented APL model with many segments, it is preferable to extract only the information needed by the end-users, so that the amount of output data is reduced. Here is an example on how to obtain a couple of accuracy indicators by segment: insert into “USER_APL”.”MDA_FORECAST_ACCURACY”
select “Oid” as “Segment”, “MAE”, “MAPE”
from “SAP_PA_APL”.”sap.pa.apl.debrief.report::TimeSeries_Performance”
(:apl_prop, :apl_metr)
where “Partition” = ‘Validation’; To know more about APL Read More Technology Blog Posts by SAP articles

#SAP

#SAPTechnologyblog