SID Tables in #SAP #BW
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.
Thanks for sharing Nice blog.Clearly explained the concept of SID.
if the masterdata entry is not in use anymore which makes deletion of masterdata only possible with time intense checks.What's time intense check?
Regards,
B Devi
thanks. Due to the checks the system has to do to validate if the entry can be deleted. Have a look at note 1370848 about the deletion process.
Martin
Nice document.
Regards,
Sushant
Nice entry, thanks.
A question related to SAP BW Workspaces and local providers: Can one prevent SAP BW from generating new SIDs when uploading/creating a local provider, in other words only allow dimensions/characteristics from the local provider if they already exist in the Master Data tables?
Thanks Martin for sharing the Nice Blog...
I would like to add that due to SID's only Support to Multiple Languages has been established in Extended Star Schema..
Very Informative .Thanks for Posting !
Regards
Yogesh Narwani
Hi Martin,
I thought SID is the abbreviation for Surrogate Identifcation number.
I like your style how you write your blogs and tell the basics for beginners!
Regards,
Jürgen