Understanding the Different Types of Views

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

After completing this lesson, you will be able to:

  • Explain the types of views used in graphical modeling

Calculation Views

Before creating calculation views, you must fully understand the purpose of each type of calculation view so that you can choose the type that meets your requirements.

SAP HANA Cloud supports the following types of graphical calculation views:

Calculation View TypePropertiesDefault Upper Node
DIMENSION

Used to create re-usable master data views. This is mostly done to provide dimensions to the CUBE with Star Join, but also possible to consume DIMENSIONS directly by some clients.

Projection
CUBEUse for data analysis with multidimensional, ad-hoc navigation, but no dimensions allowed.Aggregation
CUBE with Star JoinSimilar to a CUBE dimension view except the ability to join dimensions using calculation views of type DIMENSION.Star Join

DIMENSION Calculation Views

Watch this video to learn more about the Dimension Calculation Views.

CUBE Calculation View

When you want to create a view that includes measures, you use a calculation view of the type CUBE. By default, the result of this type of view will always be aggregated by the attributes requested by the external query. So, even though the calculation view may be able to provide many attributes and measures, the measures are always automatically aggregated by the attributes that were requested by the query. This behavior is different to the SQL Access Only calculation view type.

This type of calculation view is optimized for OLAP style analysis, where slice and dice is required over the measures by any combination of attributes within the model.

A CUBE calculation view does not allow dimensions (based on DIMENSION type calculation views) to be included. All attributes used in analysis come from within the defined model.

CUBE Calculation View with Star Join

An extension to the basic CUBE type of calculation view is the CUBE with Star Join.

In addition to the capabilities of the CUBE type of calculation view, a CUBE with Star Join calculation view allows you to join DIMENSION calculation views to the basic model so that you significantly expand the analysis capabilities. For example, if you create a basic sales cube which provides only limited attributes such as customer and product, you could then join the PRODUCT dimension calculation view to provide large numbers of useful attributes that further describe the customer or product. You could then aggregate the sales revenues of the cube by customer industry, product color, and so on, rather than by the dimensions.

Note
For SAP HANA on-premise, there is an additional calculation view type: SQL Access Only. This type of view was chosen to prevent its direct consumption by external tools. For SAP HANA Cloud, you are no longer able to select this type of calculation view but you can flag any type of calculation view as End User View to achieve the same behavior. Calculation views of the type SQL Access Only, that are imported from SAP HANA on-premise, are supported with their intended behavior. You simply cannot create new ones.

Virtual Data Models for Multidimensional Scenarios

In this scenario, a Cube with Star Join calculation view enables multidimensional reporting that leverages the source transactional data (joining the VBAP and VBAK tables) and then adds three DIMENSION calculation views Product, Customer and Employee

Multidimensional tools support hierarchies for navigation, filtering and aggregation, as well as prompts (variables and input parameters) for efficient pre-filtering of data.

Supported Data Source Types in Graphical Calculation Views

The following is a list of the main data source types that are supported in SAP HANA Cloud calculation views. It makes a distinction based on whether the data source is located in the same database as the calculation view that consumes it, or in a different database (tenant) within the same multi-tenant system.

Supported Data Source Types in SAP HANA Calculation Views

Data Source TypeLocated in the Same DatabaseLocated in Another Database in the Same Multi-Tenant System
Row TableYesYes
Column TableYesYes
SQL ViewsYesYes
Graphical Calculation ViewsYesYes
Table FunctionsYesNo
Virtual TablesYesYes
Caution

If a table has been created INSIDE an HDI container using an SQL statement (that is, without using a design-time file of type .hdbtable), you might see this table as an available data source when creating a calculation view, but the build of the calculation view will fail.

As an aside, you will learn in the unit, Modeling the Persistence Layer, that this way of creating a table (without a design-time file) in an HDI container is not recommended at all.

Row or Column Table

To identify whether an existing table in SAP HANA is a column table or a row table, you have the following options:

  • From the Database Explorer, connected to an HDI Container or the Catalog view of a classical schema:

    • For a table: check the table icon, or open its definition (right-click the table and choose Open) and check the TYPE field.

    • For a synonym: open its definition (right-click the synonym and choose Open). The definition mentions For Table (Column Store) <target table name> or For Table (Row Store) <target table name>

  • From the SQL Console:

    • Query the system table M_TABLES.

      Example:

      Code snippet
      SELECT "SCHEMA_NAME", "TABLE_NAME", "TABLE_TYPE" FROM "M_TABLES"
      WHERE "SCHEMA_NAME" = 'TRAINING'
      AND "TABLE_NAME" = 'SALES_DATA'
      Copy code

Table Functions

Table functions can be used to define complex data sources using SQL Script. These data sources can then be consumed by a calculation view.

As a general rule, you should always try to use the standard functionality of the calculation view using the graphical editors but sometimes you might need to revert to code to produce the desired outcome.

DIMENSION Calculation Views

