SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Course Index
Close
X
SAP HANA Basics
Course Overview
1. Introduction to SAP HANA
2. SAP HANA Architecture
3. SAP HANA Studio
4. SAP HANA Modeling
5. Reporting on SAP HANA
6. What is Next?
<< Previous
Next >>

4.11 Build Your First Scripted Calculation View

In this session we will see how to create a graphical calculation view in SAP HANA.

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

Scenario:

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:

      Raw Data tab:

Congratulation!! You have successfully created your first scripted calculation view.

Click here to go to Next Chapter.

Connect to The SAP HANA Forum to ask any question, provide feedback or share information.

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy