Databases Reference
In-Depth Information
the future. As more data points are added, the End Date can be changed to a
termination date, and a new record for the current value can be entered.
It is good practice to name the change tracking columns consistently across
dimensions in the data model so that they are easily recognizable. In the previous
display, the column names VALID_FROM_DTTM and VALID_TO_DTTM are used.
After a time/date range has been specified, you can find the desired value when
querying the dimension table. Instead of just looking for a specific value in a column of
data, you have the ability to perform a query for current and/or historical data. For
example, a query could be performed to return the latest address for a household.
Because the table has stored date information (in the Beginning Date and End Date),
you can determine the value for this or any other data.
Selecting Columns for Change Detection
To optimize the loading of the dimension table, change detection should be enabled
only for those columns that are relevant to the analysis that you will perform. For
example, the following display shows the
Detect Changes
tab in the properties window
for the SCD Type 2 Loader.
Display 12.2
Detect Changes Tab
In the previous display, change detection will be performed only for the dimension
table columns in the
Selected columns
pane on the right. The columns that remain in
the
Available columns
pane on the left are apparently not relevant to the analysis
that will be performed on the dimension table.
The actual change detection is done by a checksum-like algorithm that computes a
distinct value based on all the selected input columns. These checksum-like values are
compared to detect change. This methodology is fast because it does not require large
text-based or column by column compares to detect changes against each row, which
quickly gets prohibitively expensive as data volumes rise.
It is possible to persist the checksum table to a permanent data set in the
Options
tab of the SCD Type 2 Loader. This saves the step of having to re-create the checksum
Search WWH ::
Custom Search