Database Reference
In-Depth Information
• The
FIL_InsertNewRecord
filter transformation allows only the
NewFlag
record 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. The
EXP_KeyProcessing_InsertNew
ex-
pression transformation multiplies the
NEXTVAL
value by
1000
and loads
1
as
the current flag for each new row.
• The
FIL_InsertChangedRecord
filter transformation allows only the
ChangedFlag
record to get passed to
UPD_ChangedInserts
, which inserts
changed records into the target, which is
EMPLOYEE_SCD2_FLAG1
. The
EXP_KeyProcessing_InsertChanged
expression transformation incre-
ments the primary key by
1
and loads the current flag as
1
to indicate that the up-
dated row contains the current data.
• The
FIL_UpdateChangedRecord
filter transformation passes the primary
key of the previous value for every
ChangedFlag
record to
UPD_ChangedUpdate
, which updates changed records in the target, which is
EMPLOYEE_SCD2_FLAG2
. The
EXP_KeyProcessing_UpdateChanged
expression transformation changes the current flag to
0
to indicate the row doesn't
contain the current data anymore.
Let's work through each transformation that is used in the SCD2 mapping:
•
Source qualifier
(
SQ_EMP_FILE
): This extracts the data from the file or table
that you used as the source in the mapping. It passes data to the downstream
transformations, that is, lookup, expression, and filter transformation.
•
Lookup
(
LKP_GetData
): This is used to look up the target table. It caches the
existing data from
EMPLOYEE_SCD2_FLAG
. The
EMPLOYEE_ID=IN_EMPLOYEE_ID
condition will compare the data with the
source and target table. It passes the data based on the comparison with the ex-
pression transformation.
•
Expression
(
EXP_DetectChanges
): This receives the data from the upstream
transformation and based on that, it creates two flags, which are
NewFlag
and
ChangedFlag
:
◦
Condition for NewFlag
:
IIF(ISNULL(PM_PRIMARYKEY), TRUE,
FALSE)
◦
Condition for ChangedFlag
:
IIF(NOT
ISNULL(PM_PRIMARYKEY) AND
