Creating ODI Interface: Simple Transformations Use Case Scenario:

Practices for Lesson 8: Overview Practice Overview

Practice 8-1: Creating ODI Interface: Simple Transformations Use Case Scenario:

John created the new ODI models and reverse-engineered all the tables and files in the models, defined the constraints on the models, and checked data quality. Now, John begins working on creating ODI Interfaces to pass data between models and perform simple data transformations.

These interfaces will then be used for the development of his integration project.

Background:

After the models are defined and the quality of the data is verified, you need to create an ODI Interface to perform transformations. In this practice, you create simple interfaces with one target table and a single source. You will run these interfaces and check the execution.

In this practice, you create three interfaces. First, you create an interface called INT_8-1, loading the TRG_COUNTRY datastore in the Oracle Sales Application model with the content of the SRC_REGION table from the HSQL Orders Application model. This simple interface has no transformations.

The second interface, INT_8-2, is a duplicate of INT_8-1, to which flow control is activated, and constraints in the target table are checked.

The third interface, INT_8-3, loads the TRG_COUNTRY datastore in the Oracle Sales

Application model with the content of the SRC_CITY table from the HSQL Orders Application model. In this interface, flow control is activated, constraints in the target table are checked, and city population values are transformed from individual to times 1,000.

Note: Completing this practice is critical for other 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 an interface called INT_8-1, loading the TRG_COUNTRY datastore in the Oracle Sales Application model with the content of the SRC_REGION table from the HSQL Orders Application model. You must map the columns of the same name without any

transformation. Ensure that you take only distinct records.

a. In Designer, click the Projects tab, and expand the HandsOnLoads project. Expand the HandsOn folder. Select the Interfaces node, right-click, and select the New Interface option.

b. In the Interface window, enter INT_8-1 as the name of the interface. Ensure that the Optimization Context is Global. Click the Mapping tab.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

c. In the Designer Navigator to the left, click the Models tab. In the tree view, expand the Oracle Sales Application model. Drag the TRG_COUNTRY datastore from the tree view to the Target Datastore zone (see the following screen: the panel to the right of the panel with text stating “Drag datastores…. as sources for this dataset”). The datastore appears in this zone.

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ฺ

d. Expand the HSQL Orders Application model and drag the SRC_REGION datastore from the model tree to the Sources zone of your diagram (the panel to the left of the target panel). An Automap dialog box appears. Click Yes. Now the system

automatically maps fields in the source and target datastores.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

e. Click the Flow tab. After the flow diagram initializes, you may want to use the zoom-out button to see all the objects in the diagram. Within the diagram, click the box for Target + Staging Area that is labeled Target (ORACLE_ORCL_LOCAL), then in the Target Area – Property Inspector panel, select the Distinct Rows check box. If open, click the

button to minimize the Messages log. Your screen now looks as shown here.

Note: Confirm that the Distinct Rows check box is selected.

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. Click Save to save your interface. If the Locking Object window appears, select “Don’t show this window next time,” and click Yes.

2. Run this interface, and check the content of the TRG_COUNTRY table.

a. Expand the Interfaces node, right-click the newly created interface INT_8-1, and then select Execute.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

b. Click OK in the Execution window, and then click OK when the Session Started message appears.

c. Click the Operator tab to open the ODI Operator Navigator. The Operator window appears.

Note: In Operator, you may need to click the Refresh button to view the new session.

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ฺ

d. In the Session List tab, expand the All Executions node. The session called “INT_8-1” should appear complete. Expand this session in the tree view as shown:

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

e. In your interface window, click the Mapping tab. In Target datastore – TRG_COUNTRY, right-click COUNTRY, and then select Data.

f. A window appears with the loaded data.

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ฺ

g. Verify your data and close this window. Close INT_8-1 tab. If the Unlocking Object window appears, select “Don’t show this window next time” check box and click Yes.

3. Create an interface called INT_8-2 that loads the TRG_REGION datastore in the Oracle Sales Application model with the content of the SRC_REGION table from the Orders Application–HSQL model. You must map the columns of the same name without any transformation. Ensure that flow control is activated and all constraints in the target table are checked.

Note: Because this new interface uses the same source as the previous one, you will duplicate it.

a. Click the Designer tab. Expand the Projects tab, the HandsOnLoads project, and the HandsOn folder. Expand the Interfaces node and select INT_8-1. Right-click and select Duplicate Selection, and then click Yes in the confirmation box. A copy of your interface appears.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

