SAP HANA: Calculation View

Calculation views are composite views that are used to calculate complex tabular results using either explicit SQL script code or a data flow graph that is visually created in the SAP HANA Studio.

It can consume other analytical/attribute/calculation views and tables. It can perform complex calculations that are not possible with other views.

The calculation views are of the following types:

•         Graphical calculation views are created using the graphical editor

•         Scripted calculation views are created using the SQL editor

Complex calculations, which are not possible through the graphical approach, can be created using SQL script.

Calculation views can be used in the same way as analytic views.

In contrast to analytic views, it is possible to join several fact tables in a calculation view where measures are selected from the different fact tables.

Following are general guidelines for deciding whether to use calculation views or analytic views:

•         Use analytic views where mostly aggregation of data is required. These views perform very well on database selection of data, but the opportunities for transformations are limited.

•         Use calculation views where more than one fact table needs to be combined and where complex calculations or other transformations are needed.

•         For a simple union of analytic views, the graphical method is good.

•         For complicated transformations, use the SQL method. Where possible, use the proprietary variant of the language called for, which, in this case, is SQL Script.

Graphical calculation views support the following types of calculation nodes:

Projection node

This node is used to define filters and select columns. Usually, you put each data source, such as an embedded table or view, into a projection node and apply a filter to cut down data size as early as possible.

Join node

This node is to define joins. If one table needs to join multiple tables, you need to join them one by one in separate nodes. Alternatively, you can create a star join node and then join from one table to multiple tables at one node.

With a star join, all the tables need to be wrapped into calculation views. Star joins are like analytic views except they allow you to create measures from different tables.

You can make inner joins, outer joins, referential joins, and text joins on a calculation view as well, but there is a limitation on text joins. The filter on the WHERE clause of a query will not be pushed down to the table level when there is a text join in the calculation view. If this causes performance issues, then you need to consider a different approach than using a text join.

On the join node, SAP HANA also supports the spatial join, which enables you to handle calculations between 2-D geometries performed using predicates, such as intersects, contains, and more.

Aggregation node

This node is used to define aggregation. You can define the aggregation type as SUM, MIN, MAX, or COUNT. You also can create a counter, which is used to calculate COUNT DISTINCT.

Union node

•         Unions are used to combine the result set of two or more data sources.

•         The funny fact about this UNION node here is that it doesn't work like a UNION.

•         It works as a UNION ALL operator.

•         This means that keeps piling one data set below the next without aggregating them.

Rank node

With a rank node, you can filter data based on rank.

This is the exact replacement for RANK function in SQL. We can define the partition and order by clause based on the requirement. You can set the SORT DIRECTION as DESCENDING (TOP N) Or ASCENDING (BOTTOM N).

The THRESHOLD field is to set the value of N. PARTITION BY COLUMN sets the columns to partition the records into multiple windows.

In each window, it returns the Top N or Bottom N records. The DYNAMIC PARTITION ELEMENTS checkbox allows you to choose the columns dynamically from the list under PARTITION BY COLUMN in case some of these columns do not exist in the query.

SAP HANA: Variables/Input Parameters

Analytic and calculation views can contain variables, that can be bound to specific attributes. When calling the view, you can send values for those variables. These variables can be used, for example, to filter the results.

Variables are bound to columns and are used for filtering using WHERE clauses. As such, they can only contain the values available in the Columns they relate to.

Important features of variables:

·         You use variables to filter data at runtime. You assign values to these variables by entering the value manually, or by selecting it from the drop-down list.

·         You can also define whether the Variable is Mandatory or if it should have a Default Value.

·         You can apply variables only in analytic and calculation views.

Variable Types:

The following types of Variables are supported:

Single Value: Use this to apply a filter to a Single Value.

Interval: Use this where you want the user to specify a set start and end to a selected Interval.

Range: Use this when you want the end user to be able to use operators such as “Greater Than” or “Less Than”.

System Tables to see Variable Information:

SAP HANA includes the following set of tables that contain information about the variables defined for views: There are 4 system tables under the schema _SYS_BI which contains information about Variables.

1.      BIMC_VARIABLE

2.      BIMC_VARIABLE_ASSIGNMENT

3.      BIMC_VARIABLE_VIEW

4.      BIMC_VARIABLE_VALUE

Input Parameters:

You use input parameters to define internal parameterization of the view to obtain the desired functionality when you run the view.

Input parameters in HANA are used to filter the data using the user input value as a source to perform additional calculations which helps to solve dynamic data population scenarios.

Difference between Variable and Input parameter:

Variable	Input Parameter
Variables are defined on attributes to filter the data based on user input during runtime	Input parameters defined to take user input during runtime and use it for data filtering or for dynamic calculations.
Variables apply filter after execution of all nodes till the semantics (at the top level)	Input parameters can apply filters at any projection level
Cannot be used as input for calculated columns and restricted columns	Can be used as input for calculated and restricted columns
It allows multiple values as input	It allows multiple values as input only at calculation view but not at analytic view level

Example:

            Here I use 3 tables named as Product, Sales_IP, Region.

Step 1: Create a Calculation view by right click the Package and click New and select Calculation View.

Step 2: A Pop-up will appear and provide details which is required and click Finish.

Step 3:  Drag a join node into the Scenario pane named as Join_1, in that drag and drop the required tables. Here I use SALES_IP and REGION. And make a link between using join based on the relationship.

Step 4:  Drag another join node into the scenario pane named as Join_2, in that drag and drop the required tables. Here I use Join_1 and REGION table. And make a link between using join based on the relationship.

Step 5: And then connect the Join_2 to the Aggregation node. Finally, it looks like as shown as below:

Step 6: Now we have to create an Input parameter, from the output pane, right-click the Input parameter and click New. A Pop-up will appear and provide necessary details as shown as below and click ok.

Step 7: Now we have to create a calculated column to know the Net amount which is discounted amount from the Sales Amount. From the output pane, right-click the calculated column and click New. A Pop-up will appear and provide necessary details as shown as below and click ok.Here I used that input parameter in an expression.

Step 8: Then now create a variable to filter the region. From the details pane, click Parameters/Variables tab and click create an icon. A pop-up will appear and provide necessary details as shown as below:

Step 9: Click ok and validate and Activate the view.

Step 10: Result

            When we click data preview a pop-up will appear to give a value for variable and input parameter as shown as below:

Calculation View wizard in SAP HANA

The Calculation View wizard window offers you 8 options to be configured or

selected, as outlined in the table.