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
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing
+
-
Predictive Analysis

Graphical Calculation View in 10min

In the article SAP HANA Modeling Introduction and SAP HANA Calculation View we explained the basics of SAP HANA data modeling.
Calculation view can be defined as either graphical views or scripted views. In this article we will learn how we can create graphical calculation view.

Prerequisite:
Scenario:
Suppose there are 3 tables PRODUCTS, REGION and SALES.



We need to find out
    • What are the sales value for "Asia" region?
    • What are the total sales value for "Shirts"?
    • What are the total sales value for "Europe" for "Jackets"?
Let us create a graphical calculation view to get answer of the above mentioned questions.

Create new tables in SAP HANA and fill them with data:
  1. Open HANA Studio and expand the SAP HANA system.
  2. Go to your schema. Right-click on your schema and select SQL editor.
    SAP HANA Studio
    Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query.
    create schema <schema_name>;
  3. Copy and paste the below script in SQL editor and execute.

    ----REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME

    ---- Create Product table
    create column table "<YOUR SCHEMA>"."PRODUCT"(
          "PRODUCT_ID" INTEGER,
          "PRODUCT_NAME" VARCHAR (100),
          primary key ("PRODUCT_ID")
    );

    insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse');
    -- Create Region table
    create column table "<YOUR SCHEMA>"."REGION"(
          "REGION_ID" INTEGER,
          "REGION_NAME" VARCHAR (100),
          "SUB_REGION_NAME" VARCHAR (100),
          PRIMARY KEY ("REGION_ID")
    );

    insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North-America');
    insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South-America');
    insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India');
    insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan');
    insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany');

    -- Create Sales table
    create column table "<YOUR SCHEMA>"."SALES"(
          "REGION_ID" INTEGER ,
          "PRODUCT_ID" INTEGER ,
          "SALES_AMOUNT" DOUBLE,       PRIMARY KEY ("REGION_ID", "PRODUCT_ID") );

    insert into "<YOUR SCHEMA>"."SALES" values(100,1,100);
    insert into "<YOUR SCHEMA>"."SALES" values(100,2,90);
    insert into "<YOUR SCHEMA>"."SALES" values(100,5,85);
    insert into "<YOUR SCHEMA>"."SALES" values(200,2,80);
    insert into "<YOUR SCHEMA>"."SALES" values(200,1,75);
    insert into "<YOUR SCHEMA>"."SALES" values(300,3,85);
    insert into "<YOUR SCHEMA>"."SALES" values(400,4,75);
    insert into "<YOUR SCHEMA>"."SALES" values(500,1,65);
    insert into "<YOUR SCHEMA>"."SALES" values(500,2,65);
  4. After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing.
    SAP HANA Studio

Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:
GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
If you miss this step, an error will occur when you activate your views later.
Click here to know more about this.

Steps for creating calculation view:

  1. Open HANA Studio and make sure you are in Modeler perspective. Right click on the package and choose "Calculation View"
  2. Enter a name and a description. Select Subtype as "Graphical" and Data Category as "Cube". Then click on "Finish".
    SAP HANA Calculation View
  3. You will see the graphical editor as shown in image below.
    SAP HANA Calculation View
  4. Click on the "Join" symbol and drag it in the scenario area to create Join block. Click on "+" sign of "Join" and add REGION tables.
    SAP HANA Calculation View
  5. Similarly add SALES table.
  6. Select the REGION_ID from REGION table. Drag it and drop it on the REGION_ID of SALES table. Join type can be changed in the Properties tab. Let it be Inner Join as of now.
    SAP HANA Calculation View
  7. Add REGION_NAME, SUB_REGION_NAME, PRODUCT_ID AND SALES_AMOUNT to the Columns by right clicking and clicking on "Add To Output". Alternatively you can also click on the grey color circle before the column name to add it to output.
    SAP HANA Calculation View
  8. Click on the "Join" symbol again and drag it in the scenario area to create another Join block. Connect the previous Join block to new Join block with the arrow.
    SAP HANA Calculation View
  9. Click on "+" sign of new Join block and add PRODUCT table.
    SAP HANA Calculation View
  10. Select the PRODUCT_ID from Join_1. Drag it and drop it on the PRODUCT_ID of PRODUCT table. Select the Join type as Inner Join.
    Add REGION_NAME, SUB_REGION_NAME, PRODUCT_ID, SALES_AMOUNT and PRODUCT_NAME to the Columns by right clicking and clicking on "Add To Output".
    SAP HANA Calculation View
  11. Add the Join block "Join_2" to "Aggregation" block by arrow. Add all the columns to output.
  12. Select Semantics. Now we need to specify which columns are attributes and which columns are measures. Click on the "Auto Assign" button to do it automatically.
    Alternatively you can also specify the Attribute/measure by clicking on Type down-arrow. Select SALES_AMOUNT as measure and rest of the column as attribute.
    SAP HANA Calculation View
  13. Activate the calculation view. 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:
    SAP HANA Calculation View

    Raw Data tab:
    SAP HANA Calculation View
Congratulation!! You have successfully created your first Graphical Calculation View.

Continue reading:


Support us by sharing this article.



Explore More
Close X
Close X

8 thoughts on “SAP HANA Graphical Calculation View

  1. Nilesh Aherrao says:

    Useful Information.

    Please let me know how you create SALES TABLE, I mean you have used (“REGION_ID”, “PRODUCT_ID”) as a PRIMARY KEY.

    It works but when I tried to use (“REGION_ID”, “PRODUCT_ID”) as a FOREIGN KEY, I received an error message.

    In a table one can use only one PRIMARY KEY but here it works for 2 PRIMARY KEY as well.

    Thanks,
    Nilesh Aherrao

    • Admin says:

      Hi Nilesh,
      In HANA, you cannot use the syntax “as FOREIGN KEY”. Foreign key is just a logical concept but in HANA you cannot force it.

      If you just copy the SQL scripts and run it will work. Did you try that?

  2. RAMs says:

    Thanks for sharing

  3. John says:

    how to do stacked bar chart by products or regions ?
    nothing happens if I select Vertical stacked bar in comparison to ordinary barchar.

    Thank you

  4. Hari Kumar says:

    Hi,
    Can any one tell me the importance of the different nodes(Join, Union, Projection & Aggregation) found in building Calc views?

    Thanks

  5. Harikumar says:

    Hello Sir
    The links explain definition of Join & union.
    I want to understand the NODES & their importance in building a calculation view.
    Can you please suggest.

  6. Prathamesh P says:

    1. I have created Calculation view in HANA studio, for which the values which are declared as measure are rounded off and even i am not able to see decimal places for the same.

    2. when i compare this values with SE16n in ECC i am finding this mismatch

    3. In ECC , total stock e.g 12,705.668 are appearing in HANA as Zero .and rest values are rounded off.

    How can we replicate the same values in HANA.

    Kindly suggest any settings i have to do for the same.

    Awaiting your response

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

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