Oracle Data Integrator (ODI) is Oracle’s data integration platform engineered to provide fast and easy data transformation and data integration processes, through E-LT (Extract – Load Transform) mechanism, between different technologies. For example, using ODI’s transformation mechanism I can create daily snapshots of my data residing in an Oracle DB and store these snapshots in a XML format. These XML snapshot files can serve as XML payloads for an external web-service to consume this data. Alternatively, I can use the same mechanism to create file-system based daily backups of my data in a defined XML format. Oracle DB and XML are just two of the many technologies that can be used within ODI to do data transformations.
This blog assumes you have some basic prior knowledge about the ODI tool. If you are new to ODI, you can refer to the ODI Tutorial for a quick introduction or the ODI Develop Guide for comprehensive details on the specific features of the ODI tool mentioned below. For this demonstration, I am using the sample HR schema within Oracle XE as my Oracle DB source. I will be doing a data-dump from the Employees table of this schema to a XML document.
1. Create Oracle Physical and Logical Architecture.
Under Topology, create your Oracle Data Server, its physical schema, and its logical schema.
2. Prepare your XSD and sample XML document.
Create the XSD for the format you will want your data to be displayed in your XML.
3. Create XML Physical and Logical Architecture.
Under Topology, create your XML Data Server, its physical schema, and its logical schema.
There is a known issue with ODI 11.1.1, 40.4.1 SQL Exception “Unknown Token” appears when using Complex File or XML as Staging Area. Details can be found here. Based on the workaround provided by Oracle, changes have been highlighted in the image below.
4. Create Oracle DB Model.
Under Designer, in the Models section, create your Oracle DB Model, using the Oracle Logical schema you already created.
5. Create XML Model.
Under Designer, in the Models section, create your XML Model, using the XML Logical schema you already created.
6. Create Project.
Now that you have your Models created, you have to create a new ODI project to be able to create your Interface to do the ELT, using those Models. Under Designer, in the Projects section, create a new Project.
7. Import Knowledge Modules.
The two KMs you will need for the ELT are LMK SQL to SQL and IKM XML Control Append. Import them into the project. Full description of these KMs can be found in the Dev Guide mentioned earlier.
8. Create Interface.
You will need to create your Interface to map data-elements between your Source and Target Models. Under your project, under Interfaces, create a new Interface.
On your Interface, in the Mapping section, design your mappings between the Source and Target Models.
On your Interface, in the Flow section, select your Source (Oracle DB) and assign the LMK SQL to SQL.
On your Interface, in the Flow section, select your Staging Area and assign the IKM XML Control Append and set the CREATE_XML_FILE = true.
On your Interface, in the Flow section, select your Target (XML) and assign the IKM XML Control Append and set the CREATE_XML_FILE = true.
9. Save All and Execute Interface.
Save All and you are done. When you execute your Interface, your XML document (Target) with the data from your Oracle DB table (Source) should be created.
