SAP Automated Predictive Library with Timestamp type instead Date

[Former Member]

Hello,

I would like to perform some analysis like regression/classification, clustering, or timeseries with the Automated Predictive Library APL.

I have a table with Timestamp type ( 23.09.2011 19:30:00.0, 23.09.2011 19:45:00.0,23.09.2011 20:00:00.0 ....) i create a model with APL but I got empty table for "MODEL_TRAIN_BIN", "OPERATION_LOG", "SUMMARY" and  "INDICATORS"

I tried train and apply but i got this error:

[APL error] Automated Analytics error (-2147024809): phase 'KxCPPInterf::IKxenModel::sendMode()'  [[Root[7f2c5ed9f800]:The date variable (ZEITSTEMPEL) is not valid. It is constant. ]]

I would like to know if we can perform the timeseries with a Timestamp type or should we use only Date variable.

Thanks in advance

Here is my SQL :

drop type IST_VERBRAUCH_T;
create type IST_VERBRAUCH_T as table (
  "ZEITSTEMPEL" TIMESTAMP,
  "IST_VERBRAUCH" DECIMAL(10,3)
);


-- --------------------------------------------------------------------------
-- Create table type for the forecast output
-- --------------------------------------------------------------------------


drop type IST_VERBRAUCH_OUT_T;
create type IST_VERBRAUCH_OUT_T as table (
  "ZEITSTEMPEL" TIMESTAMP,
  "IST_VERBRAUCH" DECIMAL(10,3),
  "kts_1" DOUBLE
);


-- --------------------------------------------------------------------------
-- Create AFL wrappers for the APL function
-- --------------------------------------------------------------------------
-- the AFL wrapper generator needs the signature of the expected stored proc
drop table FORECAST_SIGNATURE;
create column table FORECAST_SIGNATURE like PROCEDURE_SIGNATURE_T;


insert into FORECAST_SIGNATURE values (1, 'USER_APL','FUNCTION_HEADER_T', 'IN');
insert into FORECAST_SIGNATURE values (2, 'USER_APL','OPERATION_CONFIG_T', 'IN');
insert into FORECAST_SIGNATURE values (3, 'USER_APL','VARIABLE_DESC_T', 'IN');
insert into FORECAST_SIGNATURE values (4, 'USER_APL','VARIABLE_ROLES_T', 'IN');
insert into FORECAST_SIGNATURE values (5, 'USER_APL','IST_VERBRAUCH_T', 'IN');
insert into FORECAST_SIGNATURE values (6, 'USER_APL','IST_VERBRAUCH_OUT_T', 'OUT');
insert into FORECAST_SIGNATURE values (7, 'USER_APL','OPERATION_LOG_T', 'OUT');
insert into FORECAST_SIGNATURE values (8, 'USER_APL','SUMMARY_T', 'OUT');
insert into FORECAST_SIGNATURE values (9, 'USER_APL','INDICATORS_T', 'OUT');
call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('USER_APL','APLWRAPPER_FORECAST');
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('APL_AREA','FORECAST','USER_APL', 'APLWRAPPER_FORECAST', FORECAST_SIGNATURE);




-- --------------------------------------------------------------------------
-- Create the input/output tables used as arguments for the APL function
-- --------------------------------------------------------------------------
drop table FUNC_HEADER;
create table FUNC_HEADER like FUNCTION_HEADER_T;
insert into FUNC_HEADER values ('Oid', '#42');
insert into FUNC_HEADER values ('LogLevel', '8');


-- Create a view which contains the sorted dataset
drop view IST_VERBRAUCH_SORTED;
create view IST_VERBRAUCH_SORTED as select "ZEITSTEMPEL","IST_VERBRAUCH" from POC_ENVIAM_APP.IST_VERBRAUCH order by "ZEITSTEMPEL" asc;


drop table FORECAST_CONFIG;
create table FORECAST_CONFIG like OPERATION_CONFIG_T;
insert into FORECAST_CONFIG values ('APL/Horizon', '21');
insert into FORECAST_CONFIG values ('APL/TimePointColumnName', 'ZEITSTEMPEL');
insert into FORECAST_CONFIG values ('APL/LastTrainingTimePoint', '23.09.2011 19:30:00.0');


drop table VARIABLE_DESC;
create table VARIABLE_DESC like VARIABLE_DESC_T;




drop table VARIABLE_ROLES;
create table VARIABLE_ROLES like VARIABLE_ROLES_T;


insert into VARIABLE_ROLES values ('ZEITSTEMPEL', 'input');
insert into VARIABLE_ROLES values ('IST_VERBRAUCH', 'target');


drop table FORECAST_OUT;
create table FORECAST_OUT like IST_VERBRAUCH_OUT_T;


drop table OPERATION_LOG;
create table OPERATION_LOG like OPERATION_LOG_T;


drop table SUMMARY;
create table SUMMARY like SUMMARY_T;


