This is a series based on database design, but for OLAP and OLTP. Today i wanted to note down the differences between the Slow Changing Dimensions and what they look like

SCD 1

If a record is changed in a SCD1 then the row is simply updated. No history is kept of the previous record or when it happened.

Example:

ID Name Job
1001 John Smith IT Support Level 1

Updates to:

ID Name Job
1001 John Smith IT Manager

SCD 2

If a record is changed in SCD2 then a new row is added.

Example:

ID Name Job
1001 John Smith IT Support Level 1

Changes to:

ID Name Job
1001 John Smith IT Support Level 1
1002 John Smith IT Manager

SCD 3

Retains an original value and date for the change. Since it only includes the current and previous  its rarely used in practice.

ID Name Original Current Effective Date
1001 John Smith IT Support Level 1 2012-01-29

Updates to:

ID Name Original Current Effective Date
1001 John Smith IT Support Level 1 IT Manager 2013-07-29

Parent-Child Dimensions

A single table where columns relate to each other (Manager_Num is Parent to Emp_Num)

Emp Name Emp_Num Manager_Num
Company Owner 1 1
Boss of Site 2 1
Department Boss 3 2
Staff Member 4 3
Advertisements