Skip to main content

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. 

 odi-scd-handling-konfiguration-dimensionstabelle

Step 2 - Configuring the Columns of the Dimension Table

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

 

odi-scd-handling-konfiguration-dimensionstabelle

 

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

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

 

odi-scd-handling-einstellung-spalten

 

In the example, it looks as follows: 

 

odi-scd-handling-einstellung-spalten-beispiel

 

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". 

 

odi-scd-handling-ikm-importieren

Step 4 - Create Mapping

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

 

odi-scd-handling-mapping-erstellen

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

 

odi-scd-handling-integration-type-slowly-changing-dimension

 

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

 

odi-scd-handling-physical-layer-ikm-pruefen

 

A CKM is mandatory for this IKM. 

 

odi-scd-handling-voraussetzung-ckm

 

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. 

dominik-schuster
Your Contact
Dominik Schuster
Principal Consultant