Automating ETL using Oracle warehouse Builder – Part 2: OWB architecture

This second article of this
series briefly covers the Oracle Warehouse Builder architecture, and how it
adapts to the industry standards.

Some of the typical steps or
phases that are part of the datawarehouse life are:

1. Designing
   the DW model and the physical structures.

2. Identifying
   the source(s). (Database systems, flat files etc.)

3. Designing
   the extraction, transformation and loading processes (ETL).

4. Identify
   and manage the changes in source definitions.

5. Manage
   and upgrade the target warehouse structures.

6. Integration
   with other business intelligence tools, such as reporting tools etc.

7. Importing
   or exporting Metadata
   to facilitate data exchange between different tools (openness).

8. Design
   and establish a metadata-reporting environment.

The above-mentioned steps may
span multiple and often disparate systems, resources and even functional areas,
which give rise to significant complexities. In order to facilitate integration
between systems quickly and easily, the Object Management Group (OMG) provides a
standard, the Common Warehouse Metamodel
(CWM) that allows for the interoperability of
enterprise applications. Many ETL vendors (including Oracle)
support the CWM standard that aids in faster implementation cycles.

In
order to prototype the ETL design process (or any phase in the DWLC) with a
tool, it is necessary to understand the tool architecture and the method in
which it supports or aids in the design, development, implementation and
management of the phases or standards mentioned above.

Familiarity with DWLC phases and
the CWM goal, will lead to a better understanding of Oracle warehouse builder’s
architecture and components that will be used for prototyping and also in evaluating
the suitability of other ETL tools in your specific environment.

The OWB architecture

The OWB architecture is shown in Figure 1 below. The core concept in the OWB architecture
is OWB repository, which is stored in the Oracle database server. The basic
architecture is comprised of two components.

1. The design time component
   

2. The runtime component.

Together, the repository and the
components facilitate design, development and implementation of all phases of
DWLC as can be inferred from the figure.

Design time component

The OWB design time component
provides a highly scalable metadata repository, design editors and metadata
reporting tools that enable creating and publishing metadata.

The metadata repository contains
design objects like source definitions, library based and custom
transformations etc.

Some of the editors include:

1. Module
   editors

   The module editors define the source/target modules and editing modules (containers
   or collection of the DW objects).

2. Object
   editors

   The object editors include the
   table editor, dimension editor, fact editor etc. that enable you to edit object
   properties.

3. Mapping
   editor

   The mapping editor primarily enables
   you to design and configure how the data gets loaded into the target data
   warehouse, and is used to design a graphical representation of the relation
   between the data source objects that are used to populate the data warehouse
   target objects.

   The editor provides a set of
   operators that you can include with the mappings along with standard or custom
   transformations if required, such as key look, generating sequence etc. It
   also enables validation and generation of the code used to populate the data warehouse
   target.

4. Code
   editor
   The code editor enables editing or customizing OWB generated code.

The design time browser also
enables viewing various reports that include custom reports in addition to the
lineage, impact analysis and summary reports.

Runtime component

The OWB runtime component includes
the runtime repository and the audit viewer, which enables you to view the
runtime information of the ETL jobs that were executed using Oracle tools such
as Oracle Enterprise manager (OEM). These jobs are created in the OEM
repository when they are registered with the OEM in the mapping editor.

Integrators

OWB is capable of extracting data
from various sources including Flat files, SAP R/3, other Oracle sources, other
database sources such as DB2, and older OWB version repositories.

The integrators are components
that facilitate the extraction from non-Oracle systems such as SAP R/3
integrator or in such cases as from an older version OWB repository; it
provides the MDL (Meta Data Loader) utility for extraction of the metadata.

Bridges

This component enables transfer of
the metadata from OWB to other Oracle tools such as Oracle Discoverer, OLAP
etc., thus supporting the OMG CWM standards.

Metabase

This intermediate layer provides
services to all producers (source) and consumers (targets, Oracle tools) of
data. Services include multi-user and locking service, generation and
validation services etc.

API’s and SDK

From OWB 9.0.3.x onwards Oracle
provides fully documented public Java API’s and SDK that enable developers to programmatically
manipulate the metadata such as performing batch operations on the metadata
without having to use the OWB client GUI tools (which are also written in Java
and make use of these APIs).

The OMB (Oracle MetaBase) Plus
utility enables developers to use the OMB scripting language which gives
greater power and flexibility to extend the features of OWB such as a greater
control in integrating with other business intelligence products.

Figure 1

Conclusion

Part 3 in the series will cover a simple prototype
implementation of ETL design using OWB.

References:

Oracle
Warehouse Builder portal

Oracle
warehouse builder collateral library

OWB
documentation

The OMG data warehousing
resource page

Intelligent
Enterprise – article by Vijay Saradhi & Martin Simoneau

Gartner
Report on OWB – Jan 2003