Oracle Data Integrator (ODI) – Data Integration Strategy

In the ODI CAB Meeting held during Open World we’ve heard a lot of new insights regarding ODI Suite and several cases.

To sum up the main differentiators of ODI when talking about a Data Integration Strategy:

  • ODI Suite: Data Delivery Services by usage of ESB, the mediator
  • ODI Suite: Orchestration of composite services through Oracle BPEL Process Manager integration
  • Data Profiling & Data Quality: end to end governance, statistical analysis, cleansing of data and prevention of bad data being loaded
  • Unified workflows using knowledge modules
  • Declarative design using only databases and pl/sql, no need for other technologies
  • Datamart to deliver star schema’s
  • Hyperion integration for fast querying
  • Webservices to integrate to third party applications, e.g. for campaigns
  • Metadata Navigator for data lineage (and documentation purposes)

ODI will be incorporated throughout the entire Oracle Stack to enable pervasive data integration.

Pre-packaged data-integration for Apps (Peoplesoft, Siebel, SAP) so all ETL-flows are performed by ODI.

Tips & Tricks when using ODI for your enterprise-wide data integration:

  • Define groups in ODI
  • Use ODI Security Module for creating generic and non-generic profiles and arrange user rights
  • Use Oracle Analytic functions
  • Use naming standards for variables, user functions, …
  • Don’t call pl/sql functionality outside ODI, this would be strong wiring

For information regarding ODI you can download our presentation were we’ve compared OWB, ODI and ODI Suite: The Next Generation of Business Integration: Making the right choice!

So what is FUSION exactly … well it’s exactly what you want it to be !!!

When you’re looking into Oracle 11g, what’s coming up, you will notice one thing very clearly … FUSION. SCA, Webcenter, Jdeveloper, OWB, ODI, BPM Suite, Oracle Service Bus, … it’s all becoming 1 user experience.

If we take a look at the Oracle Fusion Approach, you will have the same user experience in each environment you working in.

The IDE’s are converging, the management console’s are converging, … in other words the developer experience, dba experience, management experience, etl experience … every person, every team will work in the same UI, having the same experiences and in other words can team up with all different teams and projects.

It’s amazing when looking at OWB 11g release … it’s ODI put in a Jdeveloper UI, it’s like you’re working inside Jdeveloper not on a web application, soa architecture, database application … no your designing your datawarehouse.

AIA patterns can be used in BPA Suite, BPM Suite will hold all the features of BPMN to deliver a BPEL Blueprint which can be enriched in your Bpel designer. Bpel designer can be used in Jdeveloper or Eclipse, as well as the ESB Designer. Having the Jdeveloper experience and talking about data integration aspects and ETL, well no problem open up OWB 11g and have the same developer experience. The IDE looks the same, works in the same manner as Jdeveloper as well for OWB as for ODI, all suites converge … were do you have that experience now?

Moving towards 11g you will have the converge of BEA and Oracle to have a stronger middleware, an enhanced governance-approach (using AL Enterprise repository) and a whole new feature set coming up in SCA.

So now the million dollar question … when can we use all this great stuff ;o)

Oracle Open World – Forms Modernization & Data Integration

From the 21st of September until the 25th 2008, Oracle OpenWorld 2008 wil take place in San Francisco. Oracle OpenWorld 2008 is the world’s largest gathering of Oracle customers, partners, developers, and technology enthusiasts.

Two sessions will be given by iAdvise/O2U at Oracle OpenWorld 2008:

  • O2U is proud to present it’s customer case study: The ZLM Case: New Forms Modernization Case Study using Oracle Forms, Web Services, JDeveloper and ESB (Hans Heremans, Nathalie Roman)

In this case study, we will showcase how you can modernize an existing Forms Application to move towards a SOA Environment. The existing Forms and web application will be sharing web services orchestrated using an Enterprise Service Bus.

In the second presentation we will talk about the data integration aspects in which different approaches and toolings will be discussed.

  • The Next Generation of Business Integration: Making the right choice! (Hans Heremans, Nathalie Roman)

In this presentation a practical overview will be given of the Data Integration and Management requirements that are needed for data integration projects today. Having a look at the different aspects that aren’t addressed in current integration projects and how these can be leveraged using the Oracle Technology Stack. Using Oracle Warehouse Builder, Oracle Data Integrator ODI Suite or SOA Suite, you’re able to address all the needs of today’s Business Integration.

If you’re interested in attending these sessions (forms, webservices, data integration, …) :

  • S298887, “Oracle Forms in an SOA World,” on Tuesday, September 23;
  • S301400, “The Next Generation of Business Integration: Making the Right Choice” on Wednesday, September 24.

I hope to see you all there !

Oracle Mix – Suggest a Session Results Are In

On the following website it’s announced, the Oracle Mix nominated sessions are in …

And we will fill in a slot regarding Data Integration Services provided in the Oracle Stack, for more information have a look at the mix abstract:

On the following blog, you will find all the sessions that have been nominated for Oracle Open World.

Next to this Oracle Mix session we are proud to present our ‘Forms To Soa’ session which will be given on Oracle Open World as well.

In other words OOW here we come !!!



ODI – Troubleshooting

What happens to your ODI interopability when changes are made in your target datastore, in other words how are changes picked up and handled in ODI?

I staged such a scenario to test the robustness of ODI and following troubleshooting hints & tips could be of help to anyone facing this situation in a real-time environment:

  • ‘The primary key is not unique, PK Errors’: I couldn’t figure out what this error was about because my sequences were defined correctly on the target datastore and the invocation in my ODI interface was defined correct as well. => Solution: delete the working tables in your sunopsis working repository, the snpw-schema. When you run the interface again it’ll work smoothly.
  • ”Join error between the table ‘x’ and the table ‘y” => This error was resolved by deleting all constraints defined on my target-model and by reverse engineering the model-object again. => New attributes, constraint are reversed as expected, but existing constraints and attributes aren’t automatically deleted, this is a manual job you’ll have to perform.
  • You’re getting ‘privileges’-exceptions when trying to execute your interfaces => make sure to grant the target-datasource dba-privileges this is needed to be able to access the snpw- and snpm-datastores.
  • Working with Excel Data : ‘-1305 : S0002 : java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘MO’. Make sure the object exists and that you spell its name and the path name correctly.’ => This error is thrown when the Excel you’re pointing to is opened up whilst running ODI interfaces. When you close the Excel file no, this exception will be resolved.

Other usefull hints & tips are always welcome.

OOW : Forms 11g – Forms to SOA – Oracle Data Integrator – OFM High Availability

Yesterday a couple of more sessions were on the agenda, which were all very interesting and of course a lot of sneak previews ;o)

Oracle Forms: Strategy and road to SOA
The first session was given by Eurotransplant, a company from the Netherlands that has implemented SOA in its forms-environment in the past few years. During this session Oracle has given an insight on Forms 11g, the road ahead and Eurotransplant gave an insight on the lifecycle they went through.

The most important keynote to keep in mind for Forms 11g is the ability to hook in bpel, human task workflows inside your forms application. The forms application will be event-driven in the future, so the end-users can react upon events through using JMS and Advanced Queueing.

The way Eurotranplant has integrated SOA into their environment was by first giving its Forms-developers a J2EE-development course using the JDeveloper environment and ADF Faces (first they used plain JSP and afterwards UIX), but Faces is the future. By using JHeadstart they could use the automatic generation framework on their existing Forms application.

In 2006 they’ve implemented the first BPEL process and hooked it up inside their forms application. A more advanced bpel process is being implemented right now and will be completed next year. To be able to hook-up bpel processes and service-enable their forms application, they’ve put the business logic which was inside their forms application inside the database. These pl/sql procedures, packages can then e called through pl/sql webservices and integrated inside new bpel processes and their forms applications.

The second session was given by Dave Berry and David Shaffer regarding the new SOA Grid enabled application.
Next generation Grid enabled SOA:


  • DataGrid Servers: Asynchronous write-behind queues to dehydrate data
  • Grid maintains one instance data of business logic, which can be a service such as bpel process
  • Build new messaging layer that takes advantage of grid-enabled to subscribe to state-changes
  • In memory access feeds (no disk-assistance required)
  • Activate/Rehydrate, relocatable bpel processes
  • Say no to HUB-and-SPOKE
  • Dehydrate current bpel process into grid, and relocate in another grid


  • Better performance and availability of bpel processes via SOA GRID
  • Release: 11g , next year
  • ESB Mediation:
  • Primarely stateless against bpel which is primarly statefull
  • Execute async-rule sending rid payload reference id through JMS
  • Subscribe to payloads via reference calls

New Model for Scaling SOA:

  • A new model for efficient resource utilization => scale up horizontally
  • Work together with Coherence for data-usage use cases
  • Use soa-grid inherently for as well cpu, memoiry
  • State-aware continuos availability for service infrastructure
  • Software-only solution, no hardware costs necessary
  • Heterogenous environment

The 3d session that I’ve scheduled today was regarding Oracle Data Integrator, which I’ve blogged about regarding setting up a migration path.

Event-Driven Business Intelligence from Heteregoneous Data using ODI:

The most important message given in this session was: ODI will be integrated in all technology stacks of Oracle, such as BI EE, Oracle SOA Suite

Enterprise Information Management:


  • Understand the past (BI)
  • Coordinate the present (BPM, BAM)
  • Plan for the future

ODI Strategy:

  • Pre-integrated with Oracle Middleware & Applications, BI Tools, Hyperion, Packaged Applications (ERP, CRM), Master Data Management, Fusion Application Pilars

ODI Offering:

  • ODI Core
  • Oracle Data Quality for DI (ODQ)
  • Oracle Data Relationship Management


  • Declarative design
  • Knowledge modules
  • Active datamodel (change data capture)
  • Batch loads are performed faster via data-caching on staging area
  • Adapters are for free in the whole ODI stack
  • Oracle Data Quality Profiling => changes it into data quality rules

And the last interesting keynote : A Game has been organized during Oracle Open World, to win a plasma screen, car, etc. and this game is using RFID-tags, BAM and ODI as its technology stack. Sadly enough only US citizens can win these prices :(, maybe I’ll need to immigrate to the US before I come to Open World ;o)

The last session for today was very interesting and gave more insight on the different integration efforts oracle is providing throughout its technology stack.

High Availability for Oracle Fusion Middleware:

New Features in OFM 11g:

  • On Demand Scalability
  • Non-stop availability
  • Unified Management: Using the Middleware Administration Server (MAS) and the MAS Repository for configuring changes in one single point of truth, the MAS. The MAS Agent will then push the changes made in the Server to the different OC4J instances existing in your farm.
  • Resource Discovery via MAS: runtime announcement of services, no downtime needed which virtualizes the OC4J connection information
  • OFM Load Balancer: create virtual IP’s
  • Cold Failover Cluster with Oracle Clusterware (CRS), Cluster Manager & Cluster Server
  • High Availability Patching through which you can patch in a sequential manner when you have different Oracle homes or perform out of place patching when you have shared Oracle Homes. Using the Out Of Place Patching you will clone the shared Oracle Home, perform the patches, and merge the cloned Oracle Home with the non-patched version

The most interesting feature and ‘stockhorse’ (stokpaardje) of Oracle: Oracle Coherence which can be described as ‘RAC for the Middletier’.

What are the different features?

  • Distributed Management Solution
  • Runs inside JVMs
  • Automatic ‘scale out’/ horizontal scaling
  • Automatic High Availability : every object in memory has a backup via another server
  • Extreme transaction processing
  • Runs business logic inside the Grid: Analytics
  • Trigger other consumers, applications when data changes in the grid using a push or pull-mechanism

That was it for today, I’ll keep you posted …

Oracle Open World – Let’s present some Use Cases

One week to go until Oracle Open World kicks of and it will be my 1st attendance, and to be honnest I’m pretty nervous ;o)

The last few weeks I’ve been trying to integrate my oracle bpel processes with a Flex User Interface, and with success … which means I have new material to discuss with the SOA Community.

The case I would like to discuss with the different fusion regional directors, ace’s and of course the product team will hold following topics (already explained on this blog):
– Use Case: Migrate existing Excel data using Oracle Data Integrator
– Use Case: Analyse Business processes using BPA Suite and BPM processes and generate bpel blue print using bpa suite
– Use Case: integrate Flex User Interface with existing Bpel Processes using Web Service Proxy Clients for the middletier

These are the 3 different approaches I’ve used so far for an existing customer, trying to evaluate BPA Suite and ODI using a real-time case and implemting a business process driven application using bpel & flex.

I’m really looking forward to the discussions to exchange experience & expertise !

See you there!

Join Me at Oracle OpenWorld Connect!