Jan 7, 2015

How to optimize Reporting Performance in SAP BI


Via Content in SCN

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.

It is recommended to use Inclusions Instead of Exclusions wherever possible as Exclusions cant access DB indices and will impact performance. 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. This is very time consuming and a high number of RKFs & CKFs can seriously hurt Query Performance that means, please open your query definition and pick up the fields which are used in the query while defining Aggregates. Delete the unused Aggregates 

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. 
Make sure you get good valuation indicators for your Aggregates, but not like below 


It is recommended to to Suppress Result rows Wherever possible. 


It is recommended to use SAP Exits whe re ever possible and bring down the Customer Exits
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


It is recommended to reduce RKFs & CKFs in the Query to as few as possible.
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.



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

Reports should be designed on Multiproviders wherever possible.

Logical and physical partitioning is recommended for bet ter performance.

Extensive use of Filters at Query level is recommended.

Select appropriate Read mode settings for Multiproviders with "H" and Infosets with "X" in RSDIPROP t-code

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

Statistical Reports performance can be improved by broadcasting query result to cache and prefilling cache.
Deletion of unused Queries is recommended
Delete temporary Query Views is recommended.

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

Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .< /p>

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.

No comments: