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.
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.
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.
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.
Choosing a Global Software Development Partner to Accelerate Your Digital Strategy
To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.
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|
|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
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.