Wednesday Oct 08, 2014

Meet the Data Integration Winners of 2014 Oracle Excellence Awards for Fusion Middleware Innovation

Last Tuesday evening we had a special celebration at Oracle OpenWorld. In an Oscars-like ceremony we announced the winners of the 2014 Oracle Excellence Awards for Fusion Middleware Innovation. You can find an overview of the Fusion Middleware Innovation Awards Ceremony and the full list of winners across all 12 categories in the following blog post: And The Winners Are....

In this blog post I would like to introduce you the 2014 Fusion Middleware Innovation Award winners in the Data Integration category: NET Serviços and Griffith University.

NET Serviços is Latin America’s largest multi-service cable company and enjoyed a steep growth in its business. However the existing system architecture was not supporting this business growth, and the new product areas and business models they were expanding to.  Net Serviços team, led by Robin Michael Gray, built a single corporate real-time integration platform in a standards-based private cloud architecture with high level of maturity of standardization and architecture documentation. They implemented the private cloud environment on Oracle Exadata and Oracle Exalogic, and for enterprise-wide real-time integration they are using Oracle Data Integrator (ODI), Oracle GoldenGate and Oracle SOA Suite. This platform enabled:

  • 60% reduction in development time
  • 50% reduction in management costs via native solutions for monitoring and error handling
  • 80% reduction in data quality problems via data validation as part of integration
  • Increased agility in identifying and fixing problems due to very innovative ODI data quality firewall and Oracle GoldenGate "error hospital" 

Griffith University is based in Australia, and is one of the most influential universities in the Asia-Pacific region. Bruce Callow, chief technology officer for Griffith University, and his team saw the opportunity to improve student experience and attract more enrollment by building a modern web and mobile interface. Griffith University deployed Oracle Data IntegratorOracle SOA Suite and Oracle Service Bus with the help of their partner Integral to build a real-time data hub that supports key applications such as Oracle RightNow Service Cloud and allows a new web and mobile interface for students. The solution also reduced the load on the core Oracle PeopleSoft system during peak load windows. With this solution Griffith University:

• Achieved unified view of students by using Oracle Data Integrator, Oracle SOA Suite, and Oracle Service Bus

• Improved visibility on the state of demand for courses and to optimize enrollments and course scheduling in real time to best meet that demand

• Increased student satisfaction in enrollment process from 20% to 86%.

You can learn more about Griffith University's solution here: Griffith University Manages Enrollment Peaks 4x Faster—Boosts Student Satisfaction and Retention Rate

Congratulations to Net Serviços and Griffith University with their partner Integral for winning this year's Oracle Excellence Awards for usion Middleware Innovation in Data Integration category! 

Tuesday Oct 02, 2012

Fast Data: Go Big. Go Fast.

For those of you who may have missed it, today’s second full day of Oracle OpenWorld 2012 started with a rumpus. Joe Tucci, from EMC outlined the human face of big data with real examples of how big data is transforming our world. And no not the usual tried-and-true weblog examples, but real stories about taxi cab drivers in Singapore using big data to better optimize their routes as well as folks just trying to get a better hair cut. Next we heard from Thomas Kurian who talked at length about the important platform characteristics of Oracle’s Cloud and more specifically Oracle’s expanded Cloud Services portfolio. Especially interesting to our integration customers are the messaging support for Oracle’s Cloud applications. What this means is that now Oracle’s Cloud applications have a lightweight integration fabric that on-premise applications can communicate to it via REST-APIs using Oracle SOA Suite. It’s an important element to our strategy at Oracle that supports this idea that whether your requirements are for private or public, Oracle has a solution in the Cloud for all of your applications and we give you more deployment choice than any vendor.

If this wasn’t enough to get the juices flowing, later that morning we heard from Hasan Rizvi who outlined in his Fusion Middleware session the four most important enterprise imperatives: Social, Mobile, Cloud, and a brand new one: Fast Data. Today, Rizvi made an important step in the definition of this term to explain that he believes it’s a convergence of four essential technology elements:

  • Event Processing for event filtering, business rules – with Oracle Event Processing
  • Data Transformation and Loading - with Oracle Data Integrator
  • Real-time replication and integration – with Oracle GoldenGate
  • Analytics and data discovery – with Oracle Business Intelligence

