SAP Predictive Analytics is a tool that offers predictive capabilities to analyze data from various sources and apply predictive algorithms to get insights from the data. The functionality is enriched with the integration of R Language. SAP Predictive Analytics and R together gives a very good pitch for the game.

The tool can take data directly from various resources, including SAP HANA, SAP BI/BW systems; and with the HANA PAL (Predictive Analysis Library) functions, we can do better analysis of the data and predict more precisely. The business use cases where these algorithms can be applied keeps increasing day by day. and one such situation is that when we want to export the data ( predicted values ) out of the tool.

In this blog, we will be taking a business use case to export content from SAP Predictive Analytics to an external database( in our case, it will be a MySQL database). The blog will cover only the system connection parameters, we do not discuss about the algorithms or the data here.

SAP Predictive Analytics Version: 2.0.0

Step 1: Check if the database driver is available

FIle.png






To connect to the external system, we need the respective database driver to be installed in your installation of SAP Predictive Analytics.

Follow the steps to see if the respective database driver is installed in your system.


From the home screen : File -> Preferences ( You also use Ctrl+P)


From the preferences menu, select the SQL Drivers menu and scroll down to the ORACLE category, where you can find the MySQL5 – JDBC driver option available. The icon will be green, if the driver is already available, else click on the “Install Drivers” button on the top right corner of the window.

You need to manually download the driver (mysql-connector-java-5.1.13-bin.jar)

Use your Oracle ID to download the original version of the driver, use “Install Driver” button to install the driver.

You may have to restart the SAP Predictive Analysis application.

Once you have restarted, you can navigate back to the preferences tab to see if the driver is properly installed.

Driver.png

Step 2: Use Data Writer


Now that we have the JDBC driver installed, SAP Predictive Analytics can now detect and communicate to any MySQL database.


Lets move on to the “designer view” of the “Predict” tab in the tool. Here click on the Data Writer category on the right side; select the JDBC driver from the database writers option.

“Predict” tab –> Data Writers -> Database Writers -> JDBC Writer.

db writer.png

Double click on the icon to configure the settings.

In the configure settings tab, we need to key in the following details.

jdbc writer.png

Database Type: It is MySQL in our case (You will get a drop down menu here)

Database Driver Path : Refer to the file location where you have stored the driver that was downloaded earlier.

Port Number: The port number allotted by the system administrator for this communication (for example here it is port 3306).

Database Name: You must specify the name of the database where the data must be stored.

Username & Password: Make sure that the username has access to the database and has privileges to create a table and store entries in it.

Table Name: You need to specify the Table Name – SAP Predictive Analysis will create a table with this name in the database provided by earlier.

If the task is going to be a repetitive one, you can select the “Overwrite, if exists” check-box. This is very much helpful to update the table with the latest predicted results.


Once this is done, you can run your analysis, and check the MySQL database to see if the results are exported there.

The same methodology can be applied to any database that is supported by SAP Predictive Analysis Tool.

/wp-content/uploads/2015/02/run_652390.png

This is the result that was automatically exported to MySQL by SAP Predictive Analytics for the R-Apriori algorithm that was used in the sample run.

/wp-content/uploads/2015/02/result_652406.png



Let me know your queries in the comments section.

Thank you.


– Tamilnesan G


To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Ivan Girya

    Thank you for your post, Tamilnesan.

    Have you tried using JDBC writer to send results to HANA table?

    How should we write the table name in this case?

    Seems like the regular HANA way “SCHEMA.TABLE_NAME” causes syntax error .

    Ivan

    (0) 
    1. M. van Foeken

      Hi Ivan,

      When you select SAP HANA as the target database of the JDBC Writer component you can specify the “SCHEMA” under ‘Database Name’. In the “Target Table Information” section at the bottom you only specify the “TABLE_NAME” without the target schema.

      Hope this helps!

      With kind regards,

      Martijn

      (0) 
      1. Ivan Girya

        Thank you, Martijn.

        Still, I’ve got an error saying:

        SAP DBTech JDBC: [257] sql syntax error: incorrect syntax near “”””: line 1 col 23 (at pos 23).

        The error is the same in both cases: whether I use quotes around the table name or not.

        (0) 
        1. M. van Foeken

          Hi Ivan,

          Can you post a screenshot of the JDBC writer component configuration? And also from the complete analysis in PA (source to target actions).

          With kind regards,

          Martijn

          (0) 
        2. Pierpaolo VEZZOSI

          Hello,

          I reproduce the exact error if I put quotes around the database name (i.e. the Schema).

          Please try not to use any quote in the database name (the schema) and the table within the JDBC component configuration.

          2 cents

          Ppaolo

          (0) 

Leave a Reply