How to optimize Reporting Performance

Introduction

                 We face many challenges in our BI projects in terms of Reporting Performance. Users always expect outstanding performance of our BI reports. No matter what ever we do in the backend (Modeling and Query Designer). I am going to share very important tips & techniques which I have learned through out my experience which consists of some standard thumb rules too.

1. It is recommended to use Inclusions Instead of  Exclusions wherever possible as Exclusions cant access DB indices and will impact performance.

2.     It is recommended to to Suppress Result rows Wherever possible.

3.     It is recommended to use SAP Exits where ever possible and bring down the Customer Exits

4.     SAP Suggests free characteristics in reports should be limited to 8-10.

         In RSRV checks, free characteristics usage is marked in red which has very high impact on reports

5.      It is recommended to reduce RKFs & CKFs in the Query to as few as possible.

         a.     Because, when huge no. of  RKFs & CKFs are Included in a Query, Restricting, Conditioning and Computations are done for each of them      during query execution.

         b.     This is very time consuming and a high number of RKFs & CKFs can seriously hurt Query Performance

6.     It is recommended to redefine your Aggregates in an optimized way by taking Statistics(BI Admin Cockpit should be in place) & Query                    definitions into Consideration.

         a.     I mean, please open your query definition and pick up the fields which are used in the query while defining Aggregates.

         b.     Delete the unused Aggregates

         c.     If the aggregates are too Large, those not only degrades the Query performance but also loading performance by longer times to roll up and           Attribute Change runs also takes longer times.

         d.     Make sure you get good valuation indicators for your Aggregates, but not like below

7.     Archiving (NLS) is recommended to archive unused data.

        a.     Reduction of online disk storage

        b.     Improvement in BW query performance

        c.     Increased data availability as rollup, change runs and backup times will be shorter

        d.     Reduced hardware consumption during loading and querying

8.     Reports should be designed  on Multiproviders wherever possible.

         a.   You can use 0INFOPROVIDER field to restrict to the specific infoproviders in Queries

         b.   You can also maintain partitioning criteria in RRKMULTIPROVHINT table

9.    Logical and physical partitioning is recommended for better performance.

10.     Extensive use of Filters at Query level is recommended.

11.     Select appropriate Read mode settings for Multiproviders with “H” and Infosets with “X” in RSDIPROP t-code

12.     Reporting on Infosets should be considered below tips :

          a.     Do not select all the fields which are part of Infoset definition like below. You can select the fields which we want to use in query only.

         b.     You should select “Use Selection of Structure Elements” in RSRT–>Properties

        c.     Do not make too many joins as it cause high runtimes to fetch data after the joins

         d.     You can set a limit of “Limit value for large Where conditions” in RSCUSTV19 Eg: 300

13.     Statistical Reports performance can be improved by broadcasting  query result  to cache and prefilling cache.

14.     Deletion of unused Queries is recommended

15.     Delete temporary Query Views is recommended.

16.     It is recommended to be careful while creating Cell Structures as they require high query run times and will lead to performance degradation.

17.     Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .

18.     Make proper Query read mode and Cache mode settings in RSRT–>Properties. The recommended Cache mode could be 1 or 5.

Conclusion :

It is always better to keep an eye on above points while developing our Business models and Queries. This blog will help us to satisfy our users with good reporting performance.