XSA Blog Series - Virtual modeling

[MKlare]

Introduction

In the fourth part of our XSA blog series we go into the details of virtual modeling and reporting. How is persistent data modeled further? Which virtual modeling options are there? Which new features do XSA Calculation Views have? How can objects be selected in other HDI containers? We will answer all these interesting questions in the following blog.

Virtual modeling basics

First, we considered the basics In the first blog with the second blog specifying the persistent structures. The third blog handled data loading so as to provide our system with content. Virtual modeling brings us into contact with old favorites from the XSC world but this time with new features.

One of the strengths of in-memory databases is virtual modeling. In contrast to classic databases, not every logic needs to be persistent to obtain good query times. Many of the common and even complex calculations can now be done on the-fly”.

For non-persistence modeling, the XSA world provides the following 4 object types as summarized in the following figure.

Fig. 1 – Virtual modeling artefacts

The following section serves as an example for the new objects. We want to join our two BillHeader and BilllItem tables and display a selection of the fields.

CDS views form part of CAP which we briefly outlined in the second blog. Involved are views based on the Core Data Services, with their own syntax and integrated in the CAP model. They can, for example, be directly saved in the schema.cds file which we created in our example project. We can directly access the corresponding associations from having saved them in our CDS model and do not need to write a Join-statement.

 

Fig. 2 – Example .cds view

 

The „cds build“ command generates the appropriate artefact which, in this case, is a .hdbview file.

Fig. 3 – Generated .hdbview

These .hdbview artefacts can also be directly written themselves. This artefact makes it possible to write „classic“ SQL and define a normal view

Fig. 4 – Example .hdbview

A table function enables the use of SQLScript and thus the use of other elements, such as IF-ELSE constructs. A drawback of the Table Functions is that the output is fixed and always needs to be specified in the correct sequence with technical name and type. The following Table Function uses the associations and only returns data on a Monday.

Fig. 5 – Example .hdbfunction

All 3 object types stated so far are strongly based on SQL and require appropriate prior knowledge. Moreover, optimization of these virtual objects largely depends on the developer’s know-how given that joins or calculations are carried out which may be of no importance for the actual query.

For many years now, SAP has been developing an object that is perfect for reporting purposes, namely Calculation Views. Strictly speaking, we need to talk about HDI Calculation Views at this point as they have a wider functional scope than the classic Calculation Views in the XSC environment.

Fig. 6 – Example .hdbcalculationview

We intend to look more precisely at this central and vital object type in the following section.

Calculation Views

Calculation Views are found widely in XSC systems and used, for instance, in the mixed modeling approach of a BW on HANA or BW/4HANA system. In a nutshell, Calculation Views provide nodes for graphically modeling standard functionality (projections, joins etc.). A particular feature of Calculation Views is that only the logic required by the query is carried out. This means, for instance, that whole joins or calculations can be left out if the relevant fields are not in the selection.

To consider all the features and options of Calculation Views at this stage would exceed the scope of this blog. That is why we are limiting ourselves here to a selection of new XSA features.

The following node types are currently selectable for HDI Calculation Views:

Fig, 7 – XSA node types

Those with XSC experience notice straight away that XSA has a number of new features. There is, for instance, no further limitation on Equi-Joins. As a result, Join conditions are definable with other operations as equal:

Fig. 8 – Non-Equi Joins

A highly interesting feature for performance optimization is hidden in the properties of an Inner Join:

Fig. 9 – Optimize Join Columns

Columns selected in a Join statement are always included for selection in a query even if they are not explicitly requested. Activation of the top option results in these columns also not being selected if the join is not needed. This allows for an even more pronounced aggregation of the data.

A longed for feature is the Windows functions scope. Analytical functions are involved here which are executable to a high performance on the HANA database. At one time this powerful tool was only implementable as Table Functions to be then incorporated in Calculation Views as projection.

Thanks to the „Window Function“ node, they are now directly executable in a Calculation View. All functions are available in the update and the relevant parameters can be graphically updated:

Fig. 10 – Window Function example

Debugging represents a major challenge in working with SQL. In contrast to imperative languages, an SQL statement has no loops or step-wise statements. A statement is either fully executed or not at all. That is why looking for errors is difficult particularly with complex and nested views. For this the Calculation View provides a practical feature: the Debug View

Fig. 11 – Start Debug

A click on this option opens up a new tab in the Debug Query properties

Fig. 12 – Generated Debug SQL

