Database Reference
In-Depth Information
When we create an SCD2 mapping using a version number, the wizard creates two addi-
tional columns in the target table:
•
PM_PRIMARY_KEY
: The wizard generates the primary key for each row to be
inserted into target. Note that
EMPLOYEE_ID
will not be the primary key in the
table.
•
PM_VERSION_NUMBER
: The wizard generates a version number for each row
inserted into table; this allows us to differentiate between current and historical
records.
The Informatica PowerCenter SCD2 mapping uses a
0
lookup transformation to look up
the data in the target table and uses the
EXP_Detect_Changes
expression transforma-
tion to compare the target data with the source data. Based on the comparison, the expres-
sion transformation marks a record as
NewFlag
or
ChangedFlag
using a flag. The
mapping is divided into the following two flows:
• The
FIL_InsertNewRecord
filter transformation allows only the
NewFlag
record to pass further and filters the
ChangedFlag
record from the first flow. It
passes new records to
UPD_ForceInserts
, which inserts these records into
the target. The sequence generator, which is
SEQ_generateKeys
, generates
the primary key for each
NewFlag
record. The
EXP_KeyProcessing_InsertNew
expression transformation multiplies the
primary key value by
1000
and loads
0
as the version number for each new row
into the target, which is
EMPLOYEE_SCD2_VERSION_NUMBER
.
• The
FIL_InsertChangedRecord
filter transformation allows only the
ChangedFlag
record to pass further and filters the records marked as
NewFlag
from second flow. It passes the changed records to
UPD_ChangedUpdate
, which replaces existing rows in the target to reflect the
latest changes. The expression transformation, which is
EXP_KeyProcessing_InsertChanged
, increments both the primary key
and version number by
1
and loads them into the target instance, which is
EMPLOYEE_SCD2_VERSION_NUMBER1
.
Let's work through each transformation that is used in the SCD2 mapping:
