ETL Prototype using Oracle Warehouse Builder – Part 3

the previous articles, we gained a basic understanding of Oracle Warehouse builder
architecture and became familiar with its various components. My previous
articles on Oracle Streams implementation etc., simply served as pointers
towards standard implementation scenarios for the developer community. However,
here, before we go ahead with building an ETL prototype, I would like to
share my views on choosing the Oracle Warehouse Builder as a tool in an Oracle
data warehouse life cycle management project and my experience (the “pain” or
ease of using it) in building this prototype.

Common concerns of choosing an ETL tool

often, we notice, the common concerns that have been raised by project managers
and other decision makers in a Data warehouse life cycle project is whether a “tool”
is good enough to achieve the desired results in relatively less time than the
traditional way of coding and maintaining the data warehouse objects. Also
raised are issues such as advantages of warehouse builder over the other
innumerable vendor tools
that are available in the market today. Most
importantly, in choosing a good team, the interviewer’s tend to focus on
whether the developer/ candidate has a good “knowledge” of the tool itself with
very less or no importance to the basic concepts of the designing, developing
and maintaining a data warehouse life cycle.

a prototype using Oracle Warehouse Builder will address most of the above
concerns with the emphasis that one of the requirements would be that the
candidate have a clear understanding of building and maintaining a data
warehouse. The focus should also be on the developer’s faculty in at least
one programming language such as UNIX shell script and PL/SQL, Java, (for an
Oracle based data warehouse) C etc. more than the tool itself.

we already know, Oracle Warehouse Builder (OWB – as the name suggests) is a
tool that can be effectively used in a complete Oracle based data warehouse
life cycle development and maintenance environment and can very well integrate
with other Oracle tools such as Oracle Portal, Oracle Discoverer and Oracle
Workflow, for implementing or achieving pertinent business functionality.

the many phases, ETL is one of the most important phases and most often, an
“ETL” tool is evaluated and chosen to implement this phase of the data
warehouse life cycle. Again, we can have disparate source for loading data into
a target warehouse schema and thus the ETL processes can range from being very
basic in nature to very complex designs. In addition, loading data into the
target warehouse schema can involve varied classes of transformations, the most
commonly used being pre-written and provided as “transformation” libraries by
vendors and others that require custom programming are developed by the
warehouse developers.

Choosing Oracle Warehouse Builder

of the pros and cons of using the Oracle Warehouse Builder over traditional
data warehouse development and management are (extensible to other tools too):


All the stages and processes
can be designed, planned and implemented systematically and actual “coding”
(for most part it may not involve coding at all) can be come later during
implementation. The graphic editors will give a complete picture of the how the
components (source modules, mappings, transformations, targets, etc.) are
involved/interact in the system and the associated process flows.

Component management. – It is
very important to keep track of all the components that go into the data
warehouse–the source modules, objects, the mapping objects, the various
transformations, the targets, processes and the real time status.

Change management – A single
change in the requirement can be implemented with relative ease as compared to
implementing in a “home-grown” approach. Such changes can also be efficiently
tracked (audit) and recorded for later analysis.

Most transformations are
readily available and do not have to be written from scratch, thereby saving a
lot of valuable time and resources. This time and resources can be devoted to
other tasks that demand attention such as performance management etc.

Visibility – Visibility is
very important in the transformation process as to what the processes are

Standards – Unlike the “home
grown” approach, design, development and implementation standards can be
effectively enforced.

Deliverables – A data
warehouse project, is never a “do-doing-done” scenario, but among other things
involves a continuous changing scenario (during the development phase and in
most cases in the “maintenance” phases too), data and performance management
(load tuning etc.) and interfacing with other systems. However, most often the
development team has to produce quick deliverables and maintain the least
possible time window in change management or implementations. In addition, Oracle
Warehouse Builder does effectively facilitate such tasks and considerably
reduces the deliverables time window.


The initial stage in
installing and configuring Oracle Warehouse Builder can be a little cumbersome
due to the various versions, compatibility, patches and “bug-fixes” but this is
offset by the many other advantages listed above.

One of the biggest advantages
of using homegrown over vendor provided ETL tools is that as a root level
developer, it has been very easy to get-in and “tweak-and fix” any code as per
the business requirement. However working with any ETL tool requires one to turn
to the product support team for “fixes” in most cases (well, developers do get
work arounds but the fact remains). Thus, there is a trade-off over such
flexibility. However, one thing to note about Oracle Warehouse Builder is,
since all of its transformations are written in PL/SQL, it is easy to
“customize” the code to suit our requirements and even get-into the
transformation and debugging if required. In addition, Oracle now provides the
Java API /SDK to programmatically
manipulate the metadata such as performing batch operations on the meta-data
without having to use their Java client tools.

In a
homegrown environment, it is only the development code/objects and plain old
editors we tend to use (usually the preferred choice) and no Java clients etc.,
which require additional resources. Again, this is
relatively trivial, and offset by the many advantages presented by Oracle
Warehouse Builder and due to the availability of resources. However,
in some cases it pays to use custom code rather than use any tool fabricated code due to performance and managibility reasons. Feel
free to share post suggestions or comments or share your experiences in the Oracle Warehouse
Builder group

Latest Articles