Calculation Views

Calculation views:

• composite views(made up of various parts)
• consumes other Analytical, Attribute, other Calculation Views and Tables
• performs complex calculations not possible with other views

Types of Views:

• A) Graphical Calculation views:  using graphical editor
• B) Scripted calculation views:  SQL editor

           
A) Graphical Calculation Views:

• Consume other analytical, attribute, calculation views and tables
• Built in Union, join projection and aggregation Nodes
• Addt'l feats: Distinct, Count, Calculation, Dynamic Joins
• No SQL or SQL Script Knowledge required

B) SQLScript-based Calculation Views:

• SQLScript: built upon SQL commands or special HANA pre-defined functions
• read only

• 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"?

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

create schema <schema_name>;

-- 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 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 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); 

Grant schema SELECT rights to _SYS_REPO user:

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. 
• If you replicate data automatically, using SAP LTR server, this command is executed automatically in the background while creating a new scema

Steps for creating calculation view:

1) Open Hana Studios: Modeler perspective: r.click: "Calculation View"

2) Name/Description;  Subtype: "Graphical",  Data Category: "Cube"

3) Graphical Editor:  Join, Projection, Union, Aggregation

4)  Click on "join" symbol and add REGION tables

5) Add SALES tables

6)  Select REGION_ID from REGION table  --> drag to REGION_ID of SALES table.  Join type can be changed in the properties tab.

7)Add 

• REGION_NAME
• SUB_REGION_NAME
• PRODUCT_ID
• SALES_AMOUNT

R.Click:  "add to output"

8)  Join symbol: 

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

9) click on + sign of new join block and add PRODUCT table

10)  PRODUCT_ID from Join_1 (Drag/drop) to PRODUCT_ID of PRODUCT table.

       Select the join type as Inner Join

       Add REGION_NAME

              SUB_REGION_NAME

              PRODUCT_ID

              SALES_AMOUNT

              PRODUCT_NAME

         "Add To Output"

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. 

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. 

================================================

Questions:

Qs:1 Which of the following nodes can you use when you create a calculation view with the SAP HANA studio graphical interface? (Choose two)


A. Data Foundation 
B. Aggregation 
C. Join 
D. Analytic Privilege

Qs:12 Which of the following can you use as a source for a graphical calculation view? (Choose three)

A. Calculation view 
B. SQL view 
C. Decision table 
D. Table 
E. Procedure

 On calculation view 3 questions for example: In what situation the calculation view is required?

Ans  When we require complex logic that cant be delivered by Attribute and Analytical Views.

11.  Calculation View can contain the following:

a.     Database Tables

b.    Attribute Views

c.     Analytic Views

d.    Calculation Views

Calculation views can also be used as business reporting view ? True

How many result tables are generated from one calculation view per session,

Ans: 1, other options 2,3,4

In a calculation view which node is used

Ans: Projection, Union, there were other options

10.  How can you evaluate the actual speed of a calculation within a DB,

 Ans: (3 answers needed) a) write and execute SQL statement, b) Use data preview, c) execute Webi, d) Execute information space view, e0 trace using python admin tool within TREX (don't ask me what I picked!)

What is SQL Script ?

SQL script is used for calculation view and is a proprietary language.

Qs:18 Where is the physical data stored, on which a calculation view is based?

A. In the _SYS_BI schema 

B. In a schema 

C. In the _SYS_BIC schema 

D. In a package

Qs:19 Which of the following automatically aggregates data? (Choose two)

A. Analytic view 

B. Calculation view 

C. Column table 

D. Attribute view

Qs:43 Which of the following are required to build a SQLScript calculation view? (Choose two)

A. Define a data foundation. 

B. Define a measure. 

C. Set the MultiDimensional Reporting property to false. 

D. Add columns to the var_out output parameter.

Qs:49 You create a graphical calculation view that is based on analytic views. What do you use to combine the data from the analytic views?

A. Referential join 

B. Projection node 

C. Aggregation node 

D. Union

Qs:48 How can you create a computed column in an information model? (Choose two)

A. Create a calculated attribute within a calculation view. 

B. Create a counter in an analytic view. 

C. Create a restricted measure in a calculation view. 

D. Create a restricted measure in an analytic view.

=================================================

Graphical View:

http://saphanatutorial.com/sap-hana-graphical-calculation-view/

Sys_repo:

http://saphanatutorial.com/_sys_repo/