1 What's New in Oracle Warehouse Builder

Native Support for Heterogeneous Databases

Oracle Warehouse Builder now provides extensive built-in support for non-Oracle databases. JDBC connectivity is added alongside previous support for ODBC and database gateways, and Oracle Warehouse Builder now supports in-database ELT operations on non-Oracle databases. Other enhancements improve access to data from non-Oracle sources such as mainframe and flat file data.

Features in this area include:

• "Code Template Mappings and JDBC Connectivity Support"

• "Enhanced Support for Flat File Imports"

• "Extensible Platform Framework"

• "Java-Based (J2EE) Control Center Agent"

• "Metadata Import from COBOL Copybooks"

SOA Integration Enhancements for ETL and Data Quality

The ETL and data quality functionality in Oracle Warehouse Builder can now be integrated into SOA-style architectures.

Features in this area include:

• "Web Service and SOA Integration Support for ETL and Data Quality"

• "Java-Based (J2EE) Control Center Agent"

Data Warehousing and Business Intelligence Enhancements

The data warehousing-specific support in Oracle Warehouse Builder has improved. These improvements provide smarter dimensional object operators for ETL and support for more storage types for dimensional objects.

Features in this area include:

• "Oracle Business Intelligence Enterprise Edition Integration"

• "Automated Orphan Management Policy for Loading Dimensional Objects"

• "OLAP Cube-Organized Materialized Views Support"

Developer Usability Enhancements

The Design Center user interface in Oracle Warehouse Builder has been extensively redesigned to improve developer productivity and make advanced features more accessible.

Features in this area include:

• "Advanced Find Support in Mapping Editor"

• "Copy and Paste of Operators and Attributes in Mapping Editor"

• "Current Configuration Drop-down Box in Design Center Toolbar"

• "Experts Available in Editor Menus"

• "Expression Editing in Operator Edit Dialog"

• "Improved User Interface for Managing Locations (for Sources and Targets)"

• "Grouping and Spotlighting of Objects in Mapping Editor"

• "Improved Metadata Search and Find for Dependency Management"

• "New JDeveloper-Style User Interface"

• "Operator Comments Included in Generated PL/SQL Code"

• "Organizing Objects with User Folders"

• "Quick Mapper in Mapping Connection Dialog Box"

Administrator Usability Enhancements

Oracle Warehouse Builder administration tasks are simplified and improved by multiple features in this release. Administration has been extended to support new feature areas such as heterogeneous database support and Web services integration.

Features in this area include:

• "Multiple Configuration Management Usability Improvements"

• "Web Service and SOA Integration Support for ETL and Data Quality"

• "Improved Management of Locations Registered in Multiple Control Centers"

• "Heterogeneous Audit and Reporting"

• "Repository Browser Changes"

• "Simplified Oracle Warehouse Builder Repository Upgrades"

ETL Mapping Enhancements

ETL mappings have been enhanced to add new transformation capabilities and to improve the productivity of developers working with flat files and designing and debugging ETL mappings.

Features in this area include:

• "Operator Comments Included in Generated PL/SQL Code"

• "Key Lookup Operator Enhancements"

• "Mapping Debugger Enhancements"

• "Enhanced Table Function Support"

• "Improved Code Generation for Extraction of Peoplesoft Application Data"

• "Support for Subqueries in Join Operator"

• "Web Service and SOA Integration Support for ETL and Data Quality"

Process Flow Enhancements

Oracle Warehouse Builder process flows have been enhanced to integrate with new activity types, out of the box, and to support integration of Oracle Warehouse Builder ETL and data quality with SOA solutions.

Features that enhance process flows include:

• "Support for EJB/Java Activity Type in Process Flows"

• "Support for OMB*Plus Activity Type In Process Flows"

• "Web Service and SOA Integration Support for ETL and Data Quality"

Advanced Find Support in Mapping Editor

The Mapping Editor has been enhanced with advanced find capabilities to help locate and constitute dates to operators, groups, and attributes in a mapping diagram, in the Available Objects tab, and in the Selected Objects tab.

This feature enhances extraction, transformation, and loading (ETL) mapping developer productivity, especially on large and complex mappings and, for example, when working with complex data sources with large numbers of tables, views, or columns.

