10 Best Modeling Strategies in SAP BW –Part 1
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
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
You have done a great job..Indeed useful document..
Waiting for part-2
Regards,
AL
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
nice but not yet 10 modeling strategies 🙂
Martin
Thanks Martin for reading the blog, yes it is only 2 in the first blog. I will post rest into different parts with example.
Regards,
Sangita
Waiting for doc-2
Good Work, Also don't restrict up to 10 only. Share other that 10 too.
Regards,
Sushant
Nice document.
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
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
ok got it fine..thanks
Great document for Modeling Design. Very effective! Thanks a lot for sharing this.
Regards,
Prabhav
Nice doc.. 🙂
Regards,
Poorna
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.
Waiting for doc-2