Skip to Content
Author's profile photo Prabhith Prabhakaran

House Keeping / Performance Tuning Activities in SAP BW Systems — PART 3

Hi All,

In many BW Projects, we have seen Basis team and the BW consultants searching around for tracing the regular and best housekeeping options and activities provided by SAP for improving the performance of BW Production servers.

I have made an attempt to summarize and mention most of those points under a single umbrella.

Basically I have divided  the activities into 4 parts namely General monitoring, System health monitoring, Performance related monitoring and Occassional activities along with some pure basis activities in the fourth part.

Part 1 can be found here –>

Part 2 can be found here –>

Part 4 can be found here –>

Applies to:

SAP NetWeaver Business Warehouse (formerly BI). This will also work on SAP BI 3.5 and BI 7.0.

Author: Prabhith Prabhakaran

Company: Capgemini India Private Limited

Author Bio:  


Prabhith is a Senior SAP BW-BOBJ Consultant with more than six years of experience and is currently working with Capgemini Consulting, India.

His area of expertise includes BW, BODS, BOBJ and HANA.

Other popular articles from the same Author:

  1. Points to be considered while integrating BW Bex queries with BO WEBI  –>
  2. SAP BW 7.3 Promising Features –>

A) Performance Related Monitoring Activities:


1) Performance related tasks:

Transaction Codes –> OS06, SM04.AL08, SM50, SM51, SM66, ST03, ST04

• Check CPU load with transaction OS06

• Check the number of users logged into the system with transactions SM04 or AL08

• Check the system workload with transaction ST03.

• Check the workload of all processes with transactions SM50,SM51,SM66

• Check the database performance with ST04.

2) Database and SAP Statistics:


• Database Statistics :

The database statistics are used by the system to optimize the query performance. You should keep the database statistics up-to-date for this reason. It is recommended that you always update the statistics if you have loaded more than a million new records into the InfoCube since the last update. It is recommended to use the process type within process chains to build up statistics on the Infocube tables. 

You can automatically recalculate the database statistics after each load or after each delta upload. Using the pushbutton Check Statistics under Performance tab, you can check the InfoCube to see if statistics exist. If no statistics exist yet, the status display changes to red. Using Recalculate Statistics, you can add the missing InfoCube statistics in the background.

You can determine the percentage of InfoCube data that is used to create the statistics. The percentage is set to 10% by default. The larger the InfoCube, the smaller you should choose the percentage, since the demand on the system for creating the statistics increases with the change in size.

For up to 10 million entries in the InfoCube, you should set the percentage of InfoCube data,that is used for creating the statistics, to 100%.

Automatic deletion and building of indexes, as well as automatic calculation of database statistics can cause conflicts with automatic compression and rollup. These processes lock one another.

3) Calculate good database statistics:

• For Oracle databases, run the BRCONNECT script to calculate good database statistics. Use Tx. DB14 to view the result of the batch job. If the job aborts or runs too long, double click on the line to review the job log.

4) Recalculate good BW statistics for Infocubes:

• Run ABAP ‘SAP_ANALYZE_ALL_INFOCUBES’ once in a week to collect good SAP statistics for Infocubes.


• Run periodically the FM ‘RSDDCVER_DB_STATISTICS’ to calculate database statistics for an Infocube.


• Use transaction RSRV (and RSRVALT) on a regular basis to check Infocubes. Most importantly tag ‘database. Options ‘DB statistics of an infocube and its aggregates’ to check the health of an Infocube.


• The SAP Statistics Cubes (technical Content) is a tool to evaluate performance key figures of queries, aggregate and roll-up processes in detail. The SAP statistics can be turned on and off for specific Info Providers in the Administrator Workbench menu; old statistics data can also be deleted.

5) Unused dimension table entries:
Transaction Code  RSRVALT

6) Remove unused dimension table entries of an Infocube:
• Run Function module ‘RSDRD_DIM_REMOVE_UNUSED’ to delete unused dimension table entries of an Infocube. You can also use RSRVALT tag ‘Infocube data’, option ‘Unused entries in the dimensions of an infocube’.


7) BW statistics:

Tools: BW statistics and Technical content queries 

• Evaluate the system performance using BW statistics/Technical content queries.


8) Read mode of queries and Info cubes:

Transaction Code –> RSRT

• Switch the read mode of infocubes and queries to type ‘H’ (in RSRT).Also, check regularly if the read mode stays ‘H’ for new infocube / query developments.

• One can find the info cubes with the wrong read mode by looking in table ‘RSDCUBE’, column ‘READMODE’ and for queries table ‘RSRREPDIR’, column ‘READMODE’.