This tab firstly comprises a generated SQL statement which with the appropriate button is executable. At first glance it does not seem to be a particular feature – the Data Explorer can also be used to execute a SQL statement onto a view. It is only when clicking a sub-node in the Calculation View that the particular feature is noticed. The Debug Query tab is now also available in this sub-node.

Fig. 13 – Debug nodes

Views for the individual nodes, which always follow the name convention “[Name of the Calculation View]/dp/[Name of the node]” are generated in the background. It thus becomes convenient to select and analyze all sub-findings of the nodes. This, in classic XSC, was only possible via detours and Data Previews – but not directly in the IDE.

All virtual objects initially only have access to those tables and views which exist in their own HDI container. But how can objects be selected which exist in other HDI containers? The isolated architecture of HDI containers makes this possible only with new artefacts and a manual steps.

Cross-Access HDI Container

In this section we want to show how objects in another HDI container can be accessed. Recap: a HDI container represents an isolated area on the database which essentially only knows about itself. No other HDI containers and their objects are visible. In our example we have the source HDI container (HDI_SOURCE) which wants to read a table from the target HDI container (HDI_TARGET). The following illustration shows the setup for cross-access with HDI containers

Fig. 14 – Cross-Access procedure

The following steps are needed:

1. Creating roles (.hdbrole) in HDI_TARGET


2. Connecting the HDI containers from adjustment to mta.yaml in HDI_SOURCE


3. Creating Grant (.hdbgrants) for reading out the roles


4. Creating Synonym for target object

This rather complex approach is the result of HDI container isolation. The HDI container (HDI_TARGET) firstly needs to define roles which permit access to its objects. The source container (HDI_SOURCE) uses a Grant artefact to incorporate these roles. Unfortunately, it is  not possible to directly access the objects. Every object needs a “synonym” which can then, for instance, be used in a Calculation View.

A practical tip: many of the artefacts have their own graphical updating dialog. However, all files can be directly edited with a right click via the Text Editor.

Fig. 15 – Updating in Text Edito

We have created an appropriate HDI container with a table

Fig. 16 – Target table in HDI_TARGET

For access, we generate the required roles in the roles folder:

Fig. 17 – Example .hdbrole without Grant

Fig. 18 – Example .hdbrole with Grant

There is now the question of why two roles are needed. The second role in its technical name has a hashtag (#) and via “Grant Option” assigns rights which, as a result, can be passed on. This two-role interplay is a technical necessity since an HDI consists of several users. The role without Grant is assigned to the _RT User (Application User), the _OO (Object Owner) User obtains the role with Grant. This role can, of course, be limited even further. In our case, reading and writing rights are assigned to all objects in the HDI container. These roles firstly need to be deployed before our HDI container can access them.

Now 3 steps need to be carried out in our XSA blog project. First of all, we update the target HDI container in the central control file (mta.yaml). Whilst this theoretically can be manually inputted, BAS provides a convenient option here in the GUI:

Fig. 19 – Connection HDI Container (1/2)

The required HDI container can simply be selected in the now-opened window:

Fig. 20 – Connection HDI Container (2/2)

In this way, the mta.yaml file is automatically adjusted. In addition, the .env file in the db folder is extended by the appropriate service.

The second step is creating the Grants file in the “cfg” folder:

Fig. 21 – Example .hdbrole

In this file, the appropriate roles are assigned to the Application User and Object Owner.

Both the HDI containers are now connected and the required rights and roles created. The synonyms need to be updated in the third and final step. Every object that is to be read from the target HDI container must be named in the synonym file.

Fig. 22 – Example .hdbsynonym

The object can now be directly used in our Calculation View.

Fig. 23 – Synonym in Calculation View

As mentioned in the second blog, the isolated HDI containers provide for greater focusing on the design. The decision on which objects are to be in an HDI container and which dependencies exist has a prime role to play in XSA. In this connection, the following facts are important in a design:

• An HDI container can only be deployed as a whole (with all its objects)


• Target objects from an HDI container have no connection to their use as synonyms. An error develops solely in the reading HDI container


• Connections of HDI containers result in dependencies with the Deployment since the object referencing the Synonym must always exist

Summary

XSA reveals its full potential in virtual modeling. Depending on application and know-how, the different artefacts enable the right tools to be selected. In particular, the performance and debugging scope of the Calculation View catch the eye.

The only downside is the connection of various HDI containers. The isolated HDI containers explain why it is necessary to have several manual adjustments and diverse artefacts. Especially with large Data Warehouse applications, updating a synonym for each new object is somewhat obstructive.

We deal with NodeJS in the fifth and final part of our blog series. Integration of NodeJS in XSA allows complex services to be developed which with pure database means and SQL would not be possible.