Preface
This preface includes the following topics:
Purpose
Oracle9i Warehouse Builder is a comprehensive toolset for individuals 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 you can 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 transformations described in this manual are grouped according to the data or function type. For example, all transformations working on dates are grouped together in one section. All functions in each section are listed alphabetically to make searching more efficient. Each transformation contains the syntax as it reads in Warehouse Builder, a description of the purpose of this transform, and examples or business use cases.
Warehouse Builder includes a set of custom functions, built using PL/SQL to make common activities easier for developers. These transformations start with the prefix WB_ and are listed at the end of the chapter.
The Oracle9i Transformation Guide contains the following chapters and appendixes.
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 for 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 OLAP 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 Public 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.
Table 0-1 lists the conventions used in this manual:
Table 0-1 Writing Conventions
Related Publications
The Warehouse Builder documentation set includes these manuals:
- Oracle9i Warehouse Builder Transformation 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/
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. OracleMetaLink is located at:
http://metalink.oracle.com
Check OracleMetaLink 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
