on 12-28-2014 8:48 PM
I'm trying to use Hana's AFL K Means clustering algorithm,
I made some adjustments to this code:
Provided in this SAP Hana Academy tutorial:
https://www.youtube.com/watch?v=VGsNRG3NkTs
Everything has been executed with the SYSTEM user, but in this line:
CALL _SYS_AFL.PAL_KM (V_KM_DATA, #KM_PARAMS, KM_RESULTS, KM_CENTERS) WITH OVERVIEW;
I get this error:
Could not execute 'CALL _SYS_AFL.PAL_KM (V_KM_DATA, #KM_PARAMS, KM_RESULTS, KM_CENTERS) WITH OVERVIEW'
SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
HOW CAN I SOLVE THIS ERROR?
This is the complete code I've been using. Executed with SYSTEM user:
CREATE TYPE PAL_T_KM_DATA AS TABLE (STORE_SK INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE, CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE);
CREATE TYPE PAL_T_KM_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));
CREATE TYPE PAL_T_KM_RESULTS AS TABLE (ID INTEGER, CENTER_ID INTEGER, DISTANCE DOUBLE);
CREATE TYPE PAL_T_KM_CENTERS AS TABLE (CENTER_ID INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE, CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE);
CREATE COLUMN TABLE PAL_KM_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));
INSERT INTO PAL_KM_SIGNATURE VALUES (1, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA', 'in');
INSERT INTO PAL_KM_SIGNATURE VALUES (2, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS', 'in');
INSERT INTO PAL_KM_SIGNATURE VALUES (3, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS', 'out');
INSERT INTO PAL_KM_SIGNATURE VALUES (4, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS', 'out');
--GRANT SELECT ON PAL_KM_SIGNATURE TO SYSTEM;
CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_KM', 'AFLPAL', 'KMEANS', PAL_KM_SIGNATURE);
-- app setup
CREATE VIEW V_KM_DATA AS
SELECT STORE_SK, SUM(COST), SUM(SALES), MAX(TRAFFIC_BAND), MAX(CUSTOMER_COUNT), MAX(GENDER_F), MAX(GENDER_M)
FROM "TRIALFIRE_CLIENTDATA"."STORE_SALES_TRAITS"
GROUP BY STORE_SK
;
CREATE COLUMN TABLE KM_RESULTS LIKE PAL_T_KM_RESULTS;
CREATE COLUMN TABLE KM_CENTERS LIKE PAL_T_KM_CENTERS;
-- app runtime
DROP TABLE #KM_PARAMS;
CREATE LOCAL TEMPORARY COLUMN TABLE #KM_PARAMS LIKE PAL_T_KM_PARAMS;
INSERT INTO #KM_PARAMS VALUES ('THREAD_NUMBER', 2, null, null);
INSERT INTO #KM_PARAMS VALUES ('GROUP_NUMBER_MIN', 50, null, null);
INSERT INTO #KM_PARAMS VALUES ('GROUP_NUMBER_MAX', 250, null, null);
INSERT INTO #KM_PARAMS VALUES ('INIT_TYPE', 1, null, null);
INSERT INTO #KM_PARAMS VALUES ('DISTANCE_LEVEL', 2, null, null);
INSERT INTO #KM_PARAMS VALUES ('MAX_ITERATION', 100, null, null);
INSERT INTO #KM_PARAMS VALUES ('NORMALIZATION', 0, null, null);
INSERT INTO #KM_PARAMS VALUES ('EXIT_THRESHOLD', null, 0.0001, null);
-------------------------------------------------------------------------
I solved this by creating a new user TEST_USER, and I granted these roles:
(note that "Grantable to other users and roles" is checked)
After that I opened an sql connection with the TEST_USER and ran this query:
GRANT AFL__SYS_AFL_AFLPAL_EXECUTE TO SYSTEM;
GRANT AFL__SYS_AFL_AFLPAL_EXECUTE_WITH_GRANT_OPTION TO SYSTEM;
And everything worked out!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jonathan,
according to SAP note 1846194 - Lack of permissons when using AFL
You should
If this appears to be necessary to your system then your user setup is
faulty and needs to be corrected.
User | Count |
---|---|
95 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.