Databases Reference
In-Depth Information
5:00 a.m. through Monday 5:00 a.m., which is a day. In that example, the
Instance Key represents a day.
If an ETL application extracts data once per week on Tuesday morning
at 8:00 a.m., then the ETL cycle would be from Tuesday 8:00 a.m. through
the following Tuesday 8:00 a.m. The smallest interval of time for which
a dimension update could exist in that data warehouse would be from
Tuesday 8:00 a.m. through the following Tuesday 8:00 a.m. The Instance
Key for that dimension table would indicate the time period Tuesday 8:00
a.m. through the following Tuesday 8:00 a.m.
If an ETL application extracts data once per month on the fifth of the
month, then the ETL cycle would be from the fifth of the month through
the fifth of the following month. The smallest interval of time for which a
dimension update could exist in that data warehouse would be from the
fifth of the month through the fifth of the following month. The Instance
Key for that dimension table would indicate the time period the fifth of the
month through the fifth of the following month.
Therefore, the ETL cycle determines the smallest time interval that can
be represented by an Instance Key. So, if you want the Instance Keys to
represent dates, then ETL cycle must run once per day. If you want the
Instance Keys to represent weeks, then the ETL cycle must run once per
week. Time-based Summary tables can summarize at a t ime hierarchy
level equal to the time interval represented by the Instance Keys. However,
time-based Summary tables cannot summarize at a level more granular
than the time interval represented by the Instance Keys.
Instance keys
The time interval between ETL cycles is the time interval represented by
an Instance Key. In that way, the ETL cycle defines the periodicity of the
Instance Keys. If the ETL cycle occurs every twenty-four hours, and an
update with an incremented Instance Key occurs every twenty-four hours,
then the interval represented by each Instance Key is twenty-four hours.
That means that all rows in a Transaction table representing that twenty-
four hour period will share the same Instance Key.
A time variant data warehouse works best when the entire data ware-
house shares the same ETL Cycle, and therefore, the same periodicity. A
shared common periodicity avoids diá¼€ cult time variant conversions and
the same relational integrity issues that arise in the presence of differing
time frames. By defining the periodicity of the ETL cycle and Instance
