SAP BW -Performance Tuning thru System Tables

I am sharing my experience with a client system (Client a fortune 500 company, Runs a beverage (Soft) business in 140 Countries). Using the SAP BW solutions and capabilities since Year 2002 now currently with SAP BI 7.4 SP Stack.

Problem Statement

Few Months ago project team took decision to enhance the DB size to accommodate the DB growth for next 24 months. Implemented the DB size enhancement considering it would cater the enterprise needs for next 24 months. Unfortunately 50% of the expansion DB size is consumed in 5 months. So I started examining the reasons, root cause and solutions.

Current Solution

Periodically the experts from BW team (couple of service providers) use following techniques to improve the performance of DB and SYS (EDW).

1. 1. BW Data housekeeping tasks
   1. Pros:
      • Able to manage all the table data growing places like 1) PSA. 2) DSO 3) Statistics 4) Infocubes
   2. Cons:
      • Unable to concentrate on the system level tables across all areas (Ex: FI, CO, MM, PP, HR, CRM…etc.)
2. 2. BW Performance tuning techniques.
   1. Pros:
      • Able to manage all the table data growing places like 1) Aggregates. 2) Request Compression 3) Infocubes Compression 4) Partitioning 5) Indexing 6)BIA
   2. Cons:
      • Unable to concentrate on the system level tables across all areas (Ex: FI, CO, MM, PP, HR, CRM…etc.)

New Solution

Now I used a different technique to identify various tables in the System which are collecting the information and storing the data in the tables from time to time.

I have searched all the EDW (Enterprise data warehouse) and found 200+ tables. Performed a thorough analysis with respect to their

1. Information / Table / Data / usage (Where & When used).

2. Number of records / Size of the table and its growth.

3. Index creation (Active/Inactive-Last creation date).

4. Needed data retention duration with respect to Client’s expectations.

After a thorough I could Identify 43 tables satisfying my above rules (Four).

Analysis

I am really surprised to know that they are

1. 50 % of tables are never used in any place

2. Few are used hardly once in a quarter. Actually the usage is for Data administration need by the administrator.

3. The transaction / data older than 2years is stored in the tables.

Needed checks

1. 1. Checked with the client team to move the data (older than 90 days) to near line storage
   2. Basis team for validation for tables and schedule of data archive jobs.

          Tables         (Records found in Millions)

1. RSMONMESS                     (334.5 Million)

• • Messages for the monitor

2. RSDDSTATAGGRDEF        (241 Million)

1. • BW statistics data

3. RSIXWWW                          (29 Million)

• • Cluster Table for Storing Web Reporting Components

4. RSDDSTATEVDATA          (18.48 Million) 

• • BW statistics data

5. RSDDSTATCOND               (17.8 Million)

• • BW statistics data

6. RSZWOBJ                             (16 Million)

• • Storage of the Web Objects

7. RSDDSTATHEADER           (11.5 Million)

• • BW statistics data

8. RSZWBOOKMARK             (11.1 Million)

1. • Header Table of the Bookmarks

9. DDLOG                  (15 Million)       

1. • Buffer Synchronization

10. RSDDSTATAGGR               (12.5 Million)

• • • BW statistics data

11. RSERRORHEAD                  (10.4 Million)

1. • Incorrect Records (Header Table) (PSA error logs)

12. RSDDSTATLOGGING        (9.5 Million)

• • BW statistics data

13. RSDDSTATDM                    (8.5 Million)

1. • BW statistics data

14. RSRWBTEMPLATE            (8.5 Million)

• • Assignment of Excel workbooks as personal templates

15. RSRWORKBOOK               (7.1 Million)

1. • Where-used list for reports in workbooks

16. RSDDSTATINFO (777 K) (6.9 Million)

1. 1. BW statistics data

17. RSDDSTATDELE                 (5.9 Million)

• • BW statistics data

18. RSERRORHEAD (4.1 Million)

• • Incorrect Records (Header Table)

19. RSERRORLOG                     (2.7 Million)

1. • Logs for Incorrect Records (PSA error logs)

20. RSBERRORLOG (2.36 Million)

1. • Logs for Incorrect Records
   • Stores error handling logs due to following and other reasons:
   • Warnings that are created during master data uploads for duplicate records
   • Single record error messages in customer-specific transformation routines
   • Table is accumulated a numerous error messages records for a DTP requests. In most cases this is due to many errors while BW is processing data and Info Package (IP) or Data Transfer Package (DTP) is setup in way that data duplicity needs to be recorded.

21. RSZWOBJTXT                     (1.1 Million)

1. • Texts for Templates/Items/Views

22. RSZWOBJXREF   (0.76 Million)

1. • Structure of the BW Objects in a Template

23. RSZWBOOKMARK            (1.18 Million)

1. • Header Table of the Bookmarks

24. RSZWVIEW                         (1.1 Million)

1. • Header Table for BW Views

25. RSZWITEM                          (2.1 Million)

1. • Header Table for BW Web Items

26. RSZWITEMATTR                (1.6 Million)

1. • Attribute Table of the Items (Contains Search Attributes) –

27. RSZWITEMDATA               (2.5 Million)

1. • BW Web Item Data (7.0)+

28. RSZWITEMXREF                (3.1 Million)

1. • Cross-Reference Table of the Items

29. RSZWBITMDATA               (1.2 Million)

1. • BW Web Item Data (7.0)+

30. RSZWBITMHEAD               (0.68 Million)

1. • Header Table for BW Web Items

31. RSZWBITMHEADTXT       (1.2 Million)

1. • Texts for BI Web Items (7.0+)

32. RSZWBITMTEXT                (0.7 Million)

1. • BW Web Templates: Language Texts

33. RSZWBITMXREF                (0.8 Million)

1. • BI Template Cross references to TLOGO Objects

34. RSZWBTMPDATA             (0.5 Million)

1. • BW Web Template Data

35. RSZWBTMPHEAD             (0.5 Million)

1. • Header Table for BW HTML Templates

36. RSZWBTMPHEADTXT  (0.8 Million)

1. • Texts for Templates/Items/Views

37. RSZWBTMPTEXT               (0.2 Million)

1. • BW Web Templates: Language Texts

38. RSZWBTMPXREF                (1.2 Million)

1. • BI Template Cross references to TLOGO Objects

39. RSRWBSTORE                    (2.1 Million)

1. • Storage for binary large objects (BW workbook tables -Excel workbooks)

40. RSSELDONE                        (1.5 Million)

• • BW staging engine tables:

41. RSRWBINDEX                     (2.0 Million)

• • List of binary large objects (Excel workbooks)

42. RSRWBINDEXT (1.8 Million)

• • Titles of binary objects (Excel workbooks)

43. RSBMNODES                      (1.5 Million)

• • “Hierarchical Log: Nodes”; If e.g. it takes a long time to set a DTP request to ‘green’ after it has been processed it is caused by large volume of data in this table. Since BI system in production a quite long time you got a lot of logs in it then you have a huge amount of entries in this table.

Assumption: The no-of records is a cumulative result of all the above 43 tables. The result count of no-of records is subjected to change as it is dependent on the EDW System (Client’s business , Reporting tools on EDW, etc,..)

Lesson Learned/Achievement: With the above analysis I could find 1,100 million records are there in the EDW which do not have any potential benefit to the client’s business or EDW system. After removing 1,100 Million records from EDW I could save 350 GB of disc space to my client.