Hana and PA expert (double Exponential smoothing) : error 423 (AFL_WRAPPER)

[Former Member]

Hi all,

I'm opening this discussion after reading tons of SAP information during whole day about the Hana wrapper issues, unfortunately I did'nt succeed to use Hana models from Predictive analysis expert.

Here is my screencap :

I tried :

- Granting access to the AFL_WRAPPER_GENERATOR and AFLPM_CREATOR to my user.

- Changing my table datatypes to Double and re importing all data

- Inserting an ID column (type integer)

- Restarting the whole process with a SYSTEM user

- Starting an instance of a scriptserver on the host

Here is an extract of my log file :

2015/11/13 17:43:04.877000|>>|S| | 9728|  38|{|||||||||||||||execute

at com.sap.pa.core.service.impl.RepositoryConnectionPool.getConnection(RepositoryConnectionPool.java:64): TraceLog Message 0

2015/11/13 17:43:04.894000|>>|S| | 9728|  38| |||||||||||||||[com.sap.pa.core.service.impl.RepositoryConnectionPool]RepositoryConnectionPool:com.sap.pa.common.RepoCredentialsImpl@3f04416f: Getting existing RepoConnection no:1

2015/11/13 17:43:04.894000|>>|S| | 9728|  38| |||||||||||||||Starting chain execution getComponentScript

2015/11/13 17:43:04.898000|>>|E| | 9728|  38| |||||||||||||||Cannot find the resource:com.sap.pa.components.params.resources.Algorithms_Descriptors_messages

2015/11/13 17:43:04.898000|>>|E| | 9728|  38| |||||||||||||||Cannot find the resource:com.sap.pa.components.params.resources.Algorithms_Properties_messages

2015/11/13 17:43:04.898000|>>|E| | 9728|  38| |||||||||||||||Cannot find the resource:com.sap.pa.components.params.resources.Algorithms_messages

2015/11/13 17:43:05.012000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:05.012000|>>|S| | 9728|  38| |||||||||||||||CREATE COLUMN TABLE "SYSTEM"."PAS7_2_PDATA_PREDICT" ("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100));

2015/11/13 17:43:05.086000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:05.086000|>>|S| | 9728|  38| |||||||||||||||INSERT INTO "SYSTEM"."PAS7_2_PDATA_PREDICT" VALUES (?,?,?);

2015/11/13 17:43:05.160000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:05.160000|>>|S| | 9728|  38| |||||||||||||||CREATE COLUMN TABLE "SYSTEM"."PAS7_2_CONTROL_TAB_PREDICT" ("Name" VARCHAR (50),"intArgs" INTEGER,"doubleArgs" DOUBLE,"strArgs" VARCHAR (100));

2015/11/13 17:43:06.137000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:06.137000|>>|S| | 9728|  38| |||||||||||||||INSERT INTO "SYSTEM"."PAS7_2_CONTROL_TAB_PREDICT" VALUES (?,?,?,?);

2015/11/13 17:43:13.935000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:13.935000|>>|S| | 9728|  38| |||||||||||||||SET SCHEMA "SYSTEM";

2015/11/13 17:43:14.307000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:14.307000|>>|S| | 9728|  38| |||||||||||||||CREATE TYPE AUT_PAS7_2_TYPE AS TABLE ("row_id"  INT , "Year" INTEGER ,"Month" INTEGER ,"annee" INTEGER ,"courseuro" DOUBLE ,"PredictedValues" DOUBLE  , "__Summary__" NCLOB  , "__VizInfo__" NCLOB  );

2015/11/13 17:43:14.382000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:14.383000|>>|S| | 9728|  38| |||||||||||||||CREATE TYPE PAS7_2_PAL_DATA_T AS TABLE("__ID__" INTEGER , "courseuro" DOUBLE );

2015/11/13 17:43:14.463000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:14.463000|>>|S| | 9728|  38| |||||||||||||||CREATE TYPE PAS7_2_PAL_CONTROL_T AS TABLE("Name" VARCHAR (50),"intArgs" INTEGER,"doubleArgs" DOUBLE,"strArgs" VARCHAR(100));

2015/11/13 17:43:14.539000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:14.540000|>>|S| | 9728|  38| |||||||||||||||CREATE TYPE PAS7_2_PAL_RESULT_T AS TABLE("TIME" INTEGER,"PredictedValues" Double);

2015/11/13 17:43:16.716000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:16.716000|>>|S| | 9728|  38| |||||||||||||||CREATE TYPE PAS7_2_PAL_SUMMARY_T AS TABLE("Summary" NVARCHAR(5000));

2015/11/13 17:43:16.786000|>>|S| | 9728|  38| |||||||||||||||Executing inDbSql:

2015/11/13 17:43:16.786000|>>|S| | 9728|  38| |||||||||||||||call SYSTEM.afl_wrapper_generator('SYSTEM_PAS7_2_DOUBLESMOOTH','AFLPAL','DOUBLESMOOTH',PAS7_2_PDATA_PREDICT);

2015/11/13 17:43:17.245000|>>|E| | 9728|  38| |||||||||||||||error executing scriptSAP DBTech JDBC: [423]: AFL error:  [423] "SYSTEM"."AFL_WRAPPER_GENERATOR": line 34 col 1 (at pos 1472): [423] (range 3) AFL error exception: AFL error:  [423] "SYSTEM"."AFLPM_CREATOR": line 10 col 5 (at pos 193): [423] (range 3) AFL error exception: AFL error: registration finished with errors, see indexserver trace

2015/11/13 17:43:17.249000|>>|E| | 9728|  38| |||||||||||||||Error while getting Folder Location

2015/11/13 17:43:17.259000|>>|S| | 9728|  38| |||||||||||||||Completed chain execution getComponentScript: 12365 milliseconds

at com.sap.pa.core.service.impl.ConnectionsHandleImpl.releaseRepositoryConnection(ConnectionsHandleImpl.java:101): TraceLog Message 0

2015/11/13 17:43:17.433000|>>|S| | 9728|  38| |||||||||||||||[com.sap.pa.core.service.impl.ConnectionsHandleImpl]Releasing repository connection for execution a2150b2f-edbb-4f62-8660-5717b0e8cb49

at com.sap.pa.hiloadapter.internal.ServerExecuteComponent.execute(ServerExecuteComponent.java:240): TraceLog Message 0

2015/11/13 17:43:17.434000|>>|E| | 9728|  38| |||||||||||||||[com.sap.pa.hiloadapter.internal.ServerExecuteComponent]Error executing using connection:SAP DBTech JDBC: [259] (at 219): invalid table name:  Could not find table/view AUT_PAS7_2_RV in schema SYSTEM: line 1 col 220 (at pos 219)

2015/11/13 17:43:17.436000|>>|S| | 9728|  38|}|||||||||||||||execute: 12.559

2015/11/13 17:43:17.437000|==|S| | 9728|  38| |||||||||||||||[com.sap.hilo.pa.backend.PATransformCommandProcessor]Clearing QueryResult and ResultSet cache as the component results may have changed

2015/11/13 17:43:17.438000|>>|E| | 9728|  38| |||||||||||||||[Error while executing the chain]

java.lang.reflect.UndeclaredThrowableException

Can someone help me with this issue?

Thank you very much for your help.

[achab]

Hi Olivier,

I will take a look on Monday. Is it an option to share the HANA dataset you use for us to try to reproduce? Or at least the data types of your tables/hana views. If you are a SAP customer, you can also log a ticket with SAP support.

Thanks & regards

Antoine

[Former Member]

Thank you Antoine, any help will be very appreciated !

[Former Member]

Hi Antoine,

Let me explain more precisely the problem with a simple example.

For instance, let's use the SAP manual example (SAP_HANA_Predictive_Analysis_Library_PAL_en.pdf) regarding to Double Smoothing (p.309)

I tried this one directly on Hana Studio with my user (OHA) and my schema (OHSCHEME) :

SET SCHEMA OHSCHEME;

DROP TYPE PAL_DOUBLESMOOTH_DATA_T;
CREATE TYPE PAL_DOUBLESMOOTH_DATA_T AS TABLE("ID" INT, "RAWDATA" DOUBLE);

DROP TYPE PAL_DOUBLESMOOTH_RESULT_T;
CREATE TYPE PAL_DOUBLESMOOTH_RESULT_T AS TABLE("TIME" INT, "OUTPUT" DOUBLE);

DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE("NAME" VARCHAR(100), "INTARGS" INT,
"DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100));

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

DROP TABLE PAL_DOUBLESMOOTH_STATISTIC_TBL;
CREATE COLUMN TABLE PAL_DOUBLESMOOTH_STATISTIC_TBL("POSITION" INT, "SCHEMA_NAME"
VARCHAR(100), "TYPE_NAME" VARCHAR(100), "PARAMETER_TYPE" VARCHAR(100));

INSERT INTO PAL_DOUBLESMOOTH_STATISTIC_TBL VALUES (1,'OHSCHEME',
'PAL_DOUBLESMOOTH_DATA_T','IN');
INSERT INTO PAL_DOUBLESMOOTH_STATISTIC_TBL VALUES(2,'OHSCHEME',
'PAL_CONTROL_T','IN');
INSERT INTO PAL_DOUBLESMOOTH_STATISTIC_TBL VALUES(3,'OHSCHEME',
'PAL_DOUBLESMOOTH_RESULT_T','OUT');
INSERT INTO PAL_DOUBLESMOOTH_STATISTIC_TBL VALUES(4,'OHSCHEME',
'PAL_DOUBLESMOOTH_STATISTIC_T','OUT');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('OHSCHEME', 'DOUBLESMOOTH_TEST_PROC');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'DOUBLESMOOTH', 'OHSCHEME',
'DOUBLESMOOTH_TEST_PROC',PAL_DOUBLESMOOTH_STATISTIC_TBL);

DROP TABLE #PAL_CONTROL_TBL;
CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL ("NAME" VARCHAR(100),
"INTARGS" INT, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100));

