SID Tables in #SAP #BW

[MGrob]

Introduction

BW uses a concept where characteristics are identified with masterdata identification numbers. (In german Stammdaten Identifikationsnummer, hence the name SID). This is different then the standard description in a star schema where dimensions contain characteristic values.

With this fact in mind there are a couple different things compared to a regular star schema as the true characteristic value is not to be found in dimensiontables.

SID-Tables

Introducing SID-tables or SID's in BW characteristics does separate BW objects completely from the use of characteristics. This allows to use the same SID tables from a characteristic multiple times with a reference to it. It can be used in InfoCubes, master data or hierarchies.
The fact that SID's are always of the INT4 type can have a positive impact on perfromance compared to, for example a producthierarchy key which is 18 char long as only the SID value is stored in the dimension. Another advantage is that SID allows characteristic with compounded keys without violating the concept of SID. For example customer sales is compounded to salesorg, division and distribution channel. This primary key can be handled in SID-tables. This allows access on BW objects as for those InfoObjects only the SID tables are needed to be accessed.

(By the way compounding characteristics always have a negative impact on reporting performance and should only be used where necessary)

The following table describes the SID tables for the InfoObject 0CUST_SALES.

Field	Data element	Description
CUSTOMER	/BI0/0ICUSTOMER	Customer
SALESORG	/BI0/0ISALESORG	SalesOrg
DIVISION	/BI0/0IDIVISION	Division
DISTR_CHAN	/BI0/0IDISTR_CHAN	Distribution Channel
SID	RSSID	Masterdata ID
CHCKFL	RSDCHCKFL	Flag: Value in check tables
DATAFL	RSDDATAFL	Flag: Value in dimension or available as attribute
INCLFL	RSDINCFL	Flag: Value is built into all inclusion tables

As an InfoObject can be used in multiple BW objects like InfoCubes and master data a deletion of masterdata entries and the respective SID entry can be critical when entries already have been used. In this case a deletion of masterdata is impossible. For BW to know that masterdata entries have been used it is documented in two SID tables called DATAFL and INCLFL. DATAFL is used in dimensions of InfoCubes or attributes of masterdata attributes while INCLFL is used in inclusiontables of external hierarchies.

As soon as a SID entry is used in an InfoCube it is flagged in master data with a value 'X' and from now on the masterdata entry cannot be deleted anymore. The disadvantage of this is once the flag is set it won't be deleted even if the masterdata entry is not in use anymore which makes deletion of masterdata only possible with time intense checks.

The name of SID-tables is related to the object name and in case of a standard InfoObject /BI0/Sxxxxxxxxxx and customer objects /BIC/Sxxxxxxxxx.

SID entries in dimensiontables

Extending the snowflake schema with SID has a significant impact on the datamodel and the structure of it. Especially the use of navigational attributes, timedependent and external hierarchy as well as texts. BW is unique in using SID.

The relationship between dimensiontables and master data tables is established through the SID of the corresponding InfoObject. In the dimensiontables only SID values are stored and never directly characteristic values. Writing the value directly would be a similar good option but there is a couple advantages over that using SID. The INT4 value is a performance gain compared to characteristics with long char values. Referencing with SID tables allows the identification of characteristics that are compounded.