Skip to Content

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

    • 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)

    • Header Table of the Bookmarks

9. DDLOG                  (15 Million)       

    • Buffer Synchronization

10. RSDDSTATAGGR               (12.5 Million)

      • BW statistics data

11. RSERRORHEAD                  (10.4 Million)

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

12. RSDDSTATLOGGING        (9.5 Million)

    • BW statistics data

13. RSDDSTATDM                    (8.5 Million)

    • BW statistics data

14. RSRWBTEMPLATE            (8.5 Million)

    • Assignment of Excel workbooks as personal templates

15. RSRWORKBOOK               (7.1 Million)

    • Where-used list for reports in workbooks

16. RSDDSTATINFO (777 K) (6.9 Million)

    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)

    • Logs for Incorrect Records (PSA error logs)

20. RSBERRORLOG (2.36 Million)

    • 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)

    • Texts for Templates/Items/Views

22. RSZWOBJXREF   (0.76 Million)

    • Structure of the BW Objects in a Template

23. RSZWBOOKMARK            (1.18 Million)

    • Header Table of the Bookmarks

24. RSZWVIEW                         (1.1 Million)

    • Header Table for BW Views

25. RSZWITEM                          (2.1 Million)

    • Header Table for BW Web Items

26. RSZWITEMATTR                (1.6 Million)

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

27. RSZWITEMDATA               (2.5 Million)

    • BW Web Item Data (7.0)+

28. RSZWITEMXREF                (3.1 Million)

    • Cross-Reference Table of the Items

29. RSZWBITMDATA               (1.2 Million)

    • BW Web Item Data (7.0)+

30. RSZWBITMHEAD               (0.68 Million)

    • Header Table for BW Web Items

31. RSZWBITMHEADTXT       (1.2 Million)

    • Texts for BI Web Items (7.0+)

32. RSZWBITMTEXT                (0.7 Million)

    • BW Web Templates: Language Texts

33. RSZWBITMXREF                (0.8 Million)

    • BI Template Cross references to TLOGO Objects

34. RSZWBTMPDATA             (0.5 Million)

    • BW Web Template Data

35. RSZWBTMPHEAD             (0.5 Million)

    • Header Table for BW HTML Templates

36. RSZWBTMPHEADTXT  (0.8 Million)

    • Texts for Templates/Items/Views

37. RSZWBTMPTEXT               (0.2 Million)

    • BW Web Templates: Language Texts

38. RSZWBTMPXREF                (1.2 Million)

    • BI Template Cross references to TLOGO Objects

39. RSRWBSTORE                    (2.1 Million)

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

To report this post you need to login first.

39 Comments

You must be Logged on to comment or reply to a post.

  1. Purushotham K

    Very usefull info to my project.

    I have a question regarding the tables placed here. Are these the common tables are system tables. Please confirm. Thank you.

    (0) 
    1. Ashok Babu Kumili Post author

      Hello Purushottam.

      They are system related tables. Those tables contain the information about the information say the data is meta data.

      Best practice: Is to take away(or Delete) the data older than 90 days from current date. Pl let me know if any other questions. Thank you.

      (0) 
  2. sandeep venkat

    Glad for the list.

    But I have (Different) system table where I planed to do a similar performance tuning. The soon I do that I’ll share to this forum.

    (0) 
  3. Atan Gani

    Dear Ashok,

    Can you share with us what tool/programs to be used for remove/delete contains all table you mention above

    Regards,

    Atan

    (0) 
  4. Arun Varadarajan

    Ashok,

    With regards to RSMONMESS – you could look at request data archiving which is a feature provided in BW7.0 upwards. This reduces the size of the RSMONMESS tables. You cannot archive this table alone and this functionality gives a lot of benefits like making your data loads faster (slightly) and also the time it takes to bring up the manage screen of the infoprovider is reduced by archiving the requests.

    https://help.sap.com/saphelp_nw73/helpdata/en/49/9500f79c1d311fe10000000a421938/content.htm

    This would help in explaining the same. But as always – test this thoroughly in your development system before implementing the same. The risks of not doing it correctly would mean that you cannot load data to some infoproviders if this is not done right..

    (0) 
      1. Shiv kumar

        Hi Ashok,

        We are currently on SAP BI 7.4,HANA DB.We are currently trying to investigate fast growing tables in BWP system.As a thumb rule can we delete the contents from all 43 tables provided above?please let me know if any precautionary measures to be taken before doing this activity…

        Thanks

        (0) 
  5. Ali Samsa

    Very Helpful Post. following the comments above from Arun, I highly recommend to not ignore or manipulate manually the BW Monitor tables for requests. This can lead to inconsistencies in request management and irreparable issues for data loading, management and reporting on BW Providers. All Tables which are in scope of archiving object BWREQARCH should follow the archiving solution. please check standard help: http://help.sap.com/saphelp_nw74/helpdata/en/49/9500f79c1d311fe10000000a421938/content.htm?frameset=/en/c5/f1c99abdd3fb4aa7febe103b5d77e5/frameset.htm&current_toc=/en/c5/f1c99abdd3fb4aa7febe103b5d77e5/plain.htm&node_id=209&show_children=false For BW >=7.3 you should select the DTP request as well. For BW before 7.3 release this checkbox to include DTP request was not available.

    (0) 
  6. Adel Abdelhaoui

    Hi Ashok,

    Regarding your statement:


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


    Could you please explain what tools/transaction you used to find out these tables not used as per your conclusion?


    >

    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.


    Thanks

    Adel


    (0) 
  7. Inbaselvan A

    Hi Ashok,

    Regarding your statement:


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


    Could you please explain what tools/transaction you used to find out these tables not used as per your conclusion?


    >

    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.

    (0) 

Leave a Reply