What is Static Control and Flow Control ? Difference between both of them

Answer:- 

What is Data Integrity(Quality) Check in ODI:-

Data Integrity Check Process checks is activated in the following cases:

When a Static Control is started (from Studio, or using a package) on a model, sub-model or datastore. The data in the datastores(table) are checked against the constraints defined in the Oracle Data Integrator model.

If a mapping is executed and a Flow Control is activated in the IKM. The flow data staged in the integration table (I$) is checked against the constraints of the target datastore, as defined in the model. Only those of the constraints selected in the mapping are checked.

In both those cases, a CKM is in charge of checking the data quality of data according to a predefined set of constraints. The CKM can be used either to check existing data when used in a "static control" or to check flow data when used in a "flow control". It is also in charge of removing the erroneous records from the checked table if specified.

Static vs Flow control in Oracle Data Integrator

FLOW CONTROL :

Data quality validation is done before loading the data into target tables.

Check Control Knowledge Module (CKM) will create E$ table and SNP_CHECK_TAB table for data quality check.

It will validate data in I$ table before inserting data into target table. If it has any errors then it will delete from I$ table and insert into E$ table and common error message and interface name into SNP_CHECK_TAB.

STATIC CONTROL :

Data quality validation is done after loading the data into target tables.

CKM will validate data on target table and if any error is detected it will be inserted to E$ table and SNP_CHECK_TAB. Remember that the incorrect entry will not be deleted like Flow control. 

What is Recycle Errors in Data Quality in ODI?

Recycle errors is process for reprocessing the failed or rejected data from E$table to I$table.

E$table will contains all errored data from Flow Control or Static control for rejected due to Duplicate or null rows (PK, UK or CHK Constraints).

First we need to rectify error data in E$table and then we can enable RECYCLE ERROR=true option In IKM level. It will select data from E$table

and It will Insert Into I$table then again It will validate on E$table If It is valid data then it will load Into Target table otherwise it will insert into E$table.

What actually happening in the  flow control :- 

• 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 E$, 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.

Error Table Structure

The E$ error table has the list of columns described in the following table:-

Columns    Description

----------------------------------------------------------------------------------

ERR_TYPE  Type of error: 

'F' when the datastore is checked during flow control

'S' when the datastore is checked using static control

ERR_MESS Error message related to the violated constraint

CHECK_DATE Date and time when the datastore was checked

ORIGIN Origin of the check operation. This column is set either to the datastore name or to a mapping                            name and ID depending on how the check was performed.

CONS_NAME Name of the violated constraint.

CONS_TYPE Type of the constraint: 'PK': Primary Key,'AK': Alternate Key,'FK': Foreign Key, 'CK': Check                            condition, 'NN': Mandatory attribute

----------------------------------------------------------------------------------

Summary Table Structure:-

The SNP_CHECK_TAB table has the list of columns described below:

Column Description

----------------------------------------------------------------------------------

ODI_CATALOG_NAME         Catalog name of the checked table, where applicable

ODI_SCHEMA_NAME Schema name of the checked table, where applicable

ODI_RESOURCE_NAME Resource name of the checked table

ODI_FULL_RES_NAME Fully qualified name of the checked table. For example ..

ODI_ERR_TYPE        Type of error:

'F' when the datastore is checked during flow control

'S' when the datastore is checked using static control

ODI_ERR_MESS         Error message

ODI_CHECK_DATE Date and time when the datastore was checked

ODI_ORIGIN Origin of the check operation. This column is set either to the datastore name                                              or to a mapping name and ID depending on how the check was performed.

ODI_CONS_NAME Name of the violated constraint.

ODI_CONS_TYPE Type of constraint: 'PK': Primary Key,'AK': Alternate Key,'FK': Foreign Key,'CK':                                            Check condition,'NN': Mandatory attribute (Not Null)

ODI_ERR_COUNT Total number of records rejected by this constraint during the check process

ODI_SESS_NO ODI         session number

ODI_PK Unique identifier for this table, where appicable

----------------------------------------------------------------------------------