Each of these four elements can be considered (and architect-ed) together on a single integrated platform that can help customers integrate any type of data (structured, semi-structured) leveraging new styles of big data technologies (MapReduce, HDFS, Hive, NoSQL) to process more volume and variety of data at a faster velocity with greater results. 

Fast data processing (and especially real-time) has always been our credo at Oracle with each one of these products in Fusion Middleware. For example, Oracle GoldenGate continues to be made even faster with the recent 11g R2 Release of Oracle GoldenGate which gives us some even greater optimization to Oracle Database with Integrated Capture, as well as some new heterogeneity capabilities. With Oracle Data Integrator with Big Data Connectors, we’re seeing much improved performance by running MapReduce transformations natively on Hadoop systems. And with Oracle Event Processing we’re seeing some remarkable performance with customers like NTT Docomo. Check out their upcoming session at Oracle OpenWorld on Wednesday to hear more how this customer is using Event processing and Big Data together.

If you missed any of these sessions and keynotes, not to worry. There's on-demand versions available on the Oracle OpenWorld website. You can also checkout our upcoming webcast where we will outline some of these new breakthroughs in Data Integration technologies for Big Data, Cloud, and Real-time in more details.

Wednesday Sep 26, 2012

Data-Driven SOA with Oracle Data Integrator

By Mike Eisterer,

Data integration is more than simply moving data in bulk or in real-time, it is also about unifying information for improved business agility and integrating it in today’s service-oriented architectures. SOA enables organizations to easily define services which may then be discovered and leveraged by varying consumers. These consumers may be applications, customer facing portals, or complex business rules which are assembling services to automate process. Data as a foundational service provider is a key component of today’s successful SOA implementations.

Oracle offers the broadest and most integrated portfolio of products to help you define, organize, orchestrate and consume data services.

If you are attending Oracle OpenWorld next week, you will have ample opportunity to see the latest Oracle Data Integrator live in action and work with it yourself in two offered Hands-on Labs. Visit the hands-on lab to gain experience firsthand:



Oracle Data Integrator and Oracle SOA Suite: Hands-on- Lab (HOL10480)

Wed Oct 3rd 11:45AM Marriott Marquis- Salon 1/2


To learn more about Oracle Data Integrator, please visit our Introduction Hands-on LAB:

Introduction to Oracle Data Integrator (HOL10481)

Mon Oct 1st 3:15PM, Marriott Marquis- Salon 1/2

If you are not able to attend OpenWorld, please check out our latest resources for Data Integration.

Tuesday Apr 24, 2012

Untangle Your Business with Oracle's Unified Data Integration and SOA

Who ever said enterprise integration - services, application or data was easy?  All the more reason, a combination approach consisting of application and data integration technologies is critical to integration success.  Combining Oracle Data Integrator and SOA Suite provides best-in-class technologies that work together in a unified manner.  ODI provides complex data integration, bulk data movement and data transformation for the underlying data, while SOA Suite links multiple,individual services to build a business process.  Tune into out webcast on April 24th 10 AM PT and hear how Oracle Data Integrator and Oracle SOA Suite together, are integral to a successful integration strategy.  Learn about ways to:

  • Improve productivity through unified and standards-based design time tooling
  • Lower TCO by optimizing business processes to run faster and more efficiently
  • Simplify deployment and management on a single runtime platform architecture, built for reliability and elasticity

Register now at

Untangle your business with unified communications

Monday Oct 10, 2011

Recap: Accomplish Application Integration with Ease Using Oracle Data Integration

The 2011 Oracle OpenWorld conference has come to a close and it was a great one indeed.  Awe-inspiring sessions, packed to full capacity HOLs and tremendous buzz and interest at the Data Integration demo pods.  In case you missed it, there was a very interesting session on Thursday morning that covered application integration with a twist.  The twist is that the session covered use cases for Oracle Data Integration as it pertained to achieving application integration. 

Here's a run down the uses cases that were covered.  

The five uses cases of application integation include: Bulk Data Processing for Application Migrations/Upgrades (Oracle Data Integrator), Zero Downtime Migrations/Upgrades (Oracle GoldenGate), Query Offloading (Oracle GoldenGate), SOA-Based Bulk Data Movement (SOA, ODI), and SOA-Based Bulk Data Transformation (SOA, ODI) .

  • Bulk Data Processing for Application Migrations/Upgrades: Oracle Data Integrator is used to perform data layer integration and move large volumes of data in bulk and execute data transformations. This scenario is for pure data integration with no process or services level integration involved. 
  • Zero Downtime Migrations/Upgrades: Many organziations are often looking to upgrade to the most recent version of their particular application, however these upgrade processes typically require downtime, which can disrupt business operations.  Oracle GoldenGate removes the need to make invasive changes to the source and target systems by seamlessly moving data in real-time, and phased upgrades can be implemented in a dramatically reduced timeframe. 
  • Query Offloading: Oracle GoldenGate enables real-time reporting off of a lower cost sytem by keeping a reporting database in sync with the original OLTP system. GoldenGate can move only the data that is required for the purpose of reporting, and heterogeneity assures the use of low cost systems that offload query execution from OLTP systems. 
  • SOA-based Bulk Data Movement and Transformation: SOA and Oracle Data Integrator work together to accomplish application integration, with each product focused on what they do best.  ODI is used for the "heavy data lifting and transformations" and is invoked by Oracle BPEL Process Manager or Oracle Service Bus.  Upon completing the data transformations, ODI sends back a response to Oracle BPEL PM and Oracle Service Bus which in turns completes the process level integration.  In addition, Oracle SOA Suite and ODI share the same design-time environment in JDeveloper.  The benefits of this use case for application integration include: reduced cost of building and maintaining separate integration services and data integrator provides a unified, logical view of the data and enables bulk database/data warehouse loading versus event-by-event loading. 

Application Integration is completely addressed when BOTH process-level and data integration are tackled together.  Proceeding with one integration over another solves only a portion of the problem and covers only a part of the application architecture.  Data is integral to applications as much as exposing application components for integration.  As a result, the combination of both data integration and process integration can integrate any resource, whether a web service, data persisted in the database, data in a flat file, business logic in a C application or transactions on a mainframe system. 

Monday Jun 20, 2011

Oracle Data Integrator Complex Files as Sources and Targets


ODI adds the new Complex File technology for use with file sources and targets. The goal is to read or write file structures that are too complex to be parsed using the existing ODI File technology. This includes:

    • Different record types in one list that use different parsing rules
    • Hierarchical lists, for example customers with nested orders
    • Parsing instructions in the file data, such as delimiter types, field lengths, type identifiers
    • Complex headers such as multiple header lines or parseable information in header
    • Skipping of lines
    • Conditional or choice fields

Similar to the ODI File and XML File technologies, the complex file parsing is done through a JDBC driver that exposes the flat file as relational table structures. Complex files are mapped to one or more table structures, as opposed to the (simple) file technology, which always has a one-to-one relationship between file and table. The resulting set of tables follows the same concept as the ODI XML driver, table rows have additional PK-FK relationships to express hierarchy as well as order values to maintain the file order in the resulting table.


The parsing instruction format used for complex files is the nXSD (native XSD) format that is already in use with Oracle BPEL. This format extends the XML Schema standard by adding additional parsing instructions to each element. Using nXSD parsing technology, the native file is converted into an internal XML format. It is important to understand that the XML is streamed to improve performance; there is no size limitation of the native file based on memory size, the XML data is never fully materialized. The internal XML is then converted to relational schema using the same mapping rules as the ODI XML driver.

How to Create an nXSD file

Complex file models depend on the nXSD schema for the given file. This nXSD file has to be created using a text editor or the Native Format Builder Wizard that is part of Oracle BPEL. BPEL is included in the ODI Suite, but not in standalone ODI Enterprise Edition. The nXSD format extends the standard XSD format through nxsd attributes. NXSD is a valid XML Schema, since the XSD standard allows extra attributes with their own namespaces.

The following is a sample NXSD schema blog.xsd:

<?xml version="1.0"?>

<xsd:schema xmlns:xsd="" xmlns:nxsd="" elementFormDefault="qualified" xmlns:tns="" targetNamespace="" attributeFormDefault="unqualified"

nxsd:encoding="US-ASCII" nxsd:stream="chars" nxsd:version="NXSD">

<xsd:element name="Root">


<xsd:element name="Header">


<xsd:element name="Branch" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=","/>

<xsd:element name="ListDate" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}"/>



<xsd:element name="Customer" maxOccurs="unbounded">


<xsd:element name="Name" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=","/>

<xsd:element name="Street" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," />

<xsd:element name="City" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />






The nXSD schema annotates elements to describe their position and delimiters within the flat text file. The schema above uses almost exclusively the nxsd:terminatedBy instruction to look for the next terminator chars. There are various constructs in nXSD to parse fixed length fields, look ahead in the document for string occurences, perform conditional logic, use variables to remember state, and many more.

nXSD files can either be written manually using an XML Schema Editor or created using the Native Format Builder Wizard. Both Native Format Builder Wizard as well as the nXSD language are described in the Application Server Adapter Users Guide. The way to start the Native Format Builder in BPEL is to create a new File Adapter; in step 8 of the Adapter Configuration Wizard a new Schema for Native Format can be created:


The Native Format Builder guides through a number of steps to generate the nXSD based on a sample native file. If the format is complex, it is often a good idea to “approximate” it with a similar simple format and then add the complex components manually. The resulting *.xsd file can be copied and used as the format for ODI, other BPEL constructs such as the file adapter definition are not relevant for ODI. Using this technique it is also possible to parse the same file format in SOA Suite and ODI, for example using SOA for small real-time messages, and ODI for large batches.

This nXSD schema in this example describes a file with a header row containing data and 3 string fields per row delimited by commas, for example blog.dat:

Redwood City Downtown Branch, 06/01/2011
Ebeneezer Scrooge, Sandy Lane, Atherton
Tiny Tim, Winton Terrace, Menlo Park

The ODI Complex File JDBC driver exposes the file structure through a set of relational tables with PK-FK relationships. The tables for this example are:

Table ROOT (1 row):


Primary Key for root element


Name of the file


Path of the file


Date of load

Table HEADER (1 row):


Foreign Key to ROOT record


Order of row in native document




Order of Branch within row




Order of ListDate within row

Table CUSTOMER (2 rows):


Foreign Key to ROOT record


Order of rows in native document




Oder of Name within row




Order of Street within row




Order of City within row

Every table has PK and/or FK fields to reflect the document hierarchy through relationships. In this example this is trivial since the HEADER and all CUSTOMER records point back to the PK of ROOT. Deeper nested documents require this to identify parent elements. All child element tables also have a order field (HEADERORDER, CUSTOMERORDER) to define the order of rows, as well as order fields for each column, in case the order of columns varies in the original document and needs to be maintained. If order is not relevant, these fields can be ignored.

How to Create an Complex File Data Server in ODI

After creating the nXSD file and a test data file, and storing it on the local file system accessible to ODI, you can go to the ODI Topology Navigator to create a Data Server and Physical Schema under the Complex File technology.


This technology follows the conventions of other ODI technologies and is very similar to the XML technology. The parsing settings such as the source native file, the nXSD schema file, the root element, as well as the external database can be set in the JDBC URL:


The use of an external database defined by dbprops is optional, but is strongly recommended for production use. Ideally, the staging database should be used for this. Also, when using a complex file exclusively for read purposes, it is recommended to use the ro=true property to ensure the file is not unnecessarily synchronized back from the database when the connection is closed. A data file is always required to be present at the filename path during design-time. Without this file, operations like testing the connection, reading the model data, or reverse engineering the model will fail.

All properties of the Complex File JDBC Driver are documented in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator in Appendix C: Oracle Data Integrator Driver for Complex Files Reference.

David Allan has created a great viewlet Complex File Processing - 0 to 60 which shows the creation of a Complex File data server as well as a model based on this server.

How to Create Models based on an Complex File Schema

Once physical schema and logical schema have been created, the Complex File can be used to create a Model as if it were based on a database. When reverse-engineering the Model, data stores(tables) for each XSD element of complex type will be created. Use of complex files as sources is straightforward; when using them as targets it has to be made sure that all dependent tables have matching PK-FK pairs; the same applies to the XML driver as well.

