Real Time Analytics with SAP HANA

SQL is used to retrieve, store, and manipulate data in the database. SQL can be studied under three subheads:

These subheads are explained as follows:

The followings are the elements of SQL:

In the upcoming chapters, we will learn how to work with SAP HANA studio and open SQL editor, so as to complete the concepts. I will show you how we work with the preceding SQL concepts. For our examples and exercises, we will use the following tables. We will create more tables in further chapters as we progress.

The following table shows you the sales_facts:

The following table shows you the CUSTOMERS data:

The following is a REGION table:

The following table shows you details of the PRODUCT table:

Let's see how we can create the preceding tables in SAP HANA:

In SQL, the editor of our schema needs to execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION

If we miss this step, an error will occur when you activate your views later.

SQLscript is a collection of extensions in Structured Query Language (SQL). The main motivation for SQLscript is to push data intensive application logic into the database, which was not being done in the classical approach where the application logic is mostly executed in an application layer.

We have the following extensions for SQLscript:

Let's do a comparative study between an SQLscript in SAP HANA and classical SQL queries to find out what the point of differences are, as shown in the following table:

The following figure shows you a graphical comparison of the classical approach and the SAP HANA approach:

The following syntax is used to create procedure via the SQL editor:

CREATE PROCEDURE {schema.}name 
            {({IN|OUT|INOUT} 
                        param_name data_type {,...})} 
            {LANGUAGE <LANG>} {SQL SECURITY <MODE>} 
            {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS 
BEGIN 
... 
END

The parameters are for:

Let's create a procedure where we will pass discount as the input parameter and get the sales report as the output parameter. We use the same tables that we created previously:

CREATE PROCEDURE HANA_DEMO."PROC_EU_SALES_REPORT"(
            IN DISCOUNT INTEGER,
            OUT OUTPUT_TABLE HANA_DEMO."EU_SALES" )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
Pvar1 = SELECT T1.REGION_NAME, T1.SUB_AREA, T2.PRODUCT_KEY, T2.AMOUNT_SOLD
            FROM HANA_DEMO.REGION AS T1
            INNER JOIN
            HANA_DEMO.SALES_FACT AS T2
            ON T1.REGION_KEY = T2.REGION_KEY;

Pvar2 = SELECT T1.REGION_NAME, T1.SUB_AREA, T1.PRODUCT_KEY, T1.AMOUNT_SOLD, T2.PRODUCT_NAME
            FROM :Pvar1 AS T1
            INNER JOIN
            HANA_DEMO.PRODUCT AS T2
            ON T1.PRODUCT_KEY = T2.PRODUCT_KEY;

OUTPUT_TABLE = SELECT SUM(AMOUNT_SOLD) AS AMOUNT_SOLD, SUM(AMOUNT_SOLD - (AMOUNT_SOLD * :DISCOUNT/ 100)) AS NET_AMOUNT,
            PRODUCT_NAME, REGION_NAME, SUB_AREA
            FROM :Pvar2 
            GROUP BY PRODUCT_NAME, REGION_NAME, SUB_AREA;
END;

We can call the previously created procedure with the following CALL statement:

CALL HANA_DEMO."PROC_SALES_REPORT" (8, null);

You can see the created procedure below our schema under the Procedure... folder.

Choose the package in which you want to create the procedure and right-click on it.

A new screen will pop up; fill in the details and click on Confirm:

The SQL console opens with default syntax; we need to put our logic in between BEGIN and END.

The following is a sample logic with which I am creating the Procedure:

On the left-hand side of the screen, you can see the output pane:

Click on it and select New…:

Define the columns which we used in the preceding procedure:

Similarly, perform the same steps for input parameters as well:

Now the procedure is ready to be called via the CALL statement.

Once we build our concept about different views, then one question that will definitely come to our mind is, should we use calculation views (not yet discussed) or procedures. We will discuss this once we have discussed the calculation view in Chapter 5, Creating SAP HANA Artifacts – Analytical Privileges and Calculation Views.