Oracle Business Intelligence Enterprise Edition Integration

Oracle Warehouse Builder now supports integration with Oracle Business Intelligence Suite Enterprise Edition (OBI EE). This integration includes:

• Derivation of ready-to-use physical, business model and presentation layer metadata for OBI EE from a data warehouse design

• Visualization and maintenance of the derived objects from within Oracle Warehouse Builder

• Deployment of the derived objects in the form of an RPD file that can be loaded into OBI EE.

• Inclusion of the derived objects in Oracle Warehouse Builder data lineage and impact analysis, such that data lineage of objects in OBI EE reports can be traced down to the individual column level.

OLAP Cube-Organized Materialized Views Support

Oracle Warehouse Builder now supports OLAP cube storage in cube-organized materialized views. This brings the performance advantages of such storage to users of Oracle Warehouse Builder data warehouse design.

Code Template Mappings and JDBC Connectivity Support

The new Code Template-based mapping framework enables implementation of data integration techniques and patterns for integration of content from non-Oracle databases with maximum performance and flexibility.

JDBC connectivity supports a wide variety of sources. Additionally, Oracle-supplied or user-developed Code Templates can use other native data integration techniques, such as bulk unloads and loads, for maximum performance on any platform.

Code Template mappings bring heterogeneous data integration support to the familiar flow-based data integration mapping paradigm that leverages existing developer skills with Oracle Warehouse Builder. Code Template mappings that load Oracle targets still support the full range of transformation capabilities available in other Oracle Warehouse Builder mappings.

Copy and Paste of Operators and Attributes in Mapping Editor

In the mapping editor, users can now copy and paste operators within a mapping or across mappings, including attribute settings.

This enhancement saves time and reduces errors in the development of complex ETL mappings that reuse common or similar elements.

Current Configuration Drop-down Box in Design Center Toolbar

In the Design Center, there is now a drop-down box that displays the active configuration of the user. This feature improves usability of the multi-configuration feature.

Enhanced Support for Flat File Imports

There are numerous improvements to support for importing flat files, including a simplified Flat File Sample Wizard, support for multi-character and hexadecimal format delimiters and enclosures, simplified support for fixed format fields, and support for bulk flat file loads into heterogeneous targets.

Flat files are frequently used for simple and high-performance data movement in ETL applications. These changes improve ETL developer productivity and provide more flexible handling of more types of flat files in more scenarios.

Enhanced Table Function Support

Oracle Warehouse Builder now has improved support for table functions, including importing metadata for existing table functions, a wizard for creating table functions from within Oracle Warehouse Builder, and better support for table functions in mappings.

Improved support simplifies using table functions for much more flexible and powerful transformations, such as user-defined aggregations, data mining sampling operators, and so on.

Experts Available in Editor Menus

It is now possible to add Oracle Warehouse Builder experts to the mapping editor menu.

This feature makes it possible to enhance and extend the functionality of the mapping editor, improving developer productivity.

Expression Editing in Operator Edit Dialog

Expressions associated with operator attributes can now be entered directly into an Operator Edit Dialog or Expression Editor, rather than requiring that these expressions be entered into a property in the Property Inspector.

Developers can finish more of their work in one place when creating operators in ETL mappings, thus improving their productivity.

Extensible Platform Framework

Platform extensibility enables users to define new platforms, represent the native data types for those platforms, and create ETL mappings that manipulate that data according to the requirements of the platform.

This feature is part of the overall improved support for heterogeneous databases in this release.

Automated Orphan Management Policy for Loading Dimensional Objects

Orphan management policy for dimensions or cubes refers to the process of handling source rows that do not meet the requirements necessary to form a valid dimension or cube record.

Oracle Warehouse Builder now supports the following orphan management policies:

• Assign a default parent

• Reject orphan rows

• No maintenance

Automated orphan management policies improve ETL developer and administrator productivity by addressing an important cause of cube and dimension load failures.

Grouping and Spotlighting of Objects in Mapping Editor

You can now temporarily or permanently group objects in the Mapping Editor so that they are collapsed to a single icon. This hides complexity in mappings. Users can also spotlight a single operator, which temporarily hides all objects in the mapping except for those objects that connect directly to the operator.

