Oracle Magazine - May/June 2010

Database Application Developer
BUSINESS INTELLIGENCE B Y MARK RIT TMAN

Oracle Warehouse
Builder 11g Release 2 and
Heterogeneous Databases
Use code template mappings to transform non-Oracle
database data at the source.

ORACLE WAREHOUSE BUILDER 11g RELEASE 2

Developers using Oracle Warehouse Builder have always been able to leverage the features and scalability of Oracle Database to load, transform, and store their data warehouse data. Oracle Warehouse Builder provides a graphical point-and-click environment that helps you easily map data between sources and targets with a set of rich SQL and PL/SQL transformations and manage the full data warehouse project lifecycle, from capture of the data model to deployment across multiple environments.

Oracle Warehouse Builder 11 g Release 2 extends this capability to non-Oracle sources and targets by leveraging technology from another Oracle product, Oracle Data Integrator. Code templates based on Oracle Data Integrator knowledge modules implement best practices for connecting to, extracting, validating, and integrating data from a wide range of databases and middleware sources. Code templates use a mix of languages and substitution variables to leverage the native capabilities of various datasources. You can also write new code templates for features or datasources not built into Oracle Warehouse Builder.

Code templates that handle various aspects of the extract, transform, and load (E TL) process come in six categories: Load code templates, for extracting data •

from sources

Integration code templates, for integrating •
(loading) data into targets
Control code templates, for validating and •
checking data quality
Change data capture (CDC) code tem- •
plates, for tracking new and changed data
in source tables
Oracle target code templates, for encap- •
sulating database-resident mapping (for-
merly known as Oracle Warehouse Builder
mapping) logic and making it available in
a code template mapping
Function code templates, for deploying •
functions, packages, and procedures in a
code template mapping
Code templates provide the technology
foundation for three key new Oracle
Warehouse Builder features: heterogeneous
database support, support for CDC, and
support for code template mappings.
Heterogeneous;database;support. Oracle
Warehouse Builder 11 g Release 2 sup-
ports access to a range of Oracle Database
instances and non-Oracle databases
through the code template framework.
Source platforms that work out of the
box with code templates include Oracle
Database, files, IBM DB2 UDB, and
Microsoft SQL Server, and support for other
platforms such as Oracle Essbase and
MySQL can be added through Oracle meta-
base scripting.