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

Product dimension where the categories of the product are represented in a
table Category .

Product
Key

Product
Name

Discontinued Category
Key

Category
Key

Category
Name

Description

p1 prod1 No c1 c1 cat1 desc1

p2 prod2 No c1 c2 cat2 desc2

p3 prod3 No c2 c3 cat3 desc3

p4 prod4 No c2 c4 cat4 desc4

Now assume that, as before, product prod1 changes its category to cat2 .
In the case of a solution of type 2, we add two temporal attributes to the
Product table. Then, applying the change above, we obtain

Product
Key

Product
Name

Discontinued Category
Key

From To

p1 prod1 No c1 2010-01-01 2011-12-31

p11 prod1 No c2 2012-01-01 9999-12-31

p2 prod2 No c1 2010-01-01 9999-12-31

p3 prod3 No c2 2010-01-01 9999-12-31

p4 prod4 No c2 2011-01-01 9999-12-31

and the Category table remains unchanged. However, if the change occurs
at an upper level in the hierarchy, for example, a description is changed,
this change needs to be propagated downward in the hierarchy. For example,
suppose that the description of category cat1 changes, as reflected in the
following table:

Category
Key

Category
Name

Description From To

c1 cat1 desc1 2010-01-01 2011-12-31

c11 cat1 desc11 2012-01-01 9999-12-31

c2 cat2 desc2 2012-01-01 9999-12-31

c3 cat3 desc3 2010-01-01 9999-12-31

c4 cat4 desc4 2010-01-01 9999-12-31

This change must be propagated to the Product table so that all sales prior
to the change refer to the old version of category cat1 (with key c1 ), while
the new sales must point to the new version (with key c11 ), as shown below:

Product
Key

Product
Name

Discontinued Category
Key

From To

p1 prod1 No c1 2010-01-01 2011-12-31

p11 prod1 No c11 2012-01-01 9999-12-31

p2 prod2 No c1 2010-01-01 9999-12-31

p3 prod3 No c2 2010-01-01 9999-12-31

p4 prod4 No c2 2011-01-01 9999-12-31