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

Finally, the type 7 approach delivers similar functionality as the type 6
solution in the case that there are many attributes in the dimension table for
which we need to support both current and historical perspectives. In a type
6 solution that would require one additional column in the dimension table
for each of such attributes, these columns will contain the current value of the
attributes. Instead, a type 7 solution would add to the fact table an additional
foreign key of the dimension table containing not the surrogate key, but the
natural key ( ProductName in our example), provided it is a durable one. In
our example, the Product dimension will be exactly the same as in the type
2 solution, but the fact table would look as follows:

TimeKey EmployeeKey CustomerKey ProductKey Product
Name

SalesAmount

t1 e1 c1 p1 prod1 100

t2 e2 c2 p11 prod1 100

t3 e1 c3 p3 prod3 100

t4 e2 c4 p4 prod4 100

The ProductKey column can be used for historical analysis based on the
product values effective when the fact occurred. In order to support current
analysis, we need an additional view, called CurrentProduct , which keeps only
current values of the Product dimension as follows:

Product
Name

Discontinued Category
Key

prod1 No c2

prod2 No c1

prod3 No c2

prod4 No c2

A variant of this approach uses the surrogate key as the key of the current
dimension, thus eliminating the need of handling two different foreign keys
in the fact table.
Leading data warehouse platforms provide some support for slowly
changing dimensions, typically type 1 to type 3. However, as we have
seen, the proposed solutions are not satisfactory. In particular, they require
considerable programming effort for their correct manipulation. As we will
discuss in Chap. 15 , temporal data warehouses have been proposed as a more
general solution to this problem. They aim at providing a temporal update
semantics to the data warehouse.

5.8 SQL/OLAP Operations

In this section, we show how the data cube, a multidimensional structure,
can be represented in the relational model. We also show how to implement
the OLAP operations in SQL using the extension called SQL/OLAP.