Database Reference
In-Depth Information
•
Source qualifier
(
SQ_EMP_FILE
): This extracts the data from the file or table
that you used as a source in the mapping. It passes data to the downstream trans-
formations, 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_VERSION_NUMBER
. The
EMPLOYEE_ID=IN_EMPLOYEE_ID
condition will compare the data with the
source table and target table. It passes the data based on the comparison with the
expression transformation.
•
Expression
(
EXP_DetectChanges
): This receives the data from an 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
(DECODE(LOCATION,PM_PREV_LOCATION,1,0)=0), TRUE,
FALSE)
It passes the data to downstream filter transformations.
•
Filter
(
FIL_InsertNewRecord
): This filters the records that come from an
upstream expression transformation and are marked as
ChangedFlag
; it only
allows records with
NewFlag
to get passes to the
UPD_ForceInserts
update
strategy.
•
Filter
(
FIL_UpdateChangedRecord
): This filters the records that come
from an upstream expression transformation and are marked as
NewFlag
; it only
allows records with
ChangedFlag
to get passed to the
UPD_ChangedInserts
update strategy.
•
Update strategy
(
UPD_ForceInserts
): This uses the
DD_INSERT
condition
to insert data into the target, which is
EMPLOYEE_SCD2_VERSION_NUMBER
.
•
Update strategy
(
UPD_ChangedInserts
): This uses the
DD_UPDATE
condi-
tion to overwrite existing
LOCATION
value into the target instance, which is
EMPLOYEE_SCD2_VERSION_NUMBER1
.
•
Sequence generator
(
SEQ_GenerateKeys
): This generates a sequence of val-
ues for each new row marked as
NewFlag
, which incrementally comes into the
target by
1
. It populates the value into the
PM_PRIMARYKEY
column in the
EMPLOYEE_SCD2_VERSION_NUMBER
target.
