SAP HANA Modeling: Complete Tutorial

Details: Last Updated: 05 September 2018

What is SAP HANA Modeling?

SAP HANA Modelling is an activity by which we create information view. Information View is similar to dimension, cube or information provider of BW. This information view is used for creating the multi-dimensional data model.

In this tutorial, you will learn-

SAP HANA Modeling Overview
SAP HANA Best Practices for Creating Information Models
SAP HANA Performance Optimization Technique

SAP HANA Modeling Overview

Modelling is an activity in which user refine or slice data in the database table by creating information view based on the business scenario. This information views can be used for reporting and decision-making purpose.

Information view is made from various combinations of content data to create a model for a business scenario.

Content Data in information view are of two types –

Attribute: Descriptive and Non-Measureable Data. E.g. Vendor ID, Vendor Name, City, etc.
Measure: Data can be quantifiable and calculated. E.g. Revenue, Quantity Sold and Counters. The measure is derived from analytic and calculation view. The measure cannot be created in Attribute view.

Types of Attribute

SAP HANA Support three Type of attributes -

Types of Attributes	Activities
Simple Attribute	It is derived from data foundation.
Calculated Attribute	It is derived from one or more existing attributes and constants. E.g. Arithmetic calculation or derive the full name from the first and last name.
Local Attribute	It is used inside modelling views (analytic View / calculation view) for Customize the behavior of attribute, so it is local to Modelling view and cannot access from outside of Modelling view.

Types of Measure

SAP HANA Support four Type of Measure –

Types of Attributes	Activities
Simple Measure	It is derived from data foundation.
Calculated Measure	It is derived from one or more exiting measure, constants and function. E.g. Arithmetic calculation.
Restricted Measure	It is used to filter value based on user-defined rules for attribute values.
Counter	It is Special types of the column that display unique number for attributes Columns (Analytic View/ Calculation View). It is used in count the one or more attributes columns.

Information Views are of three types as below –

Attribute View - This is used for master data context.
Analytic View – This is used for creating fact tables and similar to Cube of BW.
Calculation View – This is used for creating a complex view and similar to multiple Provide in BW.

In order to work in SAP HANA, privileges are required by user, below are privileges required for SAP HANA Modelling -

Privileges Required for Modelling

Privileges provide security to SAP HANA database, by which authorized user can access authorized content only.

Object Privileges –

Object privileges are SQL privileges which are used for providing read/write access on database objects. Below are object privileges are required for Modelling.

SELECT privilege on _SYS_BI Schema.
SELECT privilege on _SYS_BIC Schema.
EXECUTE privilege on REPOSITORY_REST (SYS).
SELECT privilege on Table Schema.

Package Privileges –

Package Privileges are required to authorize action on individual packages. Below are package privileges are required for data modelling-

REPO.MAINTAIN_NATIVE_PACKAGES privilege on Root Package.
REPO.READ, REPO.EDIT_NATIVE_OBJECTS & REPO.ACTIVATE_NATIVE_OBJECTS on package used for Content Objects.

Analytic Privileges –

To Access SAP HANA Information View Analytic Privileges are required -

For Full data access to all information view in SAP HANA System, "_SYS_BI_CP_ALL" analytic privileges required. For restricted data access, analytic privileges need to be created and assign to the user.

Other Privileges-

Provide Grant on Own Schema to _SYS_REPO user as 'GRANT SELECT ON SCHEMA " Schema name" TO _SYS_REPO WITH GRANT OPTION';
REPO.MAINTAIN_DELIVERY_UNITS for creating delivery Units.
REPO.IMPORT, REPO.EXPORT for Import / Export of delivery Units.
REPO.WORK_IN_FOREIGN_WORKSPACES for work in foreign workspaces.

SAP HANA Best Practices for Creating Information Models

SAP HANA Best Practice is standard while creating an object in SAP HANA Database. Below are best practice for object –

PACKAGE:

Create a top-Level package like "Development" for development work.
Create a sub-package under top-Level package for each developer.
More sub-package also can be created, if required.

