In this session we will see how to create a graphical calculation view in SAP HANA.
Prerequisite:
The scenario is same that we used for graphical calculation view. Let us see it once again.
There is an analytic view that provides Product ID, Region and Sales Amount. We will make a join with PRODUCT table to get Product Name.
Further we will calculate Discount and Net Amount using following formula:
If Product Name = “Purse” then Discount = 20%; Else Discount = 10%
Net Amount = Sales Amount – Discount
Steps for Creating Scripted Calculation View:
1. Right click on the package and choose New -> Calculation View.
Prerequisite:
- You have SAP HANA Studio installed on your machine.
- You have added an SAP HANA system in HANA Studio.
- You have created the Analytic view mentioned in session 4.8
The scenario is same that we used for graphical calculation view. Let us see it once again.
There is an analytic view that provides Product ID, Region and Sales Amount. We will make a join with PRODUCT table to get Product Name.
Further we will calculate Discount and Net Amount using following formula:
If Product Name = “Purse” then Discount = 20%; Else Discount = 10%
Net Amount = Sales Amount – Discount
Steps for Creating Scripted Calculation View:
1. Right click on the package and choose New -> Calculation View.
2. Enter a name and a description. Select Subtype as “SQL Script”.
3. You will see 2 blocks in the editor: Semantics and Script_View.
Semantics provides the summary of output structure, editor view of output objects and general view properties.
Select the Default Schema as the schema which contains tables to be used. In our example it is “SAP_HANA_TUTORIAL”.
Script_View is the place where we specify SQL script.
var1 = SELECT “REGION_NAME”, “SUB_REGION_NAME”, “PRODUCT_ID”, sum(“SALES_AMOUNT”) AS “SALES_AMOUNT”
FROM “_SYS_BIC”.”sap-hana-tutorial/AV_SALES”
GROUP BY “REGION_NAME”, “SUB_REGION_NAME”, “PRODUCT_ID”;
var2 =SELECT T1.”REGION_NAME”, T1.”SUB_REGION_NAME”, T1.”PRODUCT_ID”, T2.”PRODUCT_NAME”, T1.”SALES_AMOUNT”
from :var1 AS T1
INNER JOIN
PRODUCT AS T2
ON T1.PRODUCT_ID = T2.PRODUCT_ID;
var3 = SELECT “REGION_NAME”, “SUB_REGION_NAME”, “PRODUCT_ID”, “PRODUCT_NAME”, “SALES_AMOUNT”,
CASE
WHEN “PRODUCT_NAME” = ‘Purse’ THEN 20
ELSE 10
END AS “DISCOUNT”
FROM :var2;
var_out = select “REGION_NAME”, “SUB_REGION_NAME”, “PRODUCT_ID”, “PRODUCT_NAME”, “SALES_AMOUNT”,
“SALES_AMOUNT” – “DISCOUNT” AS “NET_AMOUNT” From :var3;
4. Right click on Columns in Output pane and click on “Create Target”.
5. This is the place where we specify the output columns. The output columns are the columns mentioned in last statement of the SQL script. Specify the name and data types as shown in figure below.
6. Select Semantics block. Now we need to specify which columns are attributes and which columns are measures. Click on the “Auto Assign” icon or click on the Type icon to specify Attribute/Measure.
Specify REGION_NAME, SUB_REGION_NAME, PRODUCT_ID, PRODUCT_NAME as attributes and SALES_AMOUNT and NET_AMOUNT as measure.
7. Right-click on your calculation view and choose “Data Preview”. After that, you can browse through the tabs named Raw Data, Distinct Values, and Analysis.
Analysis tab:
Congratulation!! You have successfully created your first scripted calculation view.
No comments:
Post a Comment