SAP BW/4HANA – SAP BW Query Performance Analysis (Part 1)
By: Bill Faison
Improved SAP BW query performance is one main business justification for upgrading SAP BW for HANA or BW/4HANA.
You have now done your upgrade, and start your testing of your SAP BW queries. The business has given you a bad performing Analysis for Office workbook as a sample to test with. You run the query on your new SAP BW for HANA or BW/4HANA system, but the performance is not that much better. What has happened?
Let’s take a deep dive into what is going on to understand where the query is spending it’s time and how we can improve the SAP BW query performance.
This blog is based on SAP BW/4HANA, but you can use these techniques on SAP BW 7.4 on HANA or SAP BW 7.5 on HANA.
All test were run on the following systems:
- SAP BW/4HANA Support 4
- SAP HANA DB 1.00.122.08.1490178281 (fa/hana1sp12)
The query is this sample is based on a SAP BW Composite Provide that is based on a SAP HANA calculation view. All master data is based on SAP Open ODS Views, and all of the key figures has exception aggregation.
The first step to get a better understanding what is going on, run the query in SAP BW using the transaction RSRT. Using the transaction RSRT is very helpful since it gives you a detailed understanding on the query performance without any network or front-end tool overhead.
Here is a link to a SAP wiki for more information the transaction RSRT
Execute the transaction RSRT, and enter in the technical name of the query.
Clicked on Execute + Debug, then checked the ‘Display Statistics Data’ and ‘Do Not Use Cache’. The option ‘Display Statistics Data’ will display the SAP BW Bex statistics for the query execution. The option ‘Do Not Use Cache’ will prevent the query from using the BW cache to give a better perspective on what is happening in SAP HANA and SAP BW.
Enter in the parameters for the query like the business is running the query.
Once the query is running, it will display the output of the query. In this example, the query returned 107826 rows. Since the report has 24 columns, there is a total of 2,587,824 cells in this report.
Clicked on the back icon to get the query statistics. Here is what we learned from the SAP BW query statics:
- Query ran for 132 seconds
- 12% of the query run time (16 seconds) of the time in the Hana View. This can been seen in the column ‘Read Time’
- 88% of the query run time (116 seconds) in the ABAP layer
- This doesn’t include ‘Not Assigned’ time since this is the wait time to enter in parameters
Let’s review some of the OLAP event to further understand what the query is processing
- 7 seconds spent with the event 13054 ABAP BICS (Read result set: ABAP time to load the result set (minus OLAP time)). This not too surprising since the report has 2,587,824 cells.
- 3 spend with the event 3110 OLAP: Data Selection(OLAP_BACK)’ (Time to sort the data read from Data Manager according to structure element selections or restricted key figures). Again this not very surprising due to the large data set of 107826 rows.
- 28 seconds spent with the event 3200 ‘OLAP: Data Transfer (RRK_DATA_GET) (OLAP features are processed, e.g. exception aggregations, and the correct number of decimal places for the data cells is determined). This run time is due to the exception aggregation not being pushed down to the HANA DB, plus the other processing it the OLAP manager on the data set. This seems to be a lot of time, but since this is being processed on the application server, the HANA database has not impact on this part of the query.
- 28 seconds spent with the event 3900 OLAP: Read Texts (Texts are read to sort the data). Several of the fields in the query are showing the text, so this amount of time is not surprising due to the number of rows in the result set.
- 28 seconds spent with the 9000 Data Manager (Time spent in the database, detail on tab Aggregation Layer). This 16 seconds to get the result set, and 11 seconds for the SID processing.
- Little time spent with the event 9010 Total DBTRANS (Total Number of Transported Records). Important to look at too understand how many records are being sent to SAP BW from the SAP HANA DB.
- Little time spent with the event 9011 Total DBSEL (Total Number of Read Records). Explains how many records that were selected in the data base. Since 9010 Total DBTRANS equals 9011 Total DBSEL there was no aggregation happening in the query.
So what have learned so far?
Due to the large data set, 88% of the query run time has been sent in the application layer. Even if we improve the HANA DB runtime, we are not going to see much gains in our query performance.
In our next blog we will look closer are the SQL statements being run to better understand how SAP BW is processing the data.