Developing an ODI XML to Database Transformation Using Interface with ODI Constraint

Purpose

This OBE tutorial describes and shows you how to create an Oracle Data Integrator (ODI) interface that imports an XML file into a database table called CLIENT. This process also involves adding an ODI constraint to the CLIENT model. Any records that do not pass the constraint are loaded to the E_CLIENT table.

Time to Complete

Approximately 1 hour

Topics

This OBE tutorial covers the following topics:

 Overview
 Scenario
 Verifying the Prerequisites
 Creating a New Project in ODI
 Inserting a New Model for the XML File
 Inserting a Model for the Database Table CLIENT
 Deleting the Constraint Copy of CITY_CLIENT
 Setting Up ODI Constraint on CLIENT Data Store
 Importing the Knowledge Modules
 Creating a New Interface to Load the XML File
 Executing the Package and Testing the Results
 Viewing Constraint Errors from Interface Execution
 Summary
 Related information

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots do not reflect the specific environment that you use. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

Oracle Data Integrator provides the ability to validate data during the load to the target using ODI constraints or database constraints. When this data is checked against a constraint using Flow Control, any errors that are found are not loaded to the target but are loaded to an errors table that is created and managed by ODI. Each row of this table represents a record that did not pass a constraint, and includes a message column that explains why the records were rejected.
This table can be edited within the ODI Designer itself or by any other tool that can edit relational tables. However, this is not always a convenient way for the end user or business user to edit the data. This example uses an ODI interface. Alternatively, you can build an ODI procedure that loads an XML file with a constraint.

Back to Topic List

Scenario

Linda works as a database administrator for Mydo Main Corporation. In Mydo Main, Linda is responsible for performing database management and integration tasks on various resources within the organization. She needs to build an ODI Scenario that will load an XML file to a relational table with a constraint. The constraint will allow only data that passes the check constraint validation to be loaded. Any rows that do not pass the constraint will be loaded to a table on the target database. The ODI Scenario will be executed, and after the ODI data integration process is finished, the ODI Scenario will then call back to a BPEL Web service to notify of any errors during the load.

 

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

Software Requirements

The system should include the following installed products:

The system should have the following configuration:

  • odi-public-ws.aar deployed in Axis2. Use the Upload Service link on the Axis2 administration page to upload this file. (Download from here.)
  • ODI Master and Work repository created. You can refer the steps from here.
  • xml, xtd, csv files for the examples. For example, the GEO_DIM.xml file included with this sample has much data and is approximately 8 MB to demonstrate using a moderately sized XML file with ODI and BPEL. You can download the complete set of demo/xml files from here.
  • Schema ( ODI_STAGE) for the target tables of the exercise and a data server and physical schema for this schema within the ODI Topology Manager. To do this, perform the following steps:
    • Start the Oracle Database 10g XE Administrative Console.
    • Log in using system as the username and oracle1 as the password.
    • From the Enterprise Manager Console, click Administration > Database Users > Create Users.
    • Enter ODI_STAGE as the username and oracle1 as the password.
    • Select the DBA check box and click Create. Note that ODI_STAGE has been created.
    • Select Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager to open ODI Topology Manager.
    • Scroll down to the left bottom pane to click the " The physical architecture..." tab.
    • At the top-left corner, in the Physical Architecture pane, click " +" to expand Technologies > Oracle.
    • Right-click Oracle and select Insert data server.
    • The "Data server: new" window appears. Enter Local_XE as the name, XE as Instance / dblink (Data Server), ODI_STAGE as the user, and oracle1 as the password. Next, click the JDBC tab.
    • For JDBC Driver value, click Browse and select Oracle JDBC Driver from the Name drop-down list. Then click OK.
    • For JDBC URL, click Browse and select jdbc:<hostname>:<port>:<sid>, and then click OK. Populate the values as per your environment details.
    • Click Test, and then OK.
    • Click OK in the " Data server: new" window.
    • In the Physical Architecture pane, click " +" to expand Technologies > Oracle > Local_XE.
    • In the Physical Schema: Local_XE.Schema window, under the Definitions tab, select ODI_STAGE in the Schema (Schema) and in the Schema (Work Schema) drop-down lists..
    • Click the Context tab.
    • Click Add context. Select Global in the Context drop-down list, and enter ODI_STAGE as the Logical schema.
    • Click OK.

    If not done before, you need to start the services and components for Database 10g XE, Oracle Data Integrator 10g(10.1.3.4), and Oracle SOA Suite 10g.

Back to Topic List

Creating a New Project in ODI


