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 –> http://scn.sap.com/docs/DOC-46602

Part 2 can be found here –> http://scn.sap.com/docs/DOC-46844

Part 4 can be found here –> http://scn.sap.com/docs/DOC-46965

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  –> http://scn.sap.com/docs/DOC-35444
2. SAP BW 7.3 Promising Features –>  http://scn.sap.com/docs/DOC-30461

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:

Transaction Codes –> DB14, ABAP ‘SAP_ANALYZE_ALL_INFOCUBES’

• 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

http://scn.sap.com/community/business-explorer/blog/2013/07/25/how-to-optimize-reporting-performance

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

http://basisondemand.com/Documents/Whitepaper_on_BW_SystemCopy.pdf

3) Periodic Jobs and Tasks in BW

http://scn.sap.com/docs/DOC-11516

4) House Keeping activities for Archiving in BW systems

http://scn.sap.com/docs/DOC-39735

5) House Keeping Task List

http://scn.sap.com/docs/DOC-46240

6) BW House Keeping and BW PCA

http://scn.sap.com/docs/DOC-46433

BR,

Prabhith