Fail in Forecast Smoothing

[Former Member]

Hi,

as I listened to the Video about Forecast Smoothing this should be only available to SPS7.

The Video: http://www.saphana.com/docs/DOC-4326

Looking into the Table SYS:AFL_FUNCTIONS_ I find the Entry "FORECASTSMOOTHING" in the Table. So I guess the Forecast Smoothing is available to me.

In HANA Studio - Help - About ... I see Version: 1.0.7000 / Build id: 386119

But running my Example of Forecast Smoothing gives me the Error:

Could not execute 'CALL _SYS_AFL.PAL_TS (V_TS_DATA, #TS_PARAMS, TS_OPTIMAL_PARAMS, TS_RESULTS) WITH OVERVIEW' in 176 ms 111 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] _SYS_AFL.AFLPAL:FORECASTSMOOTHING: [135] (range 2) AFLFunctionFatal exception: PAL error[73001502]:Ahead exception throw out, Check trace for details

My Params are:

INSERT INTO #TS_PARAMS VALUES ('THREAD_NUMBER', 2, null, null);

INSERT INTO #TS_PARAMS VALUES ('FORECAST_MODEL_NAME', null, null, 'TESM'); SESM: Single, DESM: Double, TESM: Triple

INSERT INTO #TS_PARAMS VALUES ('FORECAST_NUM', 30, null, null);

INSERT INTO #TS_PARAMS VALUES ('STARTTIME', 0, null, null);

INSERT INTO #TS_PARAMS VALUES ('CYCLE', 90, null, null);

INSERT INTO #TS_PARAMS VALUES ('FORECAST_AUTOMATIC', 0, null, null);

INSERT INTO #TS_PARAMS VALUES ('ALPHA', null, 0.1, null); 0-1 for smoothing weight

INSERT INTO #TS_PARAMS VALUES ('BETA', null, 0.1, null); 0-1 for trend (DESM & TESM)

INSERT INTO #TS_PARAMS VALUES ('GAMMA', null, 0.1, null); 0-1 for seasonality (TESM)

I try also without the FORECAST_AUTOMATIC, ALPHA, BETA, GAMMA but then I read in the Indexserver-Alert that the Param FORECAST_AUTOMATIC is missing.

As I know:

- The Data has no gaps

- The Base Example in the Documentation fails with the same error

Any suggestion?

How I can check if the Forecast SMoothing is available in my Version?

Regards,

Mansur

[Former Member]

Dear Mansur,

we have the same problem with forecast smoothing - I have posted a message on scn a while ago (see here ). Unfortunately the solution, proposed by Oleg, didn't work for us but maybe it will work for you.

Now we have even more problems with the cycle parameter...I wonder if this isn't a bug in HANA

Kind regards,
Wiola

[former_member193591]

Let us handle this one by one;

--------------------------

Did you try to
load the script that was given in SAP HANA PAL document; when you do that you
would have to do it ditto ... no changes. this is one of the process to check
if your system is ready; if you are having problem formatting it, please let me know; will format and share them.

Once you do
that if you get the error, then it is with your configuration and installation
of PAL in HANA; if that goes through then it is the script error;

-------------------------

Just to be on
the safe side, make sure the user has access to the schema that these table are
build and used in algos ...

___

Could you please check if your USER1 has access to AFL_WRAPPER_GENERATOR & AFL_WRAPPER_ERAZOR

USER1 is assigned to AFL__SYS_AFL_AFLPAL_EXECUTE role.

Does your user has select and execute permissions on the _SYS_AFL schema?

- GRANT EXECUTE ON system.afl_wrapper_generator to USER1 (logged on with SYSTEM user)

- GRANT SELECT ON user1.<signature table> to SYSTEM (logged on with your user)

GRANT CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, UPDATE, DELETE, INDEX ON SCHEMA "SCHEMA" TO USER1;

GRANT SELECT ON SCHEMA USER1 TO SYSTEM with GRANT OPTION;

GRANT SELECT ON SCHEMA "SCHEMA" TO SYSTEM with GRANT OPTION;

GRANT SELECT ON SCHEMA USER1 TO _SYS_REPO with GRANT OPTION;

GRANT SELECT ON SCHEMA "SCHEMA" TO _SYS_REPO with GRANT OPTION;

GRANT CONTENT_ADMIN TO USER1;

Install wrapper generator & eraser .... and

GRANT EXECUTE ON system.afl_wrapper_generator to USER1

GRANT EXECUTE ON system.afl_wrapper_eraser to USER1

AFL__SYS_AFL_AFLPAL_EXECUTE Role has to  be assigned to the user ... user has to have execute access for the schema under which we are generating tables;

Schema should belong to USER1, who should also be granted the privilege of executing
SYSTEM.afl_wrapper_generator and SYSTEM.afl_wrapper_eraser; and●

USER1 has been assigned the AFL__SYS_AFL_AFLPAL_EXECUTE role.

[Former Member]

Dear Arvind,

Yes I tried loading that script and everything works fine, also my code works fine but with very small amount of data. Problem only occurs when we want to use more data in our procedure.

It looks that problem is in somewhere else… similar problem occurs when we try to use more than 20 000 000 rows with Double Smoothing in FORECAST SMOOTHING algorithm.

Kind regards,
Wiola

[former_member193591]

looks like we have two threads on the same topic by you; please update the single thread with the info and close the other

[former_member184768]

Hi Mansur,

Will it be possible to share your code. Are you prefixing the schema name to the objects in signature table.

