Rittman Mead - Delivered Intelligence

As well as the 11gR2 release of the Oracle Database coming out last week, accompanying it was the 11gR2 release of Oracle Warehouse Builder, something that the OWB community has been waiting for since the 10gR2 (”Paris”) release back in 2006. This new release includes some features that were originally going to ship in Paris (changed data capture, SOA integration), some that are down to the Fusion project and new tooling (the use of the JDeveloper/SQL Developer UI, something that Oracle Data Integrator 11g will also adopt), and some that are there to facilitate integration with new products that Oracle have acquired since Paris (Oracle Data Integrator, Oracle BI Enterprise Edition, Hyperion and so on). So what are the highlights of this new release, how does it look, and how easy is it to transition from earlier releases of OWB? Well we’ll be covering these new features in a lot more detail on this blog over the next few weeks, but here’s an overview of what we’ve seen that’s new.

The first thing that strikes you when working with this new release is the new user interface. OWB 11gR2 uses the same interface as JDeveloper and SQL Developer which means that developers get a similar experience across all of Oracle’s “Fusion” tools, and you pick up some of the benefits of the JDeveloper interface including tabbed canvases, extensibility (will OWB eventually be a plug-in to a wider Fusion tools framework in the end, as with Microsoft’s Visual Studio, and will Analytic Workspace Manager be eventually delivered in this way?), and easy access to logs and server output.

I really like the tabbed canvas feature, which means that you can have the Data Object Editor, several mappings, a table data view and a process flow for example open and editable at the same time, once you experience this you won’t want to go back to the approach that previous versions of the OWB UI used. I was actually pleasantly surprised by this new UI; in the beta, I didn’t really like it and I thought it a bit slow, a bit cluttered, it felt like we were using JDeveloper rather than OWB, but in the production release I thought it worked really well. In fact the first thing I did when testing out this initial release was rebuild our standard training mappings and data objects and I took to the new interface really quickly, in fact for creating mappings etc I thought that drag and drop of data objects onto the canvas worked better than before. The mapping canvas is stripped down now that you can drag tables and the like directly from the Project Explorer onto a mapping, one thing I couldn’t find though was where to set the configuration of individual data items (tables, MVs etc) so that I could specify the tablespace, storage clauses etc – I expect this is in there somewhere but I couldn’t find it.

In fact the thing that does strike you is that, if you want to use this new release of OWB in the same way as previous releases, you can do. Previously exported MDL metadata files can be automatically converted into ones that are compatible with this release, and once you bring them in the mappings, dimensions, process flows etc are the same as you find in the previous release. Creating new dimensions, for example, uses the same wizard-based process as before, except that you can now choose a third storage option, “ROLAP with MVs” which uses the Cube Organized Materialized View feature in Oracle 11g, as an alternative to pure ROLAP or pure MOLAP.

Populating dimensions and cubes (through the cube and dimension operators) is still the same, and the Data Profiler is still there and still does what appears to be the exact same profiling checks on your data. Where the changes in this release really come in are in the areas of ODI and OBIEE integration, the ability to publish mappings as web services and the ability to leverage changed data capture in your mappings.

The most significant of these enhancements is integration with Oracle Data Integrator, an in particular the Knowledge Modules (or “Code Templates”, as OWB refers to them). ODI Knowledge Modules use a templating system and languages such as SQL, PL/SQL, the equivalents for other database platforms plus languages such as Jython to create scripts that leverage the native capabilities of each source and target platform. As such, at a stroke OWB gains the capability to for example extract and load from Microsoft SQL Server using BCP, load data into Hyperion Planning, perform incremental loads in to Teradata, even read from a Netezza database, although I presume users of OWB taking advantage of it’s “free” edition (i.e. bundled with the Oracle database) will still need to purchase an ODI-EE license to take advantage of these features.