A project is a collection of ODI objects that have been created by users. It should encompass a single functional domain. Each project contains its own knowledge modules, variables, markers, and other types of objects. These objects can be shared between different projects by duplicating them. You can also use global objects such as variables or sequences to define parameters that are common to all projects. Use a project to represent a functional domain in an integration process or in an integration project that you need to develop with ODI.

To create a new project in ODI, perform the following steps:

1.

Select Start > All Programs > Oracle > Oracle Data Integrator > Designer to open ODI Designer.

 

2.

The Oracle Data Integrator Login dialog box appears. From the Login Name drop-down list, select Repo_designer.

 

3.

The default username and password are SUPERVISOR and SUNOPSIS, respectively. Click OK to log in.

 

4.

In the bottom left pane, click the Projects tab.

 

5.

Notice that the Projects pane appears in the top-left corner. RIght-click and select Insert Project.

 

6.

The Project: New window is displayed. Enter ODI_ER_HSP as the name. Notice that the Code value is also populated with the same text. Then, click OK to view the new project populated in the Projects pane.

 

 

Back to Topic List

Inserting a New Model for the XML File

To insert a new model for the XML source file, perform the following steps:

1.

In the ODI Designer, scroll to the bottom-left pane and click the Models tab.

 

 

2.

The Models pane appears at the top-left corner. Right-click and select Insert Model.

 

3.

In the Model: New window, you need to enter the values for the new model. For this, provide the following parameters, and then click the Reverse tab.

Parameter Value
Name SRC_GEO_DIM_XML
Technology XML
Logical Schema XML_DEMO_GEO

 

4.

On the Reverse tab, from the Context drop-down list, select Global. Then scroll down and click Reverse. This generates the XML source.

 

 

5.

Click Yes to confirm. Then, click OK.

 

6.

In the Models pane, you can notice the new data store being populated. Click the " +" sign to expand and view the SRC_GEO_DIM_XML model.

 

Back to Topic List

 

Inserting a Model for the Database Table CLIENT

A relational logical schema is needed for this task. We already set up a new schema within Oracle XE named, ODI_DATA. A new physical schema was also set up within the topology manager for the ODI_DATA schema. To insert a model for the CLIENT table, perform the following steps:

1.

Click Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager to open ODI Topology Manager.

 

2.

The Security Repository Connections dialog box appears. From the Login Name drop-down list, select repository, and then click OK.

Note : The default username and password are SUPERVISOR and SUNOPSIS, respectively.

 

3. Scroll down to the bottom-left pane to click the " The physical architecture..." tab.

 

 

4.

At the top-left corner, under the Physical Architecture pane, click " +" to expand Technologies > Oracle > Local_XE > Local_XE_ODI_STAGE.

 

 

5.

Right-click the Local_XE_ODI_STAGE schema and select Edit.

 

6.

In the Physical Schema: Local_XE_ODI_STAGE window, on the Definition tab, change the value for Errors from E$_ to E$, and then click OK.

 

 

7.

In ODI Designer, click the Models tab.

 

 

8.

In the Models pane, right-click and select Insert Model.

 

 

9.

In the Model: New window, provide the following values:

Parameter Value
Name RDBMS_TARGET
Technology Oracle
Logical Schema ODI_STAGE

 

 

 

10.

Click the Reverse tab. From the Context drop-down list, select Global, and then click OK.

 

 

11.

In the Models pane, click " +" to expand RDBMS_TARGET and SRC_GEO_DIM_XML.

 

 

12.

Under SRC_GEO_DIM_XML, right-click CLIENT and select Duplicate.

 

 

13.

Click Yes.

 

 

14.

Drag the new duplicate Copy of CLIENT from the SRC_GEO_DIM_XML model to the RDBMS_TARGET model. Next, right-click the Copy of CLIENT data store within RDBMS_Target and click Edit.

Note : If an Object Locking dialog box appears, click No.

 

15.

In the Data Store: Copy of Client window, change the name to CLIENT and click OK.

Note : No physical table is created on RDBMS_TARGET. When the interface is run, it creates the CLIENT physical table.

 

 

Back to Topic List

 

Deleting the Constraint Copy of CITY_CLIENT

During the duplication of the CLIENT data store, the constraint for the XML file was also copied. To delete this constraint, perform the following steps:

1.

In ODI Designer, under the Models pane, click " +" to expand RDBMS_TARGET > CLIENT > Constraints > CITY Copy of CITY_CLIENT.

 

 

2.

Right-click CITY Copy of CITY_CLIENT and select Delete. Click Yes to confirm.

 

