My first Publication Agile-Data-Warehouse-Design-eBook | Page 207

Dimensional Design Patterns for People and Organizations, Products and Services
The self-join, in Figure 6-17, picks the historical EMPLOYEE KEYs and the cur-
rent descriptive values. When this identical-looking EMPLOYEE dimension is
used instead of the original HV version it will roll up all of Bond’s facts from his 3
different era’s (EMPLOYEE KEY 1010, 2099 and 2120) to a single location of
London or a single status of “Widowed”. Because the CV and HV swappable
dimensions are identically described they can be “hot swapped” for each other to
change the temporal focus of a query without rewriting any SQL.
187
A CV hot swappable
dimension can be
built as a self-join
view of an HV
dimension
Figure 6-17
Defining a CV
swappable
dimension
CV swappable dimensions can be built as views but for better query performance
store them as real tables or materialized views. The small amount of “wasted”
space avoids having to do the self-join inside every query.
The HV and CV swappable dimensions are not mutually exclusive, both can be
joined to a fact table in the same query, to group or filter on current and historical
values simultaneously. If this is a common requirement, you can build a more
query efficient hybrid HV/CV dimension by selecting both the CV and HV ver-
sions of attributes and co-locating them in the same swappable dimension to
provide side by side attributes for easy comparisons and more ambitious queries;
for example, a hybrid EMPLOYEE dimension would allow a query to group by
HISTORICAL CITY while filtering on CURRENT CITY.
HV and CV
swappable
dimensions can be
used in the same
query to provide
current and
historical values