Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SAP BW -Performance Tuning thru System Tables

former_member181999
Contributor
‎2014 May 23 3:54 PM
12,499

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.

40 Comments
Labels in this area
Popular Blog Posts