TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

Here at Last: Oracle Delivers Long-Awaited Extract Transform and Load Overhaul

Oracle positions OWB R2 as a full-blown competitor to enterprise ETL solutions from IBM, Informatica, and SAS Institute.

• By Stephen Swoyer
• July 26, 2006

Oracle Corp. today finally released its long-gestating—and oft-delayed—Oracle Warehouse Builder, Release 2, formerly code-named “Paris.”

Oracle positions OWB R2 as a full-blown competitor to enterprise ETL solutions from IBM Corp., Informatica Corp., and SAS Institute Corp. The database giant isn’t necessarily telling tales out of school, either. Research powerhouse Gartner Inc. last year placed OWB in the Leader quadrant of its Magic Quadrant metric. Oracle was the only relational database vendor in the Leader field, rubbing elbows with established heavyweights such as Informatica and IBM/Ascential.

Oracle announced OWB R2 just as BI This Week went to press. Officials weren’t immediately available for comment, but Oracle has briefed TDWI on several occasions about the next-gen Paris release. What follows is a summary of salient OWB R2 features, including improved bi-directional ETL capabilities and a revamped GUI-based design client. We’ll explore pricing and packaging, along with other important OWB R2 features, in a follow-up piece next week.

Has Oracle Finally Made Its Peace With Non-Oracle Sources?

OWB hasn’t always had the best story to tell when it came to heterogeneous connectivity. Of course, Oracle itself hasn’t exactly encouraged the use of non-Oracle data sources in its self-aggrandizing relational database vision. Oracle has always tolerated non-Oracle data sources, to be sure, but mostly in the sense that it supports connectivity (one way replication) from heterogeneous sources to the Oracle database—and not vice-versa.

OWB R2 changes that—to some extent. Oracle has said it plans to facilitate connectivity into non-native data sources by means of its Oracle Transparent Gateways (OTG), a series of data source-specific adapters. The OTGs in every flavor (e.g., for DB2, Sybase, PeopleSoft) have long supported bi-directional ETL—e.g., organizations can pull data from DB2 into Oracle, and can (theoretically) push data out from Oracle into DB2.

Nevertheless, OWB itself hasn’t supported bi-directional ETL: In OWB R1, Oracle doesn’t let customers push data out from the Oracle database into non-Oracle data sources. With OWB R2, that’s changed for most data sources. R2 will support bi-directional ETL into a number of different target platforms (including all major relational databases)—with the show-stopping exception of SAP. The technology to do so is probably in the OTG adapter for SAP, concedes Paul Narth, a senior group manager with Oracle who heads up the OWB R2 product effort, but for obvious reasons, Oracle isn’t enabling it in OWB.

In other words, OWB R2 does support ETL from SAP applications into the Oracle database, but not vice-versa. “The reality is that we live in a heterogeneous world,” said Narth, in an interview last year. “We could try to ignore that completely, but we would rather embrace that. We would rather that you work on Oracle, no apologies there, but if you have targets in other environments, we will push data to those.”

OWB R2 also gets a usability overhaul, complete with support for object-oriented ETL modeling capabilities and drag-and-drop workflow features. Oracle’s GUI-based ETL modeling tool, Design Time Client, has been tweaked such that users can drag-and-drop both canned and custom-built objects to create ETL process flows. (Creating custom objects is largely a point-and-click exercise, Oracle officials claim.) If the data is being extracted from an Oracle target data source, OWB R2 automatically generates PL SQL code that’s optimized for the specific version of the source database (e.g., PL SQL optimized for Oracle 9i, or PL SQL optimized for 10g). OWB R2 can also do the same thing with straight-up SQL for non-Oracle relational data stores, says Narth.

OWB R2 delivers a lot of other goodies, too, officials say, including improved data quality and data profiling capabilities, revamped scheduling and security features, metadata management facilities, enhanced cube building, and a host of other improvements. Check back with us next week as we explore the new OWB R2 release in more detail, focusing especially on how Oracle plans to package and price its would-be enterprise ETL contender.