10 Best Modeling Strategies in SAP BW –Part 1

[Former Member]

I have divided modeling strategy into different categories. In part 1 I will start with modeling strategy for info cubes with 2 key points.

A. Info cube

1. Dimension vs. Fact table Ratio

As recommended by SAP and most of SAP BW consultants are aware of general rule that Dimension table should be within 15% of fact table size.  It is recommended for good performance.

But while designing a data model, how we would determine dimension’s design to maintain a healthy dimension vs fact table ratio?

Below are some small tips which will help to achieve it.

- Keep the dimensions small.

- Check the Info cube design with sample data using tcode RSRV.

    RSRV→ All Elementary Tests →Database.

Highlight the Database Information about InfoProvider Tables, right-click (context menu), and choose Select Test.

Based on test result adjust your dimension design.

Note: If you would like to add sample records without uploading from ECC or flat file, you can use Program “CUBE_SAMPLE_CREATE”. It will provide ALV grid to input your sample records.

Caution: Use it ONLY in development or testing environment.

2. Navigational Attribute vs Dimension Info object

A great challenge when designing a data model is to decide whether to store data as a Characteristic in a dimension table (and therefore in the InfoCube) or as an attribute in a master data table and use it as Navigational attribute.

Navigational attributes instead of a simple dimensional attribute always introduce a performance penalty in terms of query execution.  It’s suggested to avoid if possible activating a large number of navigational attributes and keep them only if there are business requirements.

Reason:

- The fact table contains one foreign key column per Info Cube dimension and a column per key figure of the Info Cube.

-The dimension table consists of a dimension id (DIMID) column which constitutes the primary key of the dimension plus a column per characteristic in that dimension. Those columns hold SID (surrogate id) values of the corresponding characteristic.

- In the third layer, there are SID-tables of the characteristics. This can be a standard S-table, containing only relationship between SID and characteristic key, an X-table (SID-key relationship plus SID columns per time-independent navigational attribute), or a Y-table (SID-key relationship, timestamp, SID columns per time-dependent navigational attribute).

- In the fourth layer, there are standard S-tables for navigational attributes.

Example:

Material Group as Navigational Attribute of Material

In this case during query execution, data will be read up to fourth layer as per below diagram.

  

I would like to hear your views and suggestions.

References: SAP BI Performance & Administration