How To Calculate Student Average Marks in SAP HANA Studio Using Calculation View

Scenario : In This Scenario I am going Explaining about Calculating Student Average Marks using Calculated Column in Calculation View.

Calculation Views are used to combine 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.
Characteristic of SAP HANA Calculation View as below –

• Support Complex Calculation.
• Support OLTP and OLAP models.
• Support Client handling, language, currency conversion.
• Support Union, Projection, Aggregation, Rank, etc.

SAP HANA Calculation View are of two types –

1. SAP HANA Graphical Calculation View (Created by SAP HANA Studio Graphical editor).
2. SAP HANA Script-based calculations Views (Created by SQL Scripts by SAP HANA Studio).

Graphical Calculation Views

• Can consume other Analytical, Attribute, other Calculation Views & tables
• Built-in Union, Join, Projection & Aggregation nodes
• Provides additional features like Distinct, Count, Calculation, dynamic joins
• No SQL or SQL Script knowledge required

In this Scenario I am going Explaining about Calculating Student Average Marks using Calculated Column in Calculation View.

Follow the Below Steps to Find Student Average Marks.

Step 1: Create a Table “STUDENT_DETAILS” with following structure

Execute the table

Open the SQL console and insert the following data

Executed it and see the table data

Step 2 : Create the Calculation View

Go to Schema–>select New–>select Calculation View

Create the Calculation View with the name of “STUDENT_AVERAGE_CALCULATION”.

Select “Finish” Button Then appear the next screen.

Here take one Projection_1

Here select the “+” Symbol to select the Table “STUDENT_DETAILS”

Select the Add all to output

Here create the one Calculated Columns

Click on Calculated Columns –> select New

Create Calculated Column Name ” AVERAGE_OF_STUDENT”

In  Expression Editor give the Syntax like below to find avarage of  the marks

(Total Marks) / (number of subjects)

(“SUBJECT_1″+”SUBJECT_2″+”SUBJECT_3″+”SUBJECT_4”)/4

Now Calculated Column is created

Add “AVERAGE_OF_STUDENT” calculated column to output

Save and Activate the view.

And see the Data Preview.

OUTPUT :

In output we can see “AVERAGE_OF_STUDENT” Calculated Column.