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

The key ProductFeaturesKey must be added to the fact table Sales as a
foreign key. In this way, we prevent the dimension to grow with every change
in the sales ranking score or price range of a product, and the changes are
actually captured by the fact table. For example, assume that product prod1
initially has sales ranking 2 and price range 1-100 . A sale of this product will
be entered in the fact table with a value of ProductFeaturesKey equal to pf2 .
If later the sales ranking of the product goes up to 1 , the subsequent sales
will be entered with ProductFeaturesKey equal to pf1 .
The type 5 approach is an extension of type 4, where the primary
dimension table is extended with a foreign key to the minidimension table.
In the current example, the Product dimension will look as follows:

Product
Key

Product
Name

Discontinued CurrentProduct
FeaturesKey

p1 prod1 No pf1

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

As can be seen, this allows us to analyze the current feature values of a
dimension without accessing the fact table. The foreign key is a type 1
attribute, and thus, when any feature of the product changes, the current
ProductFeaturesKey valueisstoredinthe Product table. On the other hand,
the fact table includes the foreign keys ProductKey and ProductFeaturesKey ,
where the latter points to feature values that were current atthetimeof
the sales . However, the attribute CurrentProductFeaturesKey in the Product
dimension would allow us to roll up historical facts based on the current
product profile.
The type 6 approach extends a type 2 dimension with an additional column
containing the current value of an attribute. Consider again the type 2
solution above, where the Product dimension is extended with attributes
From and To indicating the validity interval of the tuple. Further, we add an
attribute CurrentCategoryKey that contains the current value of the Category
attribute as follows:

Product
Key

Product
Name

Discontinued Category
Key

Current
CategoryKey

From To

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

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

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

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

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

With this solution, the CategoryKey attribute can be used to group facts
based on the product category that was in effect when the facts occurred,
while the CurrentCategoryKey attribute can be used to group facts based on
the current product category.