Creating a Model Use Case Scenario: - Practices for Lesson 6: Overview Practices Overview

Practices for Lesson 6: Overview Practices Overview

Practice 6-1: Creating a Model Use Case Scenario:

John created a new ODI project and imported knowledge modules that will be used for the development of his integration project. Now, John needs to create new ODI Models and reverse-engineer all tables and files into the models.

Background:

In the previous practice, you configured the schemas containing the application data stored in the Oracle database. You now create the Oracle Sales Application model corresponding to this data and reverse-engineer the schemas’ data structures. You also reverse-engineer the structure of an XML file to a Geographic Information model that you define.

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. Start the ODI Demo source environment: Select Start > Programs > Oracle > Oracle Data Integrator > Shortcut to start the demonstration.If the Open File – Security Warning window appears, click Run. The “OracleDI Demo – Source” command shell window and two other command shell windows open.

The ODI startdemo demonstration environment provides databases that contain some of the data used in this course’s practices. Source and target data servers, as well as a demonstration repository data server, are started.

Note: You should keep these three command shell windows running (minimized) for all remaining lab practices in this course, along with the “localagent” command shell window.

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

license to use this Student Guideฺ

Practices for Lesson 6: Oracle Data Integrator Model Concepts

b. Click the New Model icon and then select New Model.

c. Specify (enter or select) the following parameters on the Definition tab:

• Name: Oracle Sales Application

• Code: ORACLE_SALES_APPLICATION

• Technology: Oracle

• Logical Schema: ORACLE_ORCL_LOCAL_SALES The Definition tab should appear as shown:

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. Click the Reverse Engineer tab, and select Development from the Context drop-down list. Click Save.

3. Reverse-engineer all the tables in this model.

a. Right-click Oracle Sales Application model and select the Reverse Engineer option. If the Confirmation window appears, click Yes.

Note: The progress of the reverse-engineering process is shown on the status bar.

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

license to use this Student Guideฺ

Practices for Lesson 6: Oracle Data Integrator Model Concepts

b. Verify the model reverse-engineered successfully. In the tree view, expand the Oracle Sales Application model. The datastores of the model appear.

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ฺ

Expand the TRG_CUSTOMER datastore, and then expand the Columns and Constraints nodes. The list of columns and constraints that were reverse-engineered for this table appear in the tree view. Check that the columns and constraints that were reverse- engineered for the TRG_CUSTOMER table correspond to its data definition language (DDL) given below. Close the tabs.

Create table TRG_CUSTOMER (

CUST_ID NUMERIC(10) not null,

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

license to use this Student Guideฺ

Practices for Lesson 6: Oracle Data Integrator Model Concepts

Note: The constraint called FK_SALES_CUST TRG_SALES is defined on another table.

As it references TRG_CUSTOMER, it also appears here.

4. Create a model for an XML file.

a. Click the New Model icon and then select New Model.

b. Specify (enter or select) the following parameters on the Definition tab:

• Name: Geographic Information

• Code: GEOGRAPHIC_INFORMATION

• Technology: XML

• Logical Schema: XML_DIM_GEO

c. The Definition tab should appear as below. Click the Reverse Engineer 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ฺ

d. Select Development from the Context drop-down list. Click Save.

5. Reverse-engineer all the tables in this model.

a. In the Models tree view, right-click Geographic Information model and select the Reverse Engineer option.

Note: The progress of the reverse-engineering process is shown on the status bar.

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

license to use this Student Guideฺ

Practices for Lesson 6: Oracle Data Integrator Model Concepts

b. Verify that the model reverse-engineered successfully. In the tree view, expand the Geographic Information model. The datastores of the model appear.

6. Open the XML file GEO_DIM.xml corresponding to the XML model that you have reverse- engineered. The file is at the following location:

C:\oracle\Middleware\Oracle_ODI1\oracledi\demo\xml a. Open Windows Explorer, and go to the

C:\oracle\Middleware\Oracle_ODI1\oracledi\demo\xml directory.

b. Right-click the GEO_DIM.xml file and select Open.

c. The XML file opens in Internet Explorer. The file structure appears as shown here:

The file hierarchy is GEOGRAPHY_DIM > country > region > city > and so on.

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

7. Compare the XML file content with the reverse-engineered structure in the Hierarchical view. In Designer’s tree view, expand the Geographic Information node, and then the Hierarchy node. Expand the datastores under this node.

The hierarchy of the XML file is reproduced here. The XML file has been mapped to a relational structure, with foreign keys to map the links between the different levels of hierarchy.

Close the Geographic Information model tab .

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

license to use this Student Guideฺ

In document ODI 11G hand book (Page 87-97)