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 re commended 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.
2. Navigational Attribute vs Dimension Info object
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:
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.
But while designing a data model, how we would determine dimension's design to maintain a healthy dimension vs fact table ratio?
- 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.
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.
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.
- 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.
In this case during query execution, data will be read up to fourth layer as per below diagram.
No comments:
Post a Comment