Oracle has a very nice ETL tool (or in Oracle parlance ELT tool). Its purpose is clear: make a easily maintainable set of workflows that show clearly the lineage from source to target.
To use ODI, we must download a zip file from Oracle. It can be found if we look for “download ODI”. We get a file (fmw_12.2.1.4.0_odi_Disk1_1of1.zip) that contains an executable whereby ODI can be installed. This is well explained in different youtube movies and on the Oracle website.
We must then install the master repository, followed by a work repository. This is also straight forward and well explained in multiple sources.
We then proceed by setting up the technical environment necessary for a SCD2 loading. We start with an input table that has four fields with one field being a key that will be used as the business key. The table is called DEPARTMENTS and looks like:
The data will be written into a target table that contains the four fields from above with 4 additional technical fields: the surrogate key, a date_from and a date_to to indicate the validity period and a field that indicates whether a record is current or not. It looks like:
The surrogate key now acts as a key, whereas the original business key is deprived of its role as key. this can be understood as we will collect multiple versions of a certain business key with different periods of validity.
We will use a sequence to generate values for the key.
We must store these items in ODI. Under the heading models, the input table and the target table can be stored:
whereas the sequence is stored under projects:
We may then describe the properties of the target table:
and defining the role of each field:
We can then create a mapping:
In a final step, we must define the knowledge modules:
and the Integration Knowledge Module with the Check Knowledge Module:
Running this mapping provides the desired result.