INSERT INTO #PAL_CONTROL_TBL VALUES ('ALPHA', NULL,0.501, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('BETA', NULL,0.072, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('FORECAST_NUM',6, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('EXPOST_FLAG',1, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MEASURE_NAME', NULL, NULL, 'MSE');

DROP TABLE PAL_DOUBLESMOOTH_DATA_TBL;
CREATE COLUMN TABLE PAL_DOUBLESMOOTH_DATA_TBL LIKE PAL_DOUBLESMOOTH_DATA_T;

INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (1,143.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (2,152.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (3,161.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (4,139.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (5,137.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (6,174.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (7,142.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (8,141.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (9,162.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (10,180.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (11,164.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (12,171.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (13,206.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (14,193.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (15,207.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (16,218.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (17,229.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (18,225.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (19,204.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (20,227.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (21,223.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (22,242.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (23,239.0);
INSERT INTO PAL_DOUBLESMOOTH_DATA_TBL VALUES (24,266.0);

DROP TABLE PAL_DOUBLESMOOTH_RESULT_TBL;
CREATE COLUMN TABLE PAL_DOUBLESMOOTH_RESULT_TBL LIKE PAL_DOUBLESMOOTH_RESULT_T ;

DROP TABLE PAL_DOUBLESMOOTH_STATISTIC_TBL;
CREATE COLUMN TABLE PAL_DOUBLESMOOTH_STATISTIC_TBL LIKE PAL_DOUBLESMOOTH_STATISTIC_T;

CALL OHSCHEME.DOUBLESMOOTH_TEST_PROC(PAL_DOUBLESMOOTH_DATA_TBL,
"#PAL_CONTROL_TBL", PAL_DOUBLESMOOTH_RESULT_TBL,
PAL_DOUBLESMOOTH_STATISTIC_TBL)WITH OVERVIEW;

SELECT * FROM PAL_DOUBLESMOOTH_RESULT_TBL;
SELECT * FROM PAL_DOUBLESMOOTH_STATISTIC_TBL;

This example works correctly and I get the results below :

Now I want to get the same result, but from my local desktop PA Expert.

First, I connect to my Hana Database and I retrive the Data :

NB : my predictiveanalytics.ini is up to date (decimals are allowed).

Then I configure  a Double Smoothing on my local PA and I start the model.

I get the error below :

Hana Double Exponential Smoothing : An error occurred while executing the query.

Details:

Cause: Error details: SAP DBTech JDBC: [423]: AFL Error: [423]

"SYSTEM"."AFL_WRAPPER_GENERATOR":line 34 col 1 (at pos 1472):[423] (range 3)

AFL error exception: AFL error: [423]"SYSTEM".AFLPM_CREATOR": line 10 col 5 (at pos 193):[423](range 3) AFL error exception: AF error: registration finished with errors, see indexserver trace.

I also tried connecting to Hana with my SYSTEM user and granting permissions on OHSCHEME and PAL but it didn't work (same error message).

Thank you very much for your help.

[achab]

Hi,

I am looking and this and will update if possible today - thanks for the detailed explanations.

Just a quick question on: First, I connect to my Hana Database and I retrive the Data.

I assume you are using the option "Connect to SAP HANA" (aka "online mode", the data stays in SAP HANA) not "Download from SAP HANA" (aka "offline mode", the data is downloaded from SAP HANA to the desktop).

Thanks & regards

Antoine

[Former Member]

Yes I use the online mode, sorry if it was not clear.

The offline mode works well by the way, I've just tried it (with the local algorithms).

[achab]

Hi, what is the revision of your SAP HANA server?

Thanks & regards

Antoine

[achab]

Hi,

I received some insights from our engineering team.

They suggest that you delete the folder %temp%\sappa (mine is located here: C:\Users\I051666\AppData\Local\Temp\sappa\) and retry the workflow, and see if this does any good.

Would that be an option for you to log a ticket with SAP support in parallel?

I tried to reproduce your problem on my "spare time" but my SAP HANA server needs more configuration I am afraid.

Thanks & regards

Antoine

[Former Member]

Hi Antoine,

Thank you very much for your help.

Actually removing the temp file does not work, I'm still getting the same error message.

I will try to contact the SAP support.

Regards,

[achab]

Hi,

Please log the support ticket ID once you are done, I'll try to keep an eye on this.

Thanks & regards

Antoine

[achab]

Hi Olivier, any update?

Thanks & regards

Antoine

[achab]

I will mark this as Assumed Answered - feel free to create new posts to follow-up