Calculation View H100

Calculation Views:

·         Visible to reporting tools

·         When the view is accessed, a function is implicitly executed

·         Function is defined in the HANA-specific Language “SQL Script”

·         Functions contain SQL commands

·         SQL in functions must be ‘read only’(no insert, update, delete, drop,…)

·         Functions can call other functions

·         Composite Views, re-uses Analytical and Attribute views

·         SQL/SQL Script/Custom Functions

Calculation View – With custom functions and calculations

Calculation Views – Similar to Virtual provider concept in BW

Information Models – Attribute, Analytic, Calculation Views and Analytic  Privilege

Calculation View:

• Define table output Structure
• Write SQL statement
• Ensure the selected fields corresponds to previously defined output structures
• SQL Scripts unlike SQL procedure can't change any data they are read only

Graphical:

·         3 types of calculated Views

·          Dimension

·         Used to build the equivalent of an attribute view.  No multidimensional reporting is enabled, and the last node is a projection node

·         Cube

·         Final node is an aggregation node.  Multidimensional reporting is possible

·         Cube with Star Join

·         Allows modeler to join one or several calculated views of type dimension in the very last stage of the model calculation

Union: used to combine the result of 2 or more data sources

·         Know all the names of all the employees of a store with different branches maintaining their own employee table

Join: used to query data from 2 or more data sources, based on some condition

·         Retrieve the sales of 2 stores maintaining the individual tables for sales based on the customer id

Projection: Used to filter or create a subset with only the columns of a table or view that are required for you model.

·         Selecting the employee name and sales quantity from a table that includes many more columns

Aggregation: Used to summarize the data of a group of rows by calculating values in a column.

·         To retrieve total sales of some product in a month.  The supported aggregation types are sum, min, and max

Star Join:  Used only in Calculation Views of Type Cube with Star Join, and only as the last calculation step ( just before the Semantics node)

SQLScript pushes data intensive application logic into the database

·         Define Function (with input and output parameters)

·         Can be a sclar value to pass parameters from the front-end tools in order to filter the results

·         Can be a table type -> to pass results from one function into another

·         The output parameter is mandatory

·         Can be locally defined table type

Creating the database object for the calculation view:

·         Metadata(the calculation view coding0 has to be translated into run-time objects)

·         Executes the coding

·         Defined table type is dropped and created

·         SQL script function is created

·         Column views are created in the chosen output schema

·         Select the attributes and measures for the output node.  This will represent the definition of the column view that the front end tools will query against

·         Final Step:  Save and activate

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

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:19 Which of the following automatically aggregates data? (Choose two)

A. Analytic view 
B. Calculation view 
C. Column table 

D. Attribute view


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

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

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.

 How can you evaluate the actual speed of a calculation within HANA DB, Ans: (3 answers needed)

a)  Write and execute SQL statement
b)  Use data previewc)  Execute WEBI 

d)  Execute information space view, e0 trace using python admin tool within TREX.

What are the reasons for building a calculation view with SQL script rather than the graphical interface

a)     To reuse standard SQL functions not provided within the modeler

b)    To create custom reusable calculation functions

3.     Which tool can you use to preview calculation view data?

a)     Data Preview

b)    SQL Editor

1.  Calculation View can contain the following:

a.     Database Tables

b.    Attribute Views

c.     Analytic Views

d.    Calculation Views

  Without Analytic Privilege, no data can be viewed from

a.     Attribute Views

b.    Analytic Views

c.     Calculation Views

62.  Calculation engine performs the following:

·         Create Joins at run-time

·         Create Calculated Measures and Calculated Attributes

·         Compute and Execute Calculation Views

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

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

Ans: 1, other options 2,3,4

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

-