LISTAGG with CLOB? String aggregation exceeding 4000 characters with XMLAGG.

We all know that the Oracle LISTAGG function does not support the CLOB datatype. In my opinion this is a severe limitation of LISTAGG (and BTW also of PIVOT and UNPIVOT etc.).

So what are your options? One option is to create your own user defined aggregate function. I found such an example on the Oracle forums.

However, if you want to use pure SQL to achieve the same, we can leverage the XMLAGG SQL/XML functionality built into the Oracle database.

As per documentation: “You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements”

Below is an example to demonstrate how this works. For demonstration purposes I don’t exceed the 4K character limit in the example below. However, I guarantee you that it works with >4K strings as well.

SELECT
   table_row_id,
   DBMS_XMLGEN.CONVERT(EXTRACT(xmltype('<?xml version="1.0"?><document>'||XMLAGG(XMLTYPE('<V>'|| DBMS_XMLGEN.CONVERT(data_value)|| '</V>')).getclobval()||'</document>'), '/document/V/text()') .getclobval(),1) AS data_value
FROM (
   SELECT 1 table_row_id,'abcdefg>' data_value FROM dual
   UNION ALL
   SELECT 1 table_row_id,'hijklmn' data_value FROM dual)
GROUP BY
   table_row_id;

Let’s have a closer look at the nested stuff in this query.

In a first step we escape the data into its XML equivalent

DBMS_XMLGEN.CONVERT(data_value)

‘abcdefg>’ ‘becomes abcdef<g’

Next we create an XMLELEMENT for each row and convert the result to XML data type

XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')

Then we do the XML aggregation

XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()

In a last step we create a well formed XML document, extract the text value from the XML elements and unescape the text.

DBMS_XMLGEN.CONVERT(EXTRACT(xmltype(''||XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()||''), '/document/V/text()') .getclobval(),1)

OBIEE 11g Repository Documentation: Extracting metadata from the RPD

With OBIEE 11g there are two methods to extract metadata from the RPD.

The first one uses the admintool.exe command to generate metadata. It was already around in OBIEE 10g. However, the syntax has changed slightly. The second uses the biserverxmlgen command.

Admintool

This is an unsupported and undocumented feature. Use at your own risk.

You have to create a command file that is then invoked by admintool.exe.

OpenOffline <path to RPD> <RPD Password>
DescribeRepository <path to output file> <encoding>
Exit

Parameters

<path to RPD>: Path to your RPD, e.g. D:OracleMiddlewareinstancesinstance1bifoundationOracleBIServerComponentcoreapplication_obis1repositorymyrpd.rpd
<RPD Password>: The password to your RPD
<path to output file>: Location on file system where the RPD metadata will be output to. The extension of <path to output file> determines the type of output: (1) .csv (comma-separated values) (2) .txt (tab-separated values) (3) .xml XML
<encoding>: Unicode, ANSI, UTF-8

You can call the command file by using the following command (This has not changed from OBIEE 10g.)

admintool.exe /command <path to your command file>, e.g. admintool.exe /command C:extract_rpd.txt

biserverxmlgen

I can see three advantages of using biserverxmlgen over admintool

(1) The Admintool /command syntax is not documented or supported
(2) The output of biserverxmlgen is more comprehensive, e.g. it contains information such as the alias in the presentation layer
(3) It may be easier to extract this. I have not tried this out, but to extract some of the info from the admintool extract can be a bit of pain, as there is a recursive relationship between logical and derived logical columns. Not sure if there is an XSD for this XML. If you have any info on this let me know.

The syntax for this is:

biserverxmlgen -R D:OracleMiddlewareinstancesinstance1bifoundationOracleBIServerComponentcoreapplication_obis1repositorymyrpd.rpd -P Admin123 -O c:rpd.xml -8

Oracle Data Integrator 11.1.1.6 has been released

The features I am most looking forward to are:

- Smart Import/Export feature. Hopefully this will allow import/export between projects in the same repository.
- Global Knowledge Modules
- Versioning of Knowledge Modules
- Tracking variables and sequences

For a full list of new features go to the Oracle website.

Limitations ODI – OBIEE data lineage

We recently evaluated the ODI-OBIEE data lineage feature that was added to the most recent release of ODI 11g.

As part of this evaluation we came across various limitations of this out of the box data lineage functionality.
First of all we came across what I believe to be two bugs (one of them serious):

1. The data lineage feature does not take temp interfaces into account, i.e. if you are using a temp interface in any of your data flows from source to final target then the data lineage is broken. I logged this issue on 17 October 2011 with Oracle Support. However, up until today they have not been able to set up a joint OBIEE 11g/ODI 11g environment to reproduce the issue. Quote “The OBIEE team is having some issues with the OBI instance”. I never bothered escalating the issue as we decided going against ODI – OBIEE data lineage anyway.

2. Another smaller issue has to do with the fact that for report columns that contain a formula, data lineage is broken.

In the report below we are using a formula.

However, in the data lineage report this is missing. I would have expected to get data lineage for any of the columns that are used. In our case for both amount sold and quantity sold.

The above are just bugs that can be easily fixed. However, the other issue we came across is more serious and cannot be addressed easily. Basically data lineage will be broken/lost if you are using views in your interfaces or procedures to populate your target tables.

The reason for this is that the view will mask the columns of the underlying table and may include additional transformation logic etc. As far as I can see, there is no easy solution to this. One such option would be to write an SQL parser that parses the columns of the underlying table(s) and maps them to the target table.
My estimate is that at least 90% or more of implementations are using views to extract data, e.g. it is best practice to use views to extract data from your data warehouse to your data marts to insulate your data marts from changes to the data warehouse.

What I would be interested in is to hear from people who are using the out of the box data lineage feature and what experience they have had and if they were able to overcome the above limitations.

ODI 11g in a Services Oriented Architecture. Part III. Consuming a RESTful web service.

Consuming web services in ODI

In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we will now look at SOA from the other angle. I will show you how we can consume data services using ODI 11g.

Consuming a RESTful web service: OpenStreetMap (OSM) API

We can use ODI to consume public web services for data augmentation purposes, e.g. we can query the OpenStreetMap Geocode web service to augment customer data with longitude and latitude information.

OpenStreetMap provides geographic information such as maps, geocoding etc free of charge. You may wonder why we are not using Google maps? Well, first of all it’s not open source and Google’s license does not allow us to store the resultsets of our web service queries. I recently read the excellent book the Filter Bubble and since then try to avoid Google et al. even more. That’s why I have replaced Google search with Scroogle search.

Anyway, let’s get back to the topic at hand. As per the OpenStreetMap license anyone can use and store their data. They only ask you to mention OSM as the source of the data.

For details on the OSM license have a look at: http://wiki.openstreetmap.org/wiki/OpenStreetMap_License

There is also a page for the OSM usage policy

The OSM geocoding documentation can be found here

In addition there is an FAQ for the geocoding API.

Below is a sample request to the OpenStreet Geocoding API

http://nominatim.openstreetmap.org/search?q=3314+Eastern+Ave,+21224,+Baltimore,+MD,+US&format=xml&polygon=0&addressdetails=1

When we put the above URL in our browser we will get the following XML in return.

We will now look at how we can extract and store this information using ODI. I will show you two ways of extracting longitude and latitude information from the XML. In the first example we use built in functionality of the Oracle database to extract the information. In the second example we will exclusively use ODI functionality to extract the data.

Example 1: Consuming a RESTful web service – ODI piggybacks on the Oracle database

If we know that the target for our RESTful web service request is an Oracle database we can use the utl_http functionality built into the Oracle database to query the OpenStreetMap geocoding web service. This is convenient as we can directly extract the attribute values we are interested in from the XML that the web service returns. We don’t have to first set down the data in an XML file on the file system and we don’t have to use ODI to load the XML file into our target database. This saves us two steps and a bit of development time.

In a first step we need to create an Access Control List (ACL) for user OE. The ACL will give user OE access to the nominatim.openstreetmap.org website, but prevents access to any other websites. ACLs are new in Oracle 11. We need to grant these privileges as user SYS.

We first create the ACL

begin
            dbms_network_acl_admin.create_acl (
                    acl             => 'utl_http.xml',
                    description   => 'Normal Access',
                    principal       => 'OE',
                    is_grant       => TRUE,
                    privilege       => 'connect',
                    start_date    => null,
                    end_date      => null
           );
   end;

Next we assign the domains we want to access to the ACL

begin
        dbms_network_acl_admin.assign_acl (
        acl => 'utl_http.xml',
        host => 'nominatim.openstreetmap.org',
        lower_port => 1,
        upper_port => 10000);
    end;

Finally we grant access to user OE to the utl_http package.

 grant execute on utl_http to OE;

Once we have granted the correct privileges to the OE user we can directly access the OpenStreetMap API via the Oracle database. We can piggyback on this functionality from within an ODI procedure.

We create the ODI procedure PRC_GEOCODE_CUSTOMERS that loads the longitude and latitude information into the OE schema.

In a first step in our procedure we create a table at runtime, which stores the XML that we retrieve from the OpenStreetMap geocode API

CREATE TABLE address_geocode (
address_geocode XMLTYPE,
customer_id NUMBER
)

In the next step we query the OpenStreetMap API. We submit customers with customer_ID 240 and 235 from the OE schema to the OSM geocode API. As we don’t want to swamp the OpenStreetMap servers with too many requests we just retrieve data for two customers.

The Oracle database allows us to make direct http requests through the little known HTTPURITYPE function, which is built on top of the utl_http package. Have a look at the Oracle Books site where more information and examples are given.

INSERT INTO address_geocode
SELECT
XMLTYPE(HTTPURITYPE('http://nominatim.openstreetmap.org/search?q='||street_address||','||postal_code||','||address_city||','||state_province||','||country_id||'&'||'format=xml'||'&'||'polygon=0'||'&'||'addressdetails=1').getclob()),
   customer_id
FROM (
  SELECT
     REPLACE(c.cust_address.street_address,' ','+') street_address,
     REPLACE(c.cust_address.postal_code,' ','+') postal_code,
     REPLACE(c.cust_address.city,' ','+') address_city,
     REPLACE(c.cust_address.state_province,' ','+') state_province,
     REPLACE(c.cust_address.country_id,' ','+') country_id,
     customer_id
  FROM
     customers c
  WHERE
     customer_id IN (240,235)
  )

If we execute the procedure we will find two XML records in our address_geocode table.

We are now ready to extract longitude and latitude information from the XML and store in table customer_geocodes together with the customer_id.

We add another step to our procedure in which we create table customer_geocodes at runtime.

CREATE TABLE customer_geocodes
   (customer_id NUMBER, longitude   NUMBER, latitude NUMBER)

We then extract longitude and latitude information from the XML and store it in table customer_geocodes. We use the Oracle XMLTABLE functionality to extract these attributes from the XML. This function allows us to convert an XML file to a relational table. More details on XMLTABLE can be found in the documentation.

INSERT INTO customer_geocodes
SELECT
   customer_id,
   xt.longitude,
   xt.latitude
     FROM address_geocode yt,
          XMLTable('searchresults/place'
                   PASSING yt.address_geocode
                   COLUMNS
                   latitude NUMBER PATH '@lat',
                   longitude NUMBER PATH '@lon'
                   ) xt

We then execute the procedure and verify that the extracted information was loaded into our customer_geocodes table:

q.e.d.

Example 2: Consuming a RESTful web service using native ODI functionality

If we don’t have the luxury of an Oracle database we can still query the OpenStreetMap API using ODI native functionality. We can make use of the Jython scripting language that ships with ODI to access the OSM API.

We create another procedure PRC_GEOCODE_CUSTOMERS_JYTHON. We add one step to the procedure and set the Technology to Jython. In the code we connect to the OSM API, retrieve the geocode XML, and store this on the C drive on the file system.

import java.net.URL as URL
import java.io.BufferedReader as BR
import java.io.InputStreamReader as SR
import java.net.HttpURLConnection as con
import base64
import string

URLFormat="http://nominatim.openstreetmap.org/search?q=3314+Eastern+Ave,21224,Baltimore,MD,US&format=xml&polygon=0&addressdetails=1"

XMLFile="C:OSMGeo_240.xml"

OSMURL=URLFormat.replace(' ','%20')

url = URL(OSMURL)
con =  url.openConnection()

con.setRequestMethod("GET")

con.connect()
responseCode=con.getResponseCode()
responseMessage=con.getResponseMessage()

if responseCode ==  200 and responseMessage == "OK":
   br=BR(SR(con.getInputStream()))
   outRESTXML = ""
   outline = br.readLine()
   while (outline != None):
          outRESTXML += outline + "n"
          outline = br.readLine()
   XMLfilehandle= open(XMLFile,'w')
   XMLfilehandle.write(outRESTXML)
   XMLfilehandle.close()
else:
   con.disconnect()

con.disconnect()

For the purpose of this walk through I have hard coded the address of the customer with CUSTOMER_ID 240.

Before we can extract the data contained in the XML file into the customer_geocodes table we first need to define the connection to the XML file in ODI Studio Topology.

In a first step we create the data server

Then we add the physical schema

Next we add the logical schema

Then we reverse engineer the model for the XML file

Once we have reverse engineered the model we should see the two new data stores Place and Searchresults in the XML_OSM_SRC model.

Before we create the interface to augment our customer data with longitude and latitude information we need to import two Knowledge Modules: LKM SQL to Oracle and IKM SQL Control Append.

The longitude and latitude information is contained in data store PLACE. We will use this data store as the source in our new temporary interface INT_OSM_XML_LOAD. Once we have added the PLACE data store to the interface drag and drop columns LAT and LON to the Target Datastore and manually add column CUSTOMER_ID to target datastore CUST_GEOCODES (right click in the target datastore and select Add Column).

As data type for column CUSTOMER_ID we select NUMBER. Enter 240 for the CUSTOMER_ID.

Note: In a real world scenario you would of course extract the customer_id from the filename of the XML file.

Next we change to the Flow tab and select LKM SQL to Oracle as the Load Knowledge Module.

We also select IKM SQL Control Append as the IKM and modify the following options:

FLOW_CONTROL: false
TRUNCATE: true
CREATE_TARG_TABLE: true

Next we execute the Interface. This should create a new table CUST_GEOCODES in the OE schema and insert longitude and latitude for customer_id 240.

This example has shown that we don’t necessarily need the advanced features of the Oracle database to query a web service using ODI.

ODI 11g in a Services Oriented Architecture. Part II. Deploying and Testing data services in Weblogic

In part I of the Oracle Data Integrator in a SOA environment series we’ve had a look at how ODI can be used conceptually in a SOA environment. In this second part we get our hands dirty and actually generate and deploy a data service in Weblogic.

Pre-requisites for the walkthrough

ODI 11g (11.1.3.5) running on Weblogic 11g (10.3.5), Oracle database 11g with OE sample schema installed. For the purpose of this paper ODI, Weblogic, and the Oracle database are installed with the OE sample schema on the same machine.

Next we create the logical data server

You should also have a good understanding of some of the basic ODI development tasks. This includes creating ODI procedures, ODI data servers, and familiarity with the XML functionality in ODI. I have a couple of ODI video tutorials on these tasks on my blog in case you are a complete novice.

Without further ado let’s get started.

Creating and deploying data services in ODI

Before we can use the data services functionality in ODI we have to make sure that we have added the “Oracle Data Integrator – SDK Web Services” product to our Weblogic domain. If you haven’t installed this already launch the configuration wizard, e.g. from D:OracleMiddlewareOracle_ODI1commonbin and extend your domain.

Next we will create the web services container.
This is where we will deploy the data services for the OE model. Go to ODI Studio Topology and add a physical data server to the JAX-WS techonology. Give the data server a name and type in the base URL for your ODI domain in Weblogic, e.g. http://localhost:8001. You will also need to supply the directory for auto-deployment of your data service. In your ODI domain in Weblogic there should be a folder autodeploy. In my environment this is at D:OracleMiddlewareuser_projectsdomainsODI-DOMAINautodeploy. This is the folder where ODI deploys the auto generated code for the data services.

Next we insert a physical schema for the data server. Leave all of the default values.

Next insert the Logical Data Server

As we will use the OE sample schema to demonstrate data services we will have to set this up as data server in ODI.

In ODI Studio Topology we create a new data server under the Oracle Technology.

Next we create the logical data server

We then reverse engineer the OE model.
Go to the ODI Studio Designer tab, create a new model and reverse engineer the OE schema.

Before we can generate the data service for the customer table we first have to create a JDBC data source to the OE schema in Weblogic.
Log on to the Weblogic console (the default URL for the Weblogic console is http://localhost:7001/console) and navigate to Services > Data Sources.
We create a new Generic Data Source and name it JDBC OE. AS JNDI name we submit jdbc/oe.

On the next screen we select the appropriate Oracle driver. In our case this is the Oracle driver (Thin) for Instance connections Version 9.0.1 and later

On the next screen we leave all of the given default values.

On the next screen we type in the connection details to our OE schema.

On the next screen we will test our connection.

On the final screen we will select both the Admin Server (in my implementation AdminServer) and the ODI Server (in my implementation odi_server1) servers as target.

We now have created our JDBC data source in Weblogic and are now in a position to create our ODI data service.

Data services in ODI are generated using a Services Knowledge Module (SKM). We first need to import this into our project.

Next we open the ORCL_OE_SRC model and navigate to the Services finger tab.

In the application server dropdown we select the WEBLOGIC_WS_CONTAINER we created earlier on. Then we submit an appropriate namespace, e.g. http://www.business-intelligence-quotient.com/ws/oe/ and package name, e.g. com.biq.ws.oe. As the Name of Data Source we supply the previously created jdbc/OE data source. We give the data service a name, e.g. WSOE. Finally we select the SKM Oracle.ODI WS from the Knowledge Module dropdown.

Next we navigate to the Deployed Datastores tab and select the Customers table.

We save our selection and click the Generate and deploy… button. We leave the default values in the popup and click OK.

If we browse to our autodeploy folder we will find the .war file in it.

Congratulations. You have deployed your first ODI data service.

Let’s verify that the data service has been deployed as an application. In the Weblogic Console navigate to Deployment and you should see _appsdir_ORCL_OE_SRC_war (autodeployed). If the application has not yet been started you need to start it manually now.

Testing our data service

We have various options when it comes to testing our data service. I will show you how to test it using the Weblogic Console and the ODI web service client OdiInvokeWebService. If you need more advanced options I recommend the Open Source web services client soapUI.

Weblogic

We log into your Weblogic console, e.g. http://localhost:7001/console , go to Deployments and expand _appsdir_ORCL_OE_SRC_war (autodeployed).

Then we click on WSCustomers and then go to the Testing tab.

We can view the WSDL by clicking the ?WSDL link.

http://localhost:7001/ORCL_OE_SRC/WSCustomers?WSDL

This will show the WSDL in your web browser

We can test the web services by clicking on Test client. This will open a new browser window that lists all of the available web services.

To retrieve the attributes for a particular customer we will invoke the getCustomers method, e.g. for CUSTOMER_ID 105

After clicking the getCustomers button this returns the Customer attributes for ID 105.

OdiIinvokeWebservice

Another way of testing our data service is to use the web service client built into ODI.

We create a new package WSCustomers and add an OdiInvokeWebService tool.

To launch the ODI web service client we click the Advanced button.

We type in the URL to the WSDL http://localhost:7001/ORCL_OE_SRC/WSCustomers?WSDL
And click the Connect to WSDL button. This will bring back a list of the available web service methods in the Operation pane.

From the list of available web services we will invoke the deleteCustomers method to delete the customer with ID 105.

In the Editor pane we set the CUSTOMER_ID to 105 and click on the Invoke Web Service button

In the Response pane in the NBAffected field you will see that one record was deleted.

ODI 11g in a Services Oriented Architecture? Exposing data services and consuming web services with ODI 11g. Part I.

ODI in a SOA environment

This will be a four part series/tutorial on using ODI in a SOA environment.

One of the most overlooked features in ODI in general and in ODI 11g in particular is the ability to fully integrate ODI into a SOA environment. Typically technical architects associate ODI with high data volume ELT type data loads. However, ODI is very versatile when it comes down to meeting data integration requirements. Apart from the usual high volume, high performance ELT loads it can also be used in a web services centric environment.

In this series I will outline how ODI 11g can be deployed in a SOA environment to make data stores available to clients as data services. I will also show you how you can use ODI to consume data and web services. In the article I will give a step by step guide on how to create, deploy, and test a data service through ODI on Weblogic (without writing a single line of code). For the example I will use the customer table in the OE sample schema (http://download.oracle.com/docs/cd/B28359_01/server.111/b28328.pdf). On top of this, ODI can act as a consumer of web services. It can access both SOAP and RESTful web services. I will walk you through consuming both SOAP and RESTful web services using ODI. For the SOAP example we will connect to the OBIEE 11g web services API. For the RESTful web services we will consume the OpenStreet Map API to augment data in the OE schema customer table by longitude and latitude information.

Before we dive into the step by step guides, however, I first want to give a brief overview on the world of SOA, data services, SOAP and RESTful web services and how they are related to ODI.

What is SOA?

SOA is not a technology. It is rather a set of design principles for enterprise application architecture. In a SOA environment software units are loosely tied together as reusable services. They are platform independent and can be consumed by applications over standard network protocols. In a SOA environment the various web and data services are orchestrated in a workflow to implement a complex business process. The main benefit of SOA is faster application development. Already existing services can be orchestrated to quickly implement a given business process.

What are data services?

Data services expose enterprise data. Typically a data service makes data in one or more database tables available to consumers in the form of a web service. Client applications and end users can retrieve this data across physical and logical boundaries. On the web this could be across different geographies. For example this happens when you query the OpenStreet Map API. Irrespective of your location the data service returns the exact same data set. In the enterprise world this could be across business units within an enterprise. For example both the marketing and sales department can consume centrally held customer data. It could also be across enterprise boundaries, e.g. company 1 consumes product data from company 2.

What obstacles do we encounter in the implementation of data services?

Exposing data as a service without a supporting Master Data Management (MDM) program is like data warehousing without MDM. Difficult at best. Ideally the data that data services expose should be integrated, ought to be cleansed of errors, should be complete, needs to be up to date, and most not violate referential integrity. How would you feel when the longitude and latitude information changes each time you query the Open Street Map API? Good data quality is key for an environment that relies on data services. Any data services program should be accompanied by a data quality and master data management initiative. The so called golden record is a pre-requisite for a successful implementation of data services.

How are data services consumed?

Data services are typically consumed as web services. In the enterprise world you will mainly find SOAP based implementations. In the Web 2.0 world a lot of data services are now implemented as RESTful web services.

Data services and web services in ODI 11g

With ODI you can both create data services and consume them via web services. Once you have integrated, cleansed, and created the golden record of your data using ODI you can also expose this master data via data services. In turn you can consume data services via the ODI web services functionality.
When implementing data services you are not limited to read only access. You can also perform other operations on your ODI data services such as: deleting, updating, filtering.
Data service functionality has been before the latest release of ODI. However, in ODI 10g the data services had to be deployed in an Axis web services stack. This was a tedious and time consuming process. In ODI 11g it is a lot easier. ODI now supports the Java API for XML Web Services (JAX-WS) and you can deploy your data services directly to your Weblogic application server. This all happens automagically with a single click of a button. You don’t have to write a single line of code.
This means that you can use ODI to do the heavy lifting in a data warehouse environment but also in a master data management program to expose your golden record to client applications via data services.
As of ODI version 11.1.1.5.0 you can now also use web service container based authentication. It is now also possible to use fully formed SOAP messages as an input to a web service call.

In part of using Oracle Data Integrator in a Services Oriented Architecture we will create and deploy some ODI data services in Weblogic.

Oracle Warehouse Builder to go into retirement. What to do?

What I had predicted in my post OWB vs ODI: What to do, what to do? a couple of years ago, is now official. Oracle Warehouse Builder will be no more. At the time I had recommended not to embark on any new OWB implementations going forward. I hope you have stuck to this advice.

Anyway, as per Director of Software Development for Oracle Data Integrator, Robert Costin Velisar, OWB 11gR2 will be the final release of Oracle Warehouse Builder. There will be support for the last OWB release throughout the Oracle 12c life cycle. However, Dani Weber from Trivadis raises some serious questions on the way forward for OWB customers.

We are again at crossroads. Once more the question: what to do?

There is no easy way out. Personally I see two options

- Hope that Oracle will provide an official migration path over the next couple of months/years. A migration from OWB to ODI while technically difficult is not impossible. With the new ODI 11g SDK this should actually be quite doable. I personally know of a few shops that have migrated from other ETL tools to ODI using the SDK. Painful and expensive but doable.
- Be a bit more proactive and investigate a ‘roll your own migration’ path. It is certainly possible with the new ODI 11g SDK and there are third party tools that can assist.

No matter which route you pursue you should start to prepare your staff for the transition and provide training in the new technology asap.

The good news is that the ODI family will get bigger now. Not such bad news for the best ETL tool in the world.

Are Oracle’s whitepapers really for the bin?

According to the MOS search they are. whitepaper = wastepaper ??

Oracle data integration book

The guys at Packt publishing have asked me to review a brand new book on Oracle data integration and consolidation: Oracle Information, Integration, Migration and Consolidation. I have written this as a guest blog for the blog best oracle books.

Still waiting on this ODI book. Hope it is out soon. Some interesting stuff in it.