Databases Reference
In-Depth Information
Considering these ETL Cycles, and their consequences, highlights the reg-
ular and reasonable nature of a twenty-four-hour ETL Cycle.
Each row of a dimension table should contain its time variant metadata.
Specifically, that is the time at which the row of data came into effect and
the time at which the row of data discontinued its effect. For every entity,
no time gaps should be allowed. This also is a consideration in the deci-
sion to choose an ETL Cycle, and therefore the periodicity, of a dimension
table. If the periodicity is at the level of the day, then the first time a row
came into effect is a date; the time when a dimension row was discontin-
ued is also a date. Chapter 12 referred to these dates as Row_First_Date
and Row_Last_Date. For continuity, these names will be used in this
chapter to identify the first date a row came into effect and the date when
a row discontinued its effect.
Also the SQL BETWEEN statement is inclusive. That means the state-
ment “between Row_First_Date and Row_Last_Date” includes both
Row_First_Date and Row_Last_Date within the set of dates that satisfy
the BETWEEN condition. For that reason, the dates in time variant meta-
data work best and easiest when the Row_First_Date and Row_Last_Date
are inclusive, meaning the Row_First_Date is the first date on which a
row is in effect and the Row_Last_Date is the last date on which a row is
in effect.
Having defined the time variant metadata concept, the time variant
metadata concept can be applied to the example in Table 13.1. In that
example, the Quality rating of the Iron Ore changed from 95% to 96%.
This is indeed a change. A row in the Raw Materials table might look like
the row in Table 13.2.
Table 13.2 shows that the first row of data for Iron Ore came into effect
on March 14, 2006. The Row_Last_Date value of Dec. 31, 9999, is a high-
values date, which means that the row's last, or discontinuation, date is not
yet known because it has not yet occurred.
Table 13.3 shows that the Quality rating for Iron Ore changed on May
12, 2010. That means that the last date on which the first row that shows
the 95% Quality rating was in effect was May 11, 2010. The second Iron
tABle 13.2
Raw Material
Name
Supplier
Quality
Row_First_Date
Row_Last_Date
Iron Ore
Hess Ironworks
95%
March 14, 2006
Dec. 31, 9999
