Creating ODI Interface: Implementing Lookup Use Case Scenario:

Practices for Lesson 9: Overview Practices Overview

Practice 9-2: Creating ODI Interface: Implementing Lookup Use Case Scenario:

John created an interface to load data in the target model from different sources and perform data transformation and filtering, and provided a date when data was updated. Now, John needs to implement a lookup to fetch additional information for the data loading in the target.

Background:

In the previous practices, you created an interface with several sources to load the TRG_CUSTOMER datastore in the Oracle Sales Application model with the content of the SRC_CUSTOMER table and the SRC_SALES_PERSON files from different models. Now, you implement the lookup to load data in the target according to the age range provided in the lookup table.

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

Your Tasks:

1. Create an ODI Interface with a new lookup.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

a. In the Designer, duplicate interface INT_9-1. Open the copy of INT_9-1 and rename the interface INT_9-2. Ensure that the Staging Area Different From Target check box is deselected. Click the Mapping tab.

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ฺ

b. In the Mapping tab, click the button to arrange the datastores in the source pane.

In the Confirmation window, click Yes. Click the Add a new Lookup icon.

c. In the Lookup Tables Wizard, select the SRC_CUSTOMER table in the Driving Table pane. In the Lookup Table pane, expand the FLAT_FILE_SRC model, and then select SRC_AGE_GROUP. For alias, enter LKUP_AGE_RANGE, and then click Next.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

d. In the screen that follows, select the AGE column in the Source pane. Select

AGE_MIN in the Lookup pane, and select Staging for the Execute on option. Then, click the Join button.

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ฺ

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

e. Edit the lookup condition to read:

SRC_CUSTOMER.AGE BETWEEN LKUP_AGE_RANGE.AGE_MIN. Select AGE_MAX in the Lookup pane, and then click the Join button again.

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ฺ

f. Edit the Lookup condition to read: SRC_CUSTOMER.AGE BETWEEN

LKUP_AGE_RANGE.AGE_MIN AND LKUP_AGE_RANGE.AGE_MAX. Click Finish.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

g. Click the button to arrange your datastores in the Source pane. In the

Confirmation window, click Yes. Select AGE_RANGE in the target datastore, and click the Expression Editor icon .

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ฺ

h. In the Expression Editor, select the AGE_RANGE column and drag it to the expression window. Verify that your expression is LKUP_AGE_RANGE.AGE_RANGE. Click OK to close the Expression Editor.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

i. In the Flow tab, click the Target area and ensure that IKM Selector is set to IKM Oracle Incremental Update. Save the interface INT_9-2.

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ฺ

Note: The source objects in your flow diagram may not exactly match the following screen. The flow diagram editor may reposition the source objects. For example, the SRC_AGE_GROUP lookup table may appear at the top.

j. In the Designer Projects tab, select INT_9-2, and then click the icon to execute your interface. Select the Simulation check box, and then click OK.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

k. In the Simulation window, note the generated code, and then click Close.

2. Run the INT_9-2 interface and view the lookup data.

a. Click the Execute button again to run your interface . Click OK. Click OK again.

Note: Do not select the Simulation check box.

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

b. Click the Operator tab and verify that your interface INT_9-2 successfully executed.

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

license to use this Student Guideฺ

Practices for Lesson 9: Designing Interfaces

c. Double-click the step that performs a lookup operation on the source flat file

SRC_AGE_GROUP (SRCSet0 - Load data), and then click the Code tab. View the code on the source and on the target.

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

d. Switch from the Operator Navigator to the Designer Navigator in the Projects tab, and open the INT_9-2 interface if it is not still open. Click the interface’s Mapping tab.

Right-click Target Datastore – TRG_CUSTOMER, and then select the Data option.

View your lookup data. Close the Data Editor window. Verify that your interface is saved and then close the tabs.

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

license to use this Student Guideฺ

In document ODI 11G hand book (Page 162-177)