b. Double-click the interface Copy of INT_8-1 to edit it.

c. In the Name field, change the name to INT_8-2, and then click the Mapping tab.

d. Click the “perform layout” button to customize the view of the Source zone. In the Confirmation window, click Yes.

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ฺ

e. In the Designer, click the Models tab, and then drag the TRG_REGION datastore from the Oracle Sales Application model to the Target Datastore zone. Leave the source datastore unchanged. Click Yes to perform automatic mapping.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

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. Click the Flow tab. In the diagram, click the box labeled Target

(ORACLE_ORCL_LOCAL). In the Target Area – Property Inspector section, scroll down the IKM option list to FLOW_CONTROL, and ensure that it is set to “true”.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

g. Click the Controls tab. Ensure that the selected knowledge module in the CKM Selector is CKM Oracle. In the Constraints section, the value for each Constraint should be “true”.

h. Click Save to save your interface.

4. Run this interface, and check the content of the TRG_REGION table.

a. Click Execute.

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. Click OK in the Execution window, and then click OK when the Session Started message appears.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

5. In ODI Operator, verify that your new interface executed successfully. You may need to refresh the list by contracting and then expanding the All Executions node in the Session List:

a. In the Interface window, click the Mapping tab. Select the TRG_REGION target datastore (click the name of the datastore), right-click, and select Data. A window appears with the loaded data.

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. Verify the data, close this window, and then close your interface INT_8-2.

6. Create an interface called INT_8-3 that loads the TRG_CITY datastore in the Oracle Sales Application model with the content of the SRC_CITY table from the HSQL Orders

Application model. Ensure that flow control is activated and all constraints in the target table are checked. You need to transform the city population values because the target table unit is “thousands of inhabitants,” whereas the source table unit is “inhabitants.”

a. If not open, click the Designer tab, and click the Projects tab. Expand

HandsOnLoads project and the HandsOn folder. Right-click the Interfaces node and select New Interface.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

b. In the Interface Definition tab, enter the name INT_8-3, and keep the default Optimization Context selection, Development. Click the Mapping tab.

c. If not open, click the Models tab. In the tree view, expand the Oracle Sales

Application model. Drag the TRG_CITY datastore from the tree view to the Target Datastore zone. The datastore appears in this zone.

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ฺ

d. Expand the HSQL Orders Application model and drag the SRC_CITY datastore from the model tree to the Sources zone of your diagram. Click Yes to perform Automatic Mapping.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

e. Select the POPULATION column in the target datastore.

f. In the Mapping Properties section of the Property Inspector, in the Implementation tab, edit the expression to populate the POPULATION column with the number of thousands of inhabitants rounded to the nearest thousand. Edit the expression to have the following: FLOOR(SRC_CITY.POPULATION/1000)

Note: You use the FLOOR function to perform rounding.

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

g. To verify the expression, click Check the Expression in the DBMS button . Click OK.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

h. Click the Flow tab. Click the box labeled Target (ORACLE_ORCL_LOCAL). In the Target Properties, scroll down the IKM option list to FLOW_CONTROL, and ensure that it is set to “true”.

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

i. Click the Controls tab. Ensure that the selected CKM is CKM Oracle. The value for each Constraint should be “true”. Click Save to save your interface. Close your interface editor tab.

7. Change an HSQL Data Server setting, to deal with a batch processing compatibility issue between ODI and the latest version of HSQL Demo.

a. In Topology Manager, click the Physical Architecture tab, expand Technologies, expand Hypersonic SQL, and double-click the HSQL_LOCALHOST_20001 data server.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

b. In the Definition tab, increase Array Fetch Size and Batch Update Size from 30 to 300.

Click Save.

8. Run this interface, and check the content of the TRG_CITY table.

a. In the Designer Projects tab, right-click interface INT_8-3, and then select Execute.

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

b. Click OK in the Execution window, and then click OK when the “Session started”

message appears.

c. Open the Operator Navigator and verify that your interface session executed successfully:

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

Note: In Operator, you may need to click the Refresh button .

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

d. In the Designer navigator, open the Models tab. In the Oracle Sales Application model, right-click the TRG_CITY datastore and select the Data option. A window appears with the loaded data. Scroll down and view the total number of loaded records and loaded data.

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

license to use this Student Guideฺ

Practices for Lesson 8: ODI Interface Concepts

e. Verify the data, and then close this window.

Note: The POPULATION column should show thousands of inhabitants.

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

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

license to use this Student Guideฺ

In document ODI 11G hand book (Page 111-141)