OEID Incremental Updates

A fairly common approach…

More often than not, when pulling data from a database into OEID, we need to employ incremental updates.   To introduce incremental updates, we need a way to identify which records have been added, updated or deleted since our last load.  This change identification is commonly referred to as change data capture, or CDC.  There is no one way to accomplish CDC and often the best approach is dictated by the mechanisms in place in the source system.  Usually the database we’re pulling from isn’t leveraging any explicit change data capture (CDC) mechanism.

Note: If you’re pulling from text files and new records are being appended, you can look at the incremental reading feature of the UniversalDataReader component (pg. 268). http://docs.oracle.com/cd/E29805_01/integrator.230/DataIntegratorDesigner.pdf.

If you’re pulling from a database, and don’t have explicit database CDC features enabled, best practices usually dictate you create an “audit” or “execution_history” table to track previous full and incremental loads. This “audit” table simply records the date and time a load started and the date and time it ended, if it ended successfully. You would need to INSERT into this table before calling your incremental load graph in Integrator. Thus, when reading your table (or, better yet, denormalized view), you could issue your SQL SELECT statement with a few other WHERE conditions that leverage a “last_update_date” column in your view like so….

SELECT * FROM <View>
WHERE view_last_update_date >= (SELECT MAX(run_start_date) FROM audit_table WHERE run_status = 'Complete')
AND view_last_update_date < (SELECT MAX(run_start_date) FROM audit_table)

 

Once this incremental load graph completes, you’d need to update your audit table row with the end datetime of the run and the status=”Complete” flag.

One thought on “OEID Incremental Updates

  1. Dan, thanks for the information. I’m having trouble with eql when trying to select/delete from the MDEX where {date column} is greater than a date value. Can you provide the EQL to do so?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s