HOUSE KEEPING / PERFORMANCE TUNING ACTIVITIES IN SAP BW SYSTEMS

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.

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

• Run frequently or schedule ABAP report ‘RSAN_RTT_CLEAR_TEMP_TABLES’

• Run frequently function module ‘RSDDS_CHANGERUN_TMPTABLS_DEL’

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.

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.

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.

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:

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

SAP Basis Upgrade Kernel Patching

Step by step how to upgrade and patch SAP Kernel.

Kernel is an executable program that exists between SAP Applications and Operating System. It consists of executable program under the path — "/sapmnt/<SID>/exe" for UNIX and \usr\sap\SID\SYS\exe\run Windows. It starts and stops application services such as message server, dispatcher, etc.

In Kernel upgrade, new .exec files replace old executable files in SAP system. To check the current version, use transaction code — SM51 > Go to Release Notes.

To check the status, go to System → Status tab → Other Kernel info

In UNIX, use disp+work − version to see information on the current Kernel version.

Follow these steps to download Kernel from SAP Market Place −

Step 1 − Open URL — https:\\service.sap.com and login with SID and password.

Step 2 − Go to SAP Support Portal from dropdown → Software Downloads.

Step 3 − Go to Support Packages and patches under software downloads. Go to Software Downloads.

All Kernel Versions are available in 64-bit −

Step 4 − Select the Kernel version and download the files as per the Operating System – Windows, Linux, Solaris, AIX, and HP-UX.

Upgrade Kernel Version

Let us now follow these steps to upgrade the Kernel version −

Step 1 − Create a directory with enough disk space and enter the name of directory.

Step 2 − Copy the upgrade files SAPEXEDB.SAR & SAPEXE.SAR files to the new directory at OS level.

Step 3 − To check directory, use 'pwd' to ensure you are in the same directory.

STep 4 − Now, uncompress .SAR files by sapcar exe.

SAPCAR –xvf sapexe.SAR

SAPCAR –xvf sapexedb.SAR

Step 5 − To take backup of existing Kernel, you have to create one more directory with the name "exe_old<ddmmyy>" and take backup of existing Kernel file.

Step 6 − To perform upgrade, stop the SAP application. You don’t need to shut down the database for Kernel upgrade but you need to stop the SAP application using this command — stopsap r3.

Step 7 − Copy the files from the new kernel directory exe_new<ddmmyy → to the existing kernel directory exe.

Step 8 − Use this command to copy — cp -rp /sapmnt/<SID>/exe_new<ddmmyy>/* /sapmnt/<SID>/exe/

Step 9 − Now if you check the current Kernel version using disp+work, then check the kernel version from OS level by the command disp+work − versions and new patch version should be displayed.

Step 10 − Login to the operating system as root specific to UNIX. Execute the script in Kernel directory — ./saproot.sh <SID>

You use this to assign correct permissions to all the executable programs in the kernel such as br* file etc.

Step 11 − Start SAP Applications using this command — startsap r3.

Step 12 − If you run Transaction SM52, you can see current kernel version level.

See also  SAP Basis Patch Management 

SAP Transport Management System Configuration Step By Step

I have 3 systems Production(SLP) Quality(SLQ) & Development (SLD) step by step i will tell you how to configure TMS for them

 Step 1- assign transport domain here I am assigning SLP as transport domain Goto T-Code STMS

 

Step 2- you will get popup, enter details as shown in below image in both boxes domain is SLP and then press Save

Step 3- you will get below screen after saving then press System Overview

 

Step 4- Click on update configuration 

 

Step 5- Click on Distribute & activate configuration

 

Step 6- select first option &click on Green tick

 

Step 7- Now Go to anther system which is Quality (SLQ) and hit STMS in SLQ elow popup will come their

 

Step 8-Click on other configuration

 

Step 9- enter target host as oup domain controller which is SLP in my case

 

Step 10- Click on Save

 

Step 11- you will get below screen with below message

 

Step 12- Now again goto STMS of SLP which is our domain controller to approve newly added system (SLQ)

 

Step 13- Click on system overview

 

Step 14- you will find newly added system which is SLQ in my case

 

Step 14- Click on newly added system and press approve

 

Step 15- Click on YES

 

Step 17- Click on Green tick

 

Step 18- you will find same status for both

 

Step 19- you will find same status for both means system is added successfully to transport domain

Repeat Step 7 to Step 18 to add third system which is SLD in my case

you will find below 3 system in System overview of SLP (Transport Domain ) after adding 3rd system (SLD)

 

Creating Transport Routes For TMS

Step 20- in transport domain controller  (SLP) Click on Transport Routes

 

Step 21-Click on Display Change

 

Step 22-Click on Change

 

Step 23-Click on Transport route editor

 

Step 24-Click on Hierarchical list editor and press enter

 

Step 25-you will find below screen

 

Step 26-Click on Display Change

 

Step 27-Click on Change

 

Step 28-Droll down Transport Layer by clicking on + mark

 

Step 29-Click on right click on transport layers and select create option

 

Step 30-enter transport layer name and description,  transport layer name should start with Z

 

Step 31- then right Click on transport route and select create option

 

Step 32-Select Consolidation and enter Development(SLD) & quality system (SLQ) and layer(ZABC) then click on green tick

 

Step 33-you can see both systems are added

 

Step 34-again right click on transport route and select create

 

Step 35-this time click on select delivery and enter quality (SLQ) and production (SLP) system and click on green tick

 

Step 36-you can see all 3 systems in transport route; now click on SAVE

 

Step 37-Give short description and enter

 

Step 38-Distribute & activate configuration

 

Step 39-your TMS is created  and active