two

Check Knowledge Module (CKM) Overview

A Check Knowledge Module (CKM) is a critical component of Oracle Data Integrator (ODI) used to ensure data integrity by performing quality checks on data according to predefined constraints.

The CKM ensures that the data loaded into the system adheres to various constraints (such as primary keys, alternate keys, foreign keys, etc.), and it stores detailed information on any violations found.

Key Components of a Standard CKM

A standard CKM maintains two main types of tables to track data quality:

1. SNP_CHECK_TAB (Summary Table):
• Purpose: This table provides a summary of data quality errors across all tables and constraints checked.
• Location: The table is created in the work schema of the default physical schema for the data server.
• Content: It contains error summaries for each checked table and constraint.
• Usage: This table is useful for analyzing the overall data quality of a model by providing a high-level overview of issues.
2. E$_ (Error Table):
• Purpose: This table stores the actual records that were rejected due to data quality violations (such as constraint violations).
• Location: One error table is created for each datastore that is checked.
• Content: It contains the rejected records that failed to pass the checks (e.g., data violating primary key, foreign key, etc.).
• Usage: This table helps identify and track the specific records that need attention.

Standard CKM Process Steps

A standard CKM executes a series of steps to validate the data integrity and maintain the necessary tables:

1. Drop and Create Summary Table:
• Drop Statement: The DROP statement is executed to remove the summary table from the previous run if necessary (typically if the designer requires a reset).
• Create Statement: The CREATE statement is executed to create the summary table (errors are tolerated if the table already exists).
2. Remove Previous Run Summary Records:
• Any records from the previous run are removed from the summary table to ensure only the current run's data is considered.
3. Drop and Create Error Table:
• Drop Statement: The DROP statement is executed to remove the error table from the previous run if required by the designer.
• Create Statement: The CREATE statement is executed to create the error table, again tolerating errors if the table already exists.
4. Remove Previous Run Error Records:
• The error table is cleaned of rejected records from the previous run to ensure only fresh violations are stored.
5. Reject Records Violating Constraints:
• Primary Key Violations: Records that violate the primary key constraint are rejected.
• Alternate Key Violations: Records that violate alternate key constraints are rejected.
• Foreign Key Violations: Records that violate foreign key constraints are rejected.
• Condition Violations: Records that violate check condition constraints (e.g., range checks, format checks) are rejected.
• Mandatory Attribute Violations: Records with mandatory attribute constraints (missing required fields) are rejected.
6. Remove Rejected Records from Checked Table (if required):
• If specified, rejected records can be removed from the checked table, ensuring that only valid data remains.
7. Insert Summary of Errors into the Summary Table:
• After all checks, the detected errors and violations are inserted into the SNP_CHECK_TAB summary table for review.

CKM Tags and Their Functions

To ensure that the CKM generates the appropriate code for each type of data check, specific tags are used. These tags tell ODI how to generate the necessary code for each type of constraint. The key tags are:

• "Primary Key": Specifies the code to check for primary key constraints.
• "Alternate Key": Specifies the code for alternate key constraints. This tag is used to check multiple alternate key constraints.
• "Join": Specifies the code for foreign key constraints. It is used to generate checks for every foreign key relationship.
• "Condition": Specifies the code for check condition constraints, such as validating data against business rules or custom conditions.
• "Mandatory": Specifies the code for mandatory attribute constraints, ensuring that required fields are not null or empty.
• "Remove Errors": Specifies the code for removing rejected records from the checked table.

Summary of the CKM Process

The CKM ensures the following:

• Data Quality Validation: Ensures that data adheres to critical integrity constraints (primary, alternate, foreign keys, check conditions, etc.).
• Error Tracking: Keeps a detailed record of errors, both at the summary level (SNP_CHECK_TAB) and the specific record level (E$_).
• Error Handling: Allows for the removal of rejected records, either from the summary table or from the checked data, based on the designer's specifications.
• Flexible Customization: The CKM process can be customized using tags to handle various types of constraints, ensuring that the correct checks are applied during execution.

This structured process ensures that the data in ODI is both high quality and consistent with the business rules defined within the model.