In this article, let’s look at Calculation View, which is one more modeling object in SAP HANA and the step by step process to create the same.
What is Calculation View?
A calculation view is a powerful and flexible information view, which can be used to define more advanced logic or complex calculations on the tables or information views available in the SAP HANA. Calculation Views provides the functionality that is available both in Attribute and Analytic View.
Mostly calculation views are used when we were not able to design the business logic with the help of attribute and analytic views. For example, we need measures from more than one fact table or need to define filters on calculated columns.
The data foundation/source of the calculation view can include any combination of tables, column views, attribute views and analytic views.
Calculation Views can be created in two ways, they are
- SQL Script
Graphical: The graphical calculation view is built with the help of nodes available in it. We have 5 different nodes available in graphical calculation view, they are
- Union All
SQL Script: When it comes to SQL script based calculation view, we can write the business logic using native SQL in the way we want. This can be created with the help of CE functions or SQL script.
This type of calculation view can be based on two ways,
- CE Functions
- SQL Script
Features of Calculation View:
- We can create both multi dimension and relational type calculation views.
- Supports complex expressions (i.e. IF, Case, Counter, filters, union, RANK).
- Supports reusing Analytic views, Attribute views and other Calculation views (Graphical and Scripted).
- Supports SAP ERP specific features (i.e. client handling, language, currency conversion).
- Provides ability to combine facts from multiple tables.
- Provides support for additional data processing operations, (i.e. Union, explicit aggregation).
- Provides ability to leverage specialized languages (i.e. R-Lang).
- Provides ability to leverage both Column and Row tables.
- Performance of certain operations (i.e. star-join, aggregation) are inferior to Analytical Views.
- Before we go ahead and see the step by steps process of creating a Calculation View in HANA, let’s look at what we want to build.
- As we discussed in ‘Introduction to modeling in SAP HANA’ article, the below diagram needs building of two Analytic Views for Sales and Payments and then a calculation view to combine both sales and payments data.
- We have already created both the analytic views required to create a calculation view. Please go through ‘SAP HANA – Analytic View Creation’ for details on how to create analytic view in SAP HANA.
- Now business want analyze their sales revenue with their finance data to know more insight on which customer has paid how much amount, what is the remaining debt and so on.
- To be able to analyze both sales and finance data at the same time, we have to create a calculation views because this involves measures from more than one place.
Pre-requisite: AN_ORDERS_SALES and AN_PAYMENTS
How to create a Calculation View in SAP HANA?
- Let’s go through the step by step process of creating a calculation view in SAP HANA.
- All the modeling objects are grouped into packages in HANA. This first step in modeling objects creation in HANA is to make sure we have package created (in our case ‘sap-student’).
- To create new calculation view, right click on the package(sap-student) → ‘New’ → Calculation View as shown below.
- This gives us the initial screen of the creation process where we need to enter technical name of the calculation view and description/label.
- We can also choose few options here like ‘Copy From’ and ‘Subtype’. For details about ‘Copy From’ and ‘Subtype’ options, please go through article ‘SAP HANA – How to create Attribute View’.
- Our object name here is ‘CA_SALES_PAYMENTS’ where CA represents ‘CAlculation View’.
The other options we have here along with ‘Copy From’ and ‘Subtype’ are,
- Type: We can create two types of calculation views as we discussed above. In this article we are going to create a graphical calculation view.
- Data Category: We have two options here, they are
- CUBE: We select this option when we have measures coming out from this view (This is same as OLAP model like analytic view)
- DIMENSION: We select this option when we don’t need any measures from the view (this is similar to OLAP structure or relational table).
We have selected ‘CUBE’ for our scenario as we have measures.
- With Star Join: If we would like to build a calculation like how we see in analytic view with star join, then we need to select this option.
Note: In start join, we cannot use either attribute view or analytic view as source. We can only use either table or calculation views as source.
- Click ‘Finish’ to proceed further.
- This step will show us the design area for Calculation View where we build the complete object and activate it once the designing is completed.
We have two main areas here, they are
- Palette: This area contains all the nodes that can be used as source to build our calculation views.
We have 5 different types of nodes, they are
- Join: This node is used to join two source objects and project the result to the next node. The join types can be inner, left outer, right outer and text join.
Note: We can only have two source objects mapped to a join node.
- Union: This is used to perform union all operation between multiple sources. The source can be n number of objects.
- Projection: This is used to fine tune our source objects before we use in next nodes like union, aggregation and rank.
We can choose the selective columns, filter the data and create additional columns.
- Aggregation: This is used to perform aggregation on specific columns based the selected attributes.
- Rank: This is the exact replacement for RANK function in SQL. We can define the partition and order by clause based on the requirement.
- Semantics: We can define and maintain the object settings here and can also make use of advanced features like variables, hierarchies and input parameters.
As we have selected data category as ‘CUBE’, we can see the default node as ‘Aggregation’. If the data category was ‘DIMENSION’ then we will have default node as ‘Projection’. We cannot remove the default node, however we can change from one to another.
Proceed to the next page to continue reading…