drop table INDICATORS;
create table INDICATORS like INDICATORS_T;


-- --------------------------------------------------------------------------
-- Execute the APL function using its AFL wrapper and the actual input/output tables
-- --------------------------------------------------------------------------
call APLWRAPPER_FORECAST(FUNC_HEADER, FORECAST_CONFIG, VARIABLE_DESC, VARIABLE_ROLES, USER_APL.IST_VERBRAUCH_SORTED, FORECAST_OUT, OPERATION_LOG, SUMMARY, INDICATORS) with overview;
select * from "USER_APL"."FORECAST_OUT" order by "ZEITSTEMPEL" asc;
select * from "USER_APL"."OPERATION_LOG";
select * from "USER_APL"."SUMMARY";
select * from "USER_APL"."INDICATORS";

[marc_daniau · Accepted solution]

APL is able to forecast with timestamp column. Could you double check your input data has a consistent series ? Here is a sample forecast done with a half hourly series.

The sample script is the following

drop type TS_HALF_HOUR_T;

create type TS_HALF_HOUR_T as table (

  "Date" TIMESTAMP,

  "Metric" DOUBLE

);

drop type FORECAST_OUT_T;

create type FORECAST_OUT_T as table (

  "Date" TIMESTAMP,

  "Metric" DOUBLE,

  "kts_1" DOUBLE

);

drop table FORECAST_SIGNATURE;

create column table FORECAST_SIGNATURE like PROCEDURE_SIGNATURE_T;

insert into FORECAST_SIGNATURE values (1, 'APL_DEVELOPER','FUNCTION_HEADER_T', 'IN');

insert into FORECAST_SIGNATURE values (2, 'APL_DEVELOPER','OPERATION_CONFIG_T', 'IN');

insert into FORECAST_SIGNATURE values (3, 'APL_DEVELOPER','VARIABLE_DESC_T', 'IN');

insert into FORECAST_SIGNATURE values (4, 'APL_DEVELOPER','VARIABLE_ROLES_T', 'IN');

insert into FORECAST_SIGNATURE values (5, 'APL_DEVELOPER','TS_HALF_HOUR_T', 'IN');

insert into FORECAST_SIGNATURE values (6, 'APL_DEVELOPER','FORECAST_OUT_T', 'OUT');

insert into FORECAST_SIGNATURE values (7, 'APL_DEVELOPER','OPERATION_LOG_T', 'OUT');

insert into FORECAST_SIGNATURE values (8, 'APL_DEVELOPER','SUMMARY_T', 'OUT');

insert into FORECAST_SIGNATURE values (9, 'APL_DEVELOPER','INDICATORS_T', 'OUT');

call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('APL_DEVELOPER','APLWRAPPER_FORECAST');

call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('APL_AREA','FORECAST','APL_DEVELOPER', 'APLWRAPPER_FORECAST', FORECAST_SIGNATURE);

drop table FUNC_HEADER;

create table FUNC_HEADER like FUNCTION_HEADER_T;

insert into FUNC_HEADER values ('Oid', '#42');

insert into FUNC_HEADER values ('LogLevel', '8');

-- Create a view which contains the sorted dataset

drop view TS_SORTED;

create view TS_SORTED as select * from APL_DEVELOPER.TS_HALF_HOUR order by "Date" asc;

drop table FORECAST_CONFIG;

create table FORECAST_CONFIG like OPERATION_CONFIG_T;

insert into FORECAST_CONFIG values ('APL/Horizon', '21');

insert into FORECAST_CONFIG values ('APL/TimePointColumnName', 'Date');

insert into FORECAST_CONFIG values ('APL/LastTrainingTimePoint', '2015-01-05 05:30:00');

drop table VARIABLE_DESC;

create table VARIABLE_DESC like VARIABLE_DESC_T;

-- let this table empty to use guess variables

drop table VARIABLE_ROLES;

create table VARIABLE_ROLES like VARIABLE_ROLES_T;

insert into VARIABLE_ROLES values ('Date', 'input');

insert into VARIABLE_ROLES values ('Metric', 'target');

drop table FORECAST_OUT;

create table FORECAST_OUT like FORECAST_OUT_T;

drop table OPERATION_LOG;

create table OPERATION_LOG like OPERATION_LOG_T;

drop table SUMMARY;

create table SUMMARY like SUMMARY_T;

drop table INDICATORS;

create table INDICATORS like INDICATORS_T;

call APLWRAPPER_FORECAST(FUNC_HEADER, FORECAST_CONFIG, VARIABLE_DESC, VARIABLE_ROLES, APL_DEVELOPER.TS_SORTED, FORECAST_OUT, OPERATION_LOG, SUMMARY, INDICATORS) with overview;

select * from "APL_DEVELOPER"."FORECAST_OUT" order by "Date" asc;

select * from "APL_DEVELOPER"."OPERATION_LOG";

select * from "APL_DEVELOPER"."SUMMARY";