9) Archieve / Delete administration tables:
SAP Note 706478 (and the referenced sub nodes) provides an overview of administration basis tables that may considerably increase in size and cause problems as a result, if the entries are not regularly archieved or deleted. Growing administration tables increase the total size of the system and negatively impact performance, for example during monitoring the requests.
Affected tables are (among others):
• Application log tables (BAL*) – Logs of several processes are collected in the application log tables. These tables tend to grow very big as they are not automatically deleted by the system and can impact the overall system performance.
• IDoc tables (EDI*) – Table EDI40 can also grow very big depending on the number of IDOC records.
• BW monitoring tables for requests and process chains (archieving only possible with BI 7.0)
• Job tables (TBTCO,TBTCP)
Archieve or delete entries in these tables are marked in the note. 
Depending on the growth rate (i.e., number of processes running in the system), either schedule the reorganization process (transaction SLG2) regularly or delete log data as soon as you notice significant DB time spent in table BALDAT (e.g., in SQL trace).
See SAP note 195157 (Application log: Deletion of logs) for more details.
See also SAP note 179046 (Rapid Expansion of table EDI40, EDI30C) for information on archiving the IDOC tables.
Delete regularly old RSDDSTAT entries.

10) Delete old Statistics:
Transaction Code  RSA1 .Tools menu ‘settings for BI statistics’.
• Delete old BW statistics on a regular basis.

11) Delete BW Statistics table:
The BW statistics table RSDDSTAT* and the BPS statistics tables UPC_STATISTIC* have to be deleted regularly.
Please follow the notes 211940,195157,179046,366869 before deleting and archiving.
For the BW Statistic tables RSDDSTAT*, the deletion of records older than x days can be done in RSA1 transaction. To delete data, call transaction RSA1, choose ‘Tools’ –> ’Settings for BI statistics and select delete statistical data. 
The time period for data, can be entered in the following screen. For the BPS statistic table UPC_STATISTIC*, the deletion of records older than x days can be done in the transaction BPS_STAT0



Related Content:

1) How to optimize Reporting Performance

2) Guide to perform efficient system copy for SAP BW Systems

3) Periodic Jobs and Tasks in BW

4) House Keeping activities for Archiving in BW systems

5) House Keeping Task List

6) BW House Keeping and BW PCA



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      This is awesome..One stop place for all options related to housekeeping activity. 🙂



      Author's profile photo Manna Das
      Manna Das

      This is gonna help a lot. Most of the time Basis consultants struggle with BW system performances. This doc will give good overview, where and how to enhance BW performance.

      Kind Regards

      Manna Das

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Very nice information for the support project.

      🙂 🙂

      Author's profile photo Prashanth Konduru
      Prashanth Konduru

      Superb ... Nice document. It really helps a lot for Support (especially for beginners).

      Good job.. keep posting ... 🙂



      Author's profile photo Prabhith Prabhakaran
      Prabhith Prabhakaran
      Blog Post Author

      Hi All,

      Thanks a lot for your comments..



      Author's profile photo Former Member
      Former Member

      Very nice document



      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      fantastic one 🙂

      Author's profile photo Chandra Janardhansudhir Kunar
      Chandra Janardhansudhir Kunar

      Hi Prabith,

      Excellent document. Very helpful.



      Author's profile photo Former Member
      Former Member

      Thanks for sharing...



      Author's profile photo Prabhith Prabhakaran
      Prabhith Prabhakaran
      Blog Post Author

      Thanks a lot for all your valuable comments.

      As and when I find some new points, I will try to incorporate the same in any of the 4 parts.


      Author's profile photo Satendra Mishra
      Satendra Mishra

      Hi its a nice information. 🙂

      got a doubt if we deleting BW Statistic is there any impact at any where in BI. If deleting what will happen and if we not deleting what will happen. Can you please elaborate for me.



      Author's profile photo Ashok Babu Kumili
      Ashok Babu Kumili

      Hello Prabhith Prabhakaran

      Thank you so much for this blog. This is very cool. I specifically love that it is SOLUTION oriented.  This is so organised and well described.

      Regards - Ashok

      Author's profile photo Cesar Benito
      Cesar Benito

      #5 resolved 8 hours of researching

      Author's profile photo Aparajit Banik
      Aparajit Banik

      A must read document...

      Thanks for sharing this KT.
      Just one request, please maintain the authorization of editing the document. Its open.
      As its an important document please close this authorization and keep it up to yourself Sir,

      Author's profile photo Yasemin ULUTURK
      Yasemin ULUTURK

      Definitely superb Prabhith. thank you for sharing.



      Author's profile photo Sudhir Vanaparthy
      Sudhir Vanaparthy

      Thanks prabhakar...good one

      Author's profile photo Former Member
      Former Member

      Very Nice Document, Very useful.

      Thank you for Sharing..

      Author's profile photo Former Member
      Former Member

      Hi Prabhith, Thanks a lot for a very very useful blog. It helped me in my current project. Regards Jeeth

      Author's profile photo William Stouffer
      William Stouffer

      This is a great posting but I have a specific question on BW Table /BI0/E0TCT_C02 which contains SAP Front-End and OLAP Statistics (Details) Table.

      In our system the table is 5Gig but it has 40 Gig of indexes.  Any suggestions would really be appreciated.

      Have to think there is a maintenance job missing or some way of purging it.