SAP HANA Modeling: Views, Tables & Data Models

What is SAP HANA Modeling?

SAP HANA Modeling is an activity by which we create an information view. An information view is similar to a dimension, a cube, or an InfoProvider in BW. This information view is used for creating the multi-dimensional data model.

The physical tables inside SAP HANA hold data in the shape the source system delivered it. Modeling adds a layer above those tables that renames columns, joins related records, applies filters, and defines calculations. Reporting tools then read that layer instead of the tables, so business users never need to know the underlying table structure.

SAP HANA Modeling Overview

Modeling is an activity in which the user refines or slices data in the database table by creating an information view based on the business scenario. These information views can be used for reporting and decision-making purposes.

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

Content data in an information view is of two types:

• Attribute: Descriptive and non-measurable data. For example, Vendor ID, Vendor Name, City, etc.
• Measure: Data that can be quantified and calculated. For example, Revenue, Quantity Sold, and Counters. A measure is derived from an analytic and a calculation view. A measure cannot be created in an attribute view.

Both building blocks are defined once inside the view and reused by every report that consumes it. The sections below list the exact variants SAP HANA supports for each.

Types of Attribute

SAP HANA supports three types of attributes:

Types of Attributes	Activities
Simple Attribute	It is derived from the data foundation.
Calculated Attribute	It is derived from one or more existing attributes and constants. For example, an arithmetic calculation, or deriving the full name from the first and last name.
Local Attribute	It is used inside modeling views (analytic view or calculation view) to customize the behavior of an attribute, so it is local to the modeling view and cannot be accessed from outside that view.

Types of Measure

SAP HANA supports four types of measures:

Types of Measures	Activities
Simple Measure	It is derived from the data foundation.
Calculated Measure	It is derived from one or more existing measures, constants, and functions. For example, an arithmetic calculation.
Restricted Measure	It is used to filter values based on user-defined rules for attribute values.
Counter	It is a special type of column that displays a unique number for attribute columns (analytic view or calculation view). It is used to count one or more attribute columns.

Once attributes and measures are decided, they are assembled into one of three information views:

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

Before any of these views can be built, the modeler account needs a specific set of authorizations.

Privileges Required for Modeling

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

Object Privileges

Object privileges are SQL privileges that are used for providing read/write access on database objects. The object privileges below are required for modeling.

• SELECT privilege on the _SYS_BI schema.
• SELECT privilege on the _SYS_BIC schema.
• EXECUTE privilege on REPOSITORY_REST (SYS).
• SELECT privilege on the table schema.

Package Privileges

Package privileges are required to authorize actions on individual packages. The package privileges below are required for data modeling.

• REPO.MAINTAIN_NATIVE_PACKAGES privilege on the root package.
• REPO.READ, REPO.EDIT_NATIVE_OBJECTS & REPO.ACTIVATE_NATIVE_OBJECTS on the package used for content objects.

Analytic Privileges

To access an SAP HANA information view, analytic privileges are required.

• For full data access to all information views in an SAP HANA system, the “_SYS_BI_CP_ALL” analytic privilege is required. For restricted data access, analytic privileges need to be created and assigned to the user.

Other Privileges

• Grant access on your own schema to the _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 and export of delivery units.
• REPO.WORK_IN_FOREIGN_WORKSPACES to work in foreign workspaces.

Difference Between Attribute View, Analytic View, and Calculation View

The three information views are not interchangeable. Each one answers a different question, and picking the wrong one is the most common modeling mistake beginners make.

Criteria	Attribute View	Analytic View	Calculation View
Purpose	Master data and descriptive context	Star schema on a single fact table	Complex logic across multiple sources
Measures allowed	No	Yes	Yes
Number of fact tables	Not applicable	One	Many
Union supported	No	No	Yes
Can consume other views	No	Attribute views only	All three types
BW equivalent	Characteristic / dimension	InfoCube	MultiProvider

A practical rule works well: build an attribute view when the output is a descriptive list, an analytic view when one fact table is measured against those descriptions, and a calculation view when the answer needs a union, a second fact table, or logic that a star schema cannot express.

SAP HANA Best Practices for Creating Information Models

An SAP HANA best practice is a standard followed while creating an object in the SAP HANA database. The best practices below apply to each object type.

PACKAGE:

• Create a top-level package such as “Development” for development work.
• Create a sub-package under the top-level package for each developer.
• More sub-packages can also be created, if required.

SCHEMA:

• Design your schema layout before the project starts. For example, DS_SCHEMA, SLT_SCHEMA, FI_SCHEMA, SD_SCHEMA, etc.
• Custom tables should sit in a separate schema.

TABLES:

• A table that will be used in reporting or OLAP should be of the column store type.
• A table that will be used in transactions or OLTP should be of the row store type.
• Give a comment or description for the table and column names 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_ means stored procedure
INPUT PARAMETERS	IP_PARA_NAME	IP_ means parameter
VARIABLES	VA_VNAME	VA_ means variable name

The naming convention only pays off if every model lives inside a planned package structure, which is created as shown next.

Creating a Package in SAP HANA Studio

Package: It is a container that holds all information about the model (attribute view, analytic view, calculation view, and so on) in a group.

Types of package: Packages are of two types, as below.

