11 Oracle Data Integrator Data Quality Integrity Control

  • Slides: 21
Download presentation
11 Oracle Data Integrator Data Quality (Integrity Control) 6 -1

11 Oracle Data Integrator Data Quality (Integrity Control) 6 -1

Objectives After completing this lesson, you will: • Know the different types of data

Objectives After completing this lesson, you will: • Know the different types of data quality business rules ODI manages. • Be able to enforce data quality with ODI. • Understand how to create constraints on datastores. 6 -2

When to Enforce Data Quality? • The IS can be broken into 3 sub-systems

When to Enforce Data Quality? • The IS can be broken into 3 sub-systems • Source application(s) • Data integration process(es) • Target application(s) • Data Quality should be managed in all three sub-systems • ODI provides the solution for enforcing quality in all three. 6 -3

Data Quality Business Rules • • Defined by designers and business analysts Stored in

Data Quality Business Rules • • Defined by designers and business analysts Stored in the Metadata repository May be applied to application data Defined in two ways: • Automatically retrieved with other metadata • Rules defined in the databases • Obtained by reverse-engineering • Manually entered by designers • User-defined rules 6 -4

From Business Rules to Constraints • De-duplication rules • Primary Keys • Alternate Keys

From Business Rules to Constraints • De-duplication rules • Primary Keys • Alternate Keys • Unique Indexes • Reference rules • Simple: column A = column B • Complex: column A = function(column B, column C) • Validation rules • Mandatory Columns • Conditions 6 -5

Overview of the Data Quality System Target Source ORDERS Integration Process Errors Static Control

Overview of the Data Quality System Target Source ORDERS Integration Process Errors Static Control is started - Automatically (scheduled) - manually SALES LINES Errors Flow Control is started - by Interfaces during execution CORRECTIONS File Error Recycling is performed - by Interfaces 6 -6 Static Control is started - by Interfaces after integra - by Packages - manually

Constraints in ODI • Mandatory Columns • Keys • Primary Keys • Alternate Keys

Constraints in ODI • Mandatory Columns • Keys • Primary Keys • Alternate Keys • Indexes • References • Simple: column A = column B • Complex: column A = function(column B) • Conditions 6 -7

Mandatory Columns 1. Double-click the column in the Models view. 2. Select the Control

Mandatory Columns 1. Double-click the column in the Models view. 2. Select the Control tab. 3. Check the Mandatory option. 4. Select when the constraint should be checked (Flow/Static). 6 -8

Keys 1. 2. 3. 4. 5. 6. 6 -9 Select the Constraints node under

Keys 1. 2. 3. 4. 5. 6. 6 -9 Select the Constraints node under the datastore. Right-click, select Insert Key. Fill in the Name. Select the Key or Index Type Go to the Columns tab Add/remove columns from the key.

Checking Existing Data with a New Key 1. 2. 3. 4. Go to the

Checking Existing Data with a New Key 1. 2. 3. 4. Go to the Control tab. Select whether the key is Defined in the Database, and is Active Select when the constraint must be checked (Flow/Static). Click the Check button to perform a synchronous check of the key. Number of duplicate rows 6 -10

Creating a Reference 1. 2. 3. 4. Select the Constraints node under the datastore

Creating a Reference 1. 2. 3. 4. Select the Constraints node under the datastore Right-click, select Insert Reference Fill in the Name Select the reference type • • 5. Select a Parent Model and Table • 6 -11 User Reference Complex Reference Set the model and table to <undefined> to manually enter the catalog, schema and table name.

Creating a User Reference 1. 2. 3. 4. 5. 6 -12 Go to the

Creating a User Reference 1. 2. 3. 4. 5. 6 -12 Go to the Columns tab Click the Add button Select the column from the Foreign Key table. Select the corresponding column from the Primary Key table. Repeat for all column pairs in the reference.

Creating a Complex Reference 1. 2. Go to the Expression tab Set the Alias

Creating a Complex Reference 1. 2. Go to the Expression tab Set the Alias for the Primary Key table. Code the Expression 3. • • 6 -13 Prefix with the tables aliases Use the Expression Editor.

Checking Existing Data with a New Reference 1. 2. Go to the Control tab.

Checking Existing Data with a New Reference 1. 2. Go to the Control tab. Choose when the constraint should be checked (Flow/Static). Click the Check button to immediately check the reference. 3. • 6 -14 Not possible for heterogeneous references.

Creating a Condition 1. 2. 3. 4. Right-click Constraints node, select Insert Condition Fill

Creating a Condition 1. 2. 3. 4. Right-click Constraints node, select Insert Condition Fill in the Name. Select ODI Condition type. Edit the condition clause • 5. 6 -15 Use the Expression Editor Type in the error message for the condition.

Checking Existing Data with a New Condition 1. 2. 3. 6 -16 Go to

Checking Existing Data with a New Condition 1. 2. 3. 6 -16 Go to the Control tab Select when the constraint must be checked (Flow/Static). Click the Check button to perform a synchronous check of the condition.

How to Enforce Data Quality in an Interface The general process: 1. Enable Static/Flow

How to Enforce Data Quality in an Interface The general process: 1. Enable Static/Flow Control 2. Set the options 3. Select the Constraints to enforce • • 4. 6 -17 Table constraints Not null columns Review the erroneous records

How to Enable Static/Flow Control 1. 2. Go to the interface’s Flow tab. Select

How to Enable Static/Flow Control 1. 2. Go to the interface’s Flow tab. Select the target datastore. ü 3. 4. 6 -18 The IKM properties panel appears. Set the FLOW_CONTROL and/or STATIC_CONTROL IKM options to “Yes”. Set the RECYCLE_ERRORS to “Yes”, if you want to recycle errors from previous runs

How to Set the Options 1. 2. 3. 4. Select the interface’s Controls tab.

How to Set the Options 1. 2. 3. 4. Select the interface’s Controls tab. Select a CKM. Set up the CKM Options. Set the Maximum Number of Errors Allowed. • • 6 -19 Leave blank to allow an unlimited number of errors. To specify a percentage of the total number of integrated records, check the % option.

How to Select Which Constraints to Enforce For flow control: • For most constraints:

How to Select Which Constraints to Enforce For flow control: • For most constraints: 1. 2. • For Not Null constraints: 1. 2. 3. 6 -20 Select the interface’s Controls tab. For each constraint you wish to enforce, select Yes. Select the interface’s Diagram tab. Select the Target datastore column that you wish to check for nulls. In the column properties panel, select Check Not Null.

How to Review Erroneous Records First, execute your interface. To see the number of

How to Review Erroneous Records First, execute your interface. To see the number of records: 1. Select the Execution tab. 2. Find the most recent execution. • The No. of Errors encountered by the interface is displayed. To see which records were rejected: 1. Select the target datastore in the Models view. 2. Right-click > Control > Errors… 3. Review the erroneous rows. 6 -21