a nálisis F aCtoRial

John is responsible for data loading, transformation, and validation. He created a project and an interface to export data from a flat file and load this data into a relational table. Now John needs to verify the quality of data loaded in the table.

Background

In the previous practices, you learned how to create ODI Interfaces, which transform data from one or more relational tables in the source to a relational table in the target and to export data from a flat file to a relational table. In this practice, you perform data quality control for the interface created in Practice 10-1.

First, for a certain datastore, you verify that the primary key constraint is marked static, and two columns are marked mandatory with static control.

You then create an interface by using that datastore as a target.

On the interface’s Flow tab, you set STATIC_CONTROL to True. On the Controls tab, you ensure that the knowledge module is set to CKM SQL (a knowledge module that you will import) and you set maximum allowed errors to 5.

For two columns in the target datastore, you select the Check Not Null check box so that control errors are generated if these columns are not loaded.

You run the interface with Flow Control set to FALSE. You verify that there are control errors on three records, and that the three records with errors were loaded into the target datastore.

Finally, you rerun the interface with Flow Control set to TRUE and verify that errors are excluded from the target datastore.

Oracle University and (Oracle Corporation) use only.

Your Tasks

1. If not connected, connect to the Work Repository, WORKREP. To connect, click Connect To Repository. Select DEV_ODI_REPO from the Login Name drop-down list. Enter SUPERVISOR in the User field and SUNOPSIS in the Password field. Click OK to login.

2. Modify INT-Exp-FF-RT (created in Practice 10-1) to enable checking data quality in the target table.

a. On the Projects tab, expand the Export-FF-RT project. Select Knowledge Modules >

Check (CKM). Right-click Check (CKM) and select Import Knowledge Modules.

Oracle University and (Oracle Corporation) use only.

b. In the File import directory field, select CKM SQL and click OK. Close the Import Report window. Verify that CKM SQL was successfully imported and is shown in the tree view.

Oracle University and (Oracle Corporation) use only.

c. On the Models tab, expand Oracle_RDBMS1 > TRG_SALES_PERSON >

Constraints, and then double-click TRG_SALES_PERSON_PK. Click the Controls tab and verify that Static is selected.

Oracle University and (Oracle Corporation) use only.

d. In the model Oracle_RDBMS1, expand TRG_SALES_PERSON > Columns, double-click the column LAST_NAME, and then double-click the Controls tab. Select Static to enable static control. Select the Mandatory check box. Click the Save button. Repeat the same step to enable Static control and Mandatory for the DATE_HIRED column.

Save your changes and close the tabs.

Oracle University and (Oracle Corporation) use only.

e. On the Projects tab, expand the Export-FF-RT project, and then duplicate the interface INT-EXP-FF-RT. Open the copy of the interface and rename it as INT-EXP-FF-RT- Quality, and then click the Mapping tab.

Oracle University and (Oracle Corporation) use only.

f. Delete the filter for the column SALES_PERSON_ID, as shown in the following screenshot, and then click the Flow tab.

Oracle University and (Oracle Corporation) use only.

g. On the Flow tab, click Target (ODI_STAGE) to open the Options section. Set the static control option to true. Click the Controls tab.

h. On the Controls tab, ensure that your KM is set to CKM SQL. Set the maximum number of errors allowed to 5. Leave the other options with the default values. Click the Mapping tab.

Oracle University and (Oracle Corporation) use only.

i. In Target Datastore, select the LAST_NAME column, and then select the Check Not Null check box. Repeat the same step for the DATE_HIRED column. Click the Save button to save your changes.

Oracle University and (Oracle Corporation) use only.

j. Execute your interface . Click the Execution tab of the INT-EXP-FF-RT-QUALITY editor to view the execution results showing three errors, as shown in the following screenshot.

Note: You may need to click the Refresh icon to view your last execution.

Oracle University and (Oracle Corporation) use only.

k. On the Models tab, right-click the TRG_SALES_PERSON target datastore and select Control > Errors. View the records with errors as shown in the following screenshot:

Oracle University and (Oracle Corporation) use only.

l. In Designer Navigator, right-click the TRG_SALES_PERSON datastore and select View Data. Verify that the three rows with errors are still inserted in the target datastore. Close the TRG_SALES_PERSON data tabs.

Oracle University and (Oracle Corporation) use only.

m. Click the INT-EXP-FF-RT-Quality tab. On the Interface screen, click the Flow tab.

Click Target (ODI_STAGE) to open the Options section. Set Flow Control to true.

Save your changes, and then execute the interface .

Oracle University and (Oracle Corporation) use only.

n. On the Models tab of Designer Navigator, right-click TRG_SALES_PERSON and select View Data. Verify that the rows with errors are excluded from the data in the target datastore. Close the tabs.

Oracle University and (Oracle Corporation) use only.