HANA PAL Neural Network training function input data error

[Former Member]

Hi experts,

I'm trying to use the CREATEBPNN function in the PAL library. I've been receiving the following error when I call the associated procedure PAL_NN_TRAIN.

Could not execute 'CALL DEVUSER.PAL_NN_TRAIN(PAL_TRAIN_NN_DATA_TBL, "#PAL_CONTROL_TBL", PAL_TRAIN_NN_RESULT_TBL, ...' in 37 ms 548 µs . 
SAP DBTech JDBC: [423]: AFL error: search table error: _SYS_AFL.AFLPAL:CREATEBPNN: [423] (range 3) AFL error exception: exception 73001030: PAL error[73001030]:Data type of column $COL$ is not supported

I've managed to replicate the error using the example provided in the documentation. The code below is identical to the docs except that I've changed the data type for "V003" from Integer to Double. I then copied the "V001" Double data to the "V003" column to rule out any errors associated with having a column of Integers in a Double column.

The unaltered example works fine. I'm using the latest version of HANA2 Express Edition.

DROP TYPE PAL_TRAIN_NN_DATA_T;
CREATE TYPE PAL_TRAIN_NN_DATA_T AS TABLE("V000" INTEGER,"V001" DOUBLE,	"V002" VARCHAR(10),	"V003" DOUBLE,	"LABEL" VARCHAR(2));

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

DROP TYPE PAL_NN_MODEL_T;
CREATE TYPE PAL_NN_MODEL_T AS TABLE("NAME" VARCHAR(100),"MODEL" CLOB);

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

--CREATE PROCEDURE
DROP TABLE PAL_NN_PDATA_TBL;
CREATE COLUMN TABLE PAL_NN_PDATA_TBL("POSITION" INT,"SCHEMA_NAME" NVARCHAR(256),"TYPE_NAME" NVARCHAR(256),"PARAMETER_TYPE" VARCHAR(7));

DELETE FROM PAL_NN_PDATA_TBL;
INSERT INTO PAL_NN_PDATA_TBL VALUES (1, 'DM_PAL', 'PAL_TRAIN_NN_DATA_T', 'IN');
INSERT INTO PAL_NN_PDATA_TBL VALUES (2, 'DM_PAL', 'PAL_CONTROL_T', 'IN');
INSERT INTO PAL_NN_PDATA_TBL VALUES (3, 'DM_PAL', 'PAL_TRAIN_NN_RESULT_T', 'OUT');
INSERT INTO PAL_NN_PDATA_TBL VALUES (4, 'DM_PAL', 'PAL_NN_MODEL_T', 'OUT');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL', 'PAL_NN_TRAIN');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'CREATEBPNN', 'DM_PAL', 'PAL_NN_TRAIN', PAL_NN_PDATA_TBL);

--RUN
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 ('HIDDEN_LAYER_SIZE', NULL, NULL, '10, 10');
INSERT INTO #PAL_CONTROL_TBL VALUES ('HIDDEN_LAYER_ACTIVE_FUNC', 1, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('OUTPUT_LAYER_ACTIVE_FUNC', 1, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('LEARNING_RATE', NULL, 0.001, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MOMENTUM_FACTOR', NULL, 0.00001, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('FUNCTIONALITY', 0, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('TRAINING_STYLE', 1, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('CATEGORY_COL', 3, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('MAX_ITERATION', 100, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('NORMALIZATION', 1, NULL, NULL);
INSERT INTO #PAL_CONTROL_TBL VALUES ('WEIGHT_INIT', 1, NULL, NULL);

DROP TABLE PAL_TRAIN_NN_RESULT_TBL;
CREATE COLUMN TABLE PAL_TRAIN_NN_RESULT_TBL LIKE PAL_TRAIN_NN_RESULT_T;

DROP TABLE PAL_CLASSIFICATION_NN_MODEL_TBL;
CREATE COLUMN TABLE PAL_CLASSIFICATION_NN_MODEL_TBL LIKE PAL_NN_MODEL_T;

DROP TABLE PAL_TRAIN_NN_DATA_TBL;
CREATE COLUMN TABLE PAL_TRAIN_NN_DATA_TBL LIKE PAL_TRAIN_NN_DATA_T;
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES(  1, 1.71, 'AC', 0, 'AA');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 10, 1.78, 'CA', 5, 'AB');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 17, 2.36, 'AA', 6, 'AA');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 12, 3.15, 'AA', 2, 'C');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 7, 1.05, 'CA', 3, 'AB');

INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES(  6, 1.50, 'CA', 2, 'AB');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 9, 1.97, 'CA', 6, 'C');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 5, 1.26, 'AA', 1, 'AA');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 12, 2.13, 'AC', 4, 'C');
INSERT INTO PAL_TRAIN_NN_DATA_TBL VALUES( 18, 1.87, 'AC', 6, 'AA');

--CALL PROCEDURE
CALL DM_PAL.PAL_NN_TRAIN(PAL_TRAIN_NN_DATA_TBL, "#PAL_CONTROL_TBL", PAL_TRAIN_NN_RESULT_TBL, PAL_CLASSIFICATION_NN_MODEL_TBL) WITH OVERVIEW;

Any help appreciated

[former_member764224 · Accepted solution]

Hello,

There is a line :

INSERT INTO #PAL_CONTROL_TBL VALUES('CATEGORY_COL', 3, NULL, NULL);

It indicates that column "V003" is a category column (counting starts with 0) which makes it only support integer and string type.

Best Regards

Zee

[Former Member · Accepted solution]

Okay, I think I see. So correct me if I'm wrong:

For each integer column that I wish to assign as a category column, I would insert a new line into #PAL_CONTROL_TBL such as

INSERT INTO #PAL_CONTROL_TBL VALUES ('CATEGORY_COL', <column number>, NULL, NULL);

Is this correct?

Cheers