Databases Reference
In-Depth Information
All the same columns
The Type 1 d imension tables have all the same columns as the Type 2
dimension tables, including the Instance Keys. Retaining all the col-
umns allows the queries and BI reports using the Type 2 “ historic con-
text” dimension to be as similar as possible to the queries and BI reports
using the Type 1 “historic context” dimensions. The same columns also
make management of the two sets of dimension tables easier and cleaner.
Also, retaining the Instance Keys, Row_First_Date, and Row_Last_Date
columns in the Type 1 “present state” dimension tables strengthens the
effect of the data quality assessment that will verify that the Type 1 dimen-
sion tables do indeed have only the most recent dimension row for each
entity. So, while it may seem strange at first to retain all the time variant
columns in the Type 1 dimension tables, that design approach adds more
value than the disk space it consumes.
only the row in effect right Now
The Type 1 Time Variant dimension table will avoid any possible confu-
sion about historic dimension rows by presenting only the row in effect
right now for a given entity. Figure 11.6 and Figure 11.7 display how this
would look in the example of a transaction involving Item Key 4. In that
example, Item Key is the Entity Key, and Item Key 4 is the entity. Since the
example includes only one item (Item 4), the Type 1 Item table includes
only one row. If the example were expanded to include four hundred
items, then the Type 1 Item table would include four hundred rows.
The other obvious observation is that the Type 1 dimension tables have
fewer rows than their Type 2 dimension table counterparts. Obviously, the
Type 1 dimension tables have fewer rows because they do not include any
history rows.
entity Primary keys
The primary key in Type 1 Time Variant dimension tables is a structural
Data Definition Language (DDL) change compared to the primary keys of
the Type 2 Time Variant dimension tables. In Type 1 Time Variant dimen-
sion tables the Entity Key is the primary key. This is different because in
a Type 1 Time Variant dimension you are not joining to an entity as of a
moment in the past. Instead, you are joining to an entity…period. In a
