Automating ETL using Oracle warehouse Builder - Part 2: OWB architecture
July 30, 2003
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:
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.
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:
The design time browser also enables viewing various reports that include custom reports in addition to the lineage, impact analysis and summary reports.
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.
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.
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.
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.
Part 3 in the series will cover a simple prototype implementation of ETL design using OWB.