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 !!!



RPE-02062: Itemtype cannot be dropped as it has a running process.

When deploying a process flow using the control center of OWB (11r1), following error is raised:

Error: RPE-02062: ItemType PKG cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor.
INFORMATIONALRPE-02071: Deployment has been aborted due to a previously reported critial error.

I used following steps to solve the issue:

Step1: Find the running processes:

SQL*Plus: Release – Production on Tue Jul 1 10:46:10 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter user-name: owf_mgr@DWHD
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OWF_MGR> SELECT item_type, item_key, begin_date, end_date, activity_status
2 FROM wf_item_activity_statuses
3 WHERE activity_status’COMPLETE’
4 AND item_type = ‘PKG’;

ITEM_TYP···ITEM_KEY·································BEGIN_DAT··· END_DATE···ACTIVITY
PKG·········WB_IK_20080627_200542_108294···27-JUN-08····················· NOTIFIED


Step 2: Remove the active process

2 WF_ENGINE.ABORTPROCESS(‘PKG’, ‘WB_IK_20080627_200542_108294′);
3 END;
4 /

PL/SQL procedure successfully completed.

OWF_MGR> commit;

Commit complete.


Step 3 Redeploy the process flow

From the logs:


OWB Paris : What happened to “Generate intermediate results”???

Prior to OWB Paris, you could easily trap the SQL code that was being generated by OWB in a mapping : you simply had to rightclick on an outgoing group and select “Generate Intermediate Result”.

At first sight, this option disappeared in OWB 10G release 2, but this is not the case… Now, how can you get an intermediate result in OWB Paris? It’s simple … If you know it.

1. Select Generate from the top bar.

2. The Generation Results window will appear.

3. From the dropdown list, select “Intermediate” instead of “Full”.

4. Finally, select the outgoing group of which you want to see the intermediate result.

Like I said : simple, if you know it … ;o)

OWB 10g R2 : Help! Where are my windows?

Imagine … You’re enthousiastically editing a table in the Object Editor of OWB Paris. You minimize the Object Details window, and drag it to somewhere on the screen … Unfortunately, by a “slip of the mouse”, you drag it outside the OWB master window, for instance into your Windows task bar … Uups! The Object Details are … GONE! Although, according to the menu, the Object Details should still be visible !?

Un-checking and re-checking the Object Details in the menu doesn’t help; closing and re-opening the Object Editor doesn’t solve the problem either; even restarting OWB does not bring back the Object Details.

However, there is a way to “fix” this : in the folder [OWB_HOME]\owb\bin\admin, hence, there are a couple of XML-files, one of which is : SchemaEditorLayout.xml.
You should replace this file with an old copy of the same file that you got from another client install who hasn’t its Object Details vanished …

Closing en re-opening the editor should bring back the Object Details.

Tip : since I experienced this phenomenom, I made a backup of the *.xml files mentioned above … just in case, as this issue does not only apply the the Object Editor …