Back to Topic List

 

Setting Up ODI Constraint on CLIENT Data Store

To set up ODI constraint on CLIENT data store, perform the following steps:

1.

In ODI Designer, expand RDBMS_TARGET > CLIENT > Constraints. Then right-click Constraints and select Insert Condition.

 

 

2.

In the Condition: New window, enter the following values, and then click OK:

Parameter Value
Name AgeConstraint
Type Oracle Data Integrator
Where cast(Client.age as numeric) >=21
Message Client must be at least 21 years old

 

Back to Topic List

 

Importing the Knowledge Modules

Before creating the interface, LKM and IKM that are needed to load and integrate the XML data are imported into the project. To do this, perform the following steps:

1.

In ODI Designer, click the Projects tab.

 

2.

Click " +" to expand ODI_ER_HSP > Knowledge Modules > Loading.

 

 

3. Right-click Loading and select Import Knowledge Modules.

 

 

4.

The Import Knowledge Modules...(XML FIle) dialog box is displayed. Click " ..." in the File import directory field. Browse to I:\ODI\oracledi\impexp and click OK.

 

 

5.

In the "Select the file(s) to import" section, scroll down to select LKM SQL to SQL and click OK.

 

6.

Similarly, for the following options under Knowledge Modules, select the highlighted values.

Parameter Value
Integration IKM SQL Control Append
Check CKM SQL

The final expanded Knowledge Modules will look as follows:

 

 

Back to Topic List

 

Creating a New Interface to Load the XML File

To create a new interface to load the XML file, perform the following steps:

1.

In ODI Designer, in the Projects pane, click " +" to expand ODI_ER_HSP > First Folder > Interfaces.

 

2.

Right-click Interface and select Insert Interface.

 

3.

The Interface: New dialog box appears. On the Definition tab, enter the following values:

Parameter Value
Name INT_LOAD_TBL_CLIENT
Staging Area Different From Target Select this option.
Memory SUNOPSIS_MEMORY_ENGINE

 

4.

Click the Diagram tab. Next, click the Models tab to drag the source and target to the ODI designer. Drag the CLIENT data store from the SRC_GEO_DIM_XML into the Source container. Drag the CLIENT data store from the RDBMS_TARGET into the Target container.

 

 

5.

Click Yes.

 

6.

Scroll down to select the Key check box.

 

 

7.

Click the Flow tab to view the execution flow.

 

 

8.

In the Target (XML_GEO_DIM) dialog box, select Yes from the RECYCLE_ERRORS drop-down list.

 

 

9.

From the CREATE_TARG_TABLE drop-down list, select Yes.

 

 

10.

Click the Controls tab and scroll down to click Apply.

Note : If an Object Locking dialog box appears, click No.

 

Back to Topic List

Executing the Package and Testing the Results

To execute the package, perform the following steps:

1.

Click Execute.

 

 

2.

In the Execution dialog box, leave the default values and click OK, and then click OK again.

 

3.

Select Start > All Programs > Oracle > Oracle Data Integrator > Operator to open ODI Operator. From the Login Name drop-down list, select Repo_designer and click OK.

Note : The default username and password are SUPERVISOR and SUNOPSIS, respectively.

 

4.

In the bottom-left pane, click the Hierarchical Sessions tab. Scroll up in the pane and click " +" to expand Date > <The date of execution> Steps > <Load Step>.

Notice the detailed steps for the execution of the load. It confirms that the ODI interface imports an XML file into a database table called CLIENT.

 

Back to Topic List

 

Viewing Constraint Errors from Interface Execution

To view or constraint errors from the interface execution, perform the following steps:

1.

In ODI Designer, under RDBMS_TARGET > CLIENT, right-click to select Control > Errors.

 

 

2.

In the Error:CLIENT dialog box, view the values. The E_CLIENT table on the target schema can now be updated, and on subsequent executions of the interface, the rows from the E_CLIENT table are recycled by ODI. If the rows now pass the constraints, they will be loaded to the target. Click OK.

 

 

Back to Topic List

 

Summary

In this lesson, you learned how to:

Back to Topic List

 Verify the prerequisites
 Create a new project in ODI
 Insert a new model for the XML file
 Insert a model for the database table CLIENT
 Delete constraint copy of CITY_CLIENT
 Set up ODI constraint on the CLIENT data store
 Import the knowledge modules
 Create a new interface to load the XML file
 Execute the package and test the results
 View constraint errors from interface execution

Related Information

 To ask a question about this OBE tutorial, post a query on the OBE Discussion Forum.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document