[ODI 10g] Flow Control And Static Control

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

 

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

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

Static Control

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

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

Key is applied on the Employee_ID at the ODI Level.

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.

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.

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

 

Checking duplicate -

Foreign Key Constraints

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

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.

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

 

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