Database Reference
In-Depth Information
When we create the mapping using this option, the wizard creates three additional
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_PREV_columnName
: For every column for which we maintain the history,
the wizard generates a previous column. In our case, we wish to maintain the his-
tory for the
LOCATION
field, so the wizard creates another column, which is
PM_PREV_LOCATION
.
•
PM_EFFECT_DATE
: This is an optional field; the wizard loads
SYSTEMDATE
in
this column to indicate insertions or updates to the record in the table.
The Informatica Power Center SCD2 mapping uses the
LKP_GetData
lookup trans-
formation to look up the data in the
Target
table and uses the
EXP_DetectChanges
expression transformation to compare the target data with the source data. Based on the
comparison, the expression transformation marks a record as
NewFlag
or
ChangedFlag
. The mapping is divided into two flows:
• The
FIL_InsertNewRecord
filter transformation allows only
NewFlag
re-
cord to pass further and filter the
ChangedFlag
record from the first flow. It
passes new records to
UPD_ForceInserts
, which inserts these records into
the target. The
SEQ_GenerateKeys
sequence generator generates the primary
key for each
NewFlag
record. If you select to create the
PM_EFFECT_DATE
column option in the wizard, the
EXP_EffectiveDate_InsertNew
expres-
sion transformation loads
SYSTEMDATE
into the
PM_EFFECT_DATE
column to
indicate the loading of new records.
• The
FIL_UpdateChangedRecord
filter transformation allows only the
ChangedFlag
record to pass further. The current data is passed from the
SQ_EMP_FILE
source qualifier, and the previous data is taken from the target by
using a lookup transformation to load the data in
PM_PREV_LOCATION
. It
passes changed records to
UPD_ChangedUpdates
, which updates changed re-
cords in the target. If you select to create the
PM_EFFECT_DATE
column in the
wizard, the expression transformation
EXP_EffectiveDate_InsertChanged
updates
SYSTEMDATE
in the
PM_EFFECT_DATE
column to indicate that new records have been updated.
Let's work through each transformation that is used in the SCD2 mapping:
