BW on HANA offers different read access modes for Query execution. This blog intends to explain the differences and give you some background information on the BW Query access to HANA. It is a sequel to my previous blog on and I will refer to some of the information I presented there.
In general BW accesses data in HANA in two different modes: Via the JOIN-Engine using standard SQL or via the (internal) HANA-API against the Calculation-Engine*. As indicated by the names of the two engines, the later supports OLAP/calculation features beyond the expressiveness of SQL. Examples are the “FEMS” handling, hierarchy aggregation, currency conversion, and “exception” aggregation. The pre-requisite to use the HANA-API is the existence of a so-called ColumnView – a metadata artifact in the HANA Calculation Engine which describes the entity relationship of the underlying tables. Such a ColumnView is automatically generated by BW for Infocubes, InfoObjects (most), and DSOs w/ SID-support.
The Query Execution Mode can be changed in transaction RSRT, for single Queries, for a set of Queries, for all Queries of an InfoProvider, or by changing the default of an InfoProvider.
- Mode “0” – no operations in HANA
- Mode “2” – individual PartProvider access
- Mode “3” – Cluster-access
- Mode “6” – Exception Aggregation
The default is execution mode “3”. Most queries in BW are defined on a MultiProvider even if they are based on just a single InfoProvider. Like this the MultiProvider serves as an “abstraction layer” between the physical storage of the data and the Query definition and thus allows for easy scalability and extensibility.
Mode “0”: Each single InfoProvider is accessed via the generated SQL-statement. The statements are executed in parallel, per default not more than 6 at the same time. The read access is restricted to the SQL syntax and similar to the read access on other databases. Of course also the SQL execution on HANA is in most cases significantly faster than on traditional databases.
Per default any SQL-statement in BW-on-HANA during Query execution has an additional hint: “olap_parallel_aggregation”. This hint forces the HANA SQL-optimizer to analyze the statement and, if possible, divert the statement to the Calculation Engine for processing. For statements generated by BW this can be done in almost all cases. I.e. even so an SQL statement is executed still the Calculation Engine is used (but without leveraging the additional calculation capabilities that can’t be expressed in SQL). Note: for debugging purpose it might be useful to turn this hint-generation off and thereby force the usage of the JOIN Engine, please see note 1734002 for details.
Mode “2”: Each single InfoProvider is accessed via a call of the HANA-API against the generated InfoProvider-ColumnView. The HANA-API includes pushing down cell-based filters coming from restricted keyfigures (the “FEMS”) and an optimized handling of hierarchy aggregation and filtering. The HANA-API is called in parallel for each InfoProvider, per default not more than 6 at the same time.
Difference to mode “0”: A simple statement where only filtering and aggregation is executed will usually show the same runtime in mode “0” and mode “2” – since both times the Calculation Engine is used. For more complex statements including many “FEMS” restrictions the mode “2” has a huge advantage as explained in detail in my blog about the “FEMS”. However, like everywhere, there are some very exotic cases, where you indeed may see a slightly faster runtime in mode “0”, compared to mode “2” (note 1858333 describes this in some detail).
Mode “3”: It’s the default, because we assume this to be the best execution mode for more than 90% of the Queries. The MultiProvider is analyzed and a cluster of InfoProviders with a “homogenous” mapping is created. A CalculationScenario is created in HANA at runtime combining the ColumnViews of the InfoProviders. Then a single statement is executed via the HANA-API for this cluster. The other InfoProviders are accessed independently, but in parallel.
Difference to mode “2”: The mode “3” basically pushes the UNION operation to HANA. Depending on the dataset, this already can decrease the amount of data that has to be transferred from the database to the application server. But it also gives HANA the opportunity to optimize the access path and parallelize as it is convenient and appropriate to the current workload and expected runtime. Since it is a single statement, HANA can also re-use the result of certain operations that have to be performed for each single InfoProvider in the Cluster (e.g. a JOIN between the SID-table and a masterdata (X-/Y-) table). So by pushing this to HANA, we have much more options to speed up data processing, and over the past months the HANA Engine teams have improved this processing considerably to make the exotic queries where this „default” is not optimal even less frequent.
Mode “6”: This is the execution mode where all OLAP/calculation operations are pushed down to HANA – most important and notably the so-called Exception Aggregation. For this purpose a CalculationScenario is created combining the InfoProviders and creating the “OLAP calculation graph” with its different layers – see “the OLAP-Compiler in BW on HANA” blog for more details and a nice example. Link 1) also contains the conditions under which the Exception aggregation can be handled by HANA.
Difference to mode “3”: So why is not mode “6” the default? Creating the CalcScenario and then executing different reads for the layer results represents an overhead compared to reading the data on the most granular requested level in one go. If this result set is small, the benefit of the push-down is small or may even be counterproductive. Therefore this is still(!) a manual tuning effort. Whether or not a query qualifies for the mode “6” push-down can be seen by executing the Query in the backend transaction RSRT with the debug-mode “Execute and Explain”. The Query runtime statistics, especially the value “records transferred”, gives then an indication of the level of performance gain by the push-down. As we gain more experience with this feature and additional OLAP features are being pushed down, we will evaluate this again and may change the default or make the runtime dynamically choose the execution mode.
The gaps in the numbering are not due to some failed attempts or a counting deficit, but are left on purpose. They allow us to introduce gradually new optimizations, let them mature, gain experience, and then we can switch the default behavior. And you can be sure that we have quite a few more optimizations in the pipeline for the releases to come.
Some additional links:
- More detailed information
and examples on the cluster access and the push-down of exception aggregation can be found in the paper on BW and BWA/HANA.
- BW-on-HANA and Query performance.
(*) For simplicity I use only the term Calculation Engine. Actually there are two engines involved, both the Aggregation/Attribute Engine of HANA and the actual Calculation Engine. But since it is completely transparent for BW which one is used, I use the term Calculation Engine as a hull for both.
VN:F [1.9.22_1171]#BW-on-HANA and the Query Execution Mode,