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