Friday, 12 January 2018

Installing the Automated Predictive Library (APL) on SAP HANA Express 2.0

The Automated Mode of SAP Predictive Analytics allows the efficient creation of powerful predictive models. Installing the Automated Predictive Library (APL) on SAP HANA makes it possible to train these models directly in SAP HANA. The data does not have to be extracted into an external system, the architecture is simplified and agility is increased.

This blog is written with SAP Predictive Analytics 3.3 and SAP HANA Express 2.0 SPS02.

Prerequisites


It is assumed that you have a SAP HANA Express 2.0 up and running. You have also been able to establish a connection from the SAP HANA studio.

In case you do not have such a system available yet, you can follow the SAP HANA Express documentation. To follow the steps in this blog it is sufficient to download the “Server only virtual machine”. Some of the steps listed below are flagged for “Server + Applications VM Only”, these are not required to be able to install the APL:

  • Installing SAP HANA 2.0, express edition (Virtual Machine Method) : All steps
  • Start Using SAP HANA 2.0, express edition (Virtual Machine Method) :
    • From “Step 1: Start your VM.”
    • to “Step 20: Optional Configuration: Test your Installation using the HANA Eclipse Plugin”. When installing the development tools inside Eclipse, it is sufficient to select only the option “SAP HANA Administration (Developer Edition)” under “SAP HANA Tools”.
You should be seeing something similar to this screen. Notice that the only installed plug-in is the Application Function Library (AFL). If the APL was installed, we should see it there.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Download the APL


The APL can be downloaded from the Software center. You find it by following this path:

◈ INSTALLATIONS & UPGRADES
◈ By Alphabetical Index
◈ P
◈ SAP Predictive Analytics
◈ SAP PREDICTIVE ANALYTICS 3
◈ COMPRISED SOFTWARE COMPONENT VERSIONS
◈ PRED ANALYTICS APL 3 FOR HANA2

Before downloading, make sure to set the drop down on that page to the correct operating system. The APL has different downloads for x86 and PowerPC.

Then download the latest version, currently: “APL 3.3. PRED ANALYTICS APL 3.3 FOR HANA2”. The file itself is called SAPPAAPL3003_0-80002689.TGZ.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Copy the APL onto SAP HANA


The file you have just downloaded needs to be copied onto the SAP HANA system. I am using a program called WinSCP, but plenty of other options are available.

Connect from WinSCP to the SAP HANA operating system, logging on with the ‘hxeadm’ user and the password you had specified during the setup of SAP HANA Express.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Copy the APL you had downloaded into the /tmp folder on the SAP HANA system.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Once the file is copied, it needs to be extracted. Still in WinSCP, select the file on the right hand side, on the SAP HANA system. Right-click the file, select “File Custom Commands”, then “UnTar/GZip…”. Confirm all prompts and the file is extracted into a newly created folder.

Install APL


The actual installation of the APL can be started from within the SAP HANA Administration Console. Right-click on your System connection, choose “Lifecycle Management”, then “Platform Lifecycle Management” and finally “SAP HANA Platform Lifecycle Management”.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

You will be prompted for a password. Logon with the hxeadm user of the Linux operating system. This logon is NOT for your SAP HANA user.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Click into “Install or Update Additional Components”. Go into “Add Software Locations…” and choose the “installer” folder of the extracted APL file.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Confirm the selection with “Add”. You should see a screen that the “Automated Predictive Library” has been detected in that folder. Continue with “Next”. Confirm that you want to install this library.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

On the next screen enter the passwords for both the Linux operating system user as well as for your SAP HANA user. Continue and the install will start and complete in a few minutes.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

The APL is now installed! It will show as new plugin on the Administration panel of the SAP HANA Administration Console. If this screen was open during the install, you need to close and reopen it to see the new component.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Configure APL


The APL is installed, but not quite ready yet for use. It still needs to be configured. We will configure the system so that we can use the APL on the HXE tenant database, which comes with SAP HANA Express 2.0.

To run the APL the script server needs to be activated for the database. Open an SQL Console for the SYSTEMDB under the SYSTEM user and execute this statement:

ALTER DATABASE HXE ADD 'scriptserver';

Now make sure you have a connection to HXE in the SAP HANA Administration Console. Just specify the tenant database “HXE” when adding the system.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

Let’s create a user, under which we call the APL. I name the user SAPPA. Grant this user the role APL_EXECUTE.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

The role can also be assigned to an existing user, ie with this SQL statement:

call _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.pa.apl.base.roles::APL_EXECUTE','SAPPA');

Test APL


The APL is installed and configured. As final step we should verify everything is working well. The second line in the following SQL syntax has to be called by the user that wants to call the APL The first line switched the code execution to that user. Stating the obvious, you need to replace the Xs with the user’s real password.

CONNECT SAPPA PASSWORD XXXXXXXXX;
call "SAP_PA_APL"."sap.pa.apl.base::PING"(?)

Only if the above statement executes successfully, the APL is ready!

Bonus Material


If you just wanted to install the APL, then you are all set. Should you want to see how SAP Predictive Analytics leverages that APL, then read on. With SAP Predictive Analytics you get a graphical interface to train predictive models, without the need of using SQL.

This final part of the blog is kept rather short, it is assuming you are already fairly familiar with SAP HANA and SAP Predictive Analytics. Since the user is interacting with SAP HANA, ie to upload data, the user has been granted additional rights. To help you test this quickly, you could assign all roles visible in the SAP HANA Administration Console to the user.

On the machine you have SAP Predictive Analytics installed, you have to create a 64 bit ODBC source to SAP HANA Express. This uses the HANA ODBC driver that is installed by the SAP HANA Client, which is part of the SAP HANA Express download. In the ODBC Administrator, the driver is called HDBODBC.

The Server:Port setting for you is probably something like: hxehost:39015

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

To connect directly to HXE, you can go into “Settings” and add a property called DATABASENAME, pointing to the HXE database.

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning

We also need some data in SAP HANA Express to train a model. You can use your own data or test with sample data of SAP Predictive Analytics. There are many ways to upload data. For such a quick test I prefer to use SAP Predictive Analytics itself. Make sour your installation of SAP Predictive Analytics has the same version as the APL you installed. Open the tool and in the menu select “Toolkit”, followed by “Perform a Data Transfer”.

This options allows to easily move data. You can transfer all columns from that file through ODBC into SAP HANA:
C:\Program Files\SAP Predictive Analytics\Desktop\Automated\Samples\Census\Census01.csv

Stay in SAP Predictive Analytics to train a model. Go into “Create a Classification/Regression Model”. Select the ODBC source and choose the table that holds the data from the Census file.
Set the “class” column as target variable. Exclude the columns KxIndex and fnlwgt.

Start the model training and the status messages should show the term “APL”. The model is being trained directly within SAP HANA, without extracting the data!

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning