About Dimension Tables
About Change Tracking
Dimension tables that are loaded with the SCD Type 2
Loader consist of a surrogate key column, a business key column, change
tracking columns, and any number of detail data columns. The surrogate
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 surrogate key can be defined to consist of more than one
column, as determined by the structure of the source data. A surrogate
key is typically a system generated value that contains no semantic
meaning. It is almost always a numeric value that you can use to improve
join performance between fact and dimension tables.
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 business key. The version
number for new business keys 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 surrogate key column, the values for which are generated by
the SCD Type 2 Loader. The generated surrogate key is necessary in
order to uniquely identify individual rows in the dimension table.
The generated surrogate 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. If changes are detected in the Type 1 columns in Type 1 upgrades, the source data overwrites the target data in the current row. The data is overwritten even when data differences are not detected in the Type 2 columns.
About Generated Keys
The SCD
Type 2 Loader enables you to generate surrogate key values when you
load a dimension table. The generated surrogate key values replace
the business key as the primary key because the business key from
the source table identifies the member, not the unique row in the
dimension table.
You can configure a
simple surrogate key in the Generated Keys tab of the SCD Type 2 Loader. This surrogate key 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.