Checking Data Quality in the Model Use Case Scenario:

Practices for Lesson 7: Overview Practices Overview

Practice 7-1: Checking Data Quality in the Model Use Case Scenario:

John created new ODI Models and reverse-engineered all the tables and files in the models.

Now, John needs to check the quality of data in the models, define the constraints on the models, and detect possible errors in data.

Background:

After the models are defined, you need to check the quality of the data in these models. In this practice, you check the quality of data in the models and define constraints on models for the given sample application.

First, you create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model HSQL_SRC.

You then create a referential constraint on the SRC_CUSTOMER table’s CITY_ID column by using SRC_CITY as the parent table.

Next, you create a condition constraint on the SRC_CUSTOMER table:

Length(SRC_CUSTOMER.PHONE) > 6

Note: Completing this practice is critical for all the following practice sessions.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ

Your Tasks:

1. Create a new model, HSQL_ORDERS_APPLICATION, as a duplicate of the model HSQL_SRC.

a. In Designer, open the Models tab. Right-click HSQL_SRC model and select Duplicate Selection to duplicate the datastore. Click Yes.

b. Open Copy of HSQL_SRC and rename it as HSQL Orders Application. For Code, enter HSQL_ORDERS_APPLICATION. Click Save to save the model. If you get a warning message, click Yes to finish saving.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable

license to use this Student Guideฺ

Practices for Lesson 7: Organizing ODI Models and Creating ODI Datastores

2. Create a Reference constraint between the SRC_CUSTOMER and SRC_CITY datastores in the new model. This reference is on the CITY_ID column.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ

a. Expand the HSQL Orders Application model, and expand the SRC_CUSTOMER datastore.

b. Select the Constraints node, right-click, and select the New Reference option.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable

license to use this Student Guideฺ

Practices for Lesson 7: Organizing ODI Models and Creating ODI Datastores

c. From the Table drop-down list, select the SRC_CITY table. The name of the constraint is automatically generated. Click the Columns tab.

d. Click the Add icon, and use drop-down lists to select the CITY_ID column for both tables of reference. (Note that the Primary Table column is City, which you want to also change to CITY_ID.) Click Save.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ

3. Create a Condition constraint on SRC_CUSTOMER to check that the phone number contains six or more characters.

a. Select the Constraints node again for SRC_CUSTOMER, right-click, and select the New Condition option.

• Enter Phone Number Length in the Name field.

• Select Oracle Data Integrator Condition from the Type drop-down list.

• Enter the following expression in the Where field:

LENGTH(SRC_CUSTOMER.PHONE) > 6

• In the Message field, enter A phone number should contain more than 6 characters.

Note: You can also use the Expression Editor icon to graphically edit the expression.

b. Click the Validation icon to validate your expression syntax.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable

license to use this Student Guideฺ

Practices for Lesson 7: Organizing ODI Models and Creating ODI Datastores

On the Oracle Data Integration Information screen, click OK. Click Save to add the condition, and then close the tab.

4. Run a static check on the HSQL Orders Application model.

a. Select the HSQL Orders Application model tab and click the Control tab.

b. Select the knowledge module CKM HSQL.HandsOnLoads. Click Save. Close the HSQL Orders Application model tab.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ

5. Select the HSQL Orders Application model in the tree view, right-click, and select Control

> Check.

a. In the Execution dialog box, select Development context and then click OK.

b. Click OK when the Information dialog box notifies you that the session has started.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable

license to use this Student Guideฺ

Practices for Lesson 7: Organizing ODI Models and Creating ODI Datastores

6. Click the Operator Navigator icon tab . The Operator window appears.

Click the Hierarchical Sessions tab. Expand the All Executions node. Expand the HSQL Orders Application session. Expand its Steps node. The session should appear complete, containing steps marked with check marks and warnings, as shown here.

Note: You can optionally review the content of this session and see the different SQL commands issued to perform the check operations.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable license to use this Student Guideฺ

7. Review the errors detected in the SRC_CUSTOMER datastore.

a. Click the Designer navigator tab. On the Models tab, expand the HSQL Orders Application model, select the SRC_CUSTOMER datastore, right-click, and select Control > Errors.

b. View the table that lists the errors detected in your table. You have one join error and seven invalid phone numbers. Each line also contains the content of the invalid record.

c. Close these tabs.

Rahul Sharma (rahulฺgฺsharma@oracleฺcom) has a non-transferable

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

Practices for Lesson 8: ODI

In document ODI 11G hand book (Page 99-109)