Skip to Content
author's profile photo Former Member
Former Member

SAP Automated Predictive Library with Timestamp type instead Date

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





Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Jul 25, 2016 at 01:58 PM

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


    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 27, 2016 at 08:38 AM

    Resolved!

    Credit to Marc DANIAU 😊

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.