Error during execution of Predictive Analytics Library (PAL) in HANA for Random Forest.

[Former Member]

Dear SCN community,

We are trying to build a predictive model from the HANA PAL guide provided by SAP. Unfortunately we keep running into an error which is quite surprising since the code is copy pasted from the guide. Only the schema name is changed. Could one of the gurus please help us out? The algorithm is Random Forest.

The full code is also pasted at the bottom.

Thanks a lot!!!

**********************************************************************************

Error 1

Could not execute 'CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'RANDOMFORESTTRAIN', 'DE001_D14_710', ...' in 971 ms 345 µs .

SAP DBTech JDBC: [423]: AFL error:  [423]

"SYS"."AFLLANG_WRAPPER_PROCEDURE_CREATE": line 156 col 5 (at pos 5073): [423] (range 3) AFL error exception: AFL error: Registration of AFLLANG wrapper procedure "DE001_D14_710"."PAL_RF_TRAINING_PROC" failed with error 'unknown function'.

Error 2

Could not execute 'CALL "DE001_D14_710".PAL_RF_TRAINING_PROC(PAL_RF_DATA_TBL, #PAL_CONTROL_TBL, PAL_RF_MODEL_TBL, ...'

SAP DBTech JDBC: [328]: invalid name of function or procedure: PAL_RF_TRAINING_PROC: line 1 col 22 (at pos 21)

**********************************************************

SQL Code

SET SCHEMA DE001_D14_710;

----DROP TYPE PAL_RF_DATA_T;

CREATE TYPE PAL_RF_DATA_T AS TABLE(

"OUTLOOK" VARCHAR(20),

"TEMP" DOUBLE,

"HUMIDITY" DOUBLE,

"WINDY" VARCHAR(10),

"CLASS" VARCHAR(20)

);

----DROP TYPE PAL_RF_MODEL_T;

CREATE TYPE PAL_RF_MODEL_T AS TABLE(

"ID" INTEGER,

"TREEINDEX" INTEGER,

"MODEL" VARCHAR(5000)

);

----DROP TYPE PAL_RF_VAR_IMP_T;

CREATE TYPE PAL_RF_VAR_IMP_T AS TABLE(

"VAR" VARCHAR(100),

"IMP" DOUBLE

);

----DROP TYPE PAL_RF_ERR_RATE_T;

CREATE TYPE PAL_RF_ERR_RATE_T AS TABLE(

"TREEINDEX" INTEGER,

"ERR" DOUBLE

);

----DROP TYPE PAL_RF_CONFUSION_T;

CREATE TYPE PAL_RF_CONFUSION_T AS TABLE(

"ID" INTEGER,

"CONTENT" VARCHAR(1000)

);

----DROP TYPE PAL_CONTROL_T;

CREATE TYPE PAL_CONTROL_T AS TABLE(

"NAME" VARCHAR (100),

"INTARGS" INTEGER,

"DOUBLEARGS" DOUBLE,

"STRINGARGS" VARCHAR(100)

);

----DROP TABLE PAL_RF_PDATA_TBL;

CREATE COLUMN TABLE PAL_RF_PDATA_TBL(

"POSITION" INT,

"SCHEMA_NAME" NVARCHAR(256),

"TYPE_NAME" NVARCHAR(256),

"PARAMETER_TYPE" VARCHAR(7)

);

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (1, 'DE001_D14_710', 'PAL_RF_DATA_T', 'in');

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (2, 'DE001_D14_710', 'PAL_CONTROL_T', 'in');

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (3, 'DE001_D14_710', 'PAL_RF_MODEL_T', 'out');

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (4, 'DE001_D14_710', 'PAL_RF_VAR_IMP_T', 'out');

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (5, 'DE001_D14_710', 'PAL_RF_ERR_RATE_T', 'out');

INSERT INTO "PAL_RF_PDATA_TBL" VALUES (6, 'DE001_D14_710', 'PAL_RF_CONFUSION_T', 'out');

