Databases Reference
In-Depth Information
The tables in Figure 11.6 avoid the multiplicative explosion of rows by
retaining only one row for each entity. In Chapter 10, the joins using entity
keys caused the multiplicative explosion of rows because the dimension
tables had multiple rows for the same entity key. The dimension tables in
Figure 11.6, however, have only one row for each entity key. So, you could
say the tables in Figure 11.6 also experience the multiplicative explosion of
rows, and the multiplicative factor is the number of rows per entity, which
would be a factor of 1.
Also notice that the dimension tables in Figure 11.6 retain their Instance
Keys. The inclusion of the instance keys facilitates a data quality assess-
ment of the parity between the Type 2 and Type 1 tables. The ability to
match the entity keys, instance keys, and attributes for each entity in each
dimension table in the Type 1 and Type 2 sets of data increases the level
of confidence in the parity between the two sets of data, as compared to
matching only the entity keys and attributes.
The tables in Figure 11.7 are very similar to the tables in Figure 11.6.
The one difference is the instance keys. The instance keys in Figure 11.7
are based on rows from a dimension table that was built using Compound
Instance Keys. The resulting data is the same. The only difference is the
key structure that returned that data. The data from the Type 2 t ables
in Figure 11.4 and Figure 11.5 associated the transaction with a yellow
item delivered by Cart-Rite. However, the data from the Type 1 tables in
Figure 11.6 and Figure 11.7 associated the transaction with a g ray item
delivered by Williamson. Which answer is correct? They are both cor-
rect…for their respective time variant context.
tyPe 1 AND tyPe 2 comBINeD
A data warehouse can incorporate both forms of time variance. The
existence of the Type 1 time variant dimension tables does not preclude
the existence of Type 2 time variant dimension tables. A transaction or
dimension table is able to join to them both, as can be seen in Figures 11.4,
11.5, 11.6, and 11.7. So, there really is no reason to choose one form of time
variance at the exclusion of the other.
Figure 11.8 shows a single transaction table (in the middle of the figure)
that joins to Type 2 dimension tables (in the top of the figure) and Type 1
dimension tables (in the bottom of the figure). This approach leverages a
