Exporting Predictive Analysis (Desktop Version) results to MySQL Database
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
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.
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.
Double click on the icon to configure the settings.
In the configure settings tab, we need to key in the following details.
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.
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.
Let me know your queries in the comments section.
– Tamilnesan G