The execution of a SAP BW Query consists of many different steps which in sum make up the total runtime. With SAP BW running on SAP HANA this has been optimized as we all know. Please read for further details Klaus Nagel’s Paper about the various Query steps and runtime improvements over time by BW release.
Besides the fact that every single query benefits from the technology and performance of HANA per default, we implemented further improvements to push down logic to the database. This means we are moving calculation logic of certain operations into the database layer, which had been executed in the application server so far. In the end this is what we call “push down” and what enables us with BW on HANA even to improve very complex OLAP calculations. By the way this one major difference between BW on HANA and all other deployment options of BW.
This blog should give you so some tips and tricks on how you could verify in such cases and see whether a BW Query operations are been pushed down or not 🙂
1. Check if used OLAP functions in a query are enabled to be pushed down to SAP HANA
- The focus for Push Down are complex OLAP operations. So not every BW query has to be executed via a so called Calc. Scenario – which represents the logic as artefact in the database. There is a SAP Note, constantly been updated, showing the so far and planned optimized functions with SAP HANA:
2. Check BW Query Statistics and understand how a push down works
- Go to RSRT and check the query statistics. To do so please “Execute & Debug” without using of any cache and displaying the statistics:
- The query result is showing three key figures: Sales Order Value (Aggregation), No. of Sales Orders (Counter), Average Sales Order Value (Formula).
- (Please use F3 or back button to get the statistics after the query result)
- Query executed normally (having Operations Mode =”6”) on SAP HANA showing runtime of 4.5-5 seconds (in our not well configured test system J) and an overall selected data volume of ~13.8 mio. data records which are aggregated and calculated on database level to ~1.200 records. This amount of records has been transferred to the application server where the OLAP processor takes over, to make up the query output as we have seen it already.
- There is even a so called “HANA Calculation Engine Layer” Tab shown, were you can see the different processing steps of HANA and overall as a good indicator that the query was executed in this very bottom layer.
- Understand the mechanism of push down:
- The easiest way to see what’s happening during query execution is to compare the query statistics with a push down – like we did already – and without. This can easily be simulated via RSRT:
- “Mode 0” will use the SQL interface which is the most classic way to execute a query
- “Mode 2” which is a HANA/BWA specific option which does not use pushdown of exception aggregations like counters
- “Mode 3” optimized access in HANA / BWA without exception aggr. push down
- “Mode 6” will push down as many query steps to SAP HANA as possible
In our example to see the difference easily we’ll use the SQL interface with Mode 0.
- Considering the query statistics there are a few things obviously:
- Runtime switched from ~5 seconds to ~154 seconds for the same identical query result
- Transfer of data records to the application server has increased to ~5.4 mio records
- This amount of granular records is processed in the application server in the Analytic Manger (OLAP Processor) which took ~115 seconds instead of 0.2 seconds
- There is no HANA Calculation Layer shown in the statistics which indicates that no push down of OLAP calculations happened
3. Check if a SAP HANA Calc. Scenario is being generated and used
- A Calculation Scenario is an HANA artifact generated on database level including the logic which should be processed, so it is the key for pushing down logic in the most cases.
4. Check if exception aggregation is being possible to be pushed down
- Execute the query again and use the “Explain” functionality to see whether the query contains “Exception Aggregation” and if it is possible to be execute on data base level
The output of the Explain is a log showing which exception aggregations can be pushed down and which not. Please keep in mind that this tool is build for support cases, so the output could sometimes not be that obvious for a non developer or might be misleading in first place. 🙂
For more information about query processing with SAP HANA you can also read the following blogs: