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!

Tips & Tricks from ODTUG

The tips & tricks I’ve learned so far at different ODTUG sessions I’ve attended:

When you’re thinking about moving to fusion middleware:

  • Use a small subset of tools, no big bang approach: ADF + BC + BPEL
  • ADF Faces is still evolving, so you need to consider the rewrite that’s needed when using Faces as your UI
  • Use templates to introduce a common look-and-feel by using af:region-tags which will centralize the layout so you don’t need to go through each page when layout changes
  • Use Enterprise Business Objects, EBO’s, as they are being used in AIA, one common business object that will be the facade-layer between your web layer and model layer
  • Use Grid Control to monitor your fusion-based applications
  • Set up datasources and pooling on the Application Server not on Application Tier
  • Prevent SQL-Injection by using bind variables
  • Use black- and white-lists for defining the security-rules and do’s and donts of your application
  • Don’t define nested roles when defining security in ADF Applications, use flat structures because not all JEE Servers support nested roles

More high-level, pragmatic:

  • Concentrate on business drivers, not on technology: on the ‘what’ not the ‘how’
  • Business Services are your hub, not the database anymore

A great paradigm Basheer Khan uses:

When your moving to a new home you will tap into existing services such as electricity, phone, internet, cable-tv, water, … => this is the same approach that you will be using when moving to SOA. You will tap into existing services.

From Forms to SOA

Today Grant Ronald was in Europe for the Technology Seminars organised by Oracle, of course the subject was Forms.

For more information regarding forms, soa and the road ahead you should bookmark his blog.

The title of the seminar: ‘From Forms to SOA’ and we were invited to present our experiences with SOA, Forms and the combination of both worlds as well.

It was great to meet up with Grant, because I’m more a SOA-person as a Forms-person and it was the first time to meet up with him.

First Grant gave a presentation regarding:

  • Oracle Forms Strategy: From Client Server to SOA
  • SOA, BPEL and Web Services: Calling Services from Oracle Forms
  • Building Services in JDeveloper

It was great to hear that Grant participates in as well product management for Forms as Jdeveloper to enable the same productivity and declarative approach as forms developers are used to working with in Forms application.

In the 1st part Grant explained how both worlds, SOA and Forms, colide and the key phrase here = upgrade and integrate. First you need to move your client/server application to the web, using web forms. After the upgrade you can then integrate the needed services such as web services, bpel, esb, … into your existing web forms application. In other words there’s no need to have a big bang approach and throw away all the investments you’ve made, but upgrade and integrate. As you can read and see as well, Forms 11g will hold new functionalities to enable integration even more, such as AQ to enable asynchronous messages for example, javascript capabilities, …

In the 2nd part Grant showed how you can integrate such a Forms application with an existing bpel process, which actually is a web service, and with existing web services. The key phrase here = you don’t need to write any line of java code to enable this integration.

How to accomplish this without writing any line of java code, use Jdeveloper and the Java Importer of Forms:

  • Create a web service proxy client for the existing bpel process and web services via the ‘web service proxy client’-wizard in Jdeveloper. You just need to copy/paste the wsdl-location of the bpel process and/or external webservice and Jdeveloper will do the rest.
  • Deploy this web service proxy client to your application server, were your web forms are hosted as well
  • Generate pl/sql wrapper classes via the ‘Java Importer’ in your Forms Builder
  • And the Forms Developer can invoke web services using pl/sql functionality without any knowledge of the underlying technology

In the 3d part Grant gave a demo regarding the 4GL-experience a Forms Developer will have when working with Jdeveloper to build a ‘Forms-like’ application, in other words data-entry, – retrieval pages.

To have the same look-and-feel, capabilities, userfriendliness and declarative approach as in Forms Builder the chosen technologies for MVC are ADF Faces and BC4J, these are the 4GL-like technologies to choose when coming from a Forms-background.

Using these technologies Grant explained how to define business logic using validation in BC4J, how to define page flows in the faces config file and how to define the different screens in a declarative manner. Using BC4J Grant also explained how Entity Objects, View Objects and the Application Module relate to components known to a Forms Developer.

After his introduction different Oracle partners presented their cases regarding Forms and SOA and in that matter we’ve presented a SOA and Forms integration case from the Netherlands.

Most important part of all: we had an audience of more than 90 people, it was great to see such an amount of people and interest in these technologies.

Best of breed applications are slowly moving towards ‘best of all worlds’, there are ‘no’ limits anymore.

Proxy Authentication Failure in BPEL

When you’re trying to integrate external webservices into your bpel process and you need proxy authentication you will probably run into the following bug: 5851338.

A patchset is made available to resolve the HTTP-407 issue in your BPEL Environment. For more detailed information :

1) Download and review the readme for Patch.6869621 ( MLR # 7 of 10.1.3.3.1 )
2) Apply Patch.6869621 in a test environment.

Bpel 4 people / Bpel 4 newbees

One of the main goals I’m trying to reach is to advise customers on how to start implementation of SOA in their organisation by using a coaching approach instead of implementor approach.

In other words, guide and coach the development teams of each customer in getting familiar with SOA concepts and getting their hands dirty ;o))
The developers themselves are tought BPEL, Web Services, XML and XSD through basic hands on session, workshops in a practical way and then they start designing bpel processes.

What’s very important is this process is that each person needs to do the brainwork, in other words always start from scratch, don’t define a synchronous or asynchronous process, no define an empty process and build it up yourself.

What’s very interesting when guiding and coaching people in becoming a SOA expert, is understanding the way they think and handle functional requests and problems in a SOA Technology. This gives me the opportunity and possibility to focus more on how to better advice and guide people in learning SOA, e.g. BPEL.

The most interesting points to consider and think about when being a teacher in SOA or being a student are the following:

Being a teacher:

  • be passionate, patient, non technical, use metaphors and most don’t use abbreviations or buzz words, this ain’t cool ;o)

Basic knowledge before starting BPEL/ESB/OWSM/Mediators … Integration:

  • Web services (synchronous versus asynchronous)
  • Xml and xsd (how to define, how to work with xml data (xquery, xpath, …)

You need to be able to understand following principles:

  • Fault handling
  • Compensation handling
  • Transaction handling

Following abbreviations mean something to you:

  • JCA, JNDI, EDA, SOA, WSDL, WSIF, SOAP, JMS, RPC

How to get started when designing bpel processes:
One of the interesting tricks I’ve learned when guiding a customers’ developer in learning bpel was that he defined an empty bpel process where all needed activities where pseudo-coded.

In other words when you need to define your first bpel process using the use cases defined and described by your analysts, you just design the bpel process using empty activities which describe each needed task/action in your process.

This empty process is deployable and can then be iteratively implemented by the designer when the visual flow is checked upon with the functional developers. => THIS IS A GREAT INSIGHT and WAY OF THINKING and a very visual approach to enabling business processen (thanks to Yves for the tip !!!)

The most interesting part when starting with SOA development is that everyone that’s involved in this process is learning; it’s a learning organization where every person’s knowledge and expertise is augmented!

BPEL Process Invocation from different UI’s – Best Practices

In my previous thread I’ve talked about different tips & tricks when working with ESB, BPEL, ODI, Flex, etc.

One of these tips was tips & tricks on invocation of BPEL Processes when using different UI approaches.

A very understable explanation given by ‘Hajo Normann’ about this:


It is best practice to use the default ways to invoke a BPEL process –
create a WSDL that maps to WSIF binding in a controlled environment and to a
SOAP/HTTP binding in a more B2B type of scenario. A call to the BPEL API would
be a “custom” solution that needs way more governance to communicate with fellow
developers and to maintain properly, when compared to the straightforward
standard way.

So, I suggest to use the API only if you desperately need
to optimize and found the default implementations violating your SLAs.

Let’s have a look at the technical implications:

If you run your presentation logic in the same Java VM as your BPEL process, you would use the default mechanism of invoking the process with the default WSDL. Under the hood, the middleware creates WSIF bindings that
- are extremely memory friendly, because no redundant memory is allocated (the middleware passes a reference to the Java object)
- share transactions

In this scenario I see no advantage of calling the API.

If you run your presentation logic in an other Java VM as your BPEL process in a controlled environment, you would still benefit from using the default mechanism of invoking the process with his default WSDL. Under the hood, the middleware creates WSIF bindings that

- serialize the passed Java objects and, on the BPEL side, create the BPEL DOM
implementations that make up the BPEL instance variables. This causes a performance and memory penalty you have to pay
- share transactions

I haven’t seen any benchmarks regarding this or haven’t received more information of the Oracle Development Team so hopefully this will start up a big discussion ;o)

Soa Suite 10.1.3.3 – ESB, BPEL – Nice-to-knows, pitfalls

I’ve been checking out the different capabilities and new features of the Adapter-framework in ESB and BPEL for some weeks now and came across some nasty pitfalls, nice-to-knows, … which I would like to share with you.

Of course I would like to share thoughts, opinions and start discussions on these topics.

ESB:

  • How to define xsd-validation on file-adapter (validate payload at runtime-option isn’t available in file adapter) : In the ESB console, select the routing service which is invoked after the inbound file adapter. See the “Definition” tab. The validation option is in the “Operation Details” section. (with thanks to Ronald)

BPEL:

  • Inserting master-detail data using DB Adapter functionality : Referring to my experiences so far it’s best best way to make use of stored procedures instead of the toplink mappings file. I am mainly using the stored procedures because the tooling support in Jdeveloper (wizards, toplink ui), I still miss a good ui for the toplink support. Also it is easy to give the task to create an PL/SQL api to the PL/SQL developers that are working on a certain application. (with thanks to Orjan)

  • [Error ORABPEL-10007]: unresolved messageType for “{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage”: When you’ve defined an empty bpel process (which is best practice to do the brain-work) you will face this issue when defining fault-handling inside your bpel-process. To solve this error you need to import the RuntimeFault.wsdl inside the adapter you’re using. Following import statement needs to be added:

  • Best practices when invoking Bpel Processes from different UI’s (Flex, JSF, …) : Many thanks to Hajo for his explanation: It is best practice to use the default ways to invoke a BPEL process – create a WSDL that maps to WSIF binding in a controlled environment and to a SOAP/HTTP binding in a more B2B type of scenario. A call to the BPEL API would be a “custom” solution that needs way more governance to communicate with fellow developers and to maintain properly, when compared to the straightforward standard way. . For more details see OTN Thread: http://forums.oracle.com/forums/thread.jspa?messageID=2329384&#2329384

  • Use multiple sources in transform-activity: In 11g a new feature has been added to be able to use multiple sources using bpel 2.0 (bpel:doXslTransform(string, node-set, (string, object)*)). The workaround in 10.1.3.3 is by using the params-approach => http://blogs.oracle.com/rammenon/2007/05/07. Or by using an assign-activity with append-functionality to add the variable inside your source-target and in the same assign-activity add the process-xslt functionality to call your xsl to populate the source with the target-information.

Invoking Web Services from Database:

  • Call an esb service using the UTL_HTTP package => ORA-29266: end-of-body reached => make sure to pass variables using String-notation instead of Character-notation

Interesting New Features in 10.1.3.3 :

  • Controlling the Size of a Rejected Message (10133technotes.pdf):
    You can now control the size of a rejected message by specifying the following
    endpoint property for the inbound File/FTP adapter partner link.
    In this example, you reject 100 lines from the file since the actual file is too large.
    oracle.tip.adapter.file.debatching.rejection.quantum=”100”

  • ESB Endpoint Properties : e.g. ability to add RejectedMessageHandler to file adapter services

Enhancement Requests:

  • Ability to validate xml payload at runtime on Adapter-level instead of on domain level or routing service level
  • Ability to add xsi:nil attribute using xsl-functionality in transform-activity
  • File-adapter: Ability to skip columns besides skipping rows + ability to use special characters in column headers

Well that’s it for now … feel free to share thoughts, comments, etc.

Tips & Tricks that’ll save you a lot of time regarding ODI, Flex, Soa Suite

Latelly I’ve been spending a lot of time regarding deployment and configuration of Adobe Flex applications integrated with Bpel on an OC4J-container.

On the other hand I’ve been experimenting a lot with ODI and how the tool will handle changing data models, changing datasources, …

Lastly I’ve been playing around with invocation of Bpel Processes using Web Service Proxy Clients instead of the Bpel Invocation API.

The question that’s still unanswered because no documentation or benchmarks or best practices are available yet is: ‘What’s the best practice in invoking bpel processes, the Bpel Invocation API or Web Service Proxy Clients?’

Tips & Tricks ODI:

  • Interesting Notes: 423740.1, 423817.1
  • Grant dba-privileges to your source-db-user, otherwise you’ll get ‘insufficient privileges’ exceptions when trying to execute your interfaces

Handling changing data models (new tables, deletion of existing attributes in tables, addition of new attributes, additional relationships, … ) :

  • Go to your datamodel and reverse engineer the changed tables again. New attributes are added, deleted attributes aren’t removed yet because they’re referenced in interfaces in your ODI Designer.
  • Go to your interfaces defined in the Designer-tab in ODI: remove all active mappings to the attributes that were deleted
  • Go back to the model-tab in designer and right-click the attribute in your tabel and choose delete. When all references are updated correctly (no active mapping), the attribute will be deleted from your datamodel

Tips & Tricks Flex:

  • When you’re getting weard errors regarding ‘conversion exception between java.util.Date and java.util.Calendar’ or exceptions regarding producer or consumer-errors => make sure you’ve defined ‘autocommit’-property to false on your data services (Using LCDS)
  • How to handle static data without usage of Value Object or Data Transfer Objects => use SQLAssembler without usage of java-classes or actionscript-classes in your data-management-config.
  • rtmp-host connection failure on OC4J => try to reset the connection-port to another value than the default one which is ’2038′. I’ve defined the value to ’2035′ and now no errors are thrown anymore
  • Use [managed] attribute to make sure all changes on objects are synced between 2 clients
  • Deploying data-management-config to different JEE Servers, e.g. Tomcat and OC4J. When you deploy to tomcat you have to define the full JNDI-location which would be for example: java:/comp/env/jdbc/flexDS. When you deploy to an OC4J, you need to define the short JNDI-descriptor: jdbc/flexDS.

The other exceptions, weard behaviour was already addressed in earlier posts:

  • You need to add an xsl-choose-when-otherwise xsl-node structure to you transform-activity in your bpel-process and in the otherwise-tag define the xsi:nil=true on your attribute to make sure the web service proxy won’t set this attribute to required.
  • You need to update the toplink-mapping file being generated in bpel when using the database adapter. Relationships aren’t defined properly, because the method accessor is checked for all one-to-many relationships whilst these aren’t defined in the java wrapper classes. Uncheck these ‘map to method accessors’ and these exceptions will disappear. You can uncheck these when drilling down in your toplink.mwp file into to your object on your mappedCollections.

A beautiful one were all newbees in SOA Suite spend ages and ages to figure out what the problem is … Oracle please fix this ???

You’ve installed SOA Suite (you can choose which version) and entered a custom password during installation for your oc4jadmin-password, not the ‘welcome1′ password. You can choose your own password wright ?

But when the newbee starts creating his first bpel and esb projects, there are weard errors poping up: ‘connection timed out’, ‘jndi-location for db-adapter isn’t properly defined’, …

Well, weard but true, the ‘welcome1′ password is defined hardcoded in different ‘oc4j-ra.xml’ files used in the jmsadapter-folder, dbadapter-folder, … Make sure to change the passwords correctly when you’re drilling through the different Oracle By Example-exercices or tutorials.