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

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 those points under a single umbrella.

Basically I have divided  the activities into 4 parts namely General monitoring, System health monitoring, Performance related monitoring and Occassional 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 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) System Health Related Daily/Weekly Monitoring Activities:

Description	Done by/ Responsible Team
Make sure all BW objects are active in Production environment	BW
Check all source system connections are working	BW
Make sure that all queries are consistent and can be executed, Make also sure that there are no queries with duplicate entries	BW
Reorganize unused: Workbooks and favourites	BW
Reorganize or delete unprocessed tRFC entries / delete tRFC queues	BW
Clean-up unreferenced objects like PSA Tables, Transfer rules	BW

1) Inactive BW objects:

The following programs would be handy:  

In BW, some objects can become inactive due to some reasons like the import of a new Transport or so on. Such inactive objects may result in loading issues.It is recommended that the inactive objects should be repaired by transport rather than activating them in the system itself. In some mission critical situations, the above mentioned programs may be useful to the users.

2) Source system connection:

Transaction Code –>  RSA1, RSA13.

SE38 –> RS_LOGSYS_ACTIVATE

• Make sure that all the source system connections are active. If not, repair that failed connection with high priority.

• Inconsistent source system connections make it impossible to load data.

3) Query consistency:

Transaction Code –> RSRT

• Run ABAP ‘RSR_GEN_DIRECT_ALL_QUERIES’ to regenerate all queries.

• Make sure there are no queries with duplicate names. Run ABAP ‘RENAME_DUPLICATE_ELEMENTS’ to correct any inconsistencies.

• Make sure that the Meta data tables of queries are consistent. Run ABAP ‘ANALYZE_RSZ_TABLES’ to check and repair this.

• Make sure all queries compile (and are therefore consistent).If they don’t compile, repair or delete them.

• Queries become inactive (and inconsistent) if something changes to their corresponding info providers. Sometimes, a characteristic is deleted from the    infocube, but the concerned query is not changed and the result would be that the query won’t be executed.

4) Reorganize unused work books and favourites:

Tools: ABAP:  RSWB_ROLES_REORG

•  When users or authorization roles are deleted, workbooks linked to roles or favourites get unreferenced and cannot be called anymore. So it is always handy to run the above mentioned Abap program to reorganize the workbooks.

5) Reorganize or delete unprocessed tRFC entries:

Transaction Code –> SM58

• When a data load cancels, some residues are left behind in the system. One of those residues is tRFC’s. Transaction SM58 shows those tRFC’s and offers a reorganize (delete) option.

• In BW and all connected source systems, check all Outbound tRFC queues (transaction SM58) from time to time to see whether they contain old unsent data packages or Info idocs. The reason for these leftover entries could be that they have already been processed in BW, and therefore cannot be processed again, or they contain old requests that cannot be sent following the system copy and connection change. We should delete these tRFC requests from the queue, not only to reduce the data volume in the system but primarily to prevent from accidently sending these old entries again to BW data targets.  

• In the RFC destination (transaction SM59) from the source system to BW, the connection should be set up to prevent a terminated transfer from being restarted automatically and, most importantly, to prevent periodic automatic activation of incorrectly sent data.

6) Unreferenced objects:

• Clean-up unreferenced / inconsistent objects in the data base in the broadest sense of the word.

• Check transfer rules consistency by running ABAP ‘RSAR_TRANSTRUCTURE_CHECK’.

• Run ABAP ‘SAP_PSA_ZEROCORRECT’ to correct PSA table with partition number 0. Sometimes, a correction can be made, so the data is still available. If this correction cannot be made, the content is deleted.

• Clean-up unreferenced PSA metadata objects by using ABAP ‘RSAR_PSA_CLEANUP_DEFINITION’.

• Clean-up unused/inconsistency PSA tables by running ABAP ‘RSAR_PSA_CLEANUP_DIRECTORY’

• Correct duplicate transfer structures (table RSISOSMAP) by running ABAP ‘RSAR_RSISOSMAP_REPAIR’.

• Correct source system consistency (table ROOSGEN) by running ABAP  ‘RS_SRCSYSTEM_TS_CHECK’

• Check the consistency of DDIC names for transfer rules and info sources by running ABAP ‘RSAR_DDIC_NAME_CONFLICT’

• Correction for the technical name of transfer structures and communication structures by running ABAP ‘RSAR_STRUCTURE_NAMES_CHECK’.

• Clean obsolete entries in table DBDIFF by running ABAP ‘SAP_UPDATE_DBDIFF’.

B) System Health Related Monthly Monitoring Activities:

Description	Done by/ Responsible Team
Make sure that the Info objects are consistent, definition as well as data	BW
Delete old unused queries/ workbooks	BW
Make sure that all queries are consistent and can be executed, Make also sure that there are no queries with duplicate entries	BW
Delete temporary query views	BW
Process SAP Early Watch report	BW / BASIS

1) Info objects consistency:

Transaction Codes –> RSD1, RSD5

• Check the consistency of the content of the master data and text tables of info objects and repair any inconsistency.

2) Unused queries/ workbooks:

Transaction Code –> RSZDELETE

• Unused / Unnecessary Queries can lead to higher times in the ‘Open’ ’Query’ dialog. They also can cause an unnecessary allocation of disk space
• Check regularly whether queries are really still in use. The ‘Last used’ field in transaction RSZDELETE (delete objects) is related to the table RSZCOMPDIR. ‘Last used’ is updated in the table RSZCOMPDIR with the exact timestamp of when queries are executed with the Bex Analyzer or Web frontend.
• Run report SAP_DROP_TMPTABLES and function RSDDTMPTAB_CLEANUP( with parameter name I_NAMETYPE =03) to remove the temporary query views(‘/BIC/03xxx’).Make sure that no query execution, compression, aggregate roll up or data extraction runs while the report SAP_DROP_TMPTABLES is executed, otherwise this can result in termination of the process.

For further details, please refer SAP Note – 449891.

3) Delete temporary query views:

• The BW system uses different temporary objects for query execution, compression and   other processes. The system often generates them outside of the ABAP DDIC for performance reasons or because they cannot be defined there (as stored procedures or triggers).In general these temporary objects are deleted when the processing in the query is finished, but in case of an abortion of query, residual entries can persist.

4) Early Watch report:

• Run SAP Early Watch report. Try to solve the recommendation. The free SAP earlywatch alert service can be used to facilitate BW maintenance. It offers a unique application and basis perspective on the system. Utilize the SAP earlywatch alert as a part of the system monitoring strategy. It is also a good information source and checklist which can be used in meetings between the application and basis team.

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