About Dimension Tables
About Change Tracking
Dimension tables that are
loaded with the SCD Type 2 Loader consist of a primary key column,
a business key column, change tracking columns, and any number of
detail data columns. The primary key column is often loaded with values
that are generated by the transformation. The business keys are supplied
in the source data. Both the business key and the primary key can
be defined to consist of more than one column, as determined by the
structure of the source data.
Change
tracking columns can consist of begin and end datetime columns, a
version number column, or a current-row indicator column. You can
combine tracking methods as needed to optimize your analyses. Using
a current-row indicator column improves the performance of the SCD
Type 2 Loader.
Begin
and end datetime values specify the period of time in which each row
was the current row for that member. The following diagram shows how
data is added to begin and end datetime columns. The begin datetime
for the new current row is one second greater than the end datetime
of the former current row. The end value for the current row is a
placeholder future date.
Tracking
changes by version number increments a counter when a new row is added.
The current row has the highest version number for that member. The
version number for new members is current_version_number + 1.
Tracking
changes using a current–row indicator column loads a 1 for
the current row and 0s for all of the other rows that apply to that
same member.
The preceding
diagram shows a primary key column, the values for which are generated
by the SCD Type 2 Loader. The generated primary key is necessary in
order to uniquely identify individual rows in the dimension table.
The generated primary key values are loaded into the star schema's
fact table as foreign keys, to connect factual or numerical events
to the detail data that describes those events.
About Change Detection and Loading for SCD
In jobs that run the SCD Type 2 Loader transformation, the dimension
table loading process repeats the following process for each source
row:
-
Compare the business key of the source row to the business keys of all of the current rows in the dimension table. If no match is found, then the source row represents a new member. The source row is written to the target as the new current member for that business key. The current member contains the latest information. The loading process moves to the next source row.
-
If the business key in the source matches a business key in the target, then specified detail data columns are compared between the matching rows. If no differences in data are detected, then the source row is a duplicate of the target row. The source row is not loaded into the target as the new current row for that business key. The loading process moves on to the next source row.
-
If business keys match and data differences are detected in the columns specified for Type 2 SCD, then the source row represents a new current row for that member. The source row is written to the target, and the previous current row for that member is closed out. To close out a row, the change tracking column or columns are updated as specified, depending on the selected method of change tracking. For Type 1 updates, if changes are detected in the Type 1 columns, the source data overwrites the target data in the current row, even if data differences are not detected in the Type 2 columns.
About Generated Keys
The SCD Type 2 Loader enables you to
generate key values when you load a dimension table. The generated
values are frequently used as primary keys, because the business key
from the source table identifies the member, not the unique row in
the dimension table.
In the Generated Keys tab of the SCD Type 2 Loader, you can
configure a simple surrogate key that increments the highest existing
value in a specified column for each new row. You can also use an
expression to generate key values in other increments. To specify
a unique starting point for the keys that are generated in each load,
you can specify a lookup column. The initial key value is the highest
value in the lookup column.
Note: When loading
a fact table instead of a dimension table, you can generate simple
surrogate keys using the Lookup transformation.
In addition
to surrogate keys, you can also generate retained keys. Retained keys
provide a primary key value that consists of two columns, the begin
datetime change tracking column and a numeric column that receives
generated values. The combination of the two columns uniquely identifies
each row in the table.
The generated
value is retained because a single generated value is applied to all
of the rows that apply to a given member. When a new row is added
to an existing member, it receives the same generated value as the
other rows that apply to that member.
To enhance
performance, you should create an index for your generated key column.
If you identify your generated key column as the primary key of the
table, then the index is created automatically. Surrogate keys should
receive a unique or simple index that consists of one column. Retained
keys should receive a complex index that includes the generated key
column and the beginning datetime column.
About Cross-Reference Tables
During the process
of loading an SCD dimension table, the comparison of incoming source
rows to the current rows in the target is facilitated by a cross-reference
table. The cross-reference table consists of all of the current rows
in the dimension table, one row for each member. The columns consist
of the generated key, the business key, and a digest column named
DIGEST_VALUE.
The digest
column is used to detect changes in data between the source row and
the target row that has a matching business key. DIGEST_VALUE is a
character column with a length of 32. The values in this column are
encrypted concatenations of the data columns that were selected for
change detection. The encryption uses the MD5 algorithm, which is
described in detail at
http://www.faqs.org/rfcs/rfc1321.html.
If a cross-reference
table exists and has been identified, it is used and updated. If a
cross-reference table has not been identified, then a new temporary
table is created each time you run the job.
To increase
performance in large jobs, enable change tracking by current row indicator.
This method of change tracking can be combined with the other change
tracking methods (begin and end datetime and version number). The
current row indicator speeds up the process of creating or updating
the digest file. The performance improvement is provided by a WHERE
clause that efficiently separates current rows from closed-out rows.
About Type 1 Updates
Type 1 updates are defined as overwrites of existing
data in specified columns. When you run a Type 1 update with the SCD
Type 2 Loader transformation, digest values containing the Type 1
columns are created for the source and target. The digest values are
then compared to determine the target rows that need to be updated.
When the rows are updated, the number of writes is optimized.