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)
Search WWH ::




Custom Search