In-Depth

Oracle's Next-Gen ETL Tool--More Details

With a new knowledge encapsulation feature and improved scheduling and security, OWB Paris looks like the real deal

• By Stephen Swoyer
• 08/31/2005

Two weeks ago we took a high-level look at Oracle Corp.’s upcoming 10gR2 release of Oracle Warehouse Builder (OWB), code-named “Paris.” This week we explore many of the other new features Oracle plans to deliver in its next-generation OWB release.

Over the last year, OWB has quietly emerged as a full-blown competitor to enterprise ETL solutions from IBM Corp., Informatica Corp., and SAS Institute Corp. In the OWB Paris release, Oracle looks to solidify its status as a top-tier ETL vendor, with an improved drag-and-drop ETL programming interface, integrated data-cleansing features, and improved connectivity to and with non-Oracle data sources and targets.

Oracle has even more up its sleeve in OWB Paris, including a new “Experts” feature that’s designed to encapsulate esoteric ETL programming expertise, along with improved scheduling and security features. As a result, Oracle officials claim, OWB Paris will be as usable a tool as any tier-one ETL solution. In fact, says Paul Narth, the senior group manager who heads up Oracle’s OWB 10g R2 (“Paris”) product effort, OWB Paris should be a no-brainer for organizations that have a majority investment in Oracle’s relational database.

Encapsulating Expertise

When it comes to knowledge capture, organizations want tools that enable them to easily capture the knowledge or expertise that exists inside the heads of business and technology domain experts and expose it to the user community at large. The idea, of course, is that domain experts are freed up to focus on activities that actually deliver value to the business—instead of spending much of their time fielding requests from everyday users. In a technology practice such as ETL, which (in the case of enterprise ETL tools, especially) is a highly complicated discipline that involves both domain- and tool-specific expertise, this need is particularly acute.

Oracle’s next-generation OWB (via its improved, drag-and-drop Design Time client interface) aims to deliver ease-of-use features on par with best-of-breed ETL players such as IBM and Informatica. OWB Paris’ “Experts” technology takes this ball and runs even further with it, says Narth. After all, nobody—not IBM, Informatica, or Oracle, for that matter—is arguing that users can sit down in front of an ETL design studio and make much sense of it. But if domain experts can encapsulate esoteric ETL design processes and expose them as services that can (with varying degrees of ease) be incorporated into third-party or custom applications, they don’t have to.

For example, domain experts can use the OWB Experts editor to declaratively build interactive applications using OWB’s own UI—or a UI of their own design. These applications can run on a standalone basis, says Narth (although the OWB software must be installed) with the idea being that the end user needn’t know that OWB is running on the back end. Optionally, Expert applications can run within the OWB Design Center UI—either separately or attached to other items. “If you right-click an item, it seems like a natural extension of the product and could, say, invoke some wizard-like application that a user has built,” Narth explains.

This feature has several potential applications. “A use-case where this feature is helping is to load data from previously unknown sources into known targets. Basically as an ETL app developer, I would like to give end users the ability to load data in my system, but I don't know what the source looks like—if I did I could just write the job for them.”

Similarly, ETL developers can easily expose OWB features and functionality—like the flat file sampler wizard (which is similar to Excel), the database metadata integrator, ERP integrators, and so on—in other applications, too.

“[These] can be exposed standalone to end users to specify where [or] what the data structure is, and then point it at a target object [such as a table], and then they just say 'load,’” Narth explains. “The expert can generate all the necessary metadata [such as import the objects, create the map, etc.] to load the data from the source and load it into the target system, and then deploy and execute this code and just show the end user the loaded data—and all this time they didn't know they were using OWB.”

Improved Scheduling, Enhanced Security Model

ETL started out as a mostly batch-driven process. This has changed, of course, but even with the growing emphasis on “trickle” ETL and real-time information delivery, scheduling remains an important ETL component.

In this respect, OWB has an improved story to tell: It includes two new scheduling-oriented objects, called “Schedule Modules” and “Schedules.” The former effectively serves as an object container for the latter’s objects; the “Schedule” contains information about when and how to run an OWB Mapping Process flow. Both are powered by wizard-driven interfaces that are designed to simplify the process of scheduling ETL tasks. There’s also an Editor component that lets ETL programmers configure more advanced scheduling options.

Ditto for Security. OWB Paris ships with an improved role-based security model. This means that administrators can easily configure permissions (e.g., full control or read-only access) for individual OWB objects—a nice change of pace from OWB’s previous iterations.