The advantage of Type I is clear: it??™s easy. No extra work is required. Data is
changed and the primary key remains the same, so all history now reflects the
current values as if they have never been different. The great disadvantage of Type
I is also clear: history is lost. With Type I, it??™s impossible to credit a salesperson??™s
sales to a previous manager, to determine when a product change was introduced,
and so forth.
Another approach is the Type II slowly changing dimension. This type of
dimension structure does maintain history, usually by versioning the record and
then setting a start date and end date for each version. For example, a salesperson
named Raju starts with a company on January 1, 2007 and works for Manager Bob.
A year later, that salesperson is reassigned to Manager Maria. On the first record for
salesperson Raju, the Start Date would be January 1, 2007 and the End Date would
be January 1, 2008. A new record would be added for Raju that still maintained his
employee ID (or some other key) but was now version 2, and had a Start Date of
January 1, 2008 and no end date.
Sales would be tracked by the employee ID so that all of Raju??™s sales always
belong to him. Raju??™s sales records also have the date on which they occurred,
so that sales in 2007 roll up to Bob while sales in 2008 roll up to Maria. While
simple on the surface, this can certainly complicate working with the data, because
all queries must now look at the start and end dates for all of Raju??™s entries in the
Employee dimension table.
Pages:
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76