[ODI 11g] Changed Data Capture (CDC)

Changed Data Capture (CDC) 

Changed Data Capture as the term implies is used to capture the data that is inserted, updated and deleted at the source side and replicating the same at the target. ODI have a Journalizing KM to do the required implementation and it is basically into two types simple and consistent.

Simple is used for CDC implementation on a single table and Consistent on Multiple tables or Model.

Simple CDC – Oracle Table

  

I am going to implement the Simple CDC concept on this particular table – SRC_AGE_GROUP.

Step 1 – Adding the Table to CDC

  

Step 2 – Starting the journalizing

  

Select the user as Subscriber

Execute it

The journalizing process is established.

In the above execution ODI creates a Subscriber table in the work schema, J$ table, view and a trigger to capture any data changes when any Insert, Update and Deletion takes places.

After successful execution you will be able to see a small green clock icon.

Let’s do a simple mapping and test for CDC.

Drag the Journalized Table as source and the required target. On the Source Data store, check mark the option “ JOURNALIZED DATA ONLY “ and ODI will automatically add a filter with a required date condition and subscriber information, use the proper LKM and IKM as per your technology. For this Example I have used IKM Oracle Incremental Update.

I have run it once to check whether its working fine or not. Now let me show you an example.

Original Source

  

Modified source

  

I have removed the last column and change the third row age_max 39 to 35.

There are two ways of seeing Journalized Data.

On the Journalized Interface source and Right click on filter and click on data.

Here is the sql from where the journalized data is being retrived

select * from ODI_TEMP.JV$DSRC_AGE_GROUP SRC_AGE_GROUP where (JRN_SUBSCRIBER = ‘SUNOPSIS’ /* AND JRN_DATE < sysdate */) 

The Other way is to right click on Journalized data store in Model and Changed Data capture – > Journal Data and you will see the above data again too.

Let’s capture the Journalized data to my Target table.

Target Table Before running the Interface

As you can see, one Update and one Deletes.

 

 

Target Table after running the Interface

Repeat the above steps for Journalizing SQL Server table as we did for Oracle namely

Add to CDC

Start Journal 

And if everything is start the journalizing will start.

Source Data before Running the Journalized Interface

  

delete from dimtime_backup where TimeKey=6;
update dimtime_backup set DayNumberOfWeek=4 where TimeKey=4;

Journalized Data

  

Although there is update and Delete on the source but there is just delete in the ODI reason being I have update the same value DayNumberOfWeek=4 again for update so ODI have correctly did only the Delete as the target already had the same value which is DayNumberOfWeek=4

How to say whether you have which JKM you have , right click on your model , 

Edit – >Journalizing Tab and you see what JKM is being used and whether Simple or Consistent