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.



Calc_View_Graphical.jpg

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.



CE FUNCTION POP UP.jpg


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.


CE_FUNC Script.jpg


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’.

Repository Scrren.jpg

Import workspace.jpg

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


right click choose other.jpg

Table Function.jpg


TBF_GET_SALES_DATA.jpg

Step 3: Write your code.


TBF_GET_SALES_DATA Code.jpg

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 belowAdd TBF Table function to Calc View.jpg

Table Function Added - Final step.jpg

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.

Procedure.jpg

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.

Procedure in Calc View.jpg

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.

Final Output.jpg

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Florian Pfeffer

    Hello Anik,

    thx for sharing your experiences.

    Maybe some hints for your future developments:

    • Scripted calculation views are a kind of obsolete technique. The future way to go is to use graphical calculation views. For scripted parts table functions have to be used which can be consumed in a graphical calc. view. SAP provides migration tools to migrate scripted calc. views to graphical ones with table functions.
    • Since SPS09 it is not recommended anymore to use CE functions. Check Calculation Engine (CE) Functions – Rest in Peace | SCN and New SQLScript Features in SAP HANA 1.0 SPS9.
    • For point 3, step 1 it seems that the obsolete “.procedure” form is used. Please use only the newer “.hdbprocedure”.

    Regards,

    Florian

    (0) 

Leave a Reply