House Keeping / Performance Tuning Activities in SAP BW Systems -- PART 1

[Prabhith]

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 the BW House Keeping activities under a single umbrella.

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

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

Part 3 can be found here --> http://scn.sap.com/docs/DOC-47062

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) General Daily Monitoring Activities:

1) Space Overview
Transaction Code --> DB02
• Make sure that there’s enough database space free for the system. How much needs to be free depends on the system. If there is no enough free space, it’s a good idea to start looking into different housekeeping activities. Also we need to think about adding more disc space to the application.

• Disc space need estimations and should be planned at least 3 months ahead. These plans have to contain amounts in bytes, changes in percentage and need distribution in table spaces.

2) Data Base overview :

Transaction Code --> DB02

3) Table space Overview :

Transaction Code --> DB02

4) Free Space Monitoring :

Transaction Code --> DB02OLD

5) Batch jobs in BW System:

Transaction Code --> SM37

Check in SM37, all batch jobs which have status ‘Cancelled’. Try to solve the issue. In certain cases, re-running of the batch jobs would be handy.

6) Data loads: Info packages, DTP’s and process chains:
Transaction Codes --> RSMO, RSMON, RSPC, RSPCM
SAP delivered ABAP program: /SSA/BWT
• Make sure that all the infopackages finished successfully with Tx.RSMO
• Make sure all process chains have finished successfully, rerun them if necessary.
• Verify if transaction data loads with 0 records are valid
• Analyse PSA error requests that occur. Determine if the error records are valid.

7) Report ability of data loads in the info providers:                                                                                                                  Transaction Codes --> RSA1 (Maintenance of info cubes / DSO objects)
• Check if the data in all the info cubes are reportable. In certain cases, data would have been loaded, but aggregates are not rolled up yet.
• Check also if data in all the active DSO objects is activated and booked into all data its data targets.

😎 Missing indexes in the database:

Transaction Code --> DB02OLD

In the above screen shot, 67 indexes and 106 tables are missing.

• Make sure there are no database indexes missing other than the ones that are droppedto speed up data loads into the info cube. Perform this task multiple times a day. If info cube indexes stay missing, create them via the maintenance screen of info cubes, Performance
  tab.

9) ABAP Dumps:

Transaction Code --> ST22

• Try to solve recurring ABAP dumps. Apply SAP notes if necessary

10) System Logs:
Transaction Code --> SM21

Try to solve recurring problems in the system logs. Apply SAP notes if necessary.

11) Monitor if database back up’s have been made:

Transaction Code --> DB14

Make sure there are periodic backups been made of the complete system.

B) General Weekly Monitoring Activities:

1) BW Statistics:

Transaction Code --> RSA1, Tools – BW Statistics for info providers.

BW statistics provides you with the following options that allow you to evaluate data from both the OLAP processor and warehouse management.

You are able to:

• Get an overview of how InfoCubes, InfoObjects, InfoSources, source systems, queries, and aggregates are used.

• To determine the system performance and improve it.

• To improve the way in which aggregates are selected and used, and to reduce the cost of updating them.

C) General Monthly Monitoring Activities:

1) Aggregates: Status/Usage:
Transaction Code  --> RSA1, RSMON
Aggregates should boost query performance on an Infocube, but may affect the load performance while filling them. An aggregate should not be created lightly; there should always be a good reason for its existence. If the query runtime is mainly spent on the database, suitable aggregates should be created. The ratios between the rows selected and rows transferred indicates potential performance improvements  with aggregates.  

There are many reasons, why an aggregate might be unnecessary.

• There can be very similar aggregates that might be combined into one new aggregate.

• There can be aggregates which have never been used.

• There can be aggregates with an insufficient reduction factor compared to the Info cube.

An aggregate contain redundant data and are especially created for performance reasons, unnecessary aggregates need disk space and have to be regularly maintained via roll up. Check regularly the usage of your aggregates. It can be case that the merging of similar aggregates is possible. Some aggregates may not be used at all, or some aggregates should be similar to the size of the Info cube and lead to no performance improvements.

The Early watch report can also be checked for aggregates. 

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