These features improve productivity for developers working with complex mappings with large numbers of operators.

Heterogeneous Audit and Reporting

Auditing and reporting on run time jobs have been enhanced to show execution of all jobs required to support heterogeneous connectivity.

Users receive a unified view of all Oracle Warehouse Builder job executions on both Oracle and non-Oracle platforms.

Improved Management of Locations Registered in Multiple Control Centers

The user interface for managing the registration of locations in control centers has been reworked to improve usability, especially when working with locations registered in multiple control centers.

This change improves productivity of Oracle Warehouse Builder administrators responsible for managing locations across control centers.

Improved Metadata Search and Find for Dependency Management

The Dependency Manager, which is used to browse data lineage and impact analysis information, now includes advanced metadata searching capabilities.

Users can now more easily locate specific objects in large and complex dependency graphs. This improves productivity by making it easier to find specific objects and their lineage, and discover impacts from design changes.

Improved User Interface for Managing Locations (for Sources and Targets)

The user interface for managing Oracle Warehouse Builder locations has been reworked to improve usability and support access to non-Oracle data sources using newly supported connectivity methods.

These changes improve Oracle Warehouse Builder administrator and developer productivity in heterogeneous and Oracle-only environments.

Java-Based (J2EE) Control Center Agent

The Control Center Agent provides a Java-based run time environment that can be installed on Oracle and non-Oracle database hosts. Heterogeneous ETL mappings and Web services-related code are deployed to the Control Center Agent, and run time audit metadata is accessible within Oracle Warehouse Builder.

The Control Center Agent provides fundamental infrastructure for the heterogeneous, Code Template-based mapping support and Web services-related features of Oracle Warehouse Builder for this release.

Key Lookup Operator Enhancements

Extensive changes have been made to the key lookup operator:

• More efficient use of screen real estate.

• Support for non-equality lookups.

• Dynamic lookups, where the lookup table may be modified during the mapping execution.

These changes make the lookup operator more powerful in many situations, including improving Type 2 slowly changing dimension support.

Mapping Debugger Enhancements

There are numerous enhancements to the Oracle Warehouse Builder Mapping Editor, including:

• Improved support for watch points and enabling and disabling of individual break points.

• Support for user-defined type columns.

• Enhanced support for numerous existing operators, such as VARRAY, EXPAND, and CONSTRUCT.

• Support for key lookup and table function operators.

• Support for correlated joins.

• Improved cleanup of debugger-specific objects.

These enhancements improve productivity for ETL mapping developers, especially when working with complex mappings where the mapping debugger adds the most value.

Metadata Import from COBOL Copybooks

In this release, Oracle Warehouse Builder provides the ability to import metadata from COBOL Copybook definitions.

This improves developer productivity, by simplifying working with complex flat file data structures extracted from mainframe sources.

Multiple Configuration Management Usability Improvements

The Oracle Warehouse Builder user interface for viewing and managing multiple configurations has been redesigned to simplify and clarify previously complex tasks, including:

• Editing the configuration values for an object across all configurations

• Configuring groups of objects at the same time

• Using configuration templates to set default configuration values for different object types

• Copying and pasting of configuration attribute values

• Side-by-side editing of attribute values for multiple configurations for an object

These improvements enable users to take full advantage of the flexibility provided by multiple configurations.

New JDeveloper-Style User Interface

The Oracle Warehouse Builder Design Center user interface has been updated to use the Fusion Client Platform, the same core Integrated Development Environment (IDE) platform as Oracle JDeveloper and Oracle SQL Developer.

The advantages of this user interface framework include:

• More efficient and flexible use of screen real estate.

• Support for opening multiple editors of the same type, for example, editing several ETL mappings in different windows.

• More consistent behavior across different parts of the Oracle Warehouse Builder user interface.

This change brings the Design Center in Oracle Warehouse Builder in line with other development tools from Oracle.

Operator Comments Included in Generated PL/SQL Code

PL/SQL code generated for Oracle Warehouse Builder ETL mappings now includes detailed comments to help developers associate specific operators in a mapping with sections of the generated code.

Developers can more easily troubleshoot issues with Oracle Warehouse Builder-generated code that can only be detected when the code is deployed. This additional information enhances developer productivity.

Organizing Objects with User Folders

