Database Reference
In-Depth Information
second flow. It passes the changed records to
UPD_ChangedUpdate
, which re-
places existing rows in the target to reflect the latest changes.
Let's understand each transformation that is used in the SCD1 mapping:
•
The source qualifier
(
SQ_EMP_FILE
): This extracts the data from the file or
table 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 the
EMPLOYEE_SCD1
table.
The
EMPLOYEE_ID=IN_EMPLOYEE_ID
condition in the
Condition
tab will
compare the data with the source table and the target table. Based on the compar-
ison, it passes the required data to the expression transformation.
•
Expression
(
EXP_DetectChanges
): This receives the data from the upstream
transformation, and based on the comparison, it creates two flags, which are
NewFlag
and
ChangedFlag
. In our case, we are using the
LOCATION
field
for comparison.
For every record that comes from a source, if there is no matching record in tar-
get, we can flag that record as
NewFlag
; that is, the
EMPLOYEE_ID !=
EMPLOYEE_ID
condition signifies
NewFlag
. If no matching record is present
for
EMPLOYEE_ID
in the target, it signifies that
PM_PRIMARYKEY
will not be
available. So, the lookup transformation will return
NULL
for the
PM_PRIMARYKEY
column.
For every record that comes from a source, if there is a matching record in the tar-
get and if the location from source does not match the location for a particular
EMPLOYEE_ID
from the target, we can flag that record as
ChangedFlag
, that
is,
EMPLOYEE_ID = EMPLOYEE_ID AND LOCATION !=
PM_PREV_LOCATION
:
◦ The wizard created the condition for
NewFlag
as
IIF(ISNULL(PM_PRIMARYKEY), TRUE, FALSE)
◦ The condition for
ChangedFlag
is
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.
