Databases Reference
In-Depth Information
single set of transaction, or fact, tables that can join to both Type 1 dimen-
sion tables and Type 2 dimension tables.
The only caveat here is the possibility to cause confusion. When joining
the Transaction table to the Item table, which Item table do I join to the
Transaction table? The answer, which will be discussed in further detail
in Chapter 12, is to use three databases. The first database holds only the
transaction, or fact, tables. The second database holds the Type 1 dimen-
sion tables and views to the transaction, or fact, tables. In that Type 1
database, the Transaction table can join to only one Item table because
the Type 1 database has only one Item table. The third database holds the
Type 2 dimension tables and views to the transaction, or fact, tables. In
that Type 2 database, the Transaction table can join to only one Item table
because the Type 2 database has only one Item table. So, if you want to
view the data warehouse in the Type 1 time variant context, you query the
Type 1 database; if you want to view the data warehouse in the Type 2 time
variant context, you query the Type 2 database.
Figure 11.9 shows the combination of Type 1 a nd Type 2 d atabases,
using Compound Instance Keys to uniquely identify the Type 2 dimen-
sion rows. The dimension rows in Figure 11.8 used Simple Instance Keys
to uniquely identify every dimension row. The combination of Type 1 and
Type 2 dimension tables works equally well with Simple Instance Keys and
Compound Instance Keys. Both forms of combined time variance avoid
the multiplicative explosion of rows that is the root cause of the perfor-
mance degradation associated with a time variant data warehouse. Data
warehouse customers who wish to see enterprise transactions in their his-
torical context can query the Type 2 database. Data warehouse customers
who wish to see enterprise transactions in the present context of the enter-
prise can query the Type 1 database. The query language used to query
both databases is the standard relational SQL. By using this Time Variant
Solution Design, a data warehouse can simultaneously deliver Type 1 and
Type 2 time variant data, without the performance degradation associated
with time variance via date logic.
summAry tABles
Transaction tables contain data from individual transactions. For a ship-
ping company a Transaction table could hold all the shipping orders,
