CDS Query with Formulas
In the following we discuss some technical details of a simple transient query based on an ABAP CDS View. The delivered background information should help you to check/analyze such queries sin case they do not work as expected.
General Information to this Topic
The Analytics Annotation Analytics.query is set to true which means that this is query which can be processed by the Analytic Manager. The technical name of this test query is 2CZSTPESQLVIEW_Q3 ( 2C<SQLViewName>, see also CDS Views: Naming Conventions). It is based on a simple CDS view of type CUBE (see screenshot further below). It contains some fields of the underlying table and two simple formulas. In addition there are the following two retsrictions: Airline = 'AA' and FlightConnection = '0017'.
Please note that the formulas do not use the same annotation. The Cube is very simple:
When we run the Data Preview in the Hana Studio we get the following result displayed:
The formula Real_MaxAvailableSeats does not display the expected values. The reason is that the annotation AnalyticsDetails.query.formula is only evaluated by the Analytic Manager.
When the query is executed in RSRT, all key figures deliver the correct values:
So the calculation of the formulas is done in the Analytic Engine (in ABAP), in contrast to the Data Preview (HanaStudio) where one formula was ignored and the other one calculated on the Hana database. This we can 'see' when checking the PlanViz trace. First we take a look at the open SQL generated by the Data Preview
The system accesses the CDS query, the statement is very simple. We compare this with the SQL displayed by the query when run in RSRT:
The CDS Cube ZSTPE_FLIGHT is read. We can see that the Analytic Engine has 'translated' the CDS query defintion into a propper SQL statemnt which e.g. includes the restrictions. The calculation of the formulas is done afterwards in the OLAP Engine.
If you want to take a closer look at what exactly happens when the function Data Preview is used, it is necessary to record an SQL trace (ST05)
Please note that there is an additional filter regarding the client (see CDS Access Controls). We copy this statement and put it (slightly adapted, the propper schema has to be added) into the SQL console of the Hana Studio in order to be able to check the execution plan (PlanViz)
Te planviz excecution plan contains many details. As an example we just were searching for the formula which is calculated on the HDB: NumberOfAvailableSeats = MaximumNumberOfSeats - NumberOfOccupiedSeats = SEATSMAX - SEATSOCC (on table SFLIGHT):