SCHEMA:

Design your Schema Layout before start the project.E.g. (DS_SCHEMA, SLT_SCHEMA, FI_SCHEMA, SD_SCHEMA, etc.).
The custom table should be in a separate schema.

TABLES:

Table that will be used in reporting or OLAP should be Column store type.
Table that will be used in Transaction or OLTP should be as Row Store type.
Give comment / description for table and column name properly for clarity.

NAMING CONVENTION:

OBJECTS	Format	Description
ATTRIBUTE VIEWS	AT_PRODUCT	AT_..... means Attribute View
ANALYTIC VIEWS	AN_SALES	AN_.....means Analytic view
CALCULATION VIEWS	CA_SALES	CA_..... means Calculation view
ANALYTIC PRIVILEGES	AP_REST_AT(Attribute View) AP_REST_AN(Analytic View) AP_REST_CA(Calculation View)	AP_.... means Analytic Privileges
HIERARCHY	HI_BNAME_PC(Parent Child) HI_BNAME_LV(Level)	HI_... means Hierarchy
PROCEDURE	SP_PROCEDURENAME	SP_... Stored Procedure
INPUT PARAMETERS	IP_PARA_NAME	IP_... means parameter
VARIABLES	VA_VNAME	VA_...means variable name

Creating a Package in SAP HANA Studio

Package: It is a container that contains all information about the model (attribute view, analytic view, calculation view, etc. . .) in a group.

Types of package: Package are of two types, which is as below –

Type	Description	Icon
Structural	In Structural package, only sub-package can be created. No Information view (Attribute view, analytic view, etc.) can be created in Structural package. E.g. of Structural package – SAP, system-local, system-local. Generated, system-local. Private.
Non Structural	The Non-Structural package can contain information object and sub-packages. This is default package.

Uses of Package: Package group are all information model, and make model transporting easier. Both packages can be used in transporting.

Steps for creating Structural Package in SAP HAN Studio-

STEP 1) In this step,

Select Hana System, here it is HDB.
Go to Content folder.

STEP 2) In this step,

Select New.
Select Package option.

STEP 3) In this step,

Enter Package Name. E.g. "DHK_SCHEMA".
Enter Description for Package.
Original language and Person Responsible is selected by default.

Non-Structural Package with Name "DHK_SCHEMA" will be created in Content node as below-

STEP 4) Now, convert Non-Structural Package to Structural Package.

Select package "DHK_SCHEMA" and right click on it.
Go to edit option for the package.

STEP 5) In this step,

Select "Yes" in for Structural Options field.
Click on OK Button.

Our "DHK_SCHEMA" when changed from Non-Structural to Structural package the icon style will be changed from to. This is an indication that non-structural package is now converted to the structural package.

Step for Creating Non-Structural package under Structural Package as sub-package.

The package is created by default as Non-Structural. In Non-Structural Package, other Package and information object can be created. It is better to first create a structural package, and then create a sub-structural package in it.

STEP 1) In this step,

Select Structural Package "DHK_SCHEMA" and right click on it.
Select New -> package.

STEP 2) In this step,

Enter Sub-package name in Name field.
Enter description for it.
Click on "OK" Button.

A new Non-structural package will be created as sub package under DHK_SCHEMA package.

SAP HANA Performance Optimization Technique

There are the following rule for performance Optimization Technique –

All Information View and Table view should be used with a projection node. Projection Node improves performance by narrowing the column set.
By applying filters at projection nodes.
Avoid JOIN nodes in calculation view, Use UNION instead of it.
Use Input Parameters / Variable to restrict the dataset within Analytic / Calculation View.
The calculation should be done before aggregation.
Hierarchies need to re-define in Calculation view, Hierarchies of attribute view is not visible in calculation view.
Hierarchies of attribute view is visible in Analytic view.
The label of attribute and description of measure defined in Attribute view, Analytic view and Calculation view will not display in calculation view. We need to Re-Map it.
Do Not Mix CE Function and SQL script in Information model.