Tracking historical changes in data (slowly changing dimensions) is a very common Oracle Data Integrator (ODI) task since many industries require the ability to monitor changes and to be able to report on historical data accurately at a point in time. Implementing slowly changing dimensions (SCD) in ODI 12c is relatively easier than in 11g.
This blog post provides instructions on how to implement SCD in 12c, and points out any of the differences from 11g.
SCD Target Table Requirements
In the data warehouse (same as in 11g), the target table needs to contain these additional columns:
- A natural key. This represents the primary or unique key, usually from the source system.
- A surrogate key. This should be a number unique for each data entry that is automatically generated, usually using a sequence for example the ROW_WID column it only exists in the warehouse and is based on a DB or ODI sequence.
- A starting timestamp. Indicates what date the record is created in the data warehouse.
- An ending timestamp. Indicates what date the record changes.
- A current record flag. Indicates whether the record is the current one (1) or the historical one (0).
In this example, the following are the additional columns:
- Natural Key: EMPLOYEE_ID
- Surrogate Key: ROW_WID
- Starting Timestamp: EFF_FROM_DT
- Starting Timestamp: EFF_TO_DT
- Current Record Flag: CURRENT_FLG
Configuring the SCD Mapping
- Create the target data store model in the Designer and define the SCD behavior for each column.
In ODI 11g this was a tedious exercise, as the column editor had to be opened for each column one-by-one, then defined in the SCD Behavior area in the Description tab. In 12c, all of the columns can be defined at the same time in the Model Editor under the Attributes tab.
- In the Projects section, create the mapping to load the SCD target table.
Another thing to note is that in ODI 12c, the mapping editor has been simplified to only three tabs instead of the seven in 11g (see below). The Mapping tab is now called Logical and Quick Edit can be accessed within this area. The Flow tab is now called Physical, and Flow Control can be accessed in this area.
- After dragging and auto-mapping the source and target tables, map the SCD specific columns as follows:
- EFF_FROM_DT. Maps to ‘SYSDATE’ which captures the date when record is created.
- EFF_TO_DT. Maps to ‘SYSDATE’ which captures the date the record is changed. The default value from the SCD IKM is 01-01-2400.
- Maps to ‘Y’. The KM populates this column so no need to populate this column. (1: Current Record, 0: Past Records).
- ROW_WID. Maps to a sequence which auto-populates unique values for each record. In this case, we are mapping to a ODI sequence.
As mentioned earlier, in ODI 12c the Quick Edit tab no longer exists. Now, you can easily access the Quick Edit panel from the mapping by clicking on the target data store and by viewing the Attributes panel in the Property Inspector as shown above.
The Execute On parameter in 11g has been replaced with the Execute on Hint parameter in 12c, which can easily be specified in this same panel, as shown below.
- The only step that needs to be done in 12c which was not necessary in 11g is to define Integration Type in the target table property inspector under the Target tab.
Lastly the Integration Knowledge Module (IKM) is set in the Physical tab (formerly the Flow tab in 11g). As mentioned above, the Control tab is no longer there in 12c and Check Knowledge Modules can be defined under the Physical tab in the Property Inspector.
Quick tip: If the Primary Key of the target table is indexed, make sure it is non-unique as the historized data will create duplicate primary keys.
Once the mapping is tested, similar to 11g, it can be added to a package and then will generate a scenario for that package. This will enable data loads to capture any changes and will historicize the old data whenever the scenario is executed. In the below example, the JOB_ID for four employees changed when they were promoted to managers and this is captured in the data load.
Still have questions about implementing SCD in ODI 12c? Contact the Performance Architects team here and we would be happy to help.