In a BW/4 HANA system we generally assume that the query functions will also be executed within HANA and thus will profit from the speed advantages of HANA. Nonetheless, queries often have long runtimes, especially if the queries are amended or expanded. This blog will present a short overview of possible causes of an analysis option. Due to the scope of this topic, this blog cannot give a complete overview of this subject and can only cover part of it.
Starting scenarios
This should already be familiar to many people; at the beginning the query still runs very quickly and performs well. But after a certain amount of time or if the query is expanded (by just a few values, formulas, etc.) the response times slow down dramatically. Not only the user but also the BW consultant start to ask themselves why under certain circumstances just one value can cause such greatly worse behavior in the response times. At first glance the expansion or change that has been made hardly differs at all from the previous functions in the query, for example, only differing by an additional formula with an exception aggregation. The query has not exhibited such behavior with other formulas and values, also with exception aggregations. This therefore poses the question to all those involved as to what the causes could be and how they can be analyzed easily.
In my concrete example I will take a query that has been expanded by three values. These three values are based on a counter and two formulas that build on one another. This also works with exception aggregations. If the last value is now taken into the outline of the query, the response time of less than 20 seconds increases to more than 3 minutes.
Factors for execution in HANA
Before I go into my concrete example in more detail, I would first of all like to make a short and incomplete listing of the factors that hinder the execution of a query or of constituent parts of a query in HANA and which force a detour via the slow OLAP. The factors for this can exist at multiple levels and can be, among others:
- Status of the system: HANA revision, BW release and current Patch Level
- Data model and InfoProviders used
- Settings in the InfoProviders and the query
- Definition of the individual structure elements of the query
In the following I would like to explain these points briefly and give some information of further sources.
Status of the BW system
Here the status of the system concerns the HANA database itself, as well as the release or version status of the BW/4 HANA system. Above all, the use of exception aggregations in a query is of importance. In lower version / release levels of the BW/4 HANA system not all the exception aggregations can yet be executed directly at the HANA database. However, in the meantime (SAP HANA 1.0 SP12 or higher) these can all be executed on the HANA database. An overview of which exception aggregations and which formula operations are supported in the relevant version / release levels can be found in the SAP help (for the relevant level or status) under the path: Analysis: Model analytical query Work with query Define query in the Editor Change runtime properties of the query (tab Runtime Properties) Runtime profile properties Operations in SAP HANA Exception aggregation in SAP HANA.
Under certain circumstances specific preconditions must be met for individual exception aggregations. An example of this would be “first / last word”. OSS Notification 2156123 describes this in more detail.
Data model and InfoProviders used
The data model on which the query is based and the InfoProviders of the query that are used there likewise have an influence on possible operations within HANA. If a Calculation View is used as the basis, then possible NULL values there could hinder execution in HANA. In addition to the Calculation View, it also depends on the setting in the CompositeProvider for the characteristics to be used, whether or not the referential integrity was confirmed for the characteristic of the exception aggregation.
A push-down on the HANA can be prevented by the definition of the CompositeProvider itself, for example, through the existence of so-called “Ambiguous Joins”, hence, if Non-Unique Joins had been defined (with cardinalities such as 1:n, n:m) or else in definition problems such as the CMP problem.
Detailed explanations of the above-mentioned points can be found in an excellent contribution in the SAP SCN Wiki with the title “How to check why exception aggregation is not pushed down to HANA DB”. These points are discussed in detail there and moreover the CMP problem in a CompositeProvider is explained with an example that is easy to understand.
Settings in the query and the CompositeProvider
Settings can likewise be made in the query itself that either permit or prevent execution within HANA. This also applies to the corresponding settings in the CompositeProvider on which the query is based. These settings are in the query settings on the “Runtime properties” tab under Runtime profile properties in the Operations item in SAP HANA.
The SAP standard only permits three settings here: Defensive (J), Standard (M) and Offensive (P). Depending on the mode that had been selected (here in the query or the CompositeProvider) the system either executes the query in SAP HANA after a check, or it does not. As a minimum, the setting “Standard (M)” is required to be able to push down exception aggregations onto HANA.
In the event that the options listed here cannot be displayed in the system but instead other values with numbers – for example, “Expert: exception aggregation (6)”, then the system has already been reset to this Expert mode by an RSADMIN parameter.╝ OSS notification 2505732 and the explanations in the SAP help “Operations in SAP HANA under runtime profile properties” goes into more detail about this mode.
In the last resort, finer control of the execution of OLAP calculations in HANA can be attained with this parameter. At least “Level 7 formulas calculated in SAP HANA)” and “M (Standard)” are required for exception aggregations.
Definition of the individual structure elements of the query
Individual structure elements can also hinder a push-down onto HANA. These include, among others, exception aggregations done on values such as non-cumulative values, values with internal business volume elimination and currency and quantity conversions for a value if the conversion uses an InfoObject to determine the target currency / unit. The type of formula can also prevent a push-down of the calculation onto HANA. In this way, commutative formulas with a calculation are calculated before aggregation in HANA, even if the result of the formula does not include a currency or a unit or if the currency or unit are only taken over by one operand. Details on this are given in the SAP help under this path: Analysis Model analytical query Define query in the Editor Runtime properties of the query Runtime profile properties Operations in SAP HANA Exception aggregation in SAP HANA.
In addition, the execution of an exception aggregation cannot be pushed down onto HANA if there are virtual characteristics present or the classical characteristic 1CUDIM is present in the CompositeProvider. Additional supported and unsupported functions in a query are likewise listed in the SAP help under the Exception Aggregation item in HANA for the relevant release level.
Analysis
The cause is not always so obvious as in the case of a change that had just been made. If the initiator is not known then all that remains is a classical analysis. Here we can work pragmatically and set up and break down the query bit by bit until the cause is found or is no longer present. Or we can be somewhat more systematic and make analyses at the various levels. In the present case it was possible to show that the runtimes in the data model were acceptable. Thus, a query in the CalculationView did not take significantly longer if the relevant characteristics / values were included in the data query. Also, querying the runtimes of the query via the usual runtime analysis of the query (with transaction RSRT Execute and debug General execution options Show statistics data) shows that the runtimes take place in Event ID 3200 OLAP data transfer. Among other things, formula calculations and exception aggregations are summarized via this Event ID. An overview of Event IDs can be found in the SAP help for BW/4HANA under the path Operation Query runtime statistics Overview of statistics events (table RSDDSTATEVENTS).
In this blog I would like to concentrate on that part of the runtime concerning the execution in the query. When the query is executed in transaction RSRT there is an additional and less well-known option that will be referred to here. Under the button “Execute + Debug” there is the option “Execute and explain”.
If this option is selected, then various logs can be chosen. In our case of the analysis of the runtimes, we presume that there is a connection with formulas and exception aggregations, and therefore we select the log “Exception aggregations in SAP HANA”.
In this way we can now execute the query in the usual way in RSRT.
This procedure is also described in detail in OSS notification 2400004 – Checking SAP-HANA push-downs of OLAP functions such as exception aggregations.
The log appears if we go back to the results screen of the query ( PF3). Here we can find various steps and information. The results screen varies according to the query and query definition.
These points are noticeable:
- Query/Optimizer information – General comments on the query, including query properties such as operations in SAP HANA
- Structure elements, whose formula exception aggregation can be executed in SAP HANA.
- Structure elements, whose formula exception aggregation CANNOT be executed in SAP HANA.
The log thus gives a look at how a query is executed and whether or not all of it can be executed in SAP HANA. Here it is not only the individual values and formulas (with/without exception aggregations) that play a role, but also the settings for the query and the InfoProvider that is used and the underlying data model, as has already been described above.
Under the structure elements we can see the values and formulas that can or cannot be executed in SAP HANA. Here we can see the expensive formula with exception aggregations that are not executed in SAP HANA.
There can also be notifications in step 0 that exception aggregations are not possible in SAP HANA for this query. In my example the query has the artificial characteristic1CUDIM in the free characteristics. This characteristic hinders the execution of the entire query in HANA.
However, to some extent the corresponding OSS notifications are specified, such as a reference to OSS notification 2271658.
We very quickly get with the aid of this log an overview of the general reasons (such as the causes at the data model through possible NULL values, settings at the CompositeProvider), as well as detailed reasons as when, for example, only one formula of the query cannot be calculated in HANA.
Summary
In individual cases it is not easy to determine which OLAP steps can or cannot be executed in HANA. Even after a detailed study of the help files and blogs it is not possible to reliably (and not without an excessive amount of time spent) find all the factors influencing the query and hindering execution at the HANA database. The influencing factors described here can always be recalled if you are creating a query or if queries exhibit lengthy runtimes and are analyzed. In any case the analysis option shown here is an interesting option to get the first results quickly and by simple means when executing a query and thus having a starting point for making improvements. Of course, the runtime of a query does not only depend on these factors. But it is one module of many on the way to high-performing reports.