Skip to Content
Previous

Predictive Analytics Using SQL Scripts

By Adrian Plata

This tutorial series will cover two approaches to a sample project utilizing the predictive analytics capabilities of SAP HANA, express edition. This tutorial will show you how to use SQL scripts with the predictive analytics library.

You will learn

How to use predictive analytics with a pre-written SQL script.

Details

This tutorial describes one of many SAP HANA Academy Predictive Analysis Library tutorials. For more tutorials on using Predictive Analysis Library, see the SAP HANA Academy YouTube Playlist.


Prerequisite: Memory Cleanup (VM only)

Before running the script on your VM installation, you will need to clean up the memory processes.

Open a command line to your VM and run the following commands:

hdbcons "resman shrink"
hdbcons "mm gc -f"
hdbcons -e hdbnameserver "resman shrink"
hdbcons -e hdbnameserver "mm gc -f"
Step 1: Copy the First SAP HANA Academy Script

Go to the SAP HANA Academy GitHub site. Click on the PAL repository. From there, navigate to Code Snippets -> PAL 110 Time Series - Auto ARIMA Model.sql.

GitHub ARIMA Model

Click on Raw.

GitHub ARIMA Model Raw

Use the keys Ctrl + a to select all, and Ctrl + c to copy the script.

Step 2: Run the Model SQL Script

Open an SQL command line to your tenant database through your new user. Paste the script and click the Execute icon Execute to run the code. The Result page appears.

Note:
This script assumes the user name is DEVUSER. If you created a user with a different name, you will need to modify the script by replacing the instances of DEVUSER with your user’s name.

Results Page

Note:
The first time you run this script, you may receive the errors Could not execute ‘DROP TYPE …’ and the error Could not execute ‘DROP TABLE “#PARAMS”’. This is expected behavior as these elements are created in the script. The errors will not affect output.

This builds the model used for predictive analysis.

Step 3: Copy the Second SAP HANA Academy Script

Go back to the SAP HANA Academy GitHub site. Click on the PAL repository. From there, navigate to Code Snippets -> PAL 111 Time Series - Auto ARIMA Predict.sql.

GitHub ARIMA Predict

Click on Raw.

GitHub ARIMA Predict Raw

Use the keys Ctrl + a to select all, and Ctrl + c to copy the code.

Step 4: Run the Predict SQL Script

Back in your command line, paste the script and click the Execute icon Execute to run the code. A new Result page appears. This creates a predictive analysis table from the model you created earlier.

Note:
This script assumes the user name is DEVUSER. If you created a user with a different name, you will need to modify the script by replacing the instances of DEVUSER with your user’s name.

Results Page
Step 5: View the Results

To preview the data, go to your user login to your tenant database and cascade down to Catalog -> USER -> Views. Right-click on V_Results and click Open Data Preview.

Open Data Preview

From there, you can view the raw data or create charts under the Analysis tab.

Data Preview
Info: Script Information

For more information about the scripts, watch the SAP HANA Academy videos PAL: 110. Time Series - Auto ARIMA Model and PAL: 111. Time Series - Auto ARIMA Predict.

Next Steps

Updated 12/12/2017

Time to Complete

10 Min

Beginner

Tags

Next
Back to top