Database Reference
In-Depth Information
• The
FIL_InsertNewRecord
filter transformation allows only the
NewFlag
record to pass further and filter
ChangedFlag
from the first flow. It passes the
NewFlag
records to
UPD_ForceInserts
, which inserts these records into the
EMPLOYEE_SCD2_DATE_RANGE
target instance. The
SEQ_GenerateKeys
sequence generator generates the primary key for each
NewFlag
record. The
EXP_KeyProcessing_InsertNew
expression transformation loads
SYSTEMDATE
into the
PM_BEGIN_DATE
column and leaves
PM_END_DATE
as null. This indicates that the new record has been added from the date loaded in
PM_BEGIN_DATE
.
• The
FIL_InsertChangedRecord
filter transformation allows only the
ChangedFlag
record to get passed to
UPD_ChangedInserts
, which inserts
changed records in the
EMPLOYEE_SCD2_DATE_RANGE1
target instance. For
every
ChangedFlag
record, the
EXP_KeyProcessing_InsertChanged
expression transformation loads
SYSTEMDATE
into the
PM_BEGIN_DATE
column and leaves
PM_END_DATE
as null. This indicates that the changed record
has been added, and the changed row now 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 inserts changed records into the target, which is
EMPLOYEE_SCD2_DATE_RANGE2
. The
EXP_CalcToDate
expression trans-
formation loads
SYSTEMDATE
into
PM_END_DATE
to indicate that the row now
contains the historical data.
Let's understand 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_DATE_RANGE
. 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
:
◦
Condition for NewFlag
:
IIF(ISNULL(PM_PRIMARYKEY), TRUE,
FALSE)