Skip to Content
Author's profile photo prabhith prabhakaran

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

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 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 3 can be found here –> http://scn.sap.com/docs/DOC-47062

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/10/pho_289285.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) Occasional Monitoring Activities:

1) Infocube Indexes:

Transaction Code –> RSA1, Manage (of Info cubes)-Performance tab

• Indexes are data structure sorted values containing pointer to records in table.

Indexes are used to improve data reading performance / query performance improvement but decreases data loading/writing performance .We delete/drop them during the data loading to data target and create again after loading finished. Its recommended to include them while designing the process chain. In process chain, before loading the data to cube use the delete index process and load the cube and create index.

• Use transaction RSRV (and RSRVALT) on a regular basis to check Infocubes. Most importantly tag ‘Database’, option ‘Database indices of an infocube and its aggregate’ to check the health of the cube.

• Using the Check Indexes button, you can check whether indexes already exist and whether these existing indexes are of the correct type (bitmap indexes).

Yellow status display: There are indexes of the wrong type

Red status display: No indexes exist, or one or more indexes are faulty

You can also list missing indexes using transaction DB02, pushbutton Missing tables and Indexes under the Diagnostics folder.

• If a lot of indexes are missing, it can be useful to run the ABAP reports SAP_UPDATE_DBDIFF and SAP_INFOCUBE_INDEXES_REPAIR.

2) Temporary tables:

• Run frequently or schedule ABAP report ‘SAP_DROP_TMPTABLES’.   

/wp-content/uploads/2013/10/10_289272.png

• Run frequently or schedule ABAP report ‘RSAN_RTT_CLEAR_TEMP_TABLES’

/wp-content/uploads/2013/10/11_289274.png

• Run frequently function module ‘RSDDS_CHANGERUN_TMPTABLS_DEL’

/wp-content/uploads/2013/10/12_289273.png

3) Unused database partitions:

Tools: ABAP ‘SAP_DROP_EMPTY_PARTITIONS’.

• Remove unused and empty partitions in the F- table of Infocubes using the ABAP ‘SAP_DROP_EMPTY_FPARTITIONS’. See note 430486 for further details.

/wp-content/uploads/2013/10/13_289275.png

4) Log files:
Tools: ABAP ‘SBAL_DELETE’, ‘RSTBPDEL’, ‘RSSM_ERRORLOG_CLEANUP’.
Remove old application logs from the Database.
• Run periodically ‘SBAL_DELETE’ to remove old application logs(see note 456150)
• Run periodically ‘RSTBPDEL’ to remove old database logs(see note 706478)
• Run periodically ‘RSSM_ERRORLOG_CLEANUP’ to remove old error logs(see note 456150)

5) Archieving:
Transaction Code  –> SARA, ABAP ‘RSEXARCA’ (See notes 643541 and 653393 for more details).
Without archieving, unused data is stored in the database and the DSO’s and Infocubes can grow unrestricted. This can lead to deterioration of general performance.
The benefits of BW archieving include:
• Enables you to archive data from InfoCubes and ODS objects and delete the archived data from the BW database. This reduces the data volume and, thus, improves upload and query performance.
• Reduction of online disk storage.
• Improvements in BW query performance.
• Increased data availability as rollup, change runs and backup times will be shorter.
• Reduced hardware consumption during loading and queries.

6) Delete PSA data:
Transaction Code –> RSA15
• Determine a retention period for data in the PSA tables. This will depend on the type of data involved and data uploading strategy. If PSA data is not deleted on a regular basis, the PSA tables go unrestricted. Very large tables increase the cost of data storage, the downtime for maintenance tasks and performance of the data load.

7) Delete change log data:

• For change logs, the deletion can be done from DSO –> Manage –> Environment –> Delete change log data.

/wp-content/uploads/2013/10/14_289277.png

/wp-content/uploads/2013/10/15_289278.png

Please note that only already updated change log requests can be deleted and after deletion a reconstruction of requests for subsequent data targets using the DSO change log will not be possible.

8) Delete DTP temporary storage:

This task is only relevant for BI 7.0/7.3.The DTP can be set up from the temporary storage in case of problems.

The deletion of temporary storage can be set from DTP maintenance –> Goto –> settings for DTP Temporary storage –>Delete temporary storage.

/wp-content/uploads/2013/10/16_289280.png

/wp-content/uploads/2013/10/17_289282.png

Here you can choose for each DTP:

• For which steps you want to have a temporary storage.

• The level of detail for the temporary storage.

• The retention time of temporary storage.

9) Compression:

Transaction Code –> RSA11

• Info cubes should be compressed regularly (See notes 375132,407260,590370 for more details). Uncompressed cubes increase data volume and have negative effect on query and aggregate build performance. If too many uncompressed requests are allowed to build up in an infocube, this can eventually cause unpredictable and severe performance problems.

B) Pure Basis Activities:

/wp-content/uploads/2013/10/20_289295.png

1) Apply SAP notes / SAP service and support packs / add-on’s:

Transaction Code  –> SNOTE, SAINT, SPAM
• Implement a SAP note on demand.
• Implement SAP service packs on demand. Normal practice is that this will happen twice a year. More frequent if the BW version is rather new and the service packs comes out more frequently, lesser when the BW version is at the end of its life cycle.
• Implement BW add-ons on demand.
• To keep the system up to date, SAP recommends implementing support packages and / or patches into the system landscape on a regular basis. This should prevent already known and fixed bugs affecting your business and you can make use of product improvements. To guarantee an optimal level of support from SAP side, the system has to have an up-to-date status.
• Corrections for BW (front-end, server, plug-in or add-on) are only made available in the before mentioned support packages. With the expectation of individual cases, no description of the correction (table entries, coding) is given in the notes. In general, SAP does not carry out corrections directly in the customer system. It is recommended to apply support package stacks ,which are usually delivered quarterly.(see http://service.sap.com/sp-stacks

2) BW upgrades:
• Upgrade the BW system on demand.

3) Transport:    
Transaction Code  –> STMS
• Import transports to the system on demand.

4) Client copy activities:
Transaction Code  –> RSA13. Restore Source system.
• After a client copy of a connected source system, the connection needs to be restored.

5) Data base and Kernel settings:
Transaction Code  –> RZ10
• Reevaluate the SAP Kernel and Database settings on a yearly basis.

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

Assigned Tags

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

      Again nice information..Really helpful for us and of course for BASIS team as well.

      Thanks for sharing.

      Regards,

      AL

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran
      Blog Post Author

      Thanks Anshu...

      Author's profile photo Marc Bernard
      Marc Bernard

      Hello Prabhith,

      great blog series! I would only add some info whether certain steps are relevant for BW on HANA or not (since several are not necessary any more).

      Also it might be helpful to indicate which steps are already covered by SAP_BW_HOUSEKEEPING task list which can automate several of the steps you listed.

      Regards,
      Marc

      SAP Customer Solution Adoption (CSA)

      Author's profile photo Former Member
      Former Member

      Good Article..Keep going

      Author's profile photo Former Member
      Former Member

      Very Good document.

      But I feel BW upgrade is responsibility of Basis (80%) and BW (20%) Consultant together. As issues like inconsistency come during Technical upgrade, which should be resolve by BW consultant.

      Also ABAPER need to run the process like SPDD and SPAU.

      Regards,

      Sushant 

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      All your series are damn good 🙂 . It seems you have done tremendous research and made your BW System very healthy. 🙂 🙂

      Author's profile photo Harish Allachervu
      Harish Allachervu

      Exceptional keep going..... 😛

      Thanks for sharing such valuable information..... 😉

      Thanks & Regards,

      Harish

      Author's profile photo Former Member
      Former Member

      Thanks for sharing...

      Regards

      Amrith

      Author's profile photo Former Member
      Former Member

      Thanks for this series 🙂 .

      Regards

      Manna Das

      Author's profile photo Former Member
      Former Member

      waiting for next part 🙂 🙂 , Thanks For Sharing 😐 😐 .

      Reagdrs,
      Giri

      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.

      BR
      Prabhith

      Author's profile photo Aby Jacob
      Aby Jacob

      Good Job, Thanks again

      Author's profile photo Former Member
      Former Member

      Great Document 🙂

      Thanks for sharing 🙂

      Regards,

      Tulika

      Author's profile photo Carlos Pinto Camarero
      Carlos Pinto Camarero

      Great doc as always, Prabhith. Very useful all 4 parts!

      Many thanks for sharing.

      Regards,

      Carlos

      Author's profile photo Rajkumar Bhumij
      Rajkumar Bhumij

      Hi Prabhith,

      Wonderful documentation.... 🙂

      Helpful for both BW as well as BASIS people.

      Cheer!!!
      Rajkumar

      Author's profile photo Roland Kramer
      Roland Kramer

      Hi,

      As Marc already stated, a wonderful Blog Series. I will add this to the Document - http://scn.sap.com/docs/DOC-7856

      Best Regards

      Roland

      Author's profile photo Former Member
      Former Member

      Hi,

      Very nice doc. under single umbrella.

      Handy for people working on Production support environment...

      Thanks for Sharing Prabhith.. 😉

      Author's profile photo Former Member
      Former Member

      Hi, great job.

      Regards,

      Herminio.

      Author's profile photo Ashok Babu Kumili
      Ashok Babu Kumili

      Amazing. Extra ordinary information on performence tuning. Thank you.

      Author's profile photo Former Member
      Former Member

      Hello Prabhith,

      Can you please help me out with below list of tables which tables can be compressed using Oracle's Advanced compression?

      Type of Tables
      ODS Tables
      ODS Tables
      PSA Tbales
      Fact Tables
      RS Tables
      SXM Tables
      Dimension Tables
      EDI Tables
      Other Tables in PSAPSR3 tablespace

      Thanks,

      Hetal