SID TABLE

[Former Member]

Hi All,

What is a SID table and what it contains?

What is the significance is Surrogate ID (SID)?

How SID is generated?

How it links with Dimension table and Fact table?

Please answer in detail.

Points will b rewarded.

Thank you.

Message was edited by:

Harshal Panchal

[Former Member]

Hi Harshal,

SID is a Surrogate ID table. The importance of SID table is it makes master data tables independent of Dimension table. This is an Extended Star Schema concect. Fact table is connected to Dimension tables, each DImension table is connected a SID table, which inturn connects the corresponding master data table. Suppose say if another fact table needs the same master data table, it can connect to the master data table with the help of its SID table, by this one master data table is shared by 2 fact tables. So no redundancy of master data table occurs. In classical star schema master data table is present with in dimension table, so if another fact table needs the same master data table, its dimension table should include the master data table, here redundancy occurs, bcause master data table is duplicated.

Link: The charecteristic of a fact table is connected to Dimensional Key of a Dimension table. The DImension table contains Dimensional Key (DIM ID) and Surrogate ID (SID). This SID of Dimension table is connected to the SID of SID table. SID Table contains SID and corresponding Master data table Primary key with which it connects the Master data table.

When ever a transaction is done, say for example a Customer ( C001) has bought a product ( P001) from a comapny and thus comapny got a Revenue of 100/ . When this transaction is done, Automatically system creates a SID (say 1) for C001 in SID Table. So now SID Table has 1 , C001 as its values. For SID 1 a Dimensional Key (say 1) is created in Dimensional table. As Dimensional table has Dimensional Key / DIM ID and SID, now it will be having 1, 1 as is field values. In fact DIM ID = 1 gets enterd.

Master Data table:

customer id customer name country

C001 Raju India

SID Table:

SID customer id

1 C001

Dimension table:

DIM ID SID

1 1

Fact Table:

DIM ID DIM ID ( for P001) Revenue

1 say 5 100

So here we can clearly that fact table contains only numbers which improves the reporting performance.

Below shows the link between DIM Table, SID Table and Master Data Table:

Fact Table DIM ID = 1 -

---

>Dimension table DIM ID =1.

Dimension table DIM ID =1----

---

>Dimension table SID = 1.

Dimension table SID = 1----

---

>SID Table SID = 1.

SID Table SID = 1----

---

>SID Table customer id = C001.

SID Table customer id = C001----

---

>Master Data table customer id = C001.

Hope this info will be useful,

if so Reward

Thanks,

Veda

[Former Member]

what is the dif between cube and dso

[Former Member]

Infocube :

1) Multidimensional structure(facts and dimensions).

2)There is no overwrite mode.

3)Contains summarized data.

4)Aggregates can be created on infocubes.

5)In infocubes we have characteristics and key figures.

6)Infocubes are used for multi-dimensional reporting.

DSO:

1)Its a flat structure(transparent tables,flat tables).

2)Its consists of overwrite mode.

3)It contains detailed or more granular data.

4)Aggregates cannot be created on DSO.

5)It consists of Key field and Data fields.

6)Used for operational reporting.