Database Reference
In-Depth Information
•
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_SCD3
. 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 the upstream
transformation and based on that, it creates two flags, which are
NewFlag
and
ChangedFlag
. The conditions for both the flags are as follows:
◦
NewFlag
:
IIF(ISNULL(PM_PRIMARYKEY), TRUE, FALSE)
◦
ChangedFlag
:
IIF(NOT ISNULL(PM_PRIMARYKEY) AND
(DECODE(LOCATION,PM_PREV_LOCATION,1,0)=0), TRUE,
FALSE)
Based on the condition, it passes the data to downstream filter transformations.
•
Filter
(
FIL_InsertNewRecord
): This filters the records that come from the
upstream expression transformation and are marked as
ChangedFlag
; it allows
records with
NewFlag
to get passed to the
UPD_ForceInserts
update
strategy.
•
Filter
(
FIL_UpdateChangedRecord
): This filters the records that come
from the upstream expression transformation and are marked as
NewFlag
; it al-
lows records with
ChangedFlag
to get passed to the
UPD_ChangedUpdate
update strategy. It uses the value of the
LOCATION
field that is returned from
LKP_GetData
to load
PM_PREV_LOCATION
.
•
Update strategy
(
UPD_ForceInserts
): This uses the
DD_INSERT
condition
to insert data into the
EMPLOYEE_SCD3
target instance.
•
Update strategy
(
UPD_ChangedUpdate
): This uses the
DD_UPDATE
condi-
tion to overwrite the existing
LOCATION
field into the
EMPLOYEE_SCD3
target
instance. It passes data to
EXP_EffectiveDate_insertChanged
in order
to load
PM_PREV_LOCATION
in the target.
•
Sequence generator
(
SEQ_GenerateKeys
): This generates a sequence of val-
ues for each new row marked as
NewFlag
that comes into the target, getting in-
cremented by
1
. It passes the generated value to
EXP_KeyProcessing_InsertNew
.
