SAP HANA - Calculation View

Calculation Views are used to consume other Analytic, Attribute and other Calculation views and base column tables. These are used to perform complex calculations, which are not possible with other type of Views.

Characteristics of Calculation View

Below given are few characteristics of Calculation Views −

Calculation Views are used to consume Analytic, Attribute and other Calculation Views.

They are used to perform complex calculations, which are not possible with other Views.

There are two ways to create Calculation Views- SQL Editor or Graphical Editor.

Built-in Union, Join, Projection & Aggregation nodes.

How to create a Calculation View?

Choose the Package name under which you want to create a Calculation View. Right Cpck on Package → Go to New → Calculation View. When you cpck on Calculation View, New Window will open.

Enter View name, Description and choose view type as Calculation View, Subtype Standard or Time (this is special kind of View which adds time dimension). You can use two types of Calculation View − Graphical and SQL Script.

Graphical Calculation Views

It has default nodes pke aggregation, Projection, Join and Union. It is used to consume other Attribute, Analytic and other Calculation views.

SQL Script based Calculation Views

It is written in SQL scripts that are built on SQL commands or HANA defined functions.

Data Category

Cube, in this default node, is Aggregation. You can choose Star join with Cube dimension.

Dimension, in this default node is Projection.

Calculation View with Star Join

It does not allow base column tables, Attribute Views or Analytic views to add at data foundation. All Dimension tables must be changed to Dimension Calculation views to use in Star Join. All Fact tables can be added and can use default nodes in Calculation View.

Example

The following example shows how we can use Calculation View with Star join −

You have four tables, two Dim tables, and two Fact tables. You have to find pst of all employees with their Joining date, Emp Name, empId, Salary and Bonus.

Copy and paste the below script in SQL editor and execute.

Dim Tables − Empdim and Empdate

Create column table Empdim (empId nvarchar(3),Empname nvarchar(100));
Insert into Empdim values( AA1 , John );
Insert into Empdim values( BB1 , Anand );
Insert into Empdim values( CC1 , Jason );

Create column table Empdate (caldate date, CALMONTH nvarchar(4) ,CALYEAR nvarchar(4));
Insert into Empdate values( 20100101 , 04 , 2010 );
Insert into Empdate values( 20110101 , 05 , 2011 );
Insert into Empdate values( 20120101 , 06 , 2012 );

Fact Tables − Empfact1, Empfact2

Create column table Empfact1 (empId nvarchar(3), Empdate date, Sal integer );
Insert into Empfact1 values( AA1 , 20100101 ,5000);
Insert into Empfact1 values( BB1 , 20110101 ,10000);
Insert into Empfact1 values( CC1 , 20120101 ,12000);

Create column table Empfact2 (empId nvarchar(3), deptName nvarchar(20), Bonus integer );
Insert into Empfact2 values ( AA1 , SAP , 2000);
Insert into Empfact2 values ( BB1 , Oracle , 2500);
Insert into Empfact2 values ( CC1 , JAVA , 1500);

Now we have to implement Calculation View with Star Join. First change both Dim tables to Dimension Calculation View.

Create a Calculation View with Star Join. In Graphical pane, add 2 Projections for 2 Fact tables. Add both fact tables to both Projections and add attributes of these Projections to Output pane.

Add a join from default node and join both the fact tables. Add parameters of Fact Join to output pane.

In Star Join, add both- Dimension Calculation views and add Fact Join to Star Join as shown below. Choose parameters in Output pane and active the View.

SAP HANA Calculation View − Star Join

Once view is activated successfully, right cpck on view name and cpck on Data Preview. Add attributes and measures to values and labels axis and do the analysis.

Benefits of using Star Join

It simppfies the design process. You need not to create Analytical views and Attribute Views and directly Fact tables can be used as Projections.

3NF is possible with Star Join.

Calculation View without Star Join

Create 2 Attribute Views on 2 Dim tables-Add output and activate both the views.

Create 2 Analytical Views on Fact Tables → Add both Attribute views and Fact1/Fact2 at Data Foundation in Analytic view.

Now Create a Calculation View → Dimension (Projection). Create Projections of both Analytical Views and Join them. Add attributes of this Join to output pane. Now Join to Projection and add output again.

Activate the view successful and go to Data preview for analysis.