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.
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:
Attribute views - These define the master data views. We will introduce the different options which are available for creating table joins and we will explain how the calculated attributes can be added to a view.
Analytic views – These can be used for calculations and analyses based on transactional data with the help of a star schema. We will explain how you can define simple or calculated key figures, and how you can add dimensions.
In HANA, data modeling is possible only for the tables stored at column level (stored procedures).
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 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:
as components of other types of views, especially as dimensions of analytic views or, for a more general purpose, as nodes in calculation views.
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.
key attributes - These refer to those view attributes which specify the unique entries. These play an important part when they are used as the dimensions of an analytic view.
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 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:
Data Foundation – This is used to define the fact table.
Logical Join – This is used to add dimensions, defined by the attribute view, and to define the calculated attributes and the restrictions.
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.
by Mircea Vădan
