Changed Data Capture in ODI
Posted by Dylan Wan on June 9, 2008
ODI is the tool will also be used to develop the ETL (or ELT) for Oracle BI Applications. ODI supports Changed Data Capture by its journalizing module.
My understanding of CDC flow in ODI is as follows:
ODI creates the Journal tables in the source database. The Journal tables store/publish the references to the changed data. The Journal table includes the unique identifier of the source table and also indicates the type of changes, Insert, Update, or Delete.
ODI also generates and deploys the code in the source database for populating the journal tables. This can be simply a database trigger or the PL/SQL program that can retrieve log data from the database.
During runtime, when the changes happen, the changes will be inserted into the Journal tables.
The data are stored in the journal tables via a publish/subscribe model. Each subscriber will get its own changes and will need to consume the captured change.
When all subscribers have consumed the changes, the changes will be deleted from the journal tables.
This is a neat solution, Isn’t it?