--CALL "SYS".AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL', 'PAL_RF_TRAINING_PROC');

CALL "SYS"."AFLLANG_WRAPPER_PROCEDURE_CREATE"('AFLPAL', 'RANDOMFORESTTRAIN',

'DE001_D14_710', 'PAL_RF_TRAINING_PROC', "PAL_RF_PDATA_TBL");

----DROP TABLE PAL_RF_DATA_TBL;

CREATE COLUMN TABLE PAL_RF_DATA_TBL LIKE PAL_RF_DATA_T;

INSERT INTO PAL_RF_DATA_TBL VALUES ('Sunny', 75, 70, 'Yes', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Sunny', null, 90, 'Yes', 'Do not Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Sunny', 85, null, 'No', 'Do not Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Sunny', 72, 95, 'No', 'Do not Play');

INSERT INTO PAL_RF_DATA_TBL VALUES (null, null, 70, null, 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Overcast', 72, 90, 'Yes', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Overcast', 83, 78, 'No', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Overcast', 64, 65, 'Yes', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Overcast', 81, 75, 'No', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES (null, 71, 80, 'Yes', 'Do not Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Rain', 65, 70, 'Yes', 'Do not Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Rain', 75, 80, 'No', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Rain', 68, 80, 'No', 'Play');

INSERT INTO PAL_RF_DATA_TBL VALUES ('Rain', 70, 96, 'No', 'Play');

--DROP TABLE #PAL_CONTROL_TBL;

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL(

"NAME" VARCHAR (100),

"INTARGS" INTEGER,

"DOUBLEARGS" DOUBLE,

"STRINGARGS" VARCHAR (100)

);

INSERT INTO #PAL_CONTROL_TBL VALUES ('TREES_NUM', 300, null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES ('TRY_NUM', 3, null,null);

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

--DROP TABLE PAL_RF_MODEL_TBL;

CREATE COLUMN TABLE PAL_RF_MODEL_TBL LIKE PAL_RF_MODEL_T;

--DROP TABLE PAL_RF_VAR_IMP_TBL;

CREATE COLUMN TABLE PAL_RF_VAR_IMP_TBL LIKE PAL_RF_VAR_IMP_T;

--DROP TABLE PAL_RF_ERR_RATE_TBL;

CREATE COLUMN TABLE PAL_RF_ERR_RATE_TBL LIKE PAL_RF_ERR_RATE_T;

--DROP TABLE PAL_RF_CONFUSION_TBL;

CREATE COLUMN TABLE PAL_RF_CONFUSION_TBL LIKE PAL_RF_CONFUSION_T;

CALL "DE001_D14_710".PAL_RF_TRAINING_PROC(PAL_RF_DATA_TBL, #PAL_CONTROL_TBL,

PAL_RF_MODEL_TBL, PAL_RF_VAR_IMP_TBL, PAL_RF_ERR_RATE_TBL, PAL_RF_CONFUSION_TBL)

WITH OVERVIEW;

SELECT * FROM PAL_RF_MODEL_TBL;

SELECT * FROM PAL_RF_VAR_IMP_TBL;

SELECT * FROM PAL_RF_ERR_RATE_TBL;

SELECT * FROM PAL_RF_CONFUSION_TBL;

[pfefferf · Accepted solution]

Hello,

have you check if the RANDOMFORESTTRAIN function is available on your system. As far as I know it was introduced with HANA SPS11.

You can check it by searching it in table SYS.AFL_FUNCTIONS (Checking PAL Installation - SAP HANA Predictive Analysis Library (PAL) - SAP Library).

Probably you are on a lower SPS level in which the function is not available or if you are already on SPS11 maybe it is not installed?

Regards,

Florian

[Former Member · Accepted solution]

Thank you! We requested a HANA Upgrade!

Best regards,

Suryansh Pant