SAP HANA Introduction
- SAP HANA - Core Architecture
- SAP HANA - Information Modeler
- SAP HANA - System Monitor
- Studio Administration View
- SAP HANA - Studio
- In-Memory Computing Engine
- SAP HANA - Overview
SAP HANA Modeling
- SAP HANA - Export and Import
- SAP HANA - Information Composer
- SAP HANA - Analytic Privileges
- SAP HANA - Calculation View
- SAP HANA - Analytic View
- SAP HANA - Attribute View
- SAP HANA - Packages
- SAP HANA - Tables
- SAP HANA - Data Warehouse
- SAP HANA - Modeling
SAP HANA Reporting
- SAP HANA - Excel Integration
- SAP HANA - Crystal Reports
- Bi 4.0 Connectivity to HANA Views
- SAP HANA - Reporting View
SAP HANA Security
- SAP HANA - Auditing
- SAP HANA - License Management
- SAP HANA - Authorization methods
- SAP HANA - Authentications
- User Administration & Management
- SAP HANA - Security Overview
SAP HANA Data Replication
- SAP HANA - MDX Provider
- SAP HANA - CTL Method
- SAP HANA - DXC Method
- SAP HANA - Log Based Replication
- SAP HANA - ETL Based Replication
- SAP HANA - Data Replication Overview
SAP HANA Monitoring
- SAP HANA - Log Configuration
- SAP HANA - High Availability
- SAP HANA - Backup & Recovery
- SAP HANA - Persistent Layer
- SAP HANA - Monitoring and Alerting
SAP HANA SQL
- SAP HANA - SQL Script
- SAP HANA - SQL Data Profiling
- SAP HANA - SQL Explain Plans
- SAP HANA - SQL Synonym
- SAP HANA - SQL Triggers
- SAP HANA - SQL Sequences
- SAP HANA - SQL Stored Procedures
- SAP HANA - SQL Expressions
- SAP HANA - SQL Functions
- SAP HANA - SQL Operators
- SAP HANA - Data Types
- SAP HANA - SQL Overview
SAP HANA Useful Resources
- SAP HANA - Discussion
- SAP HANA - Useful Resources
- SAP HANA - Quick Guide
- SAP HANA - Questions and Answers
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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.
Advertisements