Databases Reference
In-Depth Information
updated row more eἀ ciently. Because the Raw Material table uses Simple
Instance Keys, the Transform function performs the same algorithm to
find the maximum Instance Key value for the table and then increments
by one the Instance Key value (e.g., max(Instance Key) + 1) for every
Insert row. Every dimension update is composed of a record that discon-
tinues the previously effective row and another record that creates a new
Insert row.
The function of defining a new Insert record exists in the Transform
output for both New and Updated rows. For that reason, the Instance Key
incrementer (e.g., max(Instance Key) + 1) should take as its input data
the Insert records from the New and Updated rows. This will allow the
Transform function to find the maximum Instance Key and increment the
Instance Key in only one iteration rather than two.
An updated row of dimension data with Compound Instance Keys is
very similar to an updated row of dimension data with Simple Instance
Keys. The CDC function compared the set of Entity Keys extracted
from the operational system to the set of Entity Keys already in the data
warehouse and found an Entity Key that exists in both sets. The row of
dimension data extracted from the operational source system is, however,
different from the row of dimension data in the data warehouse in that
the two rows have a different attribute value. This means that within the
operational source system that attribute value has changed since the pre-
vious ETL Cycle.
The Transform function will write a record that will cause the Load func-
tion to update the Row_Last_Date of the existing row. That will have the
effect of discontinuing the existing row of dimension data. The Transform
function will also write a record that will cause the Load function to insert
a new row of data for the RWU987 entity. Table 13.9 shows the final result
for the RWU987 entity.
If the dimension table is designed to use Compound Instance Keys,
then the Transform function, in addition to all the other work it per-
forms, finds the maximum Instance Key value for that Entity Key in the
tABle 13.9
Formula Changed with Compound Instance Keys
Instance
Key
Name
Fail Rate
Skill Set
Row_First_Date
Row_Last_Date
RWU987
4%
Iron Cratsman
Jan. 20, 2005
Nov. 24, 2008
1
RWU987
2%
Iron Cratsman
Nov. 25, 2008
Dec. 31, 9999
2
