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

Patient
Dimension

Date
Dimension

Sales
Fact Table
Patient_ID
Location_ID
Date_ID
Product_ID
Quality

Product
Dimension

Location
Dimension

FIGURE 3.9
Star model example.

versioning policy as to whether we overwrite the data or keep all of the
history. Each table could have a different versioning policy; however, the
policies should have an enterprise view.
There are three basic slowly changing dimensions for handling data
versioning in a star dimensional model:

• Type 1: Replace the value
• Type 2: Add a record with an effective start date and effective end date
• Type 3: Store the old value in addition to the new value on same row

Type 1
Type 1 simply writes over the previous value. The historical facts are lost.
For example, in the customer dimension, Rachael Schrader marries William
Smith. In this example, a Type 1 methodology overwrites Schrader to Smith.
The fact that Rachael was once Rachael Schrader is lost. In this example,
the data in the database would change as diagramed in Figure 3.10 .

Type 2
In a Type 2 slowly changing dimension, we add an additional row to
store the new data values with an effective start date and an effective end
date, thereby saving the historical fact that before she was Rachael Smith,
Rachael was Rachael Schrader ( Figure 3.11 ) .

Type 3
In a Type 3 slowly changing dimension, we add additional attributes to
the row to store the prior information. In our example, a Type 3 slowly