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 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 allow the graphical modelling of Data Flow
- Map from Source to Target schemas
- Integrated Data Quality
- 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 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).
OWB provides an interface for creating cronjob style schedules for process flows.
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.