Skip to Content

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.

S1.png

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.

S2.png

S3.png  

I would like to hear your views and suggestions.

References: SAP BI Performance & Administration

To report this post you need to login first.

16 Comments

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

  1. Ali Alwan
    • The query reading flow diagram is wrong. Query starts by reading master data then linking down to fact table.
    • Storing attribute directly into cube structure might cause wrong data when the attribute value modified by the user and loaded into BW, given that your transactional data is delta load. Always use the attribute as navigational attribute unless you know what are you doing.
    (0) 
    1. Sangita Kumari Post author

      Thanks Ali for reading this blog and providing your comments.

      * Diagram is to explain the concept of flowing up to fourth layer in easy and understandable way. Here is SQL statement which will help you to understand it better.

      SQL Statement

      ———————————————————————–

      SELECT

        “DU”.”SID_0CURRENCY” AS “S____801″ ,”DU”.”SID_0UNIT” AS “S____802” ,

        “D1″.”SID_MATID” AS “S____6528″ ,”X1″.”S__SW_MATGRP” AS “S____9861” , COUNT( * )  AS “Z____156” ,  SUM (  “F”.”/BIC/S1_AMOUNT”  )  AS “Z____6512” , SUM (  “F”.”/BIC/S1_QTY”  )  AS “Z____6514”

      FROM

        “/BIC/FIC_SALE” “F” JOIN “/BIC/DIC_SALE1” “D1” ON  “F” . “KEY_IC_SALE1” = “D1” . “DIMID” JOIN “/BIC/XMATID” “X1” ON  “D1” . “SID_MATID” = “X1” . “SID” JOIN “/BIC/DIC_SALEP” “DP” ON  “F” . “KEY_IC_

        SALEP” = “DP” . “DIMID” JOIN “/BIC/DIC_SALEU” “DU” ON  “F” . “KEY_IC_SALEU” = “DU” . “DIMID”

      WHERE

        ( ( ( ( “DP”.”SID_0CHNGID” = 0  ) ) AND  ( ( “DP”.”SID_0RECORDTP” = 0  ) ) AND  ( (

        “DP”.”SID_0REQUID” <= 20939  ) ) ) ) AND “X1″.”OBJVERS” = ‘A’

      GROUP BY

        “DU”.”SID_0CURRENCY” ,”DU”.”SID_0UNIT” ,”D1″.”SID_MATID” ,”X1″.”S__SW_MATGRP”

      ———————————————————————–

       

      * My emphasis is on performance. In SAP BW loading sequence is much important. However as I mentioned above decision should be made based on business requirement and overall design.

      Best Regards,

      Sangita

      (0) 
    1. Sangita Kumari Post author

      Thanks Anshu for Appreciation. Having 10 major points on my hand but it’s require time to put in a simple form through example. I’ll try my best to publish it soon.

      Regards,

      Sangita

      (0) 
  2. Antony Xavier

    Thanks for sharing….

    Regarding 1st point..I checked RSRV check for information about infoprovider table…it just gave option to choose info cube and after that nothing gets displayed regarding the table..am I missing any step here..Could you pls paste the detailed screen shots 

    (0) 
    1. Sangita Kumari Post author

      Hello Antony,

      After selecting info cube click on execute button. I am not infront of system to provide you screen shot. Please let me know if you have still any issue to execute this test.

      Regards,

      Sangita

      (0) 
  3. Y K

    I have a question on point 2.

    In which scenario we will use “Material Group as Navigational Attribute of Material” and in which scenario we will use it  “as a Characteristic in a dimension table (and therefore in the InfoCube)”.

    What will be the difference between storing data  in info cube and in master data tables as navigational attribute. How users want to see the data , from Navigational Attribute or from the info object in the info cube.

    Thanks,

    kiran.

    (0) 

Leave a Reply