select * from "APL_DEVELOPER"."INDICATORS";

[Former Member · Accepted solution]

Hello Marc,

Thanks for your reply.

I wrote the same Code, but I got null value,

Here is the code:

--The sample script is the following
drop type INPUT_VERBRAUCH_T;
create type INPUT_VERBRAUCH_T as table (
  "ZEITSTEMPEL" TIMESTAMP,
  "IST_VERBRAUCH" DOUBLE
);


drop type FORECAST_OUT_T;
create type FORECAST_OUT_T as table (
  "ZEITSTEMPEL" TIMESTAMP,
  "IST_VERBRAUCH" DOUBLE,
  "kts_1" DOUBLE
);
drop table FORECAST_SIGNATURE;
create column table FORECAST_SIGNATURE like PROCEDURE_SIGNATURE_T;


insert into FORECAST_SIGNATURE values (1, 'USER_APL','FUNCTION_HEADER_T', 'IN');
insert into FORECAST_SIGNATURE values (2, 'USER_APL','OPERATION_CONFIG_T', 'IN');
insert into FORECAST_SIGNATURE values (3, 'USER_APL','VARIABLE_DESC_T', 'IN');
insert into FORECAST_SIGNATURE values (4, 'USER_APL','VARIABLE_ROLES_T', 'IN');
insert into FORECAST_SIGNATURE values (5, 'USER_APL','INPUT_VERBRAUCH_T', 'IN');
insert into FORECAST_SIGNATURE values (6, 'USER_APL','FORECAST_OUT_T', 'OUT');
insert into FORECAST_SIGNATURE values (7, 'USER_APL','OPERATION_LOG_T', 'OUT');
insert into FORECAST_SIGNATURE values (8, 'USER_APL','SUMMARY_T', 'OUT');
insert into FORECAST_SIGNATURE values (9, 'USER_APL','INDICATORS_T', 'OUT');
call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('USER_APL','APLWRAPPER_FORECAST');
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('APL_AREA','FORECAST','USER_APL', 'APLWRAPPER_FORECAST', FORECAST_SIGNATURE);


drop table FUNC_HEADER;
create table FUNC_HEADER like FUNCTION_HEADER_T;
insert into FUNC_HEADER values ('Oid', '#42');
insert into FUNC_HEADER values ('LogLevel', '8');


-- Create a view which contains the sorted dataset
drop view VERBRAUCH_SORTED;
create view VERBRAUCH_SORTED as select * from USER_APL.TEST_IST_VERBRAUCH order by "ZEITSTEMPEL" asc;


drop table FORECAST_CONFIG;
create table FORECAST_CONFIG like OPERATION_CONFIG_T;
insert into FORECAST_CONFIG values ('APL/Horizon', '21');
insert into FORECAST_CONFIG values ('APL/TimePointColumnName', 'ZEITSTEMPEL');
--insert into FORECAST_CONFIG values ('APL/LastTrainingTimePoint', '2015-01-05 05:30:00');


drop table VARIABLE_DESC;
create table VARIABLE_DESC like VARIABLE_DESC_T;
-- let this table empty to use guess variables


drop table VARIABLE_ROLES;
create table VARIABLE_ROLES like VARIABLE_ROLES_T;
insert into VARIABLE_ROLES values ('ZEITSTEMPEL', 'input');
insert into VARIABLE_ROLES values ('IST_VERBRAUCH', 'target');


drop table FORECAST_OUT;
create table FORECAST_OUT like FORECAST_OUT_T;


drop table OPERATION_LOG;
create table OPERATION_LOG like OPERATION_LOG_T;


drop table SUMMARY;
create table SUMMARY like SUMMARY_T;


drop table INDICATORS;
create table INDICATORS like INDICATORS_T;


call APLWRAPPER_FORECAST(FUNC_HEADER, FORECAST_CONFIG, VARIABLE_DESC, VARIABLE_ROLES, USER_APL.VERBRAUCH_SORTED, FORECAST_OUT, OPERATION_LOG, SUMMARY, INDICATORS) with overview;
select * from "USER_APL"."FORECAST_OUT" order by "ZEITSTEMPEL" asc;
select * from "USER_APL"."OPERATION_LOG";
select * from "USER_APL"."SUMMARY";
select * from "USER_APL"."INDICATORS";

I don't know what is the problem because I got the kts_1 value.

Thanks in advance

[marc_daniau · Accepted solution]

Can I ask you to send the dataset and the sql script to my sap email address that is in my SCN profile ?

[Former Member · Accepted solution]

Sure!

thanks

[marc_daniau · Accepted solution]

Could you try again with that additional line ?

insert into FORECAST_CONFIG values ('APL/LastTrainingTimePoint', '2011-09-28 09:45:00');

[Former Member · Accepted solution]

I tried it but it didn't work.

Does it work for you ?

[Former Member]

Resolved!

Credit to Marc DANIAU