Master Data Notes

The dependent attributes of the characteristics are stored in separate tables called as Master Data Tables. The Master data tables are not directly linked to InfoCubes. Master Data tables can be of two types. This can be marked to individual attributes in the MDT.

-          Time Independent MDT

o    All the non time dependent attributes are placed under this table

§  /BIC/P<InfoObject>

-          Time Dependent MDT

o    Time dependendency of an attribute allows you keep track of changes to it. The time dependent attributes are stored in Q Tables. Only the attributes which are timedependent are present in this table.

§  /BIC/Q<InfoObject>.

Master Data Text : 

Textual Descriptions are saved in Text Tables.

Hierarchy :

Hierarchies of characterstrics may be stored in separate hierarchy tables.

SID Tables

Sid tables play an important role connecting the data warehouse information to the InfoCubes and the DSOs. To speed up access to InfoCubes and DataStore Objects and to allow an independent master data layers, each characteristic and attribute is assigned a SID column and their values are encoded into 4-byte integer values.

-          The SID tables are generated always.

o    /BIC/S<InfoObj>

-          Non Time Dependent – Navigational Attribute SID Table

o    This is generated when there are navigational attributed to the InfoObject

§  /BIC/X<InfoObject>

-          Time Dependent –Navigational SID Table

o    This is generated when there are time-dependent attributes

§  /BIC/Y<InfoObject>

Points-To-Remember.

If the InfoObject is Maser data, All the SID tbales are automatically generated when the Master Data is being loaded.
The SID for transactional data gets generated depending on DSO settings.
- During DSO Activation
- During Reporting
They can also be maintained during Infocube load if ‘No Referential Integrity’ check is enforced in Infopackage.

Dimension Tables

Activation of Infocube creates Dimension Tables. The columns of a dimension table are not the characteristics themselves but the SIDs of the characteristics you have chosen to be members of the InfoCube dimension (table). The unique key of a dimension table is the dimension ID (DIM-ID) that is a surrogate key (integer 4).

In the BI data model a surrogate key is used as a unique key with each dimension table and not the real most granular characteristic within the dimension. For example, for each unique combination of SID values for the different characteristics within a dimension table there is a unique surrogate key value assigned. The dimension tables are joined to the fact table using surrogate keys in BI.

There are total of 16 dimensions allowed in an Infocube. 3 pre-defined ones and 13 user-definable.

Time Dimension

Unit/Currency Dimension

Packet Dimension.

With every load into an InfoCube there is a unique packet-ID assigned. This allows you to purge erroneous loads without recreating the whole InfoCube again. The packet dimension can increase overheads during querying and can therefore be eliminated using the compress feature of the InfoCube after proven correctness of the loads up to a certain packet-ID.

Fact Tables

This is also created during the Infocube activation. They have different DIM ids (and SID ids , incase of Line dimension ) with the corresponding facts.

-          Each row in the fact table is uniquely identified by a value combination of the respective DIM-IDs / SIDs of the dimension / SID tables

-          Since the BI uses system-assigned surrogate keys, namely DIM-IDs or SIDs of 4 bytes in length per dimension to link the dimension / SID tables to the fact table, there will normally be a decrease in space requirements for keys in comparison to the use of real characteristic values for keys.

-          The dimension / master (SID) tables should be relatively small with respect to the number of rows in comparison to the fact table (factor 1:10 / 20).

There are 2 different fact tables created for Infocube.

-          F Fact Table

o    When the data is loaded to the Cube, it will be present in the F Fact Table, with the corresponding request no. We can delete the requests when the data is present here. Once the data is compressed, it is moved to E Fact Table.

o    The F-table uses b-tree indexes

-          E Fact Table

o    The compressed data is present here. When the functionality of ‘Compress’ is done for the request, then the request is merged with the data present and the request id is set to 0. No deletion of data is possible based on request numbers as they are nullified.

o    Since the data is in compressed form, it needs less space.

o    E-Table uses bitmap indexes

o    Suppose there is only one customer with C100 is doing Transactions & in 100 requests there are 100 records. Then when you eliminate the request all records are aggregated to 1 record. The key figures are summed up.

o    If there are 100 different customers doing individual transactions, and its compressed, it still has 100 records as there are different customer numbers.