EDITING BOARD
RO
EN
×
▼ BROWSE ISSUES ▼
Issue 53

View Modeling in SAP HANA

Iulia Bicu
Software Developer @ Siemens România
PROGRAMMING

When using SAP HANA, you can calculate the outcome of your business directly, on the original data, in the main memory, without any need to transfer the data. Many of these calculations can be graphically modeled as special data views in SAP HANA Studio, without the need of any supplementary code.

This paper focuses on the options that SAP HANA makes available for data modeling. These options lie at the core of the development process which produces HANA applications. The business data of a given field are memorized (usually in a normalized format) in tables from the database, which are connected via a foreign key (the so-called entity-relationship model). With the help of this data model, unique registrations can easily be made, selected or modified. Nevertheless, if the access to the data becomes more dynamic and complex, or if certain analyses or checks are necessary, the data needs to be transformed.

Types of views in SAP HANA

One example that can illustrate this topic can be a relatively simple analysis of flight bookings, and of the ratio of occupied seats throughout a flight, based on the data in the SFLIGHT model. To create these analyses, we will use the different modeling options supplied by SAP HANA, and we will explain their properties and their usage.

Figure 1. Types of views in SAP HANA

We will tackle the following types of views:

In HANA, data modeling is possible only for the tables stored at column level (stored procedures).

Processing view modeling in SAP HANA

SAP HANA basically has three types of engines that it uses. SAP HANA is based on the views requested by the model. (see Figure 2.)

Figure 2. Calculation engines in SAP HANA

The Join engine: This is used for attribute views.
Before describing the way in which attribute views are modeled, let us go through the most important concepts. Because attribute views can be used to create data views based on several tables which have different join operations, these can also be called join views. Because join operations play a major role in the work with attribute views, the access to attribute views is manipulated by the Join engine in SAP HANA.

The OLAP engine: This is used for analytic views. The term Online Analytical Processing -OLAP is often used as a synonym for analytical systems. OLAP describes the multidimensional analyses based on a star schema, while the data in a data warehouse can also be organized in flat tables in the database.

The Calculation engine: This is used for analytic views with calculated attributes and calculated views. By aggregating the data quickly and by performing the calculations on the aggregated data, you can achieve two things. First, you can reduce the data set for further processing, and, therefore, the data set which, for instance, must be transferred from the OLAP engine to the Calculation engine. Second, you can reduce the number of calculations under certain circumstances (for example, you can convert sums to a single currency).

Attribute Views

Attribute views contain a number of fields (columns) in the tables of the created data base, which are connected via foreign keys.

These are particularly relevant in the following situations:

In this paper, we will create an example to demonstrate various functional aspects.

When we model attribute views, we must differentiate between the following concepts:

You can add columns to one or more tables or define supplementary calculated columns.

The main advantage of attribute views lies in the possibility to define a view which is based on fields from several tables. Compared to the ABAP Dictionary, the views can contain only inner joins. However, the attribute views in SAP HANA allow you to use a greater variety of joins.

Attribute views can be defined from the Modeler perspective in SAP HANA Studio. To create a view, select NEW • Attribute View from the context menu of the node package. The editor used to define an attribute view contains two sections: Data Foundation and Semantics.

Figure 3. Types of views in SAP HANA

Data Foundation is used to add tables, to define joins, and to add attributes.

Figure 3. is a simple example based on the SFLIGHT table. By selecting the Semantics node, you can further maintain the metadata for your views. For example, you can specify whether an attribute is a key field of the view. Remember that each attribute view must contain at least one key field. Moreover, you can define attribute labels or hide attributes which can be useful in the context of calculated fields.

We will add the SCARR and SPFLI tables to the SFLIGHT view, and the result shows you the name of the airline company, information about the departure and the destination of each flight (see Figure 4).

Figure 4. The result of the SFLIGHT view

We will move forward and we will dynamically calculate the columns of a view, by explaining how an attribute view can be used to read the data from different tables, using different types of joins.

We will define a calculated attribute, with the ROUTE name, which will display the route, the place of departure and its airport, plus the arrival destination and its airport.

Figure 5. The ROUTE attribute and its results

Analytic View

Analytic views are special views in SAP HANA which are used to calculate and analyze key figures. For example, we can model an analysis of the flight bookings based on the features of the customer and their flight. In such an analysis, the booking prices and the luggage weight will be examined as key figures. Moreover, we can define another calculation based on the luggage weight, calculation which shows whether we have extra luggage (more than 20 kg).

The analyses usually focus on transactional data (purchase orders, documents, invoices, etc.). The corresponding table is mentioned as a fact table. The data includes one or more key figures or measures – for instance, the invoice value – which is relevant for the data analysis. Fact tables usually contain a large number of entries. Moreover, fact tables can contain data from several tables in the database. The key figures must originate from a single table and the attributes of the other tables are used as foreign key. A typical example would be the fact tables which contain the header data and the line elements.

Similarly to attribute views, analytic views are created in SAP HANA Studio via the contextual menu in the package, via Modeler. The editor for analytic views entails three sections:

Calculation View

Calculation views are used each time the capabilities of attribute views and analytic views cannot meet the requirements. This happens when it is necessary to flexibly combine several views, as in Figure 6.

The main difference between a calculation view and the other view types introduced so far is that the calculation views can combine any view type. A calculation view is based on calculation model that consists of nodes and operations. These nodes can be tables or other types of views. Calculation views are graphically modeled in a tree-like structure. Each leaf actually represents a table or a view. The other nodes define data operations.

The calculation views are created using the same procedure as in the case of attribute views and analytic views.

Figure 6. Calculation View

Calculation views can be created using the graphic editor or the SQL Script editor.

I hope that, after you read this paper, you will have a better understanding of what the model data can do for you, with the aid of the three view types in SAP HANA.

Sponsors

  • comply advantage
  • ntt data
  • 3PillarGlobal
  • Betfair
  • Accenture
  • Siemens
  • Bosch
  • FlowTraders
  • MHP
  • Connatix
  • MetroSystems
  • BoatyardX
  • Colors in projects

VIDEO: ISSUE 96 LAUNCH EVENT

Iulia Bicu wrote also