Preface

This preface includes the following topics:

Purpose

Oracle9i Warehouse Builder is a comprehensive toolset for practitioners who move and transform data, develop and implement business intelligence systems, perform metadata management, or create and manage Oracle databases and metadata. This guide describes how to use Warehouse Builder to:

Create a definition of a data warehouse.
Configure the definitions for a physical instance of the data warehouse.
Validate the set of definitions and their configurations.
Generate a set of scripts to create and populate the data warehouse instance.
Generate data transformation scripts.
Deploy and initially load the data warehouse instance.
Maintain the physical instance by conditionally refreshing it with generated scripts.
Integrate Warehouse Builder metadata with other Business Intelligence products.
Populate Oracle Discoverer EULs and OLAP catalogs for analyzing the data warehouse.

Audience

This guide is intended for data warehouse practitioners, including:

Business Intelligence application developers
Warehouse architects, designers, and developers--especially SQL and PL/SQL developers
Data analysts and those who develop extract, transform, and load routines
Developers of large-scale products based on data warehouses
Warehouse administrators
System administrators
Other MIS professionals

In order to use the information in this guide, you need to be comfortable with the concepts of Relational Database Management Systems and Data Warehouse design. For information on data warehousing, refer to the Oracle9i Data Warehousing Guide. Also, you need to be familiar with Oracle's relational database software products such as Oracle9i, SQL*Plus, SQL*Loader, Oracle Enterprise Manager, and Oracle Workflow.

How This Guide Is Organized

The User's Guide contains the following chapters and appendixes.

Chapter 1, "Overview of Oracle9i Warehouse Builder", describes the overall product architecture and capabilities, as well as the benefits, of using Warehouse Builder. It also describes how to logon and introduces the components displayed in the main console and preferences and options you can set up.
Chapter 2, "Getting Started with Warehouse Builder", describes how to start building projects using Warehouse Builder features.

Part I, "Defining Source and Target Objects", addresses creating and configuring the metadata definitions of objects from which you source data and to which you load data. This part includes the following chapters:

Chapter 3, "Defining Oracle Data Objects", describes how to create definitions for relational and dimensional target objects in Warehouse Builder. The relational objects include Tables, Views, Materialized Views, Sequences, and External Tables. The dimensional objects include Dimensions and Cubes.
Chapter 4, "Importing Data Definitions", describes how to import definitions for data sources using the Warehouse Builder import wizards. This chapter describes how to import data definitions from Oracle and non-Oracle database systems and flat file sources.
Chapter 5, "Configuring Data Objects", describes how to assign physical properties to the target and source objects you defined in your logical model. The chapter first describes how to create indexes and partitions for design objects such as tables, materialized views, dimensions, and cubes defined in Warehouse Builder. The chapter then discusses how to assign physical properties to Warehouse Builder object definitions.

Part II, "Designing ETL Objects", addresses designing and configuring the extraction, transformation, and loading processes that you use to move data. ETL objects include mappings, transformations, and process flows. This part includes the following chapters:

Chapter 6, "Designing Mappings", describes how to create and design mappings to extract data from sources, transform it, and load it into targets.
Chapter 7, "Debugging Mappings", describes the Warehouse Builder debugging capabilities for data flows within the Mapping Editor.
Chapter 8, "Using Mapping Operators", provides details on how to use the different operators in a mapping to transform data. This chapter also describes how to use the Expression Builder to create expressions that operate on data as it flows from a source to a target.
Chapter 9, "Using Transformations" provides details on how to import, create, and use transformations in mappings.
Chapter 10, "Designing Process Flows", describes how to create and design process flows that interrelate mappings and other activities internal or external to Warehouse Builder (such as email, FTP commands, and operating system executables).
Chapter 11, "Configuring ETL Objects", describes how to configure mappings and process flows for deployment to a specific database instance. It also discusses the advantages and provides instructions on how to use partition exchange loading for faster performance.

Part III, "Deploying and Executing", addresses deploying the objects you have defined and executing the processes you have designed to move data. This part includes the following chapters:

