provided by a WHERE clause that efficiently separates current rows from closed-out
Cross-reference tables are identified on the Options tabs of the following
transformations: SCD Type 2 Loader and Key Effective Date, in the field Cross-
Reference Table Name.
Two Methods for Generating the Change Digest Column
The SCD Type 2 Loader supports two methods for generating the change digest column
(DIGEST_VALUE column) in a cross-reference table. To specify one of these methods,
open the properties window for that transformation and select Options ð SCD Options.
Select v.1.1 or v2.1 in the Change digest version field.
The v1.1 method is the default. The v2.1 method uses a different method to concatenate
the data columns that were selected for change detection. Try v2.1 if the SCD Type 2
transformation does not detect changes in certain scenarios.
For example, suppose that two consecutive Type 2 columns with data type char are
loaded on day X with the following values: col_1="AB" , col_2="C"
The following delta record might contain these values: col_1="A" , col_2="BC"
The v1.1 method uses string handling functions that would concatenate these values into
an intermediate string value of "ABC" for both the original record and the delta record.
As a result, identical DIGEST_VALUEs would be incorrectly generated for both
records, and the change will not be detected. If you encounter this problem, try the v2.1
method, which uses different string handling functions.
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.
You can combine Type 2 and Type 1 updates in the same job. Use Type 2 updates to
maintain a history of changes for important columns. Use Type 1 updates to maintain
accurate and complete information in your dimension table, without generating new
target rows for each change.
About Fact Tables
Fact tables are combined with dimension tables to make up star schemas. Fact tables
describe events using numeric data. Dimension tables provide detail data that describe
the events. Examples of factual events include the sale of an item or a transaction in a
bank account. Each such event is represented by a single row in a fact table.
The columns in a fact table consist of one or more numeric columns that relate to an
event and a series of foreign key columns that connect the event to the detail data in the
About Fact Tables 527