Database Reference
In-Depth Information
At this point, you should be very clear with the different types of SCD. We need to prac-
tically implement these concepts in Informatica PowerCenter. Informatica PowerCenter
provides a utility called wizard to implement the SCD. Using this wizard, you can easily
implement any SCD. In the upcoming chapters, we will learn how to use the wizard to im-
plement SCD1, SCD2, and SCD3.
Before you proceed to the next section, make sure you have a proper understanding of the
transformations in Informatica PowerCenter. You should be clear about the source qualifi-
er, expression, filter, router, lookup, update strategy, and sequence generator transforma-
tions. The wizard creates a mapping using all these transformations to implement the SCD
functionality.
When we implement SCD, there will be some new records that will need to be loaded into
the target table, and there will be some existing records for which we need to maintain the
history.
Note
The record that appears for the first time in the table will be referred to as the NEW record,
and the record for which we need to maintain the history will be referred to as the
CHANGED record. Based on the comparison of the source data with the target data, we
will decide which one is the NEW record and which one is the CHANGED record.
To start, we will use a sample file as our source and an Oracle table as our target in order
to implement SCDs. Before we implement an SCD, let's talk about the logic that will
serve our purpose, and then we will fine-tune the logic for each type of SCD:
1. Extract all records from the source.
2. Look up at the target table and cache all the data.
3. Compare the source data with the target data to flag the NEW and CHANGED re-
cords.
4. Filter the data based on the NEW and CHANGED flags.
5. Generate the primary key for every new row inserted into the table.
6. Load the NEW record into the table and update the existing record, if needed.
Based on the specific SCD, the preceding logic will be modified to a certain extent.
Search WWH ::




Custom Search