Wednesday, October 6, 2010

Surrogate ID -- Master Data Tables

Standard Info cube consists of fact table surrounded by many dimension table. SID table links these Dimension tables to master data tables.

SID is surrogate ID generated by the system. The SID tables are created when we create a master data IO.  In SAP BI Extended star schema, the distinction is made between two self contained areas: Info cube & Master data tables and connecting SID tables.

The master data doesn't reside in the Extended Star schema but resides in separate tables which are shared across all the star schemas in SAP BI.

An Unique Numeric ID , the SID is generated which connects the dimension tables of the infocube to that of the master data tables.
The dimension tables contain the DIM IDs and SIDs of a particular Characteristic Info Object. Using this SID Table the Master data ( attributes and texts of Info Object) is accessed.

 List of Technical Tables  

F  - Fact Table - Uncompressed Fact Table - Contains Direct data for cube Request wise ( Based on B-Tree Index ) 
E  - Fact Table - Compress cube - Contains compressed data without Request IDs( Request ID would be 'zero') ( based on  Bitmap Index ) 
   
M - View of Master Data Table  - /BI0/MMATERIAL
P - Time Independent Master Data Table - /BI0/PMATERIAL
Q  - Time Dependent Master Data Table - 
/BI0/QMATERIAL

H - Hierarchy table  - /BI0/HMATERIAL
J  - Hierarchy interval table  - /BI0/JMATERIAL
K - Hierarchy SID table - /BI0/KMATERIAL
I  - SID Hierarchy structure      - /BI0/IMATERIAL
S  - SID table  - /BI0/SMATERIAL
X - Time Independent SID table for Attr - /BI0/XMATERIAL
Y  - Time Dependent SID table fir Attr  - 
/BI0/YMATERIAL

T  - Text Table - /BI0/TMATERIAL 
   

SID -- Master data Tables


Surrogate Keys are automatically generated uniform keys, which are uniquely identifying specific real-world key values. 


SID are only the connectivity link between DIM IDs and Master Data Tables .


Let us take an Example of a Material Master Data Tables and understand the various connectivities with SID table.











4 comments:

garun42 said...

Really Good!

Unknown said...

Its very nice and very useful

Unknown said...

very simple and clear

Navath said...

Short and good1... Thanks buddy