Skip to Content

Predictive Analytics Using SQL Scripts

Beginner
10 min.
SAP HANA, express edition, Tutorial, Beginner
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

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.

  • Step 1

    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:

    bash
    Copy
    hdbcons "resman shrink"
    bash
    Copy
    hdbcons "mm gc -f"
    bash
    Copy
    hdbcons -e hdbnameserver "resman shrink"
    bash
    Copy
    hdbcons -e hdbnameserver "mm gc -f"
  • Step 2

    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 3

    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 4

    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 5

    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 6

    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
  • Step 7

    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.

Back to Top
Back to top