Chapter 12, "Validating Objects", describes how to verify the integrity of the objects you have defined and identify any problems or possible errors that would occur during deployment. It also describes how you can view generated scripts (for example, DDL or PL/SQL scripts).
Chapter 13, "Deploying Target Systems", describes how to deploy the physical instance of your target data system. It describes how to upgrade your target system and how to save deployment scripts for future uses. It also describes how to run mapping and process flow scripts that have been previously deployed to a target data system.
Chapter 14, "Auditing Deployment and Execution", describes how to use Warehouse Builder's web-based Runtime Audit Browser to view information about deployments and executions.

Part IV, "Managing Metadata", addresses metadata management tasks, such as import, export, and snapshots for purposes of backup and versioning, and metadata browsing and reporting. This part includes the following chapters:

Chapter 15, "Importing and Exporting with the Metadata Loader (MDL)", describes the utilities provided to import and export metadata for the purposes of backup and upgrade.
Chapter 16, "Metadata Change Management", describes how to use metadata snapshots to perform metadata change management. It describes how to use snapshots to backup, versioning, compare, and restore metadata.
Chapter 17, "Metadata Browsing and Reporting", describes how to use Warehouse Builder's web-based Design Browser to browse metadata and view metadata reports.
Chapter 18, "Managing Warehouse Builder Browser", describes how to configure Warehouse Builder's portal-based Design Browser and how to create custom metadata reports.
Chapter 19, "Extending Warehouse Builder Functionality", describes the plug-in interfaces for the Warehouse Builder Security Registration package (including how the central schema owner can register all the prospective Warehouse Builder users to the repository), the Warehouse Builder Metadata Loader, and the Warehouse Builder Runtime Platform.

Part V, "Integrating and Extending Warehouse Builder", discusses the features that allow you to integrate Oracle9i Warehouse Builder with other Business Intelligence products. This part includes the following chapters:

Chapter 20, "Data Quality: Name and Address Cleansing", describes how to perform parse table maintenance if you have purchased Oracle Pure Name and Address and are using the Name and Address operator to cleanse customer data.
Chapter 21, "Using SAP R/3 Data in Warehouse Builder", describes how Warehouse Builder integrates with SAP source systems. You can import definitions for SAP data into a Warehouse Builder repository, use the SAP definitions in mappings, generate ABAP or PL/SQL code, and deploy it to your target. This chapter also describes how to extract and load SAP data from the source system into a target.
Chapter 22, "Integrating Warehouse Builder Metadata with Other BI Products", describes the utilities provided for interchanging Warehouse Builder metadata with that of other Business Intelligence products.

Part VI, "Appendix", contains material referenced by earlier parts. This part includes the following appendices:

Appendix A, "Keyboard Shortcuts" lists keyboard shortcuts for Warehouse Builder commands.
Appendix B, "Reserved Words", lists the words reserved for Warehouse Builder.
Appendix C, "Using the XML Toolkit", describes how to retrieve and store data from XML documents in a target schema.
Appendix D, "Warehouse Builder Public Views", lists the Warehouse Builder Public Views that can be accessed through SQL. These views can be used to extend the reporting capabilities of Warehouse Builder.
Appendix E, "Public Objects", lists the first, second, third, and fourth class objects on the navigation tree. You can use this information to better understand the behavior of user-defined properties and metadata snapshots.

New in Release 9.2

Enhancements to the Mapping Editor: Mapping Debugger

Warehouse Builder now provides you with extensive debugging capabilities for your mappings from within the Mapping Editor. Use the Mapping Debugger to locate logical design errors in your mappings. The new features allow you to step through the data flow of a mapping using comprehensive debugging functions such as setting breakpoints and watches and interactively changing test data.

Enhanced Support of Multiple Targets: Correlated Commit

This release introduces a new commit strategy for mappings with multiple targets. In previous releases, Warehouse Builder performed independent commits. That is, Warehouse Builder committed and rolled back each target separately and independently of other targets. In addition to this option, Warehouse Builder now also performs correlated commits. Warehouse Builder considers all targets collectively and commits or rolls back data uniformly across all targets. Use the correlated commit when it is important to ensure that every row in the source impacts all affected targets uniformly.

Direct Partition Exchange Loading