DIMENSION calculation views are used to provide context. This context is provided by master data tables which give meaning to data.

For example, if data in a car sales database only contains a numeric ID for each dealer, you can use a DIMENSION calculation view to provide information about each dealer. Using this method, you could then display the names and addresses of the car dealers and many other useful descriptive attributes that give context to the data.

DIMENSION calculation views are used to select a subset of columns and rows from master data tables. DIMENSION calculation views are not valid for measures. They can contain only attributes.

A DIMENSION calculation view does not need to be based on a single table. On the contrary, you can use them to join master data tables to each other to create complex views. For example, to join Products to Product Categories.

Shared DIMENSION Calculation Views

DIMENSION calculation views are reusable objects and can be shared between several CUBE calculation views.

For example, the product attribute view can be used both in a purchase order CUBE calculation view and in a sales order CUBE calculation view.

Supported Characters for Views Names and View Objects

SAP HANA supports all the Unicode characters in object names; that is, views, columns, input parameters, and so on.

However, a number of characters are forbidden in the object names. These characters are \ / : * ?" < > | . ; ' $ % , ! # + and the space character.

Supported Types of Nodes

DIMENSION calculation views support most types of nodes, such as Projection, Join, Union, Aggregation, and Ranking. This allows a very flexible design of DIMENSION calculation views. The most commonly used nodes are Join nodes (to join master data tables) and Projection nodes (to filter data, select specific columns from the master data tables, and create calculated attribute columns).

Calculated Attributes

It is possible to create additional calculated columns in a DIMENSION calculation view.

For example, you have two columns containing the first and last name of the customer, but you would like to have all this information (first and last name) in a single column. You can do this by creating a calculated column based on string manipulations.

  • The calculation can be an arithmetic or just a character string manipulation.

  • Calculated columns also support non-measure attributes as part of the calculation.

  • It is possible to nest calculated columns, so that one calculated column in turn is based on other calculated columns.

Time-Based Dimension Calculation Views

Apart from the Standard type for calculation views, the TIME type is used to create DIMENSION calculation views that apply exclusively to time data.

Tables that contain measures generally have date or date-time columns to clearly define when an event occurred, or what period of the year a measure relates to, such as a month or a quarter. The purpose of these time-based DIMENSION calculation views is to provide additionaldate/time attributes that expand the navigation possibilities, for example, by deriving the week number or the fiscal period from a date column.

You can use time-based DIMENSION calculation views to build a time hierarchy that corresponds to your needs, and convert a timestamp into simpler attributes, such as year, month, or day of the week. This means that you do not have to provide all possible time attributes in the source record or create complicated SQL functions to generate the additional attributes. All we requires is a timestamp in the source record and from that we can derive all possible time attributes using the generated hierarchy.

Among others, one key benefit of using time-based DIMENSION calculation views is that you can easily summarize the data that the calculation view exposes. For example, based on daily data, you can choose to retrieve summarized/aggregated data by calendar week, or by month/quarter, or provide a drill-down over the entire time spectrum, for example, from year to half-year, to quarter, to month, to week, to day. You can even go further and drill to hour, minute and seconds.

Note
Even if it does not appear explicitly in the user interface, a calculation view of the subtype Time is automatically created as a DIMENSION calculation view. You can check that in the semantics of the view (in the View Properties tab).

Different Calendar Types in Time Calculation View

Measures in Calculation Views

Compared with attributes, measures bring an additional level of complexity to calculation views. In particular, you must define the relevant behavior of views when measures are aggregated based on a number of attributes.

Note
Attributes are regular columns and, generally, they do not need to be aggregated. Aggregating data in a DIMENSION calculation view would just be like a Select Distinct statement.

In SAP HANA Cloud, two types of graphical modeling objects handle measures:

  • Calculation views of type CUBE

  • Calculation views of type CUBE with Star Join

In a calculation view, the measures can originate from several data sources, that is, several tables (local tables or via a synonym), calculation views, table functions, and so on.

For example, you can join two different tables and extract measures from both of them.

Standard Preview or Custom Query

When you design a calculation view, you have to keep in mind that the output data depends heavily on the query that the client tool executes on top of the view. Common criteria impacting the result set include the following:

  • Selected attributes (and measures)

  • Aggregate functions applied by the client query

  • Ordering defined on one or several columns

To check the behavior of a calculation view that has a complex calculation scenario, we recommend that you create custom SQL queries on top of the view and test several scenarios. This is especially useful when some of the measures, or all of them, cannot be aggregated with the common Sum function, for example when working with ratios, averages, counter columns, and rank nodes.

This approach enables you to enhance the design of the view, if possible, or at least to understand how end users must query the view to get relevant results.

Caution
Always keep in mind that calculation views in SAP HANA Cloud have a special behavior because they are instantiated at runtime, which means that their execution depends on the query that is run on top of them by the front-end tool (SQL console, reporting tool, and so on).

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