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 manual provides instructions for installing Oracle9i Warehouse Builder and a core set of related products. Use this manual to install the Warehouse Builder client, server, and Warehouse Builder repositories on your Windows or UNIX machines.
You can modify this installation procedure to accommodate variations in hardware and software configurations, or in the products you install with Warehouse Builder. Refer to the Oracle9i Warehouse Builder Release Notes for further details.
Audience
This manual is written for those responsible for installing Warehouse Builder, including:
- Business Intelligence application developers
- Warehouse administrators
- System administrators
- Other MIS professionals
To install Warehouse Builder, you must be familiar with installing Oracle9i.
How This Guide Is Organized
This manual is organized as follows:
- Chapter 1, "Warehouse Builder Installation Requirements", discusses the Warehouse Builder installation requirements. Verify your system requirements to install the Warehouse Builder client, server, and repositories.
- Chapter 2, "Installing Oracle9i Warehouse Builder Components", provides step-by-step instructions for a typical installation of the Warehouse Builder client, server, browser, and the design and runtime repositories.
- Chapter 3, "Upgrading to Release 9.2.0.3 and Migrating Data", provides step-by-step instructions for upgrading metadata from a previous version of Warehouse Builder to Oracle9i Warehouse Builder.
- Chapter 4, "Deinstalling Warehouse Builder Components", explains how to deinstall the Warehouse Builder components.
- Appendix A, "Preparing an Oracle 8.1.7 or 9i Database", lists modifications you need to make to the init.ora file to prepare your database for the Warehouse Builder installation.
- Appendix B, "Installing Oracle Enterprise Manager and Oracle Workflow", provides guidelines for installing and configuring Oracle Enterprise Manager and Oracle Workflow with Warehouse Builder to schedule jobs and manage job dependencies.
- Appendix C, "Upgrading Metadata with the MDL File Upgrade Utility", provides information on using the MDL File Upgrade Utility if you want to upgrade metadata separately, instead of upgrading it as an automatic part of the MDL import.
- Appendix D, "Diagnostics and Debugging", provides troubleshooting guidelines for your installation.
New in Release 9.2.0.3
Changes to the Installation and Upgrade of Oracle9i Warehouse Builder
- Warehouse Builder is now available on HP-UX and AIX: Starting with this release, Warehouse Builder is available on UNIX (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.)
- No need to run the MDL File Upgrade Utility: Metadata exported from a prior version of Warehouse Builder is now automatically upgraded when you import it into Oracle9i Warehouse Builder Release 9.2.0.3. In the Warehouse Builder graphic user interface, the MDL import detects the version of the import file and prompts you for an upgrade file name and log. In OMB Scripting Language, the
OMBIMPORT FROM MDL_FILE scripting command now has NO_UPGRADE option which prevents the automatic upgrade. In the Warehouse Builder Public API, a new setAutoUpgrade parameter upgrades your import file when set to 'T'. In the command line interface, an AUTOUPGRADE parameter upgrades your import file when set to 'Y'. In previous versions of Warehouse Builder, you had to separately upgrade an MDL file using the MDL File Upgrade Utility before importing it into a later version of Warehouse Builder.
- The MDL Import UI will now check if the MDL file specified for import is from a prior MDL release. If the file is determined to be from a prior MDL release, the user will be prompted to specify the name of the output file for the upgrade as well as the name of the upgrade log file. Once the upgrade completes successfully, the file created by upgrade will then be imported.
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.
Enhancements to 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.
Enhancements to Partition Exchange Loading
Direct PEL: 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.
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 9.0.4
The following new features were introduced in Oracle9i Warehouse Builder:
Changes to the Installation of Oracle9i Warehouse Builder
- Warehouse Builder is now available on UNIX: 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. (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.)
- Installing Oracle Workflow Client is not required: While the Oracle Workflow Runtime components are still required for a complete Warehouse Builder installation, the role of the Oracle Workflow Client has been replaced with Warehouse Builder Process Flow Editor. You can choose to install the Oracle Workflow Client in order to view deployed Warehouse Builder processes in Oracle Workflow.
- No need to install Warehouse Builder Workflow Queue Listener: The Workflow Queue Listener has been replaced by Runtime Platform Service, a new feature in Warehouse Builder.
- Instructions for installing Oracle9i Warehouse Builder Name and Address: Instructions for this installation are in Chapter 2, "Installing Oracle9i Warehouse Builder Components" of this guide. In previous releases, instructions for installing Oracle Warehouse Builder Name and Address were published in the Oracle Pure Name and Address Installation Guide, which no longer exists separately.
- Warehouse Builder Runtime Audit Browser Client: There is now a client version of Runtime Audit Browser, which can be configured independently of Oracle9iAS. You also have the option to install an integrated version of the Runtime Audit Browser with Oracle9iAS, but this is no longer required.
The Oracle9i Warehouse Builder Runtime Audit Browser client comes pre-installed with a Runtime Repository. You only have to set up an Oracle HTTP server on your Oracle9i database instance.
- Warehouse Builder Design Browser Client: There is now a client version of Design Browser, which can be configured independently of Oracle9iAS. You also have the option to install an integrated version of the Design Browser with Oracle9iAS, but this is not required.
The Oracle9i Warehouse Builder Design Browser client comes pre-installed with a Design Repository. You only have to set up an Oracle HTTP server on your Oracle9i database instance.
- Integration with Oracle9i Internet Application Server is optional: You have the option to integrate Warehouse Builder Design Browser and Runtime Audit Browser with Oracle9iAS, but this is not required.
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.
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 conventions are also used in this manual:
Related Publications
The Warehouse Builder documentation set includes these manuals:
- Oracle9i Warehouse Builder Installation and Configuration Guide
- Oracle9i Warehouse Builder User's 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 other documents listed below:
- 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. 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
