ODI- Static Error Control and Data Quality checks using CKM in Oracle Data Integrator

In order to perform Data Quality checks and initial cleaning of the data ODI provides 2 kinds of DQ checks: Static Control and Flow Control.
Static Control->This is used to check the quality of the data that is already present in the Data store, that is why this is known as Static.
Flow Control->This is used to check the quality of the data at the time of Integration process, that is when the data is being processed into the Target table, at that time we can separate out the error records (i.e. the records that do not full fill the constraints set for the Data Quality)

In this post we will see Static control:

Take the SCOTT schema and the EMP table from the schema.

Now add one record to the table:

INSERT INTO SCOTT.EMP
VALUES (1234,'Test','Test',null,sysdate,1000,NULL,10);


Assume that this record came while extract process and this is bad data and now you want to do a static check of your data and want to know quality before initiating the transfers to Target database .

ODI part:
1. Reverse Engg this table to some Model in ODI.

2. Import your technology specific CKM into the Project as shown in the screen shot.

3. In the Emp datastore add one constraint.

4. Start the DQ check process.

5. Once the session is complete Click on Control-Errors, this will display the records that do not satisfy the constraint.

Understanding the process (CKM)
1. The operator window for your session will look something like this.

2. One SNP_CHECK_TAB is created in the work schema of the Default physical schema.
3. E$ tables will be created in the work Schema.
4. The insert CK errors step will display the code like this:
from SCOTT.EMP  EMP
where not (
ENAME<>'Test'
)

this is where the data is getting filtered out into the E$ table.