Regards,

Ravi

[Former Member]

Hello Ravi,

yes sure you can have the Code. But it is finally exactly the same as Sampled in the Predictive Analytics Documentation. Just the Schema is changed.

I am able to run several other Analytics (smpl: Tripple Expo. Smoothing) as in the Documentation and also in own solutions. SO I do not see any Configuration / Right Problems.

First the Code, below the Errormessage:

SET SCHEMA ENE_TIS;

DROP TYPE PAL_FORECASTSINGLESMOOTHING_DATA_T;

CREATE TYPE PAL_FORECASTSINGLESMOOTHING_DATA_T AS TABLE ("TIMESTAMP" INT, "VALUE" DOUBLE);

DROP TYPE PAL_CONTROL_T;

CREATE TYPE PAL_CONTROL_T AS TABLE ("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE, "strArgs" VARCHAR(100));

DROP TYPE PAL_OUTPARAMETER_T;

CREATE TYPE PAL_OUTPARAMETER_T AS TABLE ("NAME" VARCHAR(100), "VALUE" DOUBLE);

DROP TYPE PAL_FORECASTSINGLESMOOTHING_FORECAST_T;

CREATE TYPE PAL_FORECASTSINGLESMOOTHING_FORECAST_T AS TABLE ("TIMESTAMP" INT, "VALUE" DOUBLE, "DIFFERENCE" DOUBLE);

DROP table PAL_FORECASTSINGLESMOOTHING_PDATA_TBL;

CREATE column table PAL_FORECASTSINGLESMOOTHING_PDATA_TBL("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100));

insert into PAL_FORECASTSINGLESMOOTHING_PDATA_TBL values (1,'ENE_TIS.PAL_FORECASTSINGLESMOOTHING_DATA_T','in');

insert into PAL_FORECASTSINGLESMOOTHING_PDATA_TBL values (2,'ENE_TIS.PAL_CONTROL_T','in');

insert into PAL_FORECASTSINGLESMOOTHING_PDATA_TBL values (3,'ENE_TIS.PAL_OUTPARAMETER_T','out');

insert into PAL_FORECASTSINGLESMOOTHING_PDATA_TBL values(4,'ENE_TIS.PAL_FORECASTSINGLESMOOTHING_FORECAST_T','out');

GRANT SELECT ON ENE_TIS.PAL_FORECASTSINGLESMOOTHING_PDATA_TBL to SYSTEM;

CALL SYSTEM.afl_wrapper_eraser('PALFORECASTSMOOTHING');

call SYSTEM.afl_wrapper_generator('PALFORECASTSMOOTHING','AFLPAL','FORECASTSMOOTHING',PAL_FORECASTSINGLESMOOTHING_PDATA_TBL);

DROP TABLE #PAL_CONTROL_TBL;

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL ("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100));

INSERT INTO #PAL_CONTROL_TBL VALUES ('FORECAST_MODEL_NAME',null,null,'SESM');

INSERT INTO #PAL_CONTROL_TBL VALUES ('OPTIMIZER_TIME_BUDGET',5,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('OPTIMIZER_RANDOM_SEED',5,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('THREAD_NUMBER',8,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('ALPHA',null,0.1,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('FORECAST_NUM',2,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('STARTTIME',0,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('FORECAST_AUTOMATIC',1,null,null);

-- input tables

DROP TABLE PAL_FORECASTSINGLESMOOTHING_DATA_TBL;

CREATE COLUMN TABLE PAL_FORECASTSINGLESMOOTHING_DATA_TBL("TIMESTAMP" INT NOT NULL PRIMARY KEY, "VALUE" DOUBLE);

-- input data

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (0,200.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (1,135.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (2,195.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (3,197.5);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (4,310.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (5,175.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (6,155.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (7,130.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (8,220.0);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (9,277.5);

INSERT INTO PAL_FORECASTSINGLESMOOTHING_DATA_TBL VALUES (10,235.0);

DROP TABLE PAL_OUTPARAMETER_TBL;

CREATE COLUMN TABLE PAL_OUTPARAMETER_TBL("NAME" VARCHAR(100), "VALUE" DOUBLE);

-- forecast result tables

DROP TABLE PAL_FORECASTSINGLESMOOTHING_RESULT_TBL;

CREATE COLUMN TABLE PAL_FORECASTSINGLESMOOTHING_RESULT_TBL("TIMESTAMP" INT NOT NULL PRIMARY KEY, "VALUE" DOUBLE, "DIFFERENCE" DOUBLE);

call _SYS_AFL.PALFORECASTSMOOTHING( PAL_FORECASTSINGLESMOOTHING_DATA_TBL, "#PAL_CONTROL_TBL", PAL_OUTPARAMETER_TBL, PAL_FORECASTSINGLESMOOTHING_RESULT_TBL) with overview;

select * from PAL_OUTPARAMETER_TBL;

select * from PAL_FORECASTSINGLESMOOTHING_RESULT_TBL;

Errormessage:

Could not execute 'call _SYS_AFL.PALFORECASTSMOOTHING( PAL_FORECASTSINGLESMOOTHING_DATA_TBL, "#PAL_CONTROL_TBL", ...' in 236 ms 715 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] _SYS_AFL.AFLPAL:FORECASTSMOOTHING: [135] (range 2) AFLFunctionFatal exception: PAL error[73001502]:Ahead exception throw out, Check trace for details

As you see the error happens at the call of the FC-Smooth.

I am not sure how to find the real reason for that.

regards,

Mansur