So how well will this work in practice? Well we’ll cover this in more depth on the blog in the next few weeks, and I’ve got an article coming out for Oracle Magazine in the next issue that takes a closer look at this feature, but to me the test of it will be how well developers can make use of, and understand, the two mapping paradigms that now exist in OWB. Either you can create traditional-style OWB mappings that work in the same way as mappings in earlier releases, or you can take advantage of Code Template mappings that use the ODI functionality, and use the ODI agent to execute rather than the OWB Control Center. We’re now in the process of updating our internal and external training materials to incorporate this new functionality, and the test of it will be whether code templates become an integral feature of OWB (like, say, the dimension operator in OWB10gR2) or end up being a poorly-understood, infrequently-used feature (like Experts, say).

Apart from code templates being exposed in their “raw” form as described above, you also find they are used to enabled some of the other new functionality in this release, in particular support for reading from and writing to non-Oracle databases such as SQL Server, and to provide support for Changed Data Capture against Oracle database sources. Changed Data Capture is a feature of OWB mappings that was originally slated to appear in the 10gR2 “Paris” release of OWB but was pulled just prior to the production release. In 11gR2, this feature has now appeared and is delivered through the use of ODI “journalize” knowledge modules, fully integrated into the OWB module definition UI (though will you need to license ODI to use this feature?) This hides the complexity of code templates whilst extending the functionality of OWB, giving it the same ability to use the same Oracle asynchronous changed data capture (as well as synchronous CDC) that I use with ODI in this previous article on OTN.

This same approach of using ODI Knowledge Modules behind the scenes is used to provide native (or at least JDBC) connectivity through to non-Oracle sources and targets, such as Microsoft SQL Server. This makes setting up non-Oracle sources and targets as easy as Oracle sources and targets, something it was a lot harder to do in earlier releases (and required the use of ODBC on Windows and Linux, or expensive Oracle Gateways if you were running on Unix).

Another area that’s new in this release is support for the creation of OBIEE metadata directly from within OWB. This works in the same way as you derived Discoverer metadata in previous OWB releases (and presumably requires the Enterprise ETL Option for the database, or more recently a license for ODI-EE), in that you generally select the tables, facts, dimensions and so on that you’d like to create OBIEE metadata for, the metadata is then generated and then you deploy it to OBIEE.

Where the feature falls down a bit (and this is not really OWB’s fault) is that the end product of this is a UDML file, which you then have to manually apply to your RPD using the NQUDMLexec.exe utillity that comes with the Oracle BI Server. UDML is a bit of a strange area for most customers (and officially unsupported), presumably this will change to xUDML in future releases but the manual application will still stay whilst OBIEE metadata is held in RPD files rather than a relational database.

Whilst deriving metadata is great when you have a dimensional model in OWB (OBIEE dimensions are also created in the business model and mapping layer in this instance), you can also manually create your own logical tables by joining up normalized tables. This manual creation of logical tables doesn’t seem to extend to creating multiple logical table sources, for example, so you’ll still need a copy of the BI Administration tool to apply more complex changes to your OBIEE repository (and of course there’s no facility to import these changes back into OWB, which makes it a bit of a one-way process).

Finishing up for the time being, the last major new feature I’ve come across so far is the ability to expose both regular and code template mappings as web services, and to create application server modules that allow us to create web services directly. This makes use of OC4J (as opposed to WebLogic) as the application server type, presumably this will also allow “classic” Oracle Application Server but it’s a shame it doesn’t use Weblogic, as this is the new standard across tools such as OBIEE (for 11g), Discoverer (now), the BI Apps and so on.

So, what do I think? Well I’ve been testing it on Linux, and from first impressions the new UI and the new features look well put together. Time will tell as to whether they all work as expected (I can see limitations already with the OBIEE integration, and I wonder how understandable the code templates feature is going to be for most people), but it certainly muddies the water even further as to whether you should opt for OWB 11gR2 for your next project (which on the face of it seems to include the best of ODI), or plain vanilla ODI 10g. More on this, and more details on these new OWB features, in due course.

Posted in Oracle Warehouse Builder | 8 Comments »