To use any APL functions with the direct technique, you must do the following:
- Generate the input table “types” required by the generic APL function with your specific data structures
- Call that AFL procedure, for example, from an SQLScript procedure
You can find more details in SAP HANA Automated Predictive Library (APL) Reference document at https://help.sap.com/pa.
Note:
Due to a recent change on the HANA MDC setup on trial, we had to review the code provided below and use the “hard-coded way” to use the APL. Once the setup is fixed, we will revert to the nicer and shorter version of the script.
Use the following code to generate the types and the model configuration:
/* Set the function header to prepare logging configuration */
DROP TYPE "TUTORIAL_PREDICTIVE"."T_FUNCTION_HEADER";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_FUNCTION_HEADER" AS TABLE (
"KEY" NVARCHAR(50),
"VALUE"NVARCHAR(50)
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_FUNCTION_HEADER";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_FUNCTION_HEADER" LIKE "TUTORIAL_PREDICTIVE"."T_FUNCTION_HEADER";
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_FUNCTION_HEADER" VALUES ( 'Oid','TIME_SERIES');
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_FUNCTION_HEADER" VALUES ( 'LogLevel','8');
/* Set the Time Series algorithm configuration*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_OPERATION_CONFIG";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_OPERATION_CONFIG" AS TABLE (
"KEY" NVARCHAR(1000),
"VALUE" NVARCHAR(300),
"CONTEXT" NVARCHAR(100)
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG" LIKE "TUTORIAL_PREDICTIVE"."T_OPERATION_CONFIG";
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG" VALUES ('APL/Horizon', '21', '');
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG" VALUES ('APL/TimePointColumnName', 'Date', '');
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG" VALUES ('APL/LastTrainingTimePoint', '2001-12-29 00:00:00', '');
/* We don't need to set the variable, so it will be guessed from the data*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_VARIABLE_DESC";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_VARIABLE_DESC" AS TABLE (
"RANK" Integer,
"NAME" NVARCHAR(127),
"STORAGE" NVARCHAR(10),
"VALUETYPE" NVARCHAR(10),
"KEYLEVEL" Integer,
"ORDERLEVEL" Integer,
"MISSINGSTRING" NVARCHAR(50),
"GROUPNAME" NVARCHAR(50),
"DESCRIPTION" NVARCHAR(100),
"OID" NVARCHAR(50)
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_VARIABLE_DESC";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_VARIABLE_DESC" LIKE "TUTORIAL_PREDICTIVE"."T_VARIABLE_DESC";
/* We only need to describe the date column and the signa we are trying to predict*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_VARIABLE_ROLES";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_VARIABLE_ROLES" AS TABLE (
"NAME" NVARCHAR(127),
"ROLE" NVARCHAR(10),
"COMPOSITION_TYPE" NVARCHAR(10),
"COMPONENT_NAME" NVARCHAR(127),
"OID" NVARCHAR(50)
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_VARIABLE_ROLES";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_VARIABLE_ROLES" LIKE "TUTORIAL_PREDICTIVE"."T_VARIABLE_ROLES";
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_VARIABLE_ROLES" VALUES ('Date', 'input',null,null,null);
INSERT INTO "TUTORIAL_PREDICTIVE"."APL_VARIABLE_ROLES" VALUES ('Cash', 'target',null,null,null);
/* Now, we prepare the input table type*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_APL_INPUT" ;
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_APL_INPUT" as table (
"Date" DAYDATE,
"WorkingDaysIndices" Integer,
"ReverseWorkingDaysIndices" Integer ,
"MondayMonthInd" Integer ,
"TuesdayMonthInd" Integer ,
"WednesdayMonthInd" Integer ,
"ThursdayMonthInd" Integer ,
"FridayMonthInd" Integer ,
"BeforeLastMonday" Integer ,
"LastMonday" Integer ,
"BeforeLastTuesday" Integer ,
"LastTuesday" Integer ,
"BeforeLastWednesday" Integer ,
"LastWednesday" Integer ,
"BeforeLastThursday" Integer ,
"LastThursday" Integer ,
"BeforeLastFriday" Integer ,
"LastFriday" Integer ,
"Last5WDaysInd" Integer ,
"Last5WDays" Integer ,
"Last4WDaysInd" Integer ,
"Last4WDays" Integer ,
"LastWMonth" Integer ,
"BeforeLastWMonth" Integer ,
"Cash" DOUBLE
);
/* Now, we prepare the output table*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_APL_MODEL_RESULT" ;
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_APL_MODEL_RESULT" as table (
"Date" DAYDATE,
"Cash" DOUBLE,
"kts_1" DOUBLE
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_MODEL_RESULT";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_MODEL_RESULT" LIKE "TUTORIAL_PREDICTIVE"."T_APL_MODEL_RESULT";
/* Now, we prepare the opertion log table*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_OPERATION_LOG";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_OPERATION_LOG" AS TABLE (
"OID" NVARCHAR(50),
"TIMESTAMP" TIMESTAMP,
"LEVEL" Integer,
"ORIGIN" NVARCHAR(50),
"MESSAGE" NCLOB
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_OPERATION_LOG";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_OPERATION_LOG" LIKE "TUTORIAL_PREDICTIVE"."T_OPERATION_LOG";
/* Now, we prepare the summary table*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_SUMMARY";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_SUMMARY" AS TABLE (
"OID" NVARCHAR(50),
"KEY" NVARCHAR(100),
"VALUE" NVARCHAR(200)
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_SUMMARY";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_SUMMARY" LIKE "TUTORIAL_PREDICTIVE"."T_SUMMARY";
/* Now, we prepare the indicator table*/
DROP TYPE "TUTORIAL_PREDICTIVE"."T_INDICATORS";
CREATE TYPE "TUTORIAL_PREDICTIVE"."T_INDICATORS" AS TABLE (
"OID" NVARCHAR(50),
"VARIABLE" NVARCHAR(127),
"TARGET" NVARCHAR(127),
"KEY" NVARCHAR(127),
"VALUE" NCLOB,
"DETAIL" NCLOB
);
DROP TABLE "TUTORIAL_PREDICTIVE"."APL_INDICATORS";
CREATE TABLE "TUTORIAL_PREDICTIVE"."APL_INDICATORS" LIKE "TUTORIAL_PREDICTIVE"."T_INDICATORS";
You should see following objects created in the TUTORIAL_PREDICTIVE
schema:
Now, you can execute the APL* wrapper procedure.
/* Now, we create the AFL signature table to create the wrapper procedure*/
DROP TABLE "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE";
CREATE COLUMN TABLE "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE"
(
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7)
);
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (1, 'TUTORIAL_PREDICTIVE','T_FUNCTION_HEADER', 'IN');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (2, 'TUTORIAL_PREDICTIVE','T_OPERATION_CONFIG', 'IN');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (3, 'TUTORIAL_PREDICTIVE','T_VARIABLE_DESC', 'IN');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (4, 'TUTORIAL_PREDICTIVE','T_VARIABLE_ROLES', 'IN');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (5, 'TUTORIAL_PREDICTIVE','T_APL_INPUT', 'IN');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (6, 'TUTORIAL_PREDICTIVE','T_APL_MODEL_RESULT', 'OUT');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (7, 'TUTORIAL_PREDICTIVE','T_OPERATION_LOG', 'OUT');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (8, 'TUTORIAL_PREDICTIVE','T_SUMMARY', 'OUT');
insert into "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE" VALUES (9, 'TUTORIAL_PREDICTIVE','T_INDICATORS', 'OUT');
/* Now, we drop and re-create the AFL Wrapper procedure*/
call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('TUTORIAL_PREDICTIVE','APLWRAPPER_FORECAST');
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('APL_AREA','FORECAST','TUTORIAL_PREDICTIVE', 'APLWRAPPER_FORECAST', "TUTORIAL_PREDICTIVE"."FORECAST_SIGNATURE");
/* And finally, we call the AFL Wrapper that will run the APL Forecast algorithm*/
call "TUTORIAL_PREDICTIVE"."APLWRAPPER_FORECAST"(
"TUTORIAL_PREDICTIVE"."APL_FUNCTION_HEADER",
"TUTORIAL_PREDICTIVE"."APL_OPERATION_CONFIG",
"TUTORIAL_PREDICTIVE"."APL_VARIABLE_DESC",
"TUTORIAL_PREDICTIVE"."APL_VARIABLE_ROLES",
"TUTORIAL_PREDICTIVE"."TIME_SERIES_SORTED",
"TUTORIAL_PREDICTIVE"."APL_MODEL_RESULT",
"TUTORIAL_PREDICTIVE"."APL_OPERATION_LOG",
"TUTORIAL_PREDICTIVE"."APL_SUMMARY",
"TUTORIAL_PREDICTIVE"."APL_INDICATORS"
) with overview;
As a result, the table "TUTORIAL_PREDICTIVE"."RESULT"
will be populated with the results of the Automated Time Series model where kts_1
represents the prediction.