What are the Query Tuning you do when you use reporting?

a) Install BW Statistics and use of aggregates for reporting

b) Avoid using too many characteristics in rows and columns, instead place it in free characteristics and navigate / drill-down later.

c) OLAP Cache (Change Cache TCode RSCUSTV14): It’s a technique that improves query performance by caching or storing data centrally and thereby making it accessible to various application servers. When the query is run for the first time, the results are saved to the cache so that next time when similar query is run, it does not have to read from the data target but from the cache.

d) Pre-calculated web templates

e) Use small amount of data as of starting points and do the drill down

f) Instead of running the same query each time save the query results in workbook to get the same query results for different users. Each time you run the query, it refreshes the data /same data should not fetch from data targets.

g) Complex and large reports should not run online rather they should be scheduled run during off-peak hours to avoid excessive contention for limited system resources. We should using RA to run those off-peak hours in batch mode.

h) Queries against remote cubes should be avoided as data comes from different systems.

i) If you have choice between using hierarchies and characteristics or navigational attributes, you should choose char or navigational attributes.

j) Create additional indexes

k) Use compression on cubes since the E tables are optimized for queries.

l) . Turn off warning messages on queries