• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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

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.

SAP HANA Modeling: Complete Tutorial

Non Structural

The Non-Structural package can contain information object and sub-packages. This is default package.

SAP HANA Modeling: Complete Tutorial

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,

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

SAP HANA Modeling: Complete Tutorial

STEP 2) In this step,

  1. Select New.
  2. Select Package option.

SAP HANA Modeling: Complete Tutorial

STEP 3) In this step,

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

SAP HANA Modeling: Complete Tutorial

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

SAP HANA Modeling: Complete Tutorial

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

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

SAP HANA Modeling: Complete Tutorial

STEP 5) In this step,

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

SAP HANA Modeling: Complete Tutorial

Our "DHK_SCHEMA" when changed from Non-Structural to Structural package the icon style will be changed from SAP HANA Modeling: Complete Tutorial toSAP HANA Modeling: Complete Tutorial. This is an indication that non-structural package is now converted to the structural package.

SAP HANA Modeling: Complete Tutorial

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,

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

SAP HANA Modeling: Complete Tutorial

STEP 2) In this step,

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

SAP HANA Modeling: Complete Tutorial

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

SAP HANA Modeling: Complete Tutorial

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.

 

YOU MIGHT LIKE: