Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 137

In the second solution, called type 2, the tuples in the dimension table are
versioned, and a new tuple is inserted each time a change takes place. Thus,
the tuples in the fact table will match the correct tuple in the dimension table.
In our example, we would enter a new row for product prod1 in the Product
table, with its new category cat2 .Thus,allsalespriorto t will contribute to
the aggregation to cat1 , while the ones that occurred after t will contribute
to cat2 . This solution requires the table Product to be extended with two
attributes indicating the validity interval of the tuple, let us call them From
and To . In our example, the table Product would look like the following:

Product
Key

Product
Name

Discontinued Category
Name

Description From To

p1 prod1 No cat1 desc1 2010-01-01 2011-12-31

p11 prod1 No cat2 desc2 2012-01-01 9999-12-31

p2 prod2 No cat1 desc1 2012-01-01 9999-12-31

p3 prod3 No cat2 desc2 2012-01-01 9999-12-31

p4 prod4 No cat2 desc2 2012-01-01 9999-12-31

In the table above, the first two tuples correspond to the two versions of
product prod1 , with ProductKey values p1 and p11 . The value 9999-12-31 in
the To attribute indicates that the tuple is still valid; this is a usual notation in
temporal databases. Note that since the same product participates in the fact
table with as many surrogates as there are attribute changes, to ensure correct
manipulation, it is necessary to keep track of all the tuples that pertain to the
same product. For example, counting the number of different products sold by
the company over specific time periods cannot be done by just counting the
appearance of a particular product in the fact table. Notice that since a new
record is inserted every time an attribute value changes, the dimension can
grow considerably, decreasing the performance during join operations with
the fact table. More sophisticated techniques have been proposed to address
this, and below we will comment on them.
In the type 2 approach, sometimes an additional attribute is added to
explicitly indicate which is the current row. The table below shows an
attribute denoted RowStatus , telling which is the current value for product
prod1 .

Product
Key

Product
Name

Discontinued Category
Name

Row
Status

Description From To

p1 prod1 No cat1 desc1 2010-01-01 2011-12-31 Expired

p11 prod1 No cat2 desc2 2012-01-01 9999-12-31 Current

··· ··· ··· ··· ··· ··· ··· ···

The type 2 approach for a snowflake (normalized) representation is handled
in similar way as above. Let us consider a snowflake representation for the