Handling SCD with the Oracle Data Integrator 12

After the termination of support for OWB has been officially announced, the Oracle Data Integrator (ODI) is the ETL tool of choice in the Oracle world. The development has progressed to version 12 which brought a few modifications and improvements. The GUI has continued to become more similar to OWB, although there are a few possibilities available which OWB did not offer in this way. 

In this blog entry, we will deal with the implementation of slowly changing dimensions in ODI. 

The Oracle Data Integrator

The Oracle Data Integrator (ODI) offers an elegant solution to fill slowly changing dimensions. In this process, the basic approach corresponds to the approach in ODI 11, however, the configuration of the target tables has been revised.

At first, the dimension table is created just as any other model via "reverse engineer" in ODI.  

Step 1 - Configuring Dimension Table

Firstly, the dimension table has to be set as SCD. In order to do so, one opens the data store and sets "OLAP Type" to slowly changing dimension. 

Step 2 - Configuring the Columns of the Dimension Table

The columns of the dimension table can be found in the tab "Attributes": 

Here, the value "SCD Behavior" is set for every column. 

For an SCD type 2, the columns have to be set as follows: 

In the example, it looks as follows: 

Step 3 - Importing IKM

The IKM for slowly changing dimensions is no longer available per default in ODI 12. However, it can be imported from the XML which comes with ODI. The knowledge module can be found in the folder $ORACLE_HOME/odi/sdk/xml-reference.

Here, the IKM is to be selected according to the type of the target database. For an Oracle database, that would be e.g. "IKM Oracle Slowly Changing Dimension". 

Step 4 - Create Mapping

As soon as the target table is configured, it can be integrated in the mapping in the normal manner. 

The integration type "slowly changing dimension" must be selected in the target table. 

Now, the IKM should be tested in the physical layer: 

A CKM is mandatory for this IKM. 

In case no CKM can be selected here, it has to be imported in the same way as the LKM (see step 3).

This completes the implementation of the SCD2 load and it can be tested.

Learn more on how to further adjust the IKM slowly changing dimension to your environment in the next blog article.