I have seem a growing number of so called BW on HANA performance related Incidents where the problem is not actually anything to do with the HANA database and some times not related to BW. It is often the case that the performance problem is with the frontend tool, so this could be Webi, Design studio or any third party tool that connects to BW backend system and uses a BW query or infoprovider as a datasource for a frontend report.
The first step in the analysis is to try and identify where the problem is i.e in the frontend tool, BW backend or HANA Database.
Where the frontend tool connects directly to BW application server (frontend reporting tools can also connect directly to HANA Database and not via the BW application server in this case the below is not relevant) you can use the Query monitor transaction RSRT to execute the query and collect the runtime statistics. The RSRT Query monitor transaction is a frontend Independent tool so If you then compare the query run time in RSRT compared to the overall run time of the frontend report you can make a judgement on where the performance problem lies and if it is the BW application, HANA Database or frontend reporting tool that is responsible.
The below video explains how you can execute a query with RSRT and also how to collect the run time statistics for the query. The run time statistics are important as from these we can see in what part of the query processing the time is lost:
How to execute BW Query and collect runtime statistics with RSRT transaction
There is often the expectation that after a BW on HANA migration from other database that all BW queries should be much faster. Of course if the query was slow already before database migration to HANA due to some calculations happening on the BW application server and these calculations cannot be pushed down to HANA DB level then HANA cannot help for these use cases without a redesign of the BW Query.
Good BW Query Use Cases:
A good BW query use case from a performance improvement point of view when migrating to HANA is one where at least a significant portion if not the vast majority of the run time of the query is database time compared to the overall run time of the query.
Apart from database time what other parts of the query processing can be faster with BW on HANA?:
As shown in the Diagram above we can see the different parts or components that make up the run time of a query. They are database time, time in the Analytic Manager and the client and network time. The first thing to point out is that we cannot improve the network or the client time, this is the time taken to send the data across the network from the database server to the BW application server or from BW application server to client and then display the output on the end users PC, Laptop or mobile device.
What we can possibly improve with HANA apart from the database time which we have already looked at above is the time spent in the analytic manager. The analytic manager previously known as the OLAP engine in BW is the brain of BW when it comes to BW query processing so it is responsible for navigation, filtering and aggregation among other things in the query execution. With the analytic manager what we are really talking about as shown in the diagram above is removing more and more calculations with every BW on HANA release that currently happen on the BW application server and pushing them down to the HANA Database level.
The key take away here is that as of BW740 running on HANA more and more analytic manager functions and calculations are getting optimized and pushed down to the database level on HANA. Therefore it is expected that the OLAP portion of the BW query will become even faster on HANA with future developments. One important example among many is BW formula exception aggregation. With SP09 for BW740 as shown in the Diagram below this feature will be pushed down to the HANA Database:
To get the current list of analytic manager operations that are pushed down to HANA database level please check the information in the SAP note https://css.wdf.sap.corp/sap/support/notes/2063449.
It is difficult to estimate by how much the OLAP time can be improved on HANA and it is really dependent on what OLAP functionality you use in your BW query and also what OLAP features have been pushed to HANA DB level for the BW on HANA release that you have. Good query design is also important in terms of the numbers of records that are sent from the database back to the application server and the number and complexity of the BW query calculations that need to be performed in the BW application server in the Analytic manager that cannot be pushed to HANA
- SAP Managed Tags:
3 Comments
