My first Publication Agile-Data-Warehouse-Design-eBook | Page 168

Modeling Star Schemas
147
Figure 5-9
Slowly changing
EMPLOYEE
dimension
Overwriting History: Type 1 Slowly Changing Dimensions
CV and FV dimensional attributes are implemented as Type 1 slowly changing
dimensions. When they are updated in the source system they are similarly updated
in a dimension. For FV attributes such as DATE OF BIRTH this is entirely
appropriate because an employee or customer can have only one date of birth, the
update must be a correction and nothing of value is lost. For CV attributes, the
stakeholders have decided that historically correct values are unimportant and
current values are the only ones that matter. For both CV and FV attributes history
is lost, making reports that use them “unrepeatable”. They will give different
answers if re-run because the reports will be grouped or filtered using “as is now”
not “as was then” descriptions.
CV and FV
attributes are
implemented as
Type 1 SCDs.
Changes are
handled as
updates and
history is
overwritten
Tracking History: Type 2 Slowly Changing Dimensions
HV dimensional attributes are implemented as Type 2 slowly changing dimensions.
They are not overwritten when they change in the source system. Instead new rows
are inserted with the new values, just like the change stories in Figure 5-9 that show
Bond’s various statuses and locations. HV attributes are
Creating new rows to track change presents an issue for uniquely keying a dimen-
sion. For example, Bond’s business key “007” no longer uniquely identifies a single
Employee row and must be combined with the effective date of the change to
provide a valid primary key. Unfortunately a composite key such as EMPLOYEE
ID, EFFECTIVE DATE would ruinously complicate the joining of historical facts
to the correct historical descriptions. Prior to tracking history a simple equi-join
would locate Bond’s expenses: Tracking history
Employee.Employee_ID = Expenses_Fact.Employee_ID
implemented as
Type 2 SCDs
within a dimension
means you cannot
rely on the business
key alone as a
primary key