15 October 2009

Slowly Changing Dimension (SCD)

Yes, it does.. it changes. Sometimes the data inside the dimension tables needs to be change from time to time according to the business. Lets make that clear, imagine an employee that is living in the United States:

EmpID | Country
141 | "United States"

And then he moved to Canada. In this situation you can not simply insert a new record like this:

EmpID | Country
141 | "United States"
141 | "Canada"

Because EmpID is a primary key so it can not be duplicated.

So you have 3 options:

1.Replace the record, but its not recommended because you'll lose the old record and the functionality of Data Warehouse is to keep History.

EmpID | Country

141 | "Canada"

2. Make a Surrogate Key and a Date field, in order to keep history.

S_Key | Date |EmpID | Country
8013 | 1/1/2008 | 141 | "United States"
8014 | 1/6/2010 | 141 | "Canada"

3. Make an Old Country Field.

EmpID | Country | OldCountry
141 | "Canada" | "United States"

So choose the most appropriate option that suits your situation.

No comments:

Post a Comment

Feel free to write any comments or ideas!

Slowly Changing Dimension (SCD)

Yes, it does.. it changes. Sometimes the data inside the dimension tables needs to be change from time to time according to the business. Lets make that clear, imagine an employee that is living in the United States:

EmpID | Country
141 | "United States"

And then he moved to Canada. In this situation you can not simply insert a new record like this:

EmpID | Country
141 | "United States"
141 | "Canada"

Because EmpID is a primary key so it can not be duplicated.

So you have 3 options:

1.Replace the record, but its not recommended because you'll lose the old record and the functionality of Data Warehouse is to keep History.

EmpID | Country

141 | "Canada"

2. Make a Surrogate Key and a Date field, in order to keep history.

S_Key | Date |EmpID | Country
8013 | 1/1/2008 | 141 | "United States"
8014 | 1/6/2010 | 141 | "Canada"

3. Make an Old Country Field.

EmpID | Country | OldCountry
141 | "Canada" | "United States"

So choose the most appropriate option that suits your situation.

0 Comments:

Post a Comment

Feel free to write any comments or ideas!