In previous releases, Warehouse Builder by default created a temporary table for mappings that required additional processing of source data before exchanging partitions. This occurred when the mapping contained remote sources or multiple sources joined together. Beginning in this release, you can now by-pass the creation of a temporary table and directly swap a source into a target. Use Direct PEL in a mapping to instantaneously publish fact tables that you loaded in a previously executed mapping.

Data Quality Features

Multiple Name and Address Software Providers: Beginning in this release, Warehouse Builder is compatible with multiple certified Name and Address software providers. Third-party vendors can license Name and Address software directly to you for use with Warehouse Builder. This allows you to choose a name and address provider whose offering is the most appropriate for your project.
Name-Address Operator Wizard: In previous releases, you defined the Name-Address operator using the mapping canvas and the operator Configuration Properties sheet. For improved usability, Warehouse Builder now enables you to use a wizard and Operator editor to create and edit the Name-Address operator.
Match-Merge Operator: Warehouse Builder incorporates the data quality functionality formerly available in Oracle Pure Integrate. You can use the Match-Merge operator available in the Mapping Editor to define business rules for matching and merging records. The Match-Merge operator together with the Name-Address operator support householding, the process of identifying unique households in name and address data.

Metadata Change Management

In a previous release, you could perform metadata change management using the OMB Plus scripting utility. Beginning in this release, you can also access these functions from the Warehouse Builder client user interface. Metadata change management enables you to take snapshots of metadata objects and use them for backup and history management. Snapshots are supported for any object on the navigation tree and can store information about an object alone (such as a table or module), or the objects within it as well (such as the tables within a module).

Extending Oracle9i Warehouse Builder Functionality

Security: Warehouse Builder now provides advanced repository security and auditing options that you can implement according to your security requirements. The advanced security options include the following:

Proactive Security: Warehouse Builder enables you to plug in a customized security PL/SQL implementation package in the Warehouse Builder repository to provide tailored access control to users according to the security rules defined by your organization.

Reactive Security: Warehouse Builder enables you to track audit information based on the metadata history and to determine security policies from such audit information.

Data Stewardship: Warehouse Builder enables an individual or a group of individuals to "own" portions of the metadata rather than the technical administrators. Metadata ownership thus becomes an important component of metadata security management.
RAC Support: With the 9.2 release, Warehouse Builder provides increased support for RAC features. Warehouse Builder now supports the use of net service names in the runtime. This enables you to plan maintenance of nodes in a cluster without having to reconfigure the runtime environment. Warehouse Builder also provides an increased availability in the runtime service. For example, if either the service instance or its associated node fails or is taken out of service, then the runtime service instance on a different node can take over. While the Warehouse Builder design repository can also be used in a RAC cluster, it will not take advantage of any failover features of RAC for this release.

Enhancements to Flat File Support

ZONED Data Type Support: Warehouse Builder now allows you to load fixed format data files containing ZONED decimal data. In the Flat File Sample Wizard, specify the ZONED data type for a flat file you import. The format for ZONED data is a string of decimal digits, one per byte, with the sign included in the last byte. (In COBOL, this is a SIGN TRAILING field.) The length of this field is equal to the precision (number of digits) that you specify. You may also specify a scale, which is the number of digits to the right of the decimal point.
DECIMAL Data Type Support: DECIMAL data is in packed decimal format; two digits per byte, except for the last byte, which contains a digit and sign. The DECIMAL data type includes precision and scale and therefore can represent fractional values.

Enhancements in Database Connectivity

Warehouse Builder now enables you to create public database links that can be shared across a database. Public database links can be created by repository owners, as well as any user with the CREATE PUBLIC DATABASE LINK privilege.

Warehouse Builder Available on HP-UX and AIX

Starting with this release, Warehouse Builder is available on HP-UX and AIX platforms. This new availability is an addition to the UNIX (Solaris and Linux), and Windows (NT, 2000, and XP) platforms, which have been available from previous releases. (Note that the MITI Bridges feature is only available on Windows platforms and the Name and Address Server is only available on Windows and Solaris platforms.)

Public Application Programming Interface

Starting in this release, Warehouse Builder now includes a Public Application Programming Interface (API). To access the API, unzip and extract the following file to a folder on your local machine:

<owb home directory>\owb\lib\int\pubapi_javadoc.jar

Double click on the file index.html. Select the Help link for information on how to use the API.

Added in Release 2 (9.0.4)

The following new features were introduced in Oracle9i Warehouse Builder:

Changes in the Warehouse Builder Console

Enhanced Navigation Tree: The navigation tree that displays in the Warehouse Builder console has been enhanced to improve navigation between projects and facilitate direct access to metadata repository objects. All projects are now visible from the tree, whereas previously you could only see one project at a time. Now you can expand a project node to display the contents of the active project. The module tree no longer appears in a separate window.
Wizards, Editors, and Properties Sheets: All Warehouse Builder wizards, editors, and properties sheets are now launched from the navigation tree.
Business Areas Renamed to Collections: In previous releases, you could create business areas in warehouse modules to organize objects in Warehouse Builder and to export metadata to tools such as Oracle Discoverer. Starting in this release, collections replace business areas in all functions and introduce enhancements, such as the ability to import metadata into and export metadata from a collection.
Fact Tables Renamed to Cubes: The terms fact and fact table have been replaced with cube in this release to be in line with OLAP industry standards.
Logical Names Renamed to Business Names: All references to logical names of objects have changed to business names in this release.
Toolbars in the Warehouse Builder Console: The utility drawer has been removed and the side and top toolbars in the Warehouse Builder console have been merged at the top to consolidate the most important functionality in one place.

Enhancements to Deployment

Addition of Deployment Management Objects: This release introduces three object types to assist in managing connections to deployment sources and targets: Locations, Connectors, and Runtime Repository Connections. Locations define the physical location of the deployment. Connectors define relationships between locations. Runtime Repository Connections provide information about Runtime Repositories. Using these objects, you can create multiple deployment targets for the same target design.
Single Deployment Management Interface: The Deployment Manager provides a single interface for managing deployments of all objects, and executions of deployed mappings, transformations, and process flows. It also provides immediate access to the history of previously deployed objects. Not only does the Deployment Manager enable you to perform all these tasks from one interface, but Warehouse Builder now keeps track of runtime metadata, providing you with the history of what has previously been deployed.

Enhancements to Warehouse Builder Metadata Browser

Design Metadata Browsing: The Warehouse Builder Design Browser has been enhanced to include all new exposed objects, such as external tables, locations and connectors. In addition, you can now launch the Design Browser as a standalone executable; it no longer requires Oracle9iAS to be installed for a single-user usage.
Runtime Metadata Browsing: The Warehouse Builder Runtime Audit Viewer has been replaced by the Runtime Audit Browser, which provides web-based reporting. The Runtime Audit Browser provides a more extensive set of deployment and execution audit reports than was available in previous releases. This audit data comes from information stored in the Runtime Repository and includes both deployment and execution data.

Enhancements to Warehouse Builder Programmatic Access

Warehouse Builder Public APIs: Starting with this release, Warehouse Builder offers this alternative for programmatic access to Oracle9i Warehouse Builder features: a full set of Java public APIs for application programmers who want to embed Warehouse Builder features and services in their own applications.
Warehouse Builder Scripting Language: Oracle MetaBase (OMB) Scripting Language provides access to all Warehouse Builder functions without accessing the Warehouse Builder graphical user interface. Users can access Warehouse Builder metadata and functionality by using OMB Plus, Warehouse Builder's scripting utility. This gives developers the power of using Warehouse Builder programmatically and extending its functionality where required. For more information on OMB Scripting Language, refer to the Oracle9i Warehouse Builder Scripting Reference.

Enhancements to Metadata Management