Users can create hierarchically nested folders to logically group related objects. Folders can be created within Oracle and non-Oracle database modules, non-Oracle database modules, application modules. User folders can be nested as necessary to organize objects further.

You can use folders to group related objects. Any object in one of the supported module types, such as a table or a mapping, can be moved into a folder.

For example, if a single database module contained tables, views, and ETL mappings for product and customer data, folders "Product" and "Customer" could be created, and the objects related to each category moved into the separate folders.

User folders can also be created to contain pluggable mappings. This allows organization of related pluggable mappings into groups.

User-created folders improve ETL developer productivity on complex projects, by making it easier to logically group and manage large numbers of objects.

Quick Mapper in Mapping Connection Dialog Box

In this release, Oracle Warehouse Builder introduces the Mapping Connection dialog box, a spreadsheet-like "quick mapper" for connecting operators in a mapping. This functionality replaces the Auto Mapping dialog box in earlier releases.

This improvement saves developer time and reduces errors when working with operators with a large number of inputs or outputs.

Repository Browser Changes

The Repository Browser has been updated to support foldering, to expose the new types of metadata associated with the release 11.2 feature set, and to support OC4J 10.3.3.

These changes improve manageability for Oracle Warehouse Builder.

Chunking for Parallelizing Large Table Updates

Chunking in Oracle Warehouse Builder automates the use of a "divide and conquer" approach to parallelize the processing of large updates. Users enable chunking for a mapping and define chunking criteria to partition the updates. Oracle Warehouse Builder generates PL/SQL code for the mapping, and at execution time, updates are divided according to chunking criteria, a pool of threads is allocated, and the chunks are processed in parallel.

The benefits of applying chunking include:

• Chunking provides the only method of automatically parallelizing PL/SQL code in Oracle Warehouse Builder.

• Chunking avoids the need for large rollback segments. Set-based SQL statements for large updates require large rollback segments, because a single set-based statement does not perform intermediate commits.

• Large updates can be performed incrementally, and if interrupted, chunks are processed do not have to be processed again. Without chunking, if a large update terminates for some reason, all processing must be repeated.

  See Also:

  The section on chunking data under "Using Oracle Source and Target Operators" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide

Simplified Oracle Warehouse Builder Repository Upgrades

The Repository Upgrade automatically upgrades an Oracle Warehouse Builder repository to the current release with less user intervention.

This feature simplifies the task of upgrading from one release to the next.

Support for EJB/Java Activity Type in Process Flows

A new process flow activity supports calling an EJB or Java program from within a process flow.

Customers benefit from being able to incorporate existing or new logic implemented in Java into their data integration processes.

Improved Code Generation for Extraction of Peoplesoft Application Data

Oracle Warehouse Builder can now generate SQL*Plus code to extract data from database schemas supporting the deprecated LONG data type, such as occurs in PeopleSoft application data sources.

Support for LONG data types used in PeopleSoft data enables Oracle Warehouse Builder users to integrate more effectively with PeopleSoft data or any other data source that uses the LONG data type.

Support for OMB*Plus Activity Type In Process Flows

Process flows now support an activity type for running an OMB*Plus script.

New process flow activity types increase the breadth of user-defined activities that can be incorporated into process flows and thus orchestrated and managed as part of your overall data integration process.

Support for Subqueries in Join Operator

The JOIN operator in Oracle Warehouse Builder now supports several new behaviors related to the use of subqueries in joins:

• Specifying subqueries using EXISTS, NOT EXISTS, IN, and NOT IN.

• Specifying outer joins using the input role instead of the + (plus) sign.

• Generating ANSI SQL syntax for all join types instead of only outer joins.

More flexible handling for JOIN operations improves developer productivity and makes possible more flexible data transformations.

Web Service and SOA Integration Support for ETL and Data Quality

Oracle Warehouse Builder ETL and data quality mapping, process flows, transformations, and data auditors can be published as Web services. Oracle Warehouse Builder can also consume Web services in process flows.

This feature provides point-and-click integration of the ETL and data quality functionality of Oracle Warehouse Builder into SOA-based designs, and facilitates integration with SOA-based process orchestration technologies such as Oracle BPEL Process Manager. (SOA stands for Service-Oriented Architecture).