SAP HANA Scripted Calculation View

Hello Everyone, When I was trying to learn HANA scripted calculation view, I had to spend lot of time in creating tables, views and data records in order to get my hands dirty and learn how the scripted calculation views works. What I am trying to do here is gather all of these information in this blog so that one can create their own tables, data and finally scripted calculation Views in the following ways.

We will Discuss Calculation View using.

1. SQL Script – Using CE Functions

2. Table Functions

3. Procedure

There is a basic creation of graphical calculation view shown here in saphanatutorial.com. Please use this link to build the tables REGION, SALES and PRODUCT. Also create records into these tables using the same link. This is a wonderful site to learn HANA from. Here is an overview of how the graphical calculation view will look like.

Please note here we will not learn how to create the above shown graphical view but learn how to create this calcualtion view using SQL script.

1. SQL Script – Using CE Functions

Step1: Create a new calculation view of the type script as shown below.

Step 2: Here start writing the SQL script code. We will use CE functions here. I will not spend time in explaining what each CE function does. It has been wonderfully described here. Please refer that. I will show below how the code looks like. Also make sure you have created Columns on the right hand side for the VAR_OUT to export the tabular data. Please see below.

Please find the code below to try it yourself.

/********* Begin Procedure Script ************/

BEGIN

   region = CE_COLUMN_TABLE(“HANA_TUTORIAL”.“REGION”,

             [      “REGION_ID” ,

                           “REGION_NAME” ,

                           “SUB_REGION_NAME”]);

   sales =  CE_COLUMN_TABLE(“HANA_TUTORIAL”.“SALES”,

               [    “REGION_ID”,                       

                             “PRODUCT_ID”,

                             “SALES_AMOUNT” ]);

   product = CE_COLUMN_TABLE(“HANA_TUTORIAL”.“PRODUCT”,                                        

             [  “PRODUCT_ID”,

                         “PRODUCT_NAME” ]);

   join1 = CE_JOIN(:region, :sales,

           [“REGION_ID”],

           [“REGION_ID”,

                      “SUB_REGION_NAME”,

                      “PRODUCT_ID”,

                      “SALES_AMOUNT” ]);

   join2 = CE_JOIN(:join1, :product,

           [“PRODUCT_ID”],

           [“PRODUCT_ID”,

                      “PRODUCT_NAME”,

                      “REGION_ID”,

                      “SUB_REGION_NAME”,

                      “SALES_AMOUNT”]);

   p_out = CE_PROJECTION(:join2,

           [“PRODUCT_ID”,

                      “PRODUCT_NAME”,

                      “REGION_ID”,

                      “SUB_REGION_NAME”,

                      “SALES_AMOUNT”,

            CE_CALC(‘”SALES_AMOUNT”*0.3’,DOUBLE) as “MARGIN” ]);                   

END;

/********* End Procedure Script ************/

2. SQL Script – Using Table Functions

Step 1: Please go to developement Perspective first. Choose the tab ‘Repository’ and right click on the content package and ‘Import remote workspace’.

Step 2: Rightclick on the package ‘New->Other’ Search for and choose ‘Table Function’. Click next and Put the name as shown below

Step 3: Write your code.

Code:

FUNCTION “SYSTEM”.“P1942191456::TBF_GET_SALES_DATA” ( )

       RETURNS TABLE (

        “PRODUCT_ID” int,

        “PRODUCT_NAME” varchar(100),

        “REGION_ID” int,

        “SUB_REGION_NAME” varchar(100),

“SALES_AMOUNT” double, “MARGIN” double )

       LANGUAGE SQLSCRIPT

       SQL SECURITY INVOKER AS

BEGIN

/*****************************

       Write your function logic

*****************************/

RETURN select b.“PRODUCT_ID”, c.“PRODUCT_NAME”, a.“REGION_ID”, a.“SUB_REGION_NAME”,   b.“SALES_AMOUNT”,

get_margin(b.“SALES_AMOUNT”) as “MARGIN”

from

“HANA_TUTORIAL”.“REGION” as a

inner join

“HANA_TUTORIAL”.“SALES” as b

on a.“REGION_ID” =  b.“REGION_ID”

inner Join “HANA_TUTORIAL”.“PRODUCT” as c

on b.“PRODUCT_ID” = c.“PRODUCT_ID”;

END;

I have added a scaler user defined function called ‘GET_MARGIN’ in the above code. You can refer to how to build a scaler user defined functions in this wonderful blog written by Rich Heilman here. Please find the code below.

/* Begin of Code

create function get_margin(im_var1 double)

returns result double

language SQLSCRIPT

SQL SECURITY INVOKER as

BEGIN

result := :im_var1 * 0.3;

end;

/* End of Code

Step 4: Now create a claculation view of the type graphical. Put the name and description. Keep everything as default and in the ‘Aggregation’ node click in add an object as you normally do. Here you will find the Table function you have just created as a table. Please see below

You have now added the table function like you do for tables or views. Add the fields to output and in the ‘Semantics’ you can choose ‘SALES_AMOUNT’ and ‘MARGIN’ as measures and rest as attributes.

3. SQL Script – Procedure

Step 1: Create a procedure similarly as we did in SQL script using CE Functions in the biginning of this blog. Under Catalogue->your_schema->procedure, right click and create a new procedure. Also create the output parameters, let’s say P_OUT and make sure it has all the fields you would want in the final CE_PROJECTION function. In turn structure of P_OUT should be equivalent to structure of VAR_OUT in the final scripted calculation view. This will get clearer in the next step.

The above code is an exact copy as shown in ‘SQL Script – Using CE Functions’ above

Step 2: Create a new Calculation View of the type script. And in the script area put the code as shown below. Also donot forget to create the output structure.  It is shown in the below image on the right hand side under Output. This structure is the ‘Var_Out’ structure and is equivalent to the P_OUT we had created in the above step in the procedure. (Assign the correct datatypes for the fields). Lastly go to schema ‘ _SYS_BIC -> Procedure’ and drag the Procedure in the scripting workbench, as shown below.

Finally the output for each calculation views will be the same as we tried to replicate the same output using different methods. This is the Analysis view.