The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Integration Service saves existing data in different columns of the same row and replaces the existing data with the updates. The Integration Service optionally enters the system date as a timestamp for each row it inserts or updates. In the Type 3 Dimension target, each dimension contains current dimension data.
Use the Type 3 Dimension mapping to update a slowly changing dimension table when you want to keep only current and previous versions of column data in the table. Both versions of the specified column or columns are saved in the same row.
When you use this option, the Designer creates additional fields in the target:
PM_PREV_
ColumnName
.
The Designer generates a
previous
column corresponding to each column for which you want historical data. The Integration Service keeps the previous version of dimension data in these columns.
PM_PRIMARYKEY.
The Integration Service generates a primary key for each row written to the target.
PM_EFFECT_DATE.
An optional field. The Integration Service uses the system date to indicate when it creates or updates a dimension.