Rittman Mead - Delivered Intelligence

A couple of projects that I’ve worked on recently had chosen Oracle Warehouse
Builder over

Informatica’s Powercenter. It was interesting therefore to see a new article
by Rajan Chandras that

looked at the latest version of Powercenter, and to compare how
Informatica’s offering compared to OWB10g.

According to

"(Re)Enter The Leader", Powercenter has a similar architecture to OWB:

"PowerCenter 7.0 (I’ll call it PC7) is an ETL
tool in the classic mold: data extract, transform, and load logic is
constructed in a (mostly) sequential arrangement of graphical objects that
flow from source to target. The objective is conceptually simple: Read data
from source, transform it as needed, and write it to target. Reality is a
little more complex, of course, and the construction of logic happens at three
levels.

At the lowest level, individual graphical objects can be sources, targets, or
transformations (sources and targets can be themselves considered as special
types of transformations). A source transformation is used to read from a data
source, and supply that data in sequential row-wise fashion for subsequent
processing. At the other end of the logic stream, the target transformation
receives data (again, in row-wise order) and writes it out to recipient data
structures. The remaining intermediate transformations do just that
transform data values as required.

Sources, targets, and transformations are assembled in a daisy chain to form
the next level of processing, which in PC7 is called the "mapping." A mapping
is the end-to-end flow of logic, from one or more source transformation to one
or more target transformations.

The execution of the mapping, called the "workflow" in PC7, provides the third
level of the overall logic. The workflow provides for the execution of
multiple mappings and dependencies among mappings. In standard programming
terms, the transforms are the syntax and components of the program, the
mapping is the overall program itself, and the workflow is the execution and
production of one or more programs.

There are PC7 components that correspond to these levels. The PowerCenter
Designer is the programming integrated development environment (IDE), where
you "assemble" all the sources, targets, and transformations to create a
mapping. The PowerCenter Workflow Manager is used to build a workflow around
the mapping. The Workflow Monitor provides production support capabilities for
the workflow. In addition, there are the PowerCenter Repository Manager and
the Repository Server Manager, which provide administration capabilities for
the PC7 Repository (more on the this a little later)."

All sounds familiar, with Powercenter’s graphic
objects being the source and target objects you drop on to a mapping canvas,
together with the PL/SQL transformations, mappings being the same in the two
tools, and Powercenter’s workflow being the same as the workflow interface in
OWB10g.

Reading the rest of the article, some interesting
similarities and differences were:

• Informatica Powerexchange seems similar to
  Oracle gateways, but with connectors to Peoplesoft and Siebel in addition to
  the SAP connectivity that both tools offer. Support is good in both tools for
  non-Oracle databases (DB2, SQL Server, Teradata, Sybase and so on)
• One major difference is that OWB will only
  populate Oracle 8i, 9i or 10g data warehouses, whilst Informatica works
  against any major vendor (thanks Duncan for
  pointing that one out,
  one of those ‘so obvious if you’re used to OWB, I forgot to mention it’
  moments…)
• Both tools allow you to built reusable
  components for transforming data, with Powercenter’s being specific to the
  tool whilst Oracle’s are regular PL/SQL functions and procedures.
• Informatica, like Oracle, are making a big noise
  about grid computing. "PC7 offers server grid capabilities, too, by which
  PowerCenter can distribute loads across heterogeneous Unix, Windows, or
  Linux-based computing platforms. Although grid capabilities may seem exciting,
  I don’t believe they match real-world need for grid computing yet, and I
  wouldn’t recommend using them in place of other industry grid solutions."
• The main architectural different between
  Powercenter and OWB is that Powercenter has it’s own ETL engine, that sits on
  top of the source and target databases and does it’s own data movement and
  transformation, whilst OWB uses SQL and the built-in ETL functions in 9i and
  10g to move and transform data. Interestingly the article observes that the
  Informatica approach can be slower than the approach used with OWB. "Also,
  be aware that ETL tools are in general a slower (if more elegant) alternative
  to native SQL processing (such as Oracle PL*SQL or Microsoft Transact SQL)."
• Powercenter’s use of web services and
  distributed computing looks more developed than OWB’s. "PowerCenter
  Web services are managed through the Web Services Hub, another component of
  the architecture, which supports standards such as Simple Object Access
  Protocol (SOAP), Web Services Description Language (WSDL), and Universal
  Description, Discovery, and Integration (UDDI). The architectural components
  can be collocated on a single server or spread across diverse servers, which
  allows solution parallelism, flexibility, and scalability."
• Powercenter starts at around $200,000 (yikes!)
  although there is a "Flexible pricing model.". OWB is licensed as part
  of 10gDS which is around $5000 per named user, although you’ll need the
  Enterprise Edition of the 8i, 9i or 10g database to provide the ETL
  functionality.

Historically, customers chose Informatica when they
had lots of different database sources and targets, and the transformations
between them were complex and interwoven. At one point, if you wanted a
graphical interface for building transformations, tools such as Informatica,
Constellar,
Genio and
so on were the only game in town, and you were looking at a minimum investment
of between $50,000 and $100,000 to deploy these tools. The introduction of
DTS
by Microsoft and OWB by Oracle suddenly changed the market by providing much of
the functionality of these tools as either a free database component or as a
low-cost add-on. Vendors like Informatica have responded by introducing
additional new features (such as web services integration, distributed loading
and transformation, and so on) but it’s now the case that, if you have a fairly
straightforward need to graphically extract, transform and load data, you’ll
probably find the vast majority of your needs are now met by tools like OWB, at
a far lower cost.

Interestingly, Informatica also have their own BI
query tool called

PowerAnalyzer. Sold separately from PowerCenter but designed to be used in
tandem with their ETL tool, PowerAnalyzer is a web-based query tool that creates
ROLAP queries against Oracle, IBM, Microsoft and Sybase datasources. Designed to
be deployed using J2EE application servers, it also comes with an Excel
interface and, as

Seth Grimes reports for Intelligent Enterprise, a range of prebuilt analytic
applications:

"The analytics dimension features conventional
query and reporting and online analytic processing-style slice-and-dice
analysis, and also optional packaged modules for CRM, financial, HR, supply
chain, and cross-functional analysis. Its ability to visually model analytic
workflows is one that’s not yet common. It’s intended to facilitate root-cause
analysis, although this capability appears to be limited by the packaged
analytic framework and other architectural strictures. For example, the highly
promoted Excel support doesn’t include database write-back. It also lets
analysts embed business logic in private spreadsheets rather than in a
repository, which can prove limiting when logic locked in a spreadsheet isn’t
visible to the workflow modeler and other non-Excel interface elements. Last,
PowerAnalyzer delivers only the mainstream data-analysis functions that are
found in competing BI tools, underscoring Informatica’s view that integration
and usability rather than analytic depth are the keys to market share.

PowerAnalyzer 4.0 is a credible entry in the larger BI market and will prove
compelling for organizations that require an easy, nondisruptive path for
integrating mainstream analytics into an existing computing environment."

That being said though, PowerAnalyzer is rarely (if
ever) deployed by non-Informatica PowerCenter customers, limiting it to a fairly
small audience who have a particular need for high-end ETL and specific industry
analytic templates. Looks interesting, though, albeit with a hefty pricetag.

Posted in Oracle Warehouse Builder | 2 Comments »