ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Flow Control and Static Control

Flow Control

– If enabled this option uses the CKM selected and applies before loading into the target thus avoiding wrong data to get loaded

clip_image001

What actually happening in the above flow is that , After loading the data into I$,

  • a check table is created (SNP_CHECK_TAB) , deleting previous error table  and previous errors as ODI generally does.
  • Now it creates a new Error table , and check for Primary key unique constraints , other constraints and conditions defined in Database or Model level ODI conditions and Not Null check for each column marked as Not null.
  • If records violate the above constraints and conditions, it  adds the required records into E$ table and add an entry of it into SNP_CHECK_TAB with information about schema, error message , count etc.
  • Finally the other records are inserted and updated as per the KM and logic.

E$ Table

clip_image002

In the E$ table , the completed columns and the error message including error count and constraint name is record with associated ROW_ID.

SNP_CHECK_TAB

image

Static Control

– If enabled this option used the CKM selected and applied after loading into the target.

clip_image004

Here being the PK constraint on Target table , Insert has failed .

Lets see an example where the target table has no constraint at the database level but we are applying at the ODI level

image

Key is applied on the Employee_ID at the ODI Level.

clip_image006

Here after inserting into the target table , it uses the CKM selected and check for all the constraints and conditions and finally loads the rejected records into E$ and SNP_CHECK_TAB.

clip_image007

Check at the Datastore Level.

To check for the constraint or mostly bad records at the source side , ODI have option called check at the Datastore and when checked run the CKM on that particular datastore and stores the values into the respective E$ table.

clip_image008

clip_image009

After fetching the required errors if found makes an E$ table and makes an entry at SNP_SCHECK_TAB.

Constraints , conditions and duplicated can be checked at the datastore level too

clip_image010

clip_image011

Checking duplicate –

clip_image012

Foreign Key Constraints

clip_image013

Or run Check at the datastore level for complete check for all the constraint using the CKM defined in the model.

clip_image014

Recycle Errors

ODI KM’s have an option called Recycle Errors , what actually happens is that ODI reads from the E$ tables and loads into the I$ capturing all the records which are not present in the I$ by matching on the Primary Key or Unique Keys.

Lets look at an example , here  i am having an two duplicate rows stored in my E$ table , now iam going to make remove one duplicate record.

image

I am making changes into the Source File ( for my Example) and saving and re run the interface

image

image

The new records is inserted and the records are also deleted from the error tables.

image

image

17 Comments

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.