Metadata Management and Data Governance - Data Governance: Creating Value from Information Assets - page 62

Before

Patient ID First Name Last Name

12345 Rachael Schrader

Ater

Patient ID First Name Last Name

12345 Rachael Smith

FIGURE 3.10
Slowly changing dimension Type 1 example.

Patient ID First Name Last Name Eff_Start_Date Eff_End_Date

12345 Rachael Schrader 1/18/1960 8/27/2011

78345 Rachael Smith 8/28/2011

FIGURE 3.11
Slowly changing dimension Type 2 example.

Prior First
Name

Prior Last
Name

Effective
Date Patient ID First Name Last Name

12345 Rachael Smith Rachael Schrader 8/28/2011

FIGURE 3.12
Slowly changing dimension Type 3 example.

changing dimension change would add columns to the rows to store the
old name. The prior last name field would be changed to Schrader, and the
last name field would be changed to Smith. Also, there will be an effective
date (Figure 3.12).
Type 3 slowly changing dimension is utilized less than Type 1 or Type 2
because of the overhead of additional columns. If we have a table with 60
columns and 36 columns would require versioning, the new column total
would be 96. Another disadvantage of Type 3 slowly changing dimensions
is that history is usually limited to one change. If we need more than one
change stored, we would need to add additional rows.
The main advantage is that history is stored on a single row so that we
can retrieve both the prior value and the current value at the same time.