Guide: Setting up HANA PAL for use with SAP Predictive Analysis
SAP’s Predictive Analysis allows users to leverage the HANA PAL (Predictive Analysis Library) while connected to HANA in an online mode. This results in a user friendly interface which still allows you to push the processing to the HANA server. The PAL is not installed on HANA by default so I’ll show below one way in which it can be installed. I’ve used Rev 60 of HANA for this guide.
Check if the AFL is installed:
You should first check if AFL (Application Function Library which includes PAL) is installed on your HANA server. To do this, log in to your HANA server in HANA Studio. Then open a SQL console and run the following commands:
SELECT * FROM
“SYS”.”AFL_AREAS” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
“SYS”.”AFL_AREAS” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
SELECT * FROM
“SYS”.”AFL_PACKAGES” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
“SYS”.”AFL_PACKAGES” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
SELECT * FROM
“SYS”.”AFL_FUNCTIONS” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
“SYS”.”AFL_FUNCTIONS” WHERE SCHEMA_NAME = ‘_SYS_AFL’ AND
AREA_NAME = ‘AFLPAL’;
If results get returned then you already have AFL installed (image below shows a HANA server with the AFL successfully installed).
Install AFL:
If you don’t have the AFL installed, follow the steps below to install (you can download the AFL from http://service.sap.com/swdc)
- Log in as root.
- Extract the files using SAPCAR – SAPCAR -xvf IMDB_AFL100_60_1-10012328.SAR.
- Navigate into the SAP_HANA_AFL directory which was created in step 2 and execute hdbinst:
~/tmp/SAP_HANA_AFL #./hdbinst
SAP Application
Function Libraries installation kit detected.
Function Libraries installation kit detected.
SAP HANA Database
Installation Manager – SAP AFL Installation 1.00.60.379371
Installation Manager – SAP AFL Installation 1.00.60.379371
****************************************************************************
Enter SAP HANA
system ID: HVN
system ID: HVN
Enter System
Administrator Password:
Administrator Password:
Checking
installation…
installation…
Preparing package
“AFL”…
“AFL”…
Installing SAP
Application Function Libraries to
/sapmnt/HVN/exe/linuxx86_64/plugins/afl_1.00.60.379371_1147257…
Application Function Libraries to
/sapmnt/HVN/exe/linuxx86_64/plugins/afl_1.00.60.379371_1147257…
Installing package
‘AFL’ …
‘AFL’ …
Stopping system…
Activating plugin…
Starting system…
Installation done
Log file written to
‘/var/tmp/hdb_afl_2013-07-29_16.12.49/hdbinst_afl.log’.
‘/var/tmp/hdb_afl_2013-07-29_16.12.49/hdbinst_afl.log’.
Check if AFL__SYS_AFL_AFLPAL_EXECUTE has been granted to the user who will be running the predictive models:
Grant AFL__SYS_AFL_AFLPAL_EXECUTE if the user doesn’t have it:
NOTE: there are two underscores _ between AFL and SYS.
You can grant the option through the GUI or run a SQL statement similar to the following where MyHANAUser is the username you wish to give access to:
grant AFL__SYS_AFL_AFLPAL_EXECUTE to MyHANAUser;
Check if you already have the afl_wrapper_generator and afl_wrapper_eraser procedures and access to run them:
Below we can see the procedures do indeed exist:
Here we can see that user I817307 has access to the procedures:
Add the afl_wrapper_generator and afl_wrapper_eraser procedures if they don’t exist:
- On the HANA server, navigate to the /hanamnt//<SID>/HDB <instance_number>/exe/plugins/afl/ directory and execute the afl_wrapper_generator.sql and afl_wrapper_eraser.sql scripts as HANA user SYSTEM. (An easy way to do this is to open the files in a text editor on the Linux server and copy the code back to HANA studio for execution as the SYSTEM user in a SQL console).
- You now have two procedures – AFL_WRAPPER_GENERATOR and AFL_WRAPPER_ERASER which are owned by SYSTEM.
- Grant the EXECUTE privilege on system.afl_wrapper_generator and system.afl_wrapper_eraser to your predictive analysts.
- For example, if the user name is MyHANAUser, run the commands: GRANT EXECUTE ON system.afl_wrapper_generator to MyHANAUser; GRANT EXECUTE ON system.afl_wrapper_eraser to MyHANAUser;.
Check if you’ve enabled the Script Server.
Next you’ll need to check if you have enabled the Script Server. To do this, go to the administration view of your SAP HANA system in Studio. Under Configuration | daemon.ini |scriptserver, you’ll need to check if the value is 0 or 1. 0 indicates you have not enabled the server.
Below we can see the server is indeed enabled.
Enable the Script Server if it wasn’t already in the step above:
To enable, follow Note 1650957 – SAP HANA Database:Starting the Script Server.
I personally used method B in the above note.
Please note, it’s important to TAKE A COMPLETE BACKUP as mentioned in the note.
You may need to restart your HANA instance after going through the steps.
That’s it – you have configured PAL for use by SAP Predictive Analysis! To begin using it, perform the following steps:
- Connect in online mode:
2. Select an appropriate algorithm from the Predict tab (HANA Apriori for example):
3. Follow the usual steps you would to run a predictive analysis.
GREAT STUFF John!
Thanks a ton for putting this comprehensive, user-friendly posting together for us.
Should help make everyone's experience a lot easier. 😀
Cheers,
S
Thanks John,
There are also PAL videos by Philip Mugglestone (created by Julie Blaufuss ) about setting it up at http://www.saphana.com/community/implement/hana-academy#predictive-analytics-library
Regards,
Angad
Thank you, solves my issue. Exactly what I was looking for.
Nice and simple, lovely stuff
simple and easy steps to understand for novice..
How to get _SYS_AFL schema to my hana system
Hi Divya,
If you have already followed the instructions under "Check if the AFL is installed:" and could not find the _SYS_AFL in the results then AFL is not installed. In that case, please go to instructions under "
Install AFL:"
Regards
Angad
Hi Divya,
_SYS_AFL schema gets created once you installs .SAR file downloaded from the site mentioned above in this blog in section "Install AFL:".
Regards,
Arti
Very helpful. Nice blog
greetings
Cherry
Please note that as of SPS 6 and the new SAP Lifecycle Management (LM) tool, AFL can quite easily and nicely be installed and updated from the SAP HANA Studio Lifecycle Management perspective (similar to Software Update Manager (SUM) in earlier releases.
We are planning to record a video on LM this week. I will add the link as a comment.
Regards,
Denys van Kempen
SAP HANA Academy http://academy.saphana.com
Great ! Crisp and clear instruction! Thx. for sharing.
Hello John,
thanks for this, very informative. I did create the wrapper procedure. But I do not have a different user to assign the role to. I have connected to the HANA server from HANA studio with a user name SYSTEM. I connected from SAP PA through that user name. But when I try to run any predictive algorithm model I keep getting the below error
"A critical error occurred:
An error occurred while executing the query. Error details: SAP DBTech JDBC: [423]: liveCache error: [423] SYSTEM.AFL_WRAPPER_GENERATOR: line 38 col 1 (at pos 1443): liveCache error exception: liveCache error: registration finished with errors, see indexserver trace"
I ran the query
SELECT * FROM
"SYS"."AFL_AREAS" WHERE SCHEMA_NAME = '_SYS_AFL' AND
AREA_NAME = 'AFLPAL';
and it doesn't return anything. It instead says "Could not execute 'SELECT * FROM "SYS"."AFL_AREAS" WHERE SCHEMA_NAME = '_SYS_AFL' AND AREA_NAME = 'AFLPAL'' in 2 ms 394 µs . SAP DBTech JDBC: [259] (at 20): invalid table name: Could not find table/view AFL_AREAS in schema SYS: line 2 col 7 (at pos 20) "
But I am able to see that the wrapper procedure is there and I did get a confirmation in the log when the wrapper procedure was created.
I checked the SYSTEM user name and it doesn't have the role AFL__SYS_AFL_AFLPAL_EXECUTE. I gave a try at assigning it to the user SYSTEM but ofcourse there was an error the grantor and grantee are the same.
Wondering what could have gone wrong. I tried to find any clues in the index server as suggested by another user on SCN. But there don't seem any recent entries. Any idea what could be wrong? This is on the HANA system hosted via cloudshare.
regards
Bala
Hi Bala,
Since you have system access, perhaps you could try creating a new user and grant the rights to that user?
Thanks,
John
Hi All!
I am trying to give the privilege to the root user (in my case it is system) through GUI as well as the following command:
grant AFL__SYS_AFL_AFLPAL_EXECUTE to MyHANAUser;
Both the UI and SQL console are giving the same error:
Could not modify user 'SYSTEM'. SAP DBTech JDBC: [7]: feature not supported: grantor and grantee are identical
In my case, I am trying to grant access to System user. Do I need to access some other user first and then grant privileges? Id so, then are there any other root users besides system user.
Thanks!
Hi John,
System user doesn't need the privilege as by default the privilege is given to System. since you logged in as system user and granting the privilege to system itself, that's why the error is coming.
Regards,
Arti
Hi Arti!
It is required to have the role AFL__SYS_AFL_AFLPAL_EXECUTE set in the system first.
Running the necessary script file under /hanamnt folder did the trick for me.
Nice...
I change only System value from 0 to 1. but How to enable the Script Server for host?
Thanks
Somnath A. Kadam
Hi Somnath,
I'm not clear what you are asking? Did you follow Note 1650957 - SAP HANA Database:Starting the Script Server? This note should answer your question.
Thanks,
John
Hi John,
I am trying to install the predictive analysis rds package for finance and am getting errors while activating the stored procedure sp_pal_final_setup.
This procedure becomes available when we import delivery unit HCO_RDS_PAN_FIN_PAL.tgz.
When I try to validate/activate this procedure through SYSTEM user, it gives me an sql error and insufficient privilege.
When I try to validate this procedure through another user, it allows validation successful but gives me sql error and insufficient privilege when I activate it.
Can you please provide some inputs on it if you have encountered any errors on the above package?
HANA studio/client/db/afl is currently installed to rev 82.
Predictive Analysis 1.18 installed alongwith with R.
Kindly advise.
Regards,
Saritha K
Thanks for posting this, instructions worked for me with HANA Rev 82.
Hi Former Member,
In the first step I am not able to log in .. log in as root means which username and password I have to enter.
Thanks,
Miyan