Type	Description	Icon
Structural	In a structural package, only a sub-package can be created. No information view (attribute view, analytic view, etc.) can be created in a structural package. Examples of a structural package are SAP, system-local, system-local.generated, and system-local.private.
Non-Structural	A non-structural package can contain information objects and sub-packages. This is the default package.

Uses of a package: A package groups all information models together and makes model transport easier. Both package types can be used in transport.

Steps for creating a structural package in SAP HANA Studio:

Step 1) In this step,

1. Select the HANA system, here it is HDB.
2. Go to the Content folder.

Step 2) In this step,

1. Select New.
2. Select the Package option.

Step 3) In this step,

1. Enter the package name. For example, “DHK_SCHEMA”.
2. Enter a description for the package.
3. Original language and Person Responsible are selected by default.

A non-structural package with the name “DHK_SCHEMA” will be created in the Content node, as below.

Step 4) Now, convert the non-structural package to a structural package.

1. Select the package “DHK_SCHEMA” and right-click on it.
2. Go to the Edit option for the package.

Step 5) In this step,

1. Select “Yes” for the Structural Options field.
2. Click on the OK button.

When “DHK_SCHEMA” is changed from a non-structural to a structural package, the icon style changes from to . This is an indication that the non-structural package is now converted to a structural package.

Steps for creating a non-structural package under a structural package as a sub-package:

A package is created as non-structural by default. In a non-structural package, other packages and information objects can be created. It is better to first create a structural package, and then create a sub-package inside it.

Step 1) In this step,

1. Select the structural package “DHK_SCHEMA” and right-click on it.
2. Select New -> Package.

Step 2) In this step,

1. Enter the sub-package name in the Name field.
2. Enter a description for it.
3. Click on the “OK” button.

A new non-structural package will be created as a sub-package under the DHK_SCHEMA package.

How to Activate and Validate an Information View

Saving a view only stores it in the design-time repository. Until it is activated, no runtime object exists and no report can read it. Activation generates a column view under the _SYS_BIC schema, and that generated object is what SQL and reporting tools actually query.

Step 1) Save the view. Press Ctrl+S in SAP HANA Studio. The view now exists as an inactive design-time object, marked with a diamond overlay in the Content node.

Step 2) Activate the view. Right-click the view and select Activate, or press Ctrl+F3. Choose “Activate” to deploy only this view, or “Redeploy” to rebuild the runtime object when the design-time definition has not changed but the generated object is missing.

Step 3) Read the Job Log. The Job Log view reports success or failure for every object in the activation batch. A failure entry names the object and the reason, so always expand the entry rather than relying on the summary line.

Step 4) Preview the data. Right-click the activated view and select Data Preview. The Analysis tab lets attributes be dragged to the label axis and measures to the value axis, which confirms that joins return the expected number of rows.

Step 5) Resolve common activation errors. Three failures account for most cases:

• Insufficient privilege on schema: _SYS_REPO lacks SELECT WITH GRANT OPTION on the schema holding the base tables. Grant it and activate again.
• Invalid column name: A base table column was renamed or dropped after the view was modeled. Refresh the data foundation and remap the field.
• Cyclic dependency: Two views reference each other. Break the loop by pointing one of them at the underlying table instead.

After a successful activation, the view can be queried directly with SELECT * FROM “_SYS_BIC”.”package/VIEW_NAME”, which is the fastest way to confirm the object exists before a reporting tool is connected to it.

SAP HANA Performance Optimization Technique

An activated view that returns correct results may still be slow. The rules below shape how the calculation engine executes the model.

• All information views and table views should be used with a projection node. A projection node improves performance by narrowing the column set.
• Apply filters at projection nodes.
• Avoid join nodes in a calculation view, and use a union instead where possible.
• Use input parameters or variables to restrict the dataset within an analytic or calculation view.
• Calculations should be done before aggregation.
• Hierarchies need to be redefined in a calculation view, because hierarchies of an attribute view are not visible in a calculation view.
• Hierarchies of an attribute view are visible in an analytic view.
• Labels of attributes and descriptions of measures defined in an attribute view, analytic view, or calculation view will not be displayed in a consuming calculation view. They need to be remapped.
• Do not mix CE functions and SQLScript in the same information model.

Join design also affects runtime cost. A referential join can be pruned entirely when no field from the right table is requested, which is why join type selection deserves as much attention as node placement.

Why Calculation Views Replaced Attribute and Analytic Views

SAP deprecated attribute views and analytic views along with the XS Classic model and the SAP HANA Repository. The deprecation was announced for SAP HANA 1.0 SPS 12 and restated for SAP HANA 2.0 SPS 02. Deprecated means still supported on SAP HANA 2.0, but not carried forward to SAP HANA Cloud.

The reason is consolidation rather than replacement of function. Successive support package stacks extended calculation views until they absorbed the capabilities of the other two types:

• A calculation view of data category Dimension, with a projection node at the semantic layer, does the work of an attribute view.
• A calculation view of data category Cube with a star join, with an aggregation node at the semantic layer, does the work of an analytic view.

One view type also means one optimizer path, one set of modeling patterns to learn, and one migration target. SAP ships a migration tool that converts existing attribute views, analytic views, and script-based calculation views into graphical calculation views and table functions. Existing views continue to run, so migration can be scheduled rather than rushed, but new development should start with calculation views.

FAQs