How To Tune ODI Check Knowledge Modules for Managing Referential Integrity Constraints (Doc ID 752477.1)

Last updated on OCTOBER 24, 2023

Applies to:

Goal

For efficiency purposes, a common best practice when defining a data warehouse model is to avoid creating foreign key constraints between tables. Indeed, if such components are active, prior to performing an insert/update of a record, the database will check if the data does not violate any of set of multiple integrity constraints set up between any referenced tables.

When dealing with billions of records, this involves very significant resource allocation and increases processing time.

If constraints do not exist (or may exist but have been inactivated temporarily), the ELT or manual developed SQL instructions used to load the data must ensure this data integrity. ODI offers such features through the activation of the Flow Control at the Integration Interface level.

This feature is based on the setting up and use of Oracle Data Integrator (ODI) constraints defined between datastores at the Model level. This ensures that a record containing a value which references an absent value in a parent table will not be inserted into a child table. As the constraints are not implemented in the database - but rather in ODI - they will not be reverse engineered by ODI and they will have to be manually created for each datastore.

As databases and the servers hosting them are increasingly powerful they require less and less time to control the integrity between tables. For this reason, foreign keys are being increasingly implemented at the database level resulting in the delegation of the referential integrity checks to the database for each update and insert operation.

Nevertheless, when performing the initial (one-off) loading to a data warehouse, significant performance increases may be gained by disabling database implemented integrity constraints before performing the loading of your data warehouse.

This note describes how to tune an ODI Control Knowledge Module (CKM) to dynamically manage referential constraints at runtime.

Solution