SAP HANA Calculation View

SAP HANA Calculation View

What is SAP HANA Calculation View?

Calculation view in SAP HANA is the most important type of information view as it enables you to perform advanced designing operations on the data.?You can apply advanced modeling logics on data in Calculation View which you cannot do in Analytic and Attribute view.

In Attribute view, you can only work with dimensions, whereas in analytic view, you work with multiple dimension tables linked to one fact table. This means that these two views do not allow flexible use of dimensions and measures and have a basic way of data modeling.

On the contrary, calculation views do not have such limitations and enable a user to use complex calculation logic and combine measures from more than one fact table. Along with this, you can use advanced SQL logics and multiple layers of calculation logics while creating a calculation view.

It gives you the freedom to combine and create from a range of options like Tables, Column Views, Analytic Views, Attribute View, etc. This enables a user to do multidimensional reporting using measures and dimensions from different sources.

Characteristics of Calculation View

Some important characteristics of the calculation view are given below:

• Calculation views support both OLTP and OLAP models.
• They support complex expression such as Case, Counter, IF.
• Supports special language such as R-lang.
• You can reuse Analytic Views, Attribute Views and even other Calculation Views while creating a new Calculation View.
• Calculation views support analytic privileges.
• It supports SAP ERP specific functionalities such as currency conversion, language, client handling, etc.
• Enables the user to combine measures from multiple tables.
• Users can use both column-based and row-based tables.

Scenario Panel in Calculation View

When you open a calculation view editor to create a new view, you will find a scenario panel on the left with five options;?Join, Union, Projection, Aggregation, and Rank.?A Calculation View in?SAP HANA?is created using a combination of these options.

Join –

1. A join option connects two source objects or tables into a single object. It is also referred to as a join node in the view hierarchy. The?join types?between two tables can be inner, left outer, right outer and text join.

Union –

1. The union option performs a full outer join on n number of source objects and creates a single object by combining their contents.

Projection –

1. The projection option projects the table in a certain way. You can use it to filter the columns of a table or add some columns before using it in other nodes like aggregation, rank, union, etc.
2. You can only use one source object while working in the projection node. If you wish to create more than one table than use separate projections for each. Projection nodes are used to convert column-based tables into dim calculation tables.

Aggregation –

1. In the aggregation node, you can perform aggregation on selected dimensions and measures.

Rank –

1. This is used to rank the values based on a criterion. You can define the order by clause and partition in this node based on the modeling requirements.

Steps to Create a Calculation View in SAP HANA

In this section of the SAP HANA Calculation View Tutorial, we will learn how to create a calculation view with a star join option in SAP HANA Modeler. A star join is created when we need measures from more than one fact table to use it in reporting.

Step 1: Select Tables

Open the?SAP HANA Information Modeler?and decide the tables which you want to use in making the Calculation View. You can access the tables from the?Catalog?folder under your SAP HANA system.

For understanding’s sake, let us assume that we are using four tables, two-dimension tables, and two fact tables.

Step 2: Create a DIM information view

Go to the?Content?node and select the package under which you need to create the calculation view. Right-click on the package name, select?New?then select?Calculation View.

Step 3: Converting dimension tables into DIM calculation tables

Before we start with bringing all the tables together, we need to convert the two column-based dimension tables into DIM calculation tables. For this, open a new calculation view and enter the name of the view (for instance,?DIM_CAL_1) and select the?Data Category asDimension.

Repeat this process again for every dimension table you have. (we have named the second one as?DIM_CAL_2)

Step 4: Adding dimension tables and columns

Now, add the dimension tables into the calculation view from the green?Add Object?sign on the?Projection?section.

We will add the table?EMPINFO?(under the Projection section) in the?DIM_CAL_1?view. Similarly, add another dimension table?EMPRECORDS?in?DIM_CAL_2?calculation view.

Once the tables are added and columns from them are added in the final output, save and activate the table by clicking on the green tick and arrow button on the top bar.

Step 5: Create a calculation view

Now, after converting our two column-based dimension tables as DIM tables, we will create the calculation view. Again, go to a preferred package under the?Content?node. Right-click on the package name >?New > Calculation View.

Enter the details for the new calculation view such as?Name, Label, View Type?(Calculation View),?Type, Data Category?(CUBE). Check the “With star join” box if you are creating a calculation view with a star join.

Step 6: Add measure/ fact tables

There are five types of operations given based on the scenario in which the calculation view is being created. Those are?Join, Union, Projection, Aggregation, and Rank.?All five perform different functions (as their name suggests).

We will create two projections and add one fact table in each projection. We have added?EMPFACT1?in?Projection_1?and?EMPFACT2?in?Projection_2.

Select the columns to be available in the reporting view from both fact tables. You can see the selected columns or fields in the?Output?section on the right.

Step7: Join the fact tables

Next, we will join the two fact tables (EMPFACT1?and?EMPFACT2). Select Join from the left Scenario panel. Drag and drop the two projections (Projection_1?and?Projection_2) in the Join section. The two projections will be joined and shown in the join box.

Then, we will physically join the two fact tables with the common column. Suppose?EMPID?is a common column in both the fact tables (EMPFACT1?and?EMPFACT2). Select the columns for output from the two fact tables.

Now the selected columns from the two fact tables will be in a single join table. The name of this join table is?Join_1.

Step 8: Create a star join

Next, we will join the Join table to the?Star join?section above it.

Also, we will add the two dimension tables in?DIM_CAL_1?and?DIM_CAL_2?that we added earlier, into the final star join calculation view.

So, we have added?Join_1,?DIM_CAL_1, and?DIM_CAL_2?in the Star Join section.

Step 9: Join dimension and fact tables.

Now that we have data from two dimension tables and a combined fact table, we will join the fact (join table) with the two dimension tables and select the final columns for output as shown in the image below.

A detailed view of all three tables will appear on the?Details?panel.

Step 10: Check Semantics

Moving on, we will go to the?Semantics?section to define the measures and attributes. Click on the?Auto Assign?button to automatically assign columns as measures and dimensions.

Step 11: Save and activate

Save?and?activate?the final calculation view from the green arrow button.

Step 12: Preview data from selected columns

You can preview the data by clicking on the?Data Preview?button next to the?Save and Activate?button.

In the data preview section, you can carry out analysis by creating visualizations using the available measures and dimensions.

We can also change the type of visualization from a bar graph to a bubble chart, line chart, pie chart, etc.