Security: Warehouse Builder now provides an optional repository security and auditing system that you can implement according to your security requirements. You can create a multiple user account system where multiple identifiable users can access the same Warehouse Builder repository. Warehouse Builder also enables you to plug in a customized security PL/SQL implementation package in the Warehouse Builder repository to provide tailored access control to users according to the security rules defined by your organization.
Metadata Change Management (Metadata Snapshots): Starting in this release, you can take snapshots of metadata objects and use them for backup and history management. Snapshots are supported for any object on the navigation tree and can store information about an object alone (such as a table or module), or the objects within it as well (such as the tables within a module).
Multiple Language Support (MLS): With this feature, you can store the displayed business names and descriptions in languages other than the base language of the repository. Your different translations of business names and descriptions can be used to deploy to an EUL in the language of the target user population.
Extensibility Through User-Defined Properties: Users can define additional properties for any Warehouse Builder objects using the Warehouse Builder OMB Plus scripting utility. After you define user-defined properties through scripting, you can access them in the user interface, the Oracle MetaBase (OMB) Scripting Language, Warehouse Builder APIs, and Warehouse Builder Design Browser. This enhances the extensibility of Warehouse Builder and makes it easier to integrate it with other Business Intelligence products.
Metadata Loader (Import and Export) Flexibility Enhancements: Two new features were added to enhance this area of the product. The first is the ability for you to export metadata directly from Collections. The second feature is available from the Metadata Loader command line utility. It provides you with flexibility to specify the type of actions you want to apply when you import a first-class object.

Process Flow Editor

Starting in this release, you can use the Process Flow Editor in Warehouse Builder to create and define process flows. External process operators that you previously defined in mappings are upgraded to user-defined processes and are contained within a process flow module. Process flows now integrate in the same Warehouse Builder design environment and no longer require you to use Oracle Workflow design client to perform these functions. The Warehouse Builder process flow modeler natively understands the semantic of your mappings and enables you to model activities such as FTP, email, etc.

Performance Improvements

Mapping User Interface: A new pre-defined display set, named Mapping, was added in this release. Selecting this display set causes the Mapping to only display columns that effectively are mapped, or used.
Mapping Compression: This feature automatically detects unused connections between operators and attributes in any given mapping and eliminates them from the repository. This dramatically enhances the performance of loading and storing large mappings that represent significant data flows.
Metadata Loader (Import and Export): Import and export functionality now takes advantage of the new compression feature available for each mapping. This means that the Metadata Loader now exports and imports only those mapping objects that are actually used.

Oracle9i Integration

OLAP Integration: Warehouse Builder enables you to design, deploy, and load multidimensional OLAP objects as ROLAP or MOLAP models from different data sources. After the data is loaded, you can use BI tools and applications to run complex analytical queries that answer your business questions. Using Warehouse Builder, you can now create and manage both your relational and and multidimensional objects from the same cube and dimension designs.
Advanced Queue (AQ) Integration: Warehouse Builder enables you to import Advanced Queue definitions and to use AQs as data sources and targets while designing your data warehouse. Through Advanced Queue functionality coupled with the Messaging Gateways, Warehouse Builder enables you to support messaging applications on MQ Series and Tibco as Warehouse Builder data sources. AQs also enable you to propagate change data capture from your source system to your target. The ability to integrate AQs lays the foundation for providing real time data warehousing in the future.
External Tables: Starting in this release, you can use external tables to represent data from non-relational file sources in a relational, read-only format. You can import an existing external table from an Oracle9i database. Or you can create an external table in Warehouse Builder based on a flat file definition. Warehouse Builder will generate the right DDL for you to deploy you external table to an Oracle9i database.
Oracle9i Multiple Table Inserts: Warehouse Builder takes advantage of Oracle9i database functionality and generates a multiple-table insert statement when the target database is Oracle9i. This enables you to optimize mappings to insert data into multiple tables in one operation.
Oracle9i Table Functions: Warehouse Builder introduces the Table Function operator that enables you to improve performance when loading your target system. Use this operator to develop custom code that can manipulate a set of input rows and return another set of rows possibly of different cardinality. Unlike conventional functions, table functions output a set of rows that can be queried like a physical table.

Enhanced Support for Flat Files

Unbound Flat Files as Targets: In this release, you can create a new, unbound flat file object as you create your mapping. Warehouse Builder creates a new comma-separated, single-record-type flat file in the specified location. This feature makes it easier to load the contents of a relational object into a flat file.
Outbound Reconcile for Flat Files: Outbound reconciliation makes it possible to create a new repository object from a mapping flat file. This results in a new, comma-separated file to be created where specified, provided that the flat file is new to that repository. This feature makes it easier to "quickly dump" the contents of a relational object to a flat file.
Logical Records for Delimited Files: The Flat File Sample Wizard has also been enhanced to display an improved user interface that allows you to define logical records for delimited files.
Position-Based Master-Detail Loading: Position-based master-detail flat files are now easier to load with the use of additional mapping operators.
SQL Property Extensions: You can now specify SQL properties for flat files you import into Warehouse Builder. This enables you to pre-define SQL property values for each flat file field. Thus, if mapping a flat file source to a relational target, the target column will default to these pre-defined SQL property values. These values will be used when building a relational target column or when creating an external table column.

