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

[RamanKorrapati]

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

[Prabhith]

Thanks Raman,.

[anshu_lilhori]

Hi,

This is really helpful and useful...Lot of good points and areas explored..

Regards,

AL

[MGrob]

Hi

I was thinking about making this one but it wouldn't have turned out so nice. I think its a nice addition to this http://scn.sap.com/docs/DOC-46240 or vice versa :smile:

thanks

Martin

[Former Member]

Good Doc..!

[BenedictV]

Good content and Very Nicely presented!!

Thanks a lot for the info.

You may also want to add a point about PSA deletion regularly, dont you think?

Regards,

Benedict

[Former Member]

Good One, Thanks for Sharing....

[Prabhith]

Hi All,

Thanks a lot for your comments.

vasanth.vf , This is part 1 only,  Part 2 and Part3 are in the preparation stage where all those points including PSA deletion will be covered.

BR

Prabhith.

[anshu_lilhori]

We are awaiting for all the parts eagerly.. :smile:

[Former Member]

Hi,

Thanks for sharing.

Hope to see part 2and 3 soon.

Br

LK

[Former Member]

Hi Prabhith,

Very useful and nice document :smile:

Thanks,

Purushotham.

[Former Member]

Hi,

Prabhith, thanks for sharing...

Regards

Amrith..

[former_member210321]

Hi Prabhith,

Nice one... keep moving on  :smile:

Regards,

[Former Member]

Very Useful

Regards,

Sushant

[manna_das]

Thanks for this doc Prabhith

[yasemin_kilinc]

Hi Prabith,

It is very informative and useful. Thanks for sharing.

Regards

Yasemin...

[Former Member]

Excellent work Prabith!

Thank you for sharing.

[KamalMehta]

Nice .Thanks for sharing .

Waiting for the other parts .

Regards

Kamal

[Former Member]

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.

[former_member183980]

Thanks Prabhith !!

Very useful :cool:

[former_member182196]

Very nice information for the support project.

:smile: :smile:

[Prabhith]

Hi All,

Thanks a lot for your comments..

BR

Prabhith

[former_member182470]

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. :smile: Fantastic share it is..

[impraveen0192]

Nice blog with good and  detailed explanation

Praveen Kumar

[srinu_s1]

Nice blog. Keep sharing :smile:

[Former Member]

Good Content. Very useful Information.

Thanks & Regards,

Chandra Sekhar

[Prabhith]

Thanks a lot for your comments; praveen.kumar, srinu.s, @Chandra_Sekhar,

Br

Prabhith

[Former Member]

Hi Prabhith

Your docs shows how good your presentation skills are.Very useful information

regards

Kuldeep

[jgleichmann]

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 :wink:

Thanks and Regards,

Jens

[sander_vanwilligen]

Well done, thanks a lot for sharing your knowledge !!

[Former Member]

Excellent.

[Former Member]

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 ?

[former_member219411]

Hi Prabhith,

Thanks for the steps !!

I have doubt about above steps,can we consider same steps when BW7.4 on HANA ?

Thanks.

Puru

[ulrike_schumm]

Very good documentation!

I have one question regarding 😎 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

[jgleichmann]

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

[colm_boyle]

Obviously very experienced in BW, great work, and also a great go-to for less experienced BW Consultants,

thanks,

Colm

[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

[kuzhanthaivel_s]

Thanks for sharing.

Nice documents for performance Tuning

[14283727]

Hi Prabhith,

Excellent. Would like to know whether anything published with reference to BW4HANA?

 

Param