Sunday, August 9, 2009

The biggest tables in SAP BW

In one of my April 2008’s posts I noticed a standard SAP R/3 / ECC application tables which may potentially grow rapidly and then they may cause problems. I mentioned in there some tables from other SAP solutions like CRM, SCM, MDM, APO, PI, etc. In today’s post I would like to focus on BW big tables.
• BW staging engine tables: RSMONMESS, RSSELDONE
• BW workbook tables: RSRWBSTORE
• Temporary BW tables: /BI0/0*
• RSBMNODES – “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.
• RSBERRORLOG (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 InfoPackage (IP) or Data Transfer Package (DTP) is setup in way that every data duplicity needs to be recorded.

So here it is, yes really it has 151 milions of records. I took me a half an hour to get this pop up window.

Since the fact tables of the InfoCubes are usually one of the biggest tables in a SAP NetWeaver BW you can observe their volume as well. Table names have following naming conventions:

/BIC/F* - for fact table before cube compression
/BIC/E* - for fact table after cube compression

DDIC data classes for SAP BW’s InfoProviders tables

Within SAP BW following data class of DDIC objects are important:

DDIM           Dimension Tables in BW of InfoCubes
DFACT          Facts Table in BW of InfoCubes
DODS           ODS Tables in BW

All of those have been introduced in order to improve performance while reading/writing InfoProviders. Settings of data class are maintained in "Technical Settings -> Database storage parameters" screen of TA SE11. Data class is assigned to the database tables of the InfoCube (table RSDCUBE, RSDODSO). Notice that this assignment cannot be made by any circumstances by user, only system does this while you activate InfoProvider.

Subsequently see overview of table RSDCUBE’s fields with link to data class according BW versions:

SAP BW 2.x:
CUBESIZCAT     Size category for fact tables (BasisCube and aggregates)
DIMEDATCLS     Data class for dimension tables
DIMESIZCAT     Size category for dimension tables
AGGRDATCLS     Data class for aggregate fact tables (only aggregates)

SAP BW 3.x (parameters only affect aggregates, not the cube):
AGGRDATCLS     Data class for aggregate fact tables (only aggregates)
AGGRSIZCAT     Size category for aggregate fact tables
ADIMDATCLS     Data class for aggregate dimension tables
ADIMSIZCAT     Size category for aggregate dimension tables

Furthermore see overview of RSDODSO’s fields for DSO objects as InfoProvider with link to data class:

ODSADATCLS     Data class for table with active data of the ODS
ODSMDATCLS     Data class for table with ODS input data

To see all available data classes check table: DDART (DD: Data Class in Technical Settings)
To see all available size categories check table: DGKAT (DD: Size category in technical settings)