Delving into Web Service through ODI

Today, enterprises are dealing with different data sources and targets, SaS to CRM, Data Warehouse, Social Media, etc.., Integration of data is at the heart of every initiative. Building a Data Warehouse should be initiated from both ETL as well as Data integration perspective. In order to fulfill this requirement, ODI comes into picture. ODI from Oracle is an ETL tool with Data integration capability. The primary focus of this blog is to integrate web services in ODI.

Working with Web services using Oracle Data Integrator:

Oracle Data Integrator Run-Time Services are web services that enable users to leverage Oracle Data Integrator features in a service-oriented architecture (SOA). These web services are invoked by a third-party application which manage the scenarios developed with Oracle Data Integrator.

Web Service Invocation through ODI:

Web Services can be invoked in Oracle Data Integrator packages or procedures using the OdiInvokeWebService tool. This tool allows you to invoke any third party web service, and save the response in a XML file that can be processed with Oracle Data Integrator.

OdiInvokeWebService tool:

The OdiInvokeWebService is a built-in tool in ODI to invoke a web service and to write the response to an XML file. The OdiInvokeWebService tool invokes a specific operation on a port of a web service, whose description file (WSDL) URL is provided. If this operation requires a SOAP request, it is provided either in a request file or in the tool command.

Pre-requisites:

ODI 11.1.1.9.0

Soap UI 5.2.1

Oracle 11g Express Edition

Step by Step process:

The SOAP Editor allows you to graphically build the XML request for the web service and display the response. A new SOAP project is created in the editor and an Employee file is imported into it.

Step 1: Create a new project named Test and import the WSDL file as shown below. We have the following operations in the sample file.

addGroups

getEmployeeCount

getEmployees

Step 2: Create a new SOAP Mock Service to get the response for each request. In our example, I have created a Mock Service named EmployeeDBBinding and the response for each operation can be seen below.

Step 3: Let us create a response for the getEmployees operation to fetch the Employee Details.

The response would be as below

25072, Vigneshpandi, Marimuthu.

25003, Bharath, Tadikamalla

Step 4: Make sure the following details are ready before placing the request

WSDL URL: The URL which points to the web service created and running on a particular port.

Operation Name: The Name of the operation for which the request and responses were defined. eg: getEmployees

Request: The request can be saved in a file or directly specified in the editor.

Port Type: The port type and port number on which the service runs.

Step 5: Open ODI and connect to the repository. Under the Project > Packages > Create a new Package and name it.

Step 6: Go to the diagram tab and on the left, you can find the various ODI tools listed. Click on the ODIInvokeWebService tool and drag it to the workflow area as shown below:

Step 7: Click on the tool and enter the details as mentioned below in the Properties section

WSDL URL	: http://INDIA-VIGNESHP:8088/mockEmployeeDBBinding
Port Type	:EmployeeDBPort
Operation	:getEmployees
XML Request	: Optional
Request File	: C:/Users/v.marimuthu/Desktop/employees_request.txt
Storage Mode for Response File	: New File
Response File	: C:/Users/v.marimuthu/Desktop/ODI_POC/employees_response.xml (location and name of the response file which will be created by ODI)
Response File Format	: XML

Step 8: Save the package and execute it.

Step 9: After successful execution, the XML response will be saved in the mention location as shown below:

XML to Database Transfer Using ODI:

Oracle Data Integrator Driver for XML (XML driver) handles an XML document as a JDBC data source. This allows Oracle Data Integrator to use XML documents as data servers. With this, we can query or make changes to the XML documents through standard SQL. It is necessary to generate the schema info from the XML file in the form of XSD (Extensive Schema Definition) file.

Creating Topology and XML Model Configuration:

We need to create a physical schema in ODI to represent xml. Each complex type in xml is considered as a table. Follow the below steps for configuration.

Step 1: Go to TOPOLOGY->Technologies->XML->New data server.

Provide data server name and click on the JDBC tab and select the XML driver.

In the URL tab, provide the URL in following format

jdbc:snps:xml?f=C:/Users/v.marimuthu/Desktop/ODI_POC/employees_response.xml&d=C:/Users/v.marimuthu/Desktop/ODI_POC/employees-response.xsd&ro=false&case_sens=true

Where,

f= the path to the directory where the XML response is present.

d= the path to the directory where the XSD is present.

Case_sens = Case Sensitive option (set to true)

Once the details are provided, test the connection and check if it is successful.

Step 2: Create a physical schema inside the data server and provide the name of the XML file as shown below

Step 3: Create a logical schema and attach it to the physical schema using Global Context and save it.

Step 4: Go to Designer->Model->New Model. Point to the relevant logical schema and click on the Reverse Engineer tab.

On the left, you can see the metadata of XML file in the form of Data Stores as below

Step 5: Define the target table properties and reverse engineer the metadata into ODI.

Mapping XML to DB:

Step 1: Once the source and target tables are reverse engineered, go to DESIGNER->INTERFACE->New Interface

Drag the source tables and target tables into their respective work areas and do the mapping

Step 2: Define the flow by selecting appropriate Knowledge Modules (LKM SQL to SQL and IKM SQL Control Append)

Target area will act as the staging by default.

Step 3: Execute the mapping and view the result in the target table.

The same process can be designed as a package below to capture the ETL operational statistics like ETL Start time, ETL End time, Load status, number of records extracted from each file, number of records loaded into each table

The objective of the auditing process like above is to help us

• To analyze the Quality of data that is loaded through the statistics on the number of rejected records.
• To analyze the punctuality of source systems through the time source was available.
• To maintain the incremental load process through an integer called Batch Load Number, instead of depending on the business transaction time

Once the above package executes successfully, the output will be like below.

The above implementation showcases one of the Data Integration capability of ODI. In subsequent blogs, we can have a detailed study regarding the other capabilities by delving further into the realm of ODI.