Debugging and Error Handling

There are different ways to test an nXSD file. The Native Format Builder Wizard can be used even if the nXSD wasn’t created in it; it will show issues related to the schema and/or test data. In ODI, the nXSD will be parsed and run against the existing test XML file when testing a connection in the Dataserver. If either the nXSD has an error or the data is non-compliant to the schema, an error will be displayed.

Sample error message:

Error while reading native data.
[Line=1, Col=5] Not enough data available in the input, when trying to read data of length "19" for "element with name D1" from the specified position, using "style" as "fixedLength" and "length" as "". Ensure that there is enough data from the specified position in the input.

Complex File FAQ

Is the size of the native file limited by available memory?
No, since the native data is streamed through the driver, only the available space in the staging database limits the size of the data. There are limits on individual field sizes, though; a single large object field needs to fit in memory.

Should I always use the complex file driver instead of the file driver in ODI now?
No, use the file technology for all simple file parsing tasks, for example any fixed-length or delimited files that just have one row format and can be mapped into a simple table. Because of its narrow assumptions the ODI file driver is easy to configure within ODI and can stream file data without writing it into a database. The complex file driver should be used whenever the use case cannot be handled through the file driver.

Should I use the complex file driver to parse standard file formats such as EDI, HL7, FIX, SWIFT, etc.? 
The complex file driver is technically able to parse most standard file formats, the user would have to develop an nXSD to parse the expected message. However, in some instances the use case requires a supporting infrastructure, such as message validation, acknowledgement messages, routing rules, etc. In these cases products such as Oracle B2B or  Oracle Service Bus for Financial Services will be better suited and could be combined with ODI.

Are we generating XML out of flat files before we write it into a database?
We don’t materialize any XML as part of parsing a flat file, either in memory or on disk. The data produced by the XML parser is streamed in Java objects that just use XSD-derived nXSD schema as its type system. We use the nXSD schema because is the standard for describing complex flat file metadata in Oracle Fusion Middleware, and enables users to share schemas across products.

Is the nXSD file interchangeable with SOA Suite?
Yes, ODI can use the same nXSD files as SOA Suite, allowing mixed use cases with the same data format.

Can I start the Native Format Builder from the ODI Studio?
No, the Native Format Builder has to be started from a JDeveloper with BPEL instance. You can get BPEL as part of the SOA Suite bundle. Users without SOA Suite can manually develop nXSD files using XSD editors.

When is the database data written back to the native file?
Data is synchronized using the SYNCHRONIZE and CREATE FILE commands, and when the JDBC connection is closed. It is recommended to set the ro or read_only property to true when a file is exclusively used for reading so that no unnecessary write-backs occur.

Is the nXSD metadata part of the ODI Master or Work Repository?
No, the data server definition in the master repository only contains the JDBC URL with file paths; the nXSD files have to be accessible on the file systems where the JDBC driver is executed during production, either by copying or by using a network file system.

Where can I find sample nXSD files?
The Application Server Adapter Users Guide contains nXSD samples for various different use cases.

Tuesday May 24, 2011

What’s New in the Data Integration Market?

Data integration is essential to many strategic IT initiatives including master data management (MDM), modernization, and service-oriented architecture (SOA). That’s why it is not surprising that the data integration market continues to grow in its size, importance and impact on enterprise information systems. The technology has evolved from simple extract, transform, and load (ETL) procedures to include real time data movement, bi-directional replication/synchronization along with data quality and data profiling and data services.

 The user base for data integration tools has also evolved from low-level technology for developers and DBAs to high-level environments for operations staff, data stewards, business analysts and enterprise architects. In short, what was once merely the background “plumbing” underlying our information systems now influences just about every aspect of IT, and touches just about every type of stakeholder.

 In a new white paper we have examined the state of the data integration market including the trends in business intelligence, data warehousing, data quality, consolidation, cloud computing and IT modernization initiatives that are driving data-intensive projects. You can access this brand new white paper along with other data integration focused white papers and resources here.


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« June 2016