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 –
- SAP HANA Graphical Calculation View (Created by SAP HANA Studio Graphical editor).
- 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)
Now Calculated Column is created
Add “AVERAGE_OF_STUDENT” calculated column to output
Save and Activate the view.
And see the Data Preview.
In output we can see “AVERAGE_OF_STUDENT” Calculated Column.