Databases Reference
In-Depth Information
• he Factory Worker entity Fred has the Instance Key 942 because
the dimension row for Fred in the Factory Worker table with the
Instance Key value 942 has the Row_First_Date and Row_Last_Date
values of April 14, 2007, and Dec. 31, 9999, which inclusively sur-
round the Fact row date of Aug. 27, 2008.
• he Raw Material entity Iron Ore has the Instance Key 385 because
the dimension row for Iron Ore in the Raw Material table with the
Instance Key value 385 has the Row_First_Date and Row_Last_Date
values of March 14, 2006, and May 11, 2010, which inclusively sur-
round the Fact row date of Aug. 27, 2008.
• he Location entity Atlanta Iron 35 has the Instance Key 234 because
the dimension row for Atlanta Iron 35 in the Location table with the
Instance Key value 234 has the Row_First_Date and Row_Last_Date
values of Feb. 6, 1998, and Dec. 31, 9999, which inclusively surround
the Fact row date of Aug. 27, 2008.
• he Formula entity RWU987 has the Instance Key 294 because the
dimension row for RWU987 in the Formula table with the Instance
Key value 294 has the Row_First_Date and Row_Last_Date values of
Jan. 20, 2005, and Nov. 24, 2008, which inclusively surround the Fact
row date of Aug. 27, 2008.
A Fact ETL application will perform all the lookup operations described
in this chapter to find the Instance Keys shown in Table 13.12. If the qual-
ity of the Dimension data has been assessed and certified, the Fact ETL
lookup processes can be that simple. The lookup processes repeat for every
row of data processed by a Fact ETL application.
Most ETL tools include an optimized lookup operation. Often the
dimension values are stored in memory so that the lookup operation expe-
riences I/O only on the first “read” operation against a disk drive. Every
“read” operation in the lookup that occurs thereafter is a “ read” from
memory and not from disk. A hand-coded ETL application can achieve
the same result by reading all the dimension values into an internal mem-
ory array. Then, perform all the lookup operations against the internal
memory array rather than against a dimension table stored on disk.
To see time vary, we must vary time. Table 13.13 presents another set of
Manufacture_Metals_Detail rows. The set of rows in Table 13.13 occurred
on Jan. 20, 2011. The Instance Keys, again in this example, come from
Table 13.11.