Mapping Editor Enhancements

Mapping User Interface: A new set of property tabs is now available for you to quickly create and edit mapping operators and attribute properties.
Pivot and Unpivot Operators: Starting in this release, you can add a pivot operator or an unpivot operator to a mapping. The pivot operator allows you to transform a single row of attributes into multiple rows. The unpivot operator converts multiple input rows into one output row.
Name and Address Operator Enhancements: The Name and Address operator has been enhanced to include new input roles and output attributes. The United States Postal Service Code Accuracy Support System (CASS) reporting is also supported starting with this release.

Warehouse Builder Is Now Available on UNIX Platforms

Starting with this release, Warehouse Builder is available on UNIX (Solaris, and Linux), as well as Windows (NT, 2000, and XP) platforms. This applies to all the components of Warehouse Builder, with the exception of the Name and Address libraries, which are not available on Linux in this release. (Note that the OLAP Bridges feature is only available on Windows platforms and the Name and Address Server is only available on Windows and Solaris platforms.)

Conventions

In this manual, Windows refers to the Windows NT, Windows 2000, and Windows XP operating systems. The SQL*Plus interface to Oracle9i may be referred to as SQL.

In the examples, an implied carriage return occurs at the end of each line, unless otherwise noted. You must press the Return key at the end of a line of input.

The following table lists the conventions used in this manual:

Convention	Meaning
. . .	Vertical ellipsis points in an example mean that information not directly related to the example has been omitted.
...	Horizontal ellipsis points in statements or commands mean that parts of the statement or command not directly related to the example have been omitted.
boldface text	Boldface type in text refers to interface buttons and links. Boldface type also serves as emphasis to set apart main ideas.
italicized text	Italicized text applies to new terms introduced for the first time. Italicized text also serves as an emphasis on key concepts.
`unicode text`	Unicode text denotes exact code, file directories and names, and literal commands.
`italicized unicode text`	Italicized unicode text refers to parameters whose value is specified by the user.
[]	Brackets enclose optional clauses from which you can choose one or none.

Related Publications

The Warehouse Builder documentation set includes these manuals:

Oracle9i Warehouse Builder User's Guide
Oracle9i Warehouse Builder Installation and Configuration Guide
Oracle9i Warehouse Builder Transformation Guide
Oracle9i Warehouse Builder Scripting Reference
Oracle9i Warehouse Builder Release Notes

In addition to the Warehouse Builder documentation, you can refer to the Oracle9i Data Warehousing Guide.

Oracle provides additional information sources, including other documentation, training, and support services that can enhance your understanding and knowledge of Oracle9i Warehouse Builder.

For more information on Oracle9i Warehouse Builder technical support, contact Oracle World Wide Support services at:

http://www.oracle.com/support
For the latest information on, and downloads of, software and documentation updates to Oracle9i Warehouse Builder, visit MetaLink at:

http://metalink.oracle.com
You can order other Oracle documentation at:

http://oraclestore.oracle.com

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program web site at: http://www.oracle.com/accessibility/

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.

Contacting Oracle

Oracle Metalink

Metalink is the Oracle support web site where you can find the latest product information, including documentation, patch information, BUG reports, and TAR entries. Once registered, you can access email, phone and web resources for all Oracle products. Metalink is located at:

http://metalink.oracle.com

Check Metalink regularly for Warehouse Builder information and updates.

Documentation

You can order Oracle product documentation by phone or through the World Wide Web:

Phone: Call 800-252-0303 to order documentation or request a fax listing of available Oracle documentation.
Oracle Documentation Sales Web site: http://oraclestore.oracle.com
Oracle Customer Support Web site: http://www.oracle.com/support