Skip to Content

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:

/wp-content/uploads/2013/09/pho_283383.png

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.

/wp-content/uploads/2013/09/1_283346.png

2) Data Base overview :

Transaction Code –> DB02

/wp-content/uploads/2013/09/2_283347.png

3) Table space Overview :

Transaction Code –> DB02

/wp-content/uploads/2013/09/3_283348.png

4) Free Space Monitoring :

Transaction Code –> DB02OLD

/wp-content/uploads/2013/09/4_283349.png

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.

/wp-content/uploads/2013/09/5_283350.png

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.

8) Missing indexes in the database:

Transaction Code –> DB02OLD

/wp-content/uploads/2013/09/6_283351.png

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

/wp-content/uploads/2013/09/7_283331.png

  • 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.

/wp-content/uploads/2013/09/8_283333.png

9) ABAP Dumps:

Transaction Code –> ST22

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

/wp-content/uploads/2013/09/9_283337.png

10) System Logs:
Transaction Code –> SM21

/wp-content/uploads/2013/09/10_283338.png

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.

/wp-content/uploads/2013/09/11_283339.png

B) General Weekly Monitoring Activities:

/wp-content/uploads/2013/09/15_283361.png

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:

/wp-content/uploads/2013/09/13_283344.png

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.  

/wp-content/uploads/2013/09/14_283345.png

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

To report this post you need to login first.

38 Comments

You must be Logged on to comment or reply to a post.

  1. CH Raman

    Hi Prabith,

    Excellent and superb presentation. Very well presented with screen and resolution.

    More useful one.

    Thanks for making and sharing with us.

    Regards

    Raman

    (0) 
  2. Chandra Sekhar

    Hi,

    Right now we are doing Data Archiving project. The above document will definitely help for our analysis.

    Thanks for for such good analysis.

    Regards,

    Chandra Sekhar.

    (0) 
  3. Suman Chakravarthy K

    Generally we don’t get access to DB02 and DB14 in Prod. It will be with BASIS control. You made me to see what’s happening in these t-codes. 🙂 Fantastic share it is..

    (0) 
  4. Jens Gleichmann

    Hi Prabhith,

    nice summary, thanks for collecting and sharing this. There are one point of criticism which I must tell you. The table space PSAPSR3701 which is shown in DB02 screenshot is an old TS which has not been dropped after an upgrade (brspace -f tsdrop). If you want to optimize your landscape and save space on your disks, you shouldn’t forget such tasks 😉

    Thanks and Regards,

    Jens

    (0) 
  5. dibyajoti biswal

    HI Prabhith,

    Thanks for sharing these useful informations.

    I have one doubt here that while deleting old log files(application logs,database logs or error logs) is there any risks involved or do we have to check any parameter before deleting ?

    (0) 
  6. Ulrike Schumm

    Very good documentation!

    I have one question regarding 8) Missing indexes in the database:

    In BW 7.4 SP07 ff., the Performance tab in the maintenance of the infocube where you can delete, repair and create database indexes and statistics is gone. Do you know why? Or do you have to activate this tab somewhere in the general settings no matter if it is BW 7.3 or 7.4?

    Thanks

    Ulrike

    (0) 
    1. Jens Gleichmann

      Hi Ulrike,

      do you are using BW on HANA? If do you use HANA, you do not need to create/delete/repair indexes any more. Also statistics are not relevant.

      Regards,

      Jens

      (0) 
      1. Ulrike Schumm

        Hi Jens,

        but I think the Performance tab is missing in InfoProviders in BW 7.4 on HANA that are not yet HANA-Enabled. If you start with BW on HANA via migration, you might have only selected data flows modeled as HANA-enabled for the beginning. The majority of the data flows is still modeled in the ‘old way’.

        So what is with these InfoProviders? How can you handle Performance issues with these ‘old cubes’ if statistics and indexes are not available any more?

        Thanks

        Ulrike

        (0) 

Leave a Reply