This post is to share my experiences on Query optimisation tips in SAP BW 7.3.
1. From BW 7.3, there is a newly implemented OLAP cache mode called “Query Aggregate Cache” which is recommended to be used.
Set the cache mode to “Query – Aggregate Cache” for faster query runtimes.
Note: For newly developed objects in starting BW 7.3, this option will be set automatically.
2. Please ensure correct setting in the DSO is chosen to avoid the delay with the data activation .
For example the step for generating the SID’s are necessary (Option 2) only if the DSO is used for reporting in Bex Queries and hence ensure right setting is chosen to avoid delay with the running of process chains.
Some times the performance of the queries on top of DSO’s got impacted with the option “During Reporting” and instead we have to check the possibility of generating the SID’s in backend by choosing the option “During Activation”.
3. Avoid using infosets as performance of queries on top of infosets are not good compared to cubes. We can use composite providers which combines the advantages of infosets (on the fly joins) and infocubes.
Note: We can use composite providers with the BW version BW 7.4 SPS 8.0.
4. Do not schedule the collection and monitoring job too frequently as this will increase the resource consumption unnecessarily and by that lead to reduction in overall performance under high load situations
5. Use “Query Pruning” if the multiprovider contains multiple cubes (for multiple domains) . Non usage of “Query Pruning” leads to data access from all domained providers, though the query needs access only from a single domained provider which might lead to loss in query performance. In BW on HANA, the data is kept in the memory even though it is not required.