Query Performance Optimization in BW – Few Tips
In this blog, I am going to discuss about the approach I have taken to optimize queries in SAP BW .
Two-way Bex query performance tuning can be done:
- Changing the development and design of query.
- Tuning and changing the Setting and properties.
Let’s discuss on the two options in details:
A. Tuning with proper setting:::
1. Use Program RSR_CACHE_RSRV_CHECK_ENTRIES to remove the unused Cache entries.
2. Using the Selection of Structure Elements activated.
This option you need to enable when you are selecting specific selection (restricted Key figure) out other key figure you activated the setting when filter passes to the DB and cell which have value only pass to the query in BW. Not All fields and cells are transferred. As a result, query execution time less.
This function you can also not be activated if the query has read mode ‘A’ as in this mode all the key figure value are transferred at the start of the query execution so selective process can be done.
For example –
A. Selected on Structure Element AMMOUNT (MN) Without activating Selection of Structure Elements –
Selected on Structure Element AMMOUNT (MN) With activating Selection of Structure Elements –
3. There will be a significant improvement in performance using it if:
- Your queries contain a large number of selections or restricted key figures and that need to be displayed latter on the drilldown, not in the initial screen.
- When a lot of operations used filters using selections or restricted key figures then this configuration useful.
4. Steps: Go to RSRT –à Go to Query Properties.
My recommended setting in general scenario –
5.Deactivate parallel processing for an individual query.
- Deactivate the parallel processing for an individual query which do not have sub query or formula variable with replacement path which execute another subquery. We will gate fast response time.
- By below process we can deactivate it –
- Go to RSRT-> select Execute + Debug mode -> Data Manager -> select no parallel processing –
6. Operation in BWA/HANA mode: Which is suitable for what scenario:
- 0 -No Optimized Operations in SAP HANA/No BWA: This is not efficient as this setting not allowing to do any kind of operation inside the HANADB. If there is any virtual key figure used in query, then this setting we apply.
- 2 – Individual Access per Info Provider: If we used multi-Provider and want to optimize each multi-Provider separately then we use this option,
- 3- Optimized Access: In this setting we the optimization done as a whole i.e., jointly for all the info providers. This is one of the best settings to get optimize query performance.
- 6- Exception Aggregation default: Using this setting we can get most optimize performance as it used to perform sub calculation and build sub views on it. But this setting has lots of limitation and everywhere we can not use it. If it is not suitable for your scenario then use 3- Optimize access.
A. Tuning on Development and Design time:::
The below rules are not only true for BEX query but also for other query and views also –
1.Take only those info object/fields in query which you only want to use in the report. No single additional info object/fields.
2.Use Multi provider/Composite provider, if possible, to build a query.
3.Use filter whenever is possible to reduce the dataset into query.
In filter use Not in i.e., Exclude some data point from a data set instead of IN. It improves performance –
4. Minimum use of Free Characteristics: If you know how the Report structure looks like then please use structure to develop the query. I recommended to minimum use of free characteristics. It reduces the memory load and improve the performance.
If we use structures selections inside that and enable the setting “Selection of Structure Elements” the performance improves Significantly.
5. Push the calculation to the transformation layer:
Do not do any kind of calculation i.e., Calculated key figure and Restricted key figure Query Level if it is not required to meet the criteria that cannot be fulfil without creating in query level.
Push all the calculations to the Transformation layer . Develop DSO, info cubes and other objects in different layers and use transformations Layer for Calculated characteristics and calculated info Objects. Pass those info Object/fields to the Reporting Layer.
Use that field into query.
6. Avoid Aggregate unnecessary fields, be specific to the key figures required.
7. in additional in Push the joins (lookups) before aggregation in to narrow down the dataset in info provider level. In native HANA we can use ENUM JOIN_THRU_AGGR same way by alternate we will use via lookup before aggregate whenever it is possible.
8. If required Use logical partitioning – semantic partitioning, multi provhint
9. If required use physical partitioning
10. Use OLAP cache
11. compress cubes
12. Always “Suppress Result Row” to get optimize performance for each field in the Bex query –
13 .Try to reduce characteristics drilldown at Query Level.
14. Choosing the proper decimal precisions for key figure calculation:
- Use higher precisions while creating info object using in Info Object maintenance transaction (RSD1) for key figure, and in the query monitor for all key figures of a query.
- By default, “short decfloat” with Dec floating point with 16 places selected.
Use “Long decfloat” with floating number with 32 places. This will reduce memory load.