Introducing Calculation Views

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain modeling objects

Key Vocabulary of Modeling

Before introducing modeling in SAP HANA Cloud, you must become familiar with some key concepts that are frequently used when reporting on financial or operational data, for example:

  • Measure

  • Attribute

  • Dimension

  • Star schema

  • Hierarchy

  • Semantics

Measure and Attribute

When you report on data, you have to distinguish between the following important concepts:

  • Measure

  • Attribute

Measure Versus Attribute

 MeasureAttribute
DefinitionA numeric value, such as a price, quantity, or volume, on which you can process arithmetic or statistics operations, such as sum, average, top N values, and calculations.An element that is used to describe a measure.
Examples
  • Number of products sold

  • Unit Price

  • Total Price

  • Product ID

  • Product Name

  • Customer ID

  • Customer Name

  • Sales Organization

  • Sales Org. Country

  • Sales Org. Region

  • Currency

Attributes are used to filter or aggregate the measures, in order to answer questions such as the following:

  • What are the total sales originating from Sales Org. located in the EMEA region?

  • What is the sales revenue generated by the product Cars?

Note

One key objective of modeling in SAP HANA Cloud is to create a relevant association between attributes and measures to fulfill a particular reporting requirement.

Dimension

In a number of cases, analyzing the measures is easier if you group attributes together by dimension.

In the example below, the sales organization would be treated as a dimension, with the following associated attributes:

  • Country

  • Region

Similarly, a Product ID dimension could be associated with several attributes, such as product name, product category, or supplier.

Star Schema

Watch this video to learn about the Star Schema.

Note
The term fact table is used in a generic way. Later in the course, you will learn that calculation views in SAP HANA Cloud allow you to create this fact table.

Hierarchy

A hierarchy is a structured representation of an organization, a list of products, the time dimension, and so on, by levels.

It is used to navigate the entire set of members with more ease (the location of the company, the products, or the days, weeks, months, or years) when analyzing the data.

Watch this video to learn about the hierarchy concept.

Note

Similarly, the list of products that a company sells could be organized into a hierarchy, by classifying the products by product area or product type.

Semantics

The term semantics is used to describe what a piece of data means, or relates to. For example, a piece of numeric data that you report can be of different types. Here are a few examples:

  • A monetary value

    For example, the total amount of sales orders would need to indicate the currency (for example USD, EUR, or GBP)..

  • A quantity, weight, volume, or distance

    For example, the quantity would need to specify the unit of measurement in which the data is expressed.

During modeling, we add semantics to individual columns and even the entire model to provide additional metadata. This metadata is read by consuming clients, such as reporting tools, so that the data is displayed correctly (e.g. currency or dates) or is calculated correctly (e.g. averageinstead of sum).

Calculation Views in SAP HANA

Calculation views are used in SAP HANA Cloud to build a Virtual Data Model (VDM), based on the data that resides in the SAP HANA Cloud database.

The purpose of calculation views is to organize the data from the individual transactional tables, and to perform a variety of data calculations, in order to get a relevant and meaningful set of measures and dimensions or attributes to answer a specific reporting need. You can make the data more meaningful than it is in the source tables by customizing the column names, assigning label columns to key columns, and calculating additional attributes.

Benefits of Calculation Views

Design-Time Versus Runtime Calculation Views

Caution

A view cannot expose its data before a runtime version of this view is successfully created in the SAP HANA Cloud database catalog. Similarly, any change to a design-time view is only visible in a query after the runtime view has been successfully updated.

Deploying Calculation Views

In many cases, deploying a single design-time object creates multiple corresponding runtime objects in the database container. For example, in addition to the main column view itself which contains all measures, there might also be additional column views generated that materialize the hierarchies.Depending on the query scenario, the most optimal column view will be selected automatically.

Apart from column views, deploying a calculation view also creates the necessary metadata that provides detailed information about the view, and this enables to view to be consumed by external tools.

When you deploy a source file with the Business Application Studio, SAP HANA Cloud creates the corresponding runtime object in a container, which is an abstraction layer for a database schema. This container is specific to the HDB module of your project.

Analytical Versus Transactional Requirements

In transactional applications, such as SAP ERP, the underlying data (stored in physical tables) is generally handled by the application server. This layer is necessary to handle the business process logic. The application usually reads and writes to the database SAP HANA Cloud database.

Calculation views are used only to read data, without making any changes to the SAP HANA Cloud database.

Analytical applications that do not need to write to the database can bypass the application server and directly query the SAP HANA Cloud calculation views, where data is calculated on-the-fly, in the SAP HANA Cloud in-memory database.

Checking the Output of a Calculation View

When building a calculation view, the SAP Business Application Studio allows you to use a feature called Data Preview to check the results. This simply launches the tool SAP HANA Database Explorer where your results are displayed. You can choose to view the default SQL code that generated the results. This is a simple SQL query that selects all columns with no filters on the calculation view. You can also modify the query to remove columns or add filters etc. in order to test the calculation view against different query conditions.

SAP HANA DB Explorer is a web-based tool you use to:

  • Browse database objects
  • Execute SQL in the SQL Console
  • Import and export data
  • Create and query remote sources
  • Debug, analyze and test SQLScript
  • View trace files

Watch this video to learn about the Standard Preview or Custom SQL Query.

Standard Data Preview Features

Even though the Database Explorer is not a reporting tool, it still offers analysis functionality that can be useful during modeling or troubleshooting. It is comprised of the following tabs, each offering specific capabilities:

Database Explorer Tabs

TabDisplaysUse Case
Raw DataAll dataBasic display of contents
AnalysisSelected attributes and measures in tables or graphsProfiling and analysis

Choosing the User who Executes the Data Preview

Up to SAP HANA Cloud QRC 3/2021, the data preview triggered from within the Calculation View editor (by right-clicking the up-most node, below the Semantics node) or right-clicking a design-time object in the DB module structure, was executed with a technical user ..._RT who has, by default, extensive privileges on the container's objects plus all the privileges granted to the application user in case an external schema or container is accessed.

With QRC 4/2021, from the Calculation View editor, it is possible to choose whether you want to trigger the data preview with this ..._RT user(Data Preview) or another user (Data Preview With Other Database User). This allows you to apply session contexts (client values, analytic privileges, and so on).

You must choose the other database user among the users referenced in connections defined in the Database Explorer. In particular, to query a calculation view with a standard database user, this user must be assigned to an SAP HANA Database (it is not possible to add an HDI container with another user than the ..._RT user.

You can find more information about users and security in the section Security in SAP HANA Modeling of this course.

Note
To avoid Data Preview delay, the list of Database Explorer connection is not filtered. Make sure to choose a relevant connection, that is, one made with a user who has access to the HDI Container schema.

Save progress to your learning plan by logging in or creating an account