Oracle Warehouse Builder

What is Oracle Warehouse Builder?

An all in one solution to build, deploy, and run Data Warehouse ETL (Extract, Transform, Load) code. Allows you to Extract or Deploy data using Transportable Modules. Extract data from remote source databases or transferring DW data to remote Data Mart DB.

Create all DB objects – tables, views, materialized views, packages, procedures, functions
Create schedules to execute DW batches.

OWB Components

OWB installation creates repository schema in DW database

Holds OWB metadata.
Design Center installed in client PC. Java based. Manipulates OWB metadata.
Control Center – Used to Manipulate the target DB schema, deploy objects and execute code.
Repository browser – web based reporting of data held in repository, executions, deployments etc.
OMB Plus – command line interface. Script updates to the metadata repository. Used for bulk updates.
Target schema usually exists in a separate database but can be in the same database.

OWB Key Features

Seamless integration with Oracle technologies. Integrates with other database platforms and flat file data sources.
Data Mapping – Single unit of processing bringing together one or many sources tables to populate one or many target tables.
Process Flows – Use process flows to interrelate mappings. Perform activities external to Warehouse Builder such as email, FTP commands, and operating system executables.
Scheduling DW batches via Oracle Workflow.
Transportable Tablespaces/Data Pump
Built in Data Profiling to “fix” data before DW batch even begins. Spot patterns in data, from there define “rules” for error detection/ data quality.
Built in Data Cleansing tools for De-dup data and fuzzy matching.
Impact and Lineage analysis
Handles slowly changing dimensions.
Snapshot OWB Metadata for version control
Export OWB metadata to files for version control and deployment of entire projects.
Any existing ETL type code? Use transformations to re use existing code.

Data Mappings

Data Mappings allow the graphical modelling of Data Flow

Map from Source to Target schemas
Integrated Data Quality
Match/Merge
Profiling
Pre and post processing
Pass in/out parameters
Generates SQL & PL/SQL or SQL loader scripts
Real-Time Streams (11g) & Batch
Use custom data transformations. (Re-use of code)
Built in debugging capability
Automatically generates code to log rows processed and exception handling
Row based, set based processing
Pluggable mappings for reusability of logic

Many built in data operators operations typical in DW processing such as Joiner, aggregations, filter, de-dup, expressions, match merge, pivot and unpivot, Set (union), Split data, Sort.

Process Flows

Process Flows are a graphical and declarative modelling of Process/Work Flows. Use process flows to interrelate mappings and describe dependencies between them. Perform activities external to Warehouse Builder such as email, FTP commands, and operating system executables. Call sub processes – other process flows.

Warehouse Builder process flows comply with the XML Process Definition Language (XPDL) standard set forth by the Workflow Management Coalition (WfMC).

Scheduling

OWB provides an interface for creating cronjob style schedules for process flows.

Data Transfer

Use OWB to move large data volumes quickly and easily between Oracle instances.

Perform fast bulk loads
Performance & usability gains over traditional technologies (DB Links, Flat Files,…)
Technology varies according to source:
9i source - Transportable Tablespaces
10g source – Data Pump

Fastest way to transfer data between two Oracle databases using Data Pump. Typically we don’t let users query the data warehouse directly. Rather we copy to data marts or a separate copy of the Data Warehouse database.

Looking for assistance in your Data Warehouse project using Oracle technologies? Contact Us. Alabra Consulting with over 10 years experience developing ETL routines for the Oracle DBMS.