ORACLE9i Warehouse Builder 9.2

1.0 Overview
1.1 What is Oracle9i  Warehouse Builder?^TM
Oracle9i Warehouse Builder is the only enterprise business intelligence integration design tool that manages the full lifecycle 
of data and  metadata for the Oracle9i Database.  Warehouse Builder allows users to design their own Business Intelligence 
application from start to finish. Dimensional design, ETL process design, extraction from disparate source systems, extensive metadata reporting and integration with Oracle 9iAS Discoverer, Oracle Workflow and Oracle Enterprise Manager enable an integrated Business Intelligence solution with Warehouse Builder at the core. 

1.2 How does Oracle Warehouse Builder facilitate ETL and warehouse design?
Warehouse Builder is a code generation tool based on a metadata repository, meaning that the design is translated internally by Warehouse Builder, which results in the generation of code. This code can then be used to create a data warehouse and the accompanying data transformations.

1.3 If Oracle 9i has ETL functionality for free, why do I need to buy Warehouse Builder?
The ETL functionality in the database is a framework for ETL applications. It is a set of SQL and PL/SQL utilities and commands that can be used in ETL processes. To use these commands the user either needs to write custom SQL and/or PL/SQL. Warehouse Builder provides users with a transparent and easy to use GUI to embed the database functionality in the Warehouse Builder ETL processes. 

1.4 What is the history of this tool?
Warehouse Builder is a tool developed within Oracle Development and is based upon Oracle�s previous experiences in providing ETL tools including Oracle Data Mart Builder and the Oracle Applications Data Warehouse.  Oracle Warehouse Builder was first released in beta to initial customers in mid-1999, and became generally available in February 2000.  The release 3i became available in June 2001.  Warehouse Builder release 9.2 is available in the enterprise edition of Oracle9i database (version 9.2) in July 2003.

1.5 What is the current release version?
Warehouse Builder 9.2 is the latest version and available for download on Oracle's Technology Network (OTN) in July 2003.  Warehouse Builder 9.2 will ship in the enterprise edition of the Oracle9i (9.2) database as a stand alone CD.  The Windows and Sun Solaris versions will be available in July 2003, the Linux and HP-UX versions in August 2003 and the AIX version in September 2003.

1.6 What are the new features of the latest release Warehouse Builder 9.2?
For the Warehouse Builder 9.2 release, the features include: 

• Mapping Debugger: Warehouse Builder now provides extensive debugging capabilities for your mappings from within the Mapping Editor. Use the Mapping Debugger to locate logical design errors in your mappings. Users can step through the data flow of a mapping using comprehensive debugging functions such as setting breakpoints and watch points.  Interactively the user can change test data. 

• Correlated Commit: This release introduces a new commit strategy for mappings with multiple targets.  Warehouse Builder now 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 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. 

• 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, users defined the Name Address operator using the mapping canvas and the operator Configuration Properties sheet. For improved productivity and usability, Warehouse Builder now enables users 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. Users 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: Metadata change management enables you to keep versions of metadata objects and use them for backup and history management. Version support exists 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). 

• RAC Support: Warehouse Builder now supports the use of net service names in the runtime. This enables the planned maintenance of nodes in a cluster without having to reconfigure the runtime environment. The RAC Support features enables client side loading balancing.  Server side loading balancing was already supported in previous releases.

• ZONED Data Type Support: Warehouse Builder now allows you to load fixed format data files containing ZONED decimal data. 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. 

• Public Database Links: 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. 

• Support for MITI metadata bridges to third party design tools: Warehouse Builder is pleased to announce support for MITI metadata bridges.  By downloading Mitts metadata bridges directly from their web site (www.metaintegration.net), Warehouse Builder users can import designs from over 40 third party design tools including Erwin, PowerDesigner, and Embarcadero ER Studio. 

1.8 What is status of Pure Integrate?
Pure Integrate has been desupported.  Warehouse Builder 9.2 includes the Name/Address cleansing functionality and the Match/Merge functionality of Pure*Integrate.  For more information, please refer to the Data Quality FAQ.

1.9 What is the status of Pure Extract?

Back to Top

2.0 Features and Benefits
  2.1 Source Information
  2.2 Target Information
  2.3 ETL Process
  2.4 Scalability
  2.5 Lifecycle Management
  2.6 Metadata
  2.7 Benefits

2.1 Source Information
2.1.1 Which sources are supported by Warehouse Builder?
Warehouse Builder supports the following source environments:

• Oracle RDBMS, versions 7.3.x and higher (Oracle 7.3 is not supported by Oracle 9.2)

• Flat files (character-delimited, fixed-length, single-record and multi-record) 

• SAP/R3 

• Warehouse Builder 9.2 supports 3.X.X and 4.X.X 

• XML documents (URL, Advanced Queues, documents etc.) 

• Generic Connectivity (ODBC) to non-Oracle databases

• Oracle Transparent Gateways to non-Oracle databases

• A third party solution for mainframe connectivity will be provided for most legacy and mainframe platforms in the Fall of 2003.

• Microsoft SQL Server 

• Sybase 

• Informix 

• DB2 on OS/390 (MVS) 

• DRDA for DB2 on OS/390 (MVS) 

• DRDA for DB2 on AS/400 

Warehouse Builder has the capability to detect changes in the sources and update the repository with the new information.  It provides impact analysis, which the user can use to determine whether these changes needed to be propagated in the design.

2.1.4 What CASE tools are supported by Warehouse Builder as source metadata suppliers?
Through use of the MITI metadata bridges, Warehouse Builder can read metadata definitions from:

• Sybase PowerDesigner  7.0 

• Sybase PowerDesigner/CDM 7.5 to 9.5 

• Sybase PowerDesigner/PDM 7.5 to 9.5 

• Sybase PowerDesigner/CDM, DataArchitect/CDM,  PowerSoft S-Designer/CDM 6.1.0 & 6.1.1 

• Sybase PowerDesigner/PDM,  DataArchitect/PDM , PowerSoft S-Designer/PDM 6.1.0 & 6.1.1 

• CA AllFusion ERwin Data Modeler 4.0 SP1 to 4.1 

• CA ERwin 3.0 to 3.5.2 

2.1.5 How do users access and install the MITI bridges?

2.1.9 Is Warehouse Builder capable of reconciling changes in the source system to the Warehouse Builder repository information?
Yes, Warehouse Builder can reconcile the source metadata with the actual source system. If changes are present, Warehouse Builder will - after running the reconcile - present a source impact analysis report to the user. The user can decide to go ahead and reconcile the changes or reject them. Sophisticated functionality allows the user to keep certain information (added logical keys in Warehouse Builder etc.) regardless of the discrepancy detected.

2.1.10 How do I use my SAP data as a source with Warehouse Builder?
Warehouse Builder has an SAP Integrator which generates ABAP code to extract data from a SAP source.

2.1.11 How do you install the Integrator for SAP?
The SAP Integrator is installed automatically during the installation of Warehouse Builder. Users will need a SAP dll (librfc32.dll) to run Warehouse Builder's SAP Integrator

2.1.12 Can the Integrator for SAP handle customizations of the SAP environment (for example different languages)?
The SAP Integrator can extract from customized tables. Customized tables are treated in the same way as standard tables.

2.1.13 Is it possible to mix SAP versions as sources?
Yes, defining different source modules for each version allows you to have multiple SAP sources.

2.1.14 Does the SAP installation have to be on an Oracle database?
No, the Warehouse Builder SAP Integrator is not dependent on the underlying database. SAP installed on a non-Oracle database (Informix, DB2, SQL Server or Sybase) can be accessed with the Warehouse Builder SAP Integrator.  Also Warehouse Builder generates ABAP code which is SAP proprietary language and not dependent on any database.

2.1.15 Does the Warehouse Builder SAP Integrator support ABAP code generation?
Yes, Warehouse Builder generates ABAP code for all table types in SAP (cluster, pool and transparent) to extract data from the SAP source. Since Warehouse Builder generates the ABAP code, it is not necessary for the user to know how to write ABAP code. The integrator also generates PL/SQL code for transparent tables.

2.1.16 Will SAP certify the SAP Integrator solution?
SAP does not certify solutions that extract data from an SAP system. SAP only certifies solutions that push data into SAP or SAPBW.  However, as Warehouse Builder generates ABAP code, the mechanism used is native to SAP and extensively used.

2.1.17 Does Warehouse Builder support extraction from XML sources?
Yes, Warehouse Builder supplies users with an XML toolkit. This toolkit is used to extract data from XML documents.

2.1.18 What are external tables and does Warehouse Builder support them?
External tables are user defined database objects mapped (field by field) to flat files. The flat file fields must be defined either by delimiters or by field position. After an external table has been defined, it is possible to use read only SQL commands to access the underlying flat files. Warehouse Builder 9.2 supports external tables as data sources and allows users to define external tables from a flat file within Warehouse Builder.

2.1.19 What are Advanced Queues and does Warehouse Builder support them?
Advanced Queues (AQ) are Oracle database objects that enable data propagation by using the message queuing mechanism. Messages are en-queued by a data producing application and de-queued by data consuming applications. This mechanism is widely used particularly in the application integration area. Warehouse Builder 9.2 supports AQ both as data sources as well as targets.

2.1.20 What are table functions and does Warehouse Builder support them?
Table functions are a PL/SQL construct and are defined as functions that can produce a set of rows as output instead of a single row.  Warehouse Builder does support table functions by generating code using the table function feature in the Oracle9i database. Warehouse Builder improves performance by enabling table functions to use parallel execution in the Oracle9i server.
 

2.2 Target Information
2.2.1 Which warehouse platforms does Warehouse Builder support?
Warehouse Builder is tuned and build to fully utilize the market leading Oracle database. Warehouse Builder is a code generation tool and uses the Oracle database as its transformation engine. 

2.2.2 Which hardware platforms is Warehouse Builder certified on?
Warehouse Builder supports Windows (2000, NT 4.0, or XP), Linux, HP-UX, Sun Solaris, and AIX.  The Windows and Sun Solaris versions will be available in July 2003, the Linux and HP-UX versions in August 2003 and the AIX version in September 2003.

2.2.3 Do I have to use the Oracle 9i database to use Warehouse Builder?
No, Warehouse Builder 9.2 is certified on  Oracle 8.1.7 and on Oracle9i. 

2.2.4 Can I run the Warehouse Builder generated warehouse on Oracle 8i/9i standard edition?
In general this will work, although it is not certified, because you would NOT have:

• Parallel query option 

• Bitmap indexing 

• Star transformations  

2.2.5 Does Warehouse Builder support non-Oracle databases or flat files as targets?
Yes, Warehouse Builder allows you to write data to pre-defined flat files. These flat files can be shared with other applications. However, Warehouse Builder does not directly support other relational databases as a target.

2.2.6 Does Warehouse Builder support dimensional targets?
Yes, Warehouse Builder has a wizard-driven design process for both dimensions and for cubes. Both can be viewed and printed in a diagramming mode.

2.2.7 Does Warehouse Builder support 3rd normal form targets?
Yes, Warehouse Builder lets you define 3rd normal form schemas with the wizards guiding you through the table creation process. It also provides a simple wizard to design and define keys between the tables.

2.2.8 Does Warehouse Builder provide summary support?
Yes, Warehouse Builder offers wizard-driven design for Materialized Views, the summary mechanism of the Oracle database. The database may redirect the queries generated by end user tools on the Warehouse Builder generated warehouse. Please refer to the database documentation for details. 

2.2.9 Is Warehouse Builder capable of storing tablespace information with database objects?
Yes, Warehouse Builder allows users to determine the tablespace for indexes, tables, dimensions and facts on a per object basis. Warehouse Builder adheres to the general Oracle recommended practice of applying storage characteristics via tablespaces, NOT via individual object clauses.

2.2.10 Can Warehouse Builder import Materialized View definitions?
At this point in time this is not possible with Warehouse Builder. The Materialized Views have to be defined within Warehouse Builder. However this is planned for a future release.

2.2.11 Can Warehouse Builder ensure referential integrity in the warehouse?
Yes, Warehouse Builder allows users to define constraints (Primary, Unique, Foreign and Check) on all objects. The database will enforce the referential integrity.

2.2.12 Does Warehouse Builder support incremental design and can it add changes to an already loaded warehouse?
Warehouse Builder seamlessly utilizes the Change Management functionality of Oracle Enterprise Manager to propagate changes in the metadata to the actual warehouse. Warehouse Builder can add columns, keys, tables, dimensions and facts without causing any disruption to the present warehouse. Removing of elements is supported as well.  The Change Management component of Oracle Enterprise Manager is shipped and installed as part of Warehouse Builder.

2.213 Does Warehouse Builder support OLAP (Analytic Workspace) targets?
Yes, Warehouse Builder 9.2 supports the creation and data loading for Analytic Workspaces. It is necessary to upgrade the database to the post 9.2.0.4 version containing the OLAP features in order to take advantage of this functionality. Please check the Oracle websites (metalink.oracle.com or /products/bi/olap/olap.html) for information regarding this upgrade.

2.214 Does Warehouse Builder support indexing?
Yes, Warehouse Builder 9.2 does support indexing on all data objects.  For cubes, Warehouse Builder supports the generation of bitmap indexes.

2.3 ETL Process
2.3.1 What language does the product generate to extract data from sources?
Warehouse Builder generates SQL, PL/SQL, ABAP or SQL*Loader control files, depending on the source system and on the execution strategy chosen. External processes can be run as part of a process flow or wrapped in PL/SQL.

2.3.2 Can the generated code be modified?
Yes, Warehouse Builder generates code, which can be deployed into the database and be deployed into scripts (text files). These text files can be modified and then deployed to the database with the changes. Note that these changes (if made outside of Warehouse Builder) are not stored in the repository, and that problems with modified scripts are not supported by Oracle.

2.3.3 How is the evolution of the Oracle database technology reflected in the code generated by Warehouse Builder?
Since Warehouse Builder is highly integrated with the underlying database technology, the aim is to generate the most optimal code for a certain database version. For example, the code generated for the Oracle9i database takes advantage of the  features such as merge statements, multi-table inserts, table functions etc. At the same time, continuity is preserved by the support of older database versions, such as 8.1.7 in Warehouse Builder 9.2..

2.3.4 Can the administrator influence the generated code at runtime?
Yes, the Warehouse Builder generated ETL code consists of packages. The administrator can substitute the values chosen at design time with enhanced values for the specific runtime situation. For example, if a job failed, the administrator can change the execution strategy and create a row-by-row processing mode, generating sophisticated error logging (auditing) messages. Other parameters include the audit level, the commit frequency, the error threshold, hints and the bulk load size.

2.3.5 Does Warehouse Builder support Oracle database hints for extraction and loading?
Yes, Oracle database hints can be applied to sources and targets is Warehouse Builder.

2.3.6 Does the product supply error logging and detailed error messages on the ETL process?
Yes, Warehouse Builder has levels of audit details, which can be set by the user (even at runtime). These levels range from complete logging (for debugging) to no logging at all if speed counts. The logging information is collected in the Warehouse Builder runtime schema and can be viewed using the Warehouse Builder Runtime Audit Browser.  Also the Mapping Debugger another way to locate the cause of error messages.

2.3.7 Can Warehouse Builder generate surrogate keys?
Yes, depending on the implementation language, Warehouse Builder uses either sequences (PL/SQL implementation) or a data generator (SQL*Loader implementation) to create surrogate keys.

2.3.9 What is the Name and Address operator wizard?
The Name and Address operator wizard supports parsing, standardization, postal matching, and geocoding of name/address data.  Name/address parsing is the breakdown of non-discrete input into discrete name or address components.  Name/address standardization involves modification of components to a standard version acceptable to a postal service or suitable for record matching. Postal matching involves matching an input address with postal database entries to verify and/or correct an address. Geocoding involves the collection of census and locational data and is only available for the United States.

2.3.10 What is Oracle9i Pure Name and Address? 
Pure Name and Address 9.0.4 are name and address libraries that Oracle previously offered.  Warehouse Builder users will now have a choice of third party data quality vendors to use with Warehouse Builder.  Trillium, First Logic and DataFlux are all in the process of building adapter to work with Warehouse Builder.

2.4 Scalability
2.4.1 Does the product have the ability to handle various scalability needs?
Yes, scripts are deployed to the Oracle target platforms that may scale from data marts to extremely large enterprise warehouses. Because the Oracle database is the transformation engine, a warehouse generated by Warehouse Builder scales extremely well.

2.4.2 Does the product employ engine-based technology for transformation?
Warehouse Builder generates PL/SQL for extraction and transformation.  These scripts are distributed to target data warehouse systems. The Oracle8i/9i database engine thus becomes the transformation engine. Multiprocessor capabilities of the data warehouse platform are hence fully taken advantage of.

2.4.3 Can Warehouse Builder support Oracle�s parallel inserts, updates, deletes?
Yes, Warehouse Builder does support parallel inserts, updates, deletes. All objects can be configured for the correct degree of parallelism and Warehouse Builder allows users to apply hints for further performance tuning.

2.4.4 What are the built-in performance optimization techniques to use during ETL processing?

• Merge Statements: If a load process requires the insertion of new records and update of the existing ones (a very common occurrence in ETL data processing), an optimized merge statement will be generated.

• Table Functions: Warehouse Builder will generate table functions when high performance sequential processing of large amounts of tabular data is required.

• Multi-table inserts: A statement populating multiple tables concurrently will be generated when it can be supported by mapping.

• PL/SQL Bulk Processing: Warehouse Builder uses PL/SQL bulk processing for reads and writes. Bulk processing improves the performance of SQL statements that affect multiple rows of data. This feature may increase loading performance up to approximately five times. 

• Foreign Key Constraint Manipulation: For insert operations, Warehouse Builder goes directly to the Oracle8i/9i database by issuing a single insert statement. This fastest method of inserting data is called �set-based insert�. During set-base inserts, Warehouse Builder intelligently manipulates foreign key constraints so that rows are inserted faster while still preserving referential integrity. This feature results in an approximately threefold performance increase during loading. Users can switch this behavior on and off.

• Faster Loading from Remote Tables Using the Inline View Technique: Warehouse Builder automatically detects the common scenario where one or more custom transformations are applied to multiple joined tables that are located in a remote database. This scenario is most efficient when the tables are joined in the source database and then the transformation functions are applied on a (much reduced) result set in the target database. Warehouse Builder forces the join on the remote database by means of an inline view. 

• 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.  Performance increases of up to four times have been measured.

• Parallel hints: Warehouse Builder takes advantage of the new parallel query capabilities offered in Oracle9i to load table faster.  Warehouse Builder generates the parallel hints that Oracle9i translates into parallel enabling clause to optimize the loading of tables generated by Warehouse Builder. 

2.5.1 Does the product have a scheduling feature for running jobs at specified times?
Oracle Enterprise Manager (OEM), provided with every Oracle RDBMS server, provides that capability at no extra charge. 

2.5.2 Does the product have advanced operational control features to support job or group scheduling dependencies?
Yes, Oracle9i Enterprise Edition includes Oracle Workflow that provides complex dependency management.  Warehouse Builder process flow editor takes advantage of the workflow engine back-end is part of Warehouse Builder 9.2.

2.5.3 Does Warehouse Builder support command line execution?
Yes, Warehouse Builder supports command line execution.

2.5.4 Does Warehouse Builder integrate with external schedulers?
Yes, through command line execution Warehouse Builder supports third party schedulers.

2.5.5 What features does Warehouse Builder provide for the runtime environment management?
The Deployment Manager is available for the user to manage the versioning, timing and status of the designed objects. A run-time repository contains all the relevant data on the objects above. It is also possible to deploy a single ETL design to multiple run-time environments.

2.5.6 Does the product provide the ability to restart failed ETL jobs?
Not currently.  The Warehouse Builder Runtime catalog is installed in the target schema and consists of a set of database objects (tables, sequences, triggers, etc.) that are used for error logging. The Runtime Audit Browser lets you retrieve the information stored in the Runtime tables.  In the current version this tool is for monitoring only.  In a future release its capabilities will be expanded to allow restart and recovery using both Warehouse Builder generated functionality and database functionality.  Alternatively, Oracle database's resumable statements can be used to restart your statements.

2.5.7 Are there statistics to trace data changes (e.g. how many rows were inserted, updated, or deleted)?
A set of database audit objects (tables, sequences, triggers, etc.) is used for error logging.  Warehouse Builder automatically logs audit information during extraction and loading process. The level of auditing detail can be controlled via a configuration parameter or at runtime.

2.5.8 Does the product have the capability to be integrated with certain types of monitoring tools (i.e. RDBMS monitoring tools and application monitoring tools)?
Yes. As most ETL scripts are in PL/SQL and deployed to Oracle target databases, standard monitoring tools can be used when then scripts are in use.

2.6 Metadata
2.6.1 Does the product provide a metadata repository that works in conjunction with the transformation development environment?
All development work done within Warehouse Builder including importing of source definitions, design of targets, mapping of sources to targets, and building of custom transformations, is stored in the Oracle based repository that the Warehouse Builder client is linked to.

2.6.2 Does Warehouse Builder have a published metadata model with SQL based query capability and/or an Application Program Interface (API)?
Yes, Warehouse Builder comes with a set of documented public views on the repository. Warehouse Builder's APIs give users read and write access to the repository. The Warehouse Builder repository can also be accessed via SQL.

2.6.3 What features do the Warehouse Builder Java APIs and scripting interface provide to the user?
Warehouse Builder provides open access to its functionality by Java APIs and through its powerful scripting language. By using these functions, the user can perform almost all the activities that are available from its front end user interface. Operations that might be intense in manual or data entry activities can now be greatly simplified by using the scripting approach. In addition, Oracle9i JDeveloper can be used as an integrated development environment (IDE), complete with debugging and code sensitive editor for any Warehouse Builder scripting effort.

2.6.4 Does Warehouse Builder adhere to any metadata standards in the market?
Yes, Warehouse Builder adheres to OMG-CWM (Common Warehouse Metadata) and Oracle is one of the main participants and drivers of this OMG standard.  The website for the Object Management Group is http://www.omg.org/technology/cwm/index.htm

2.6.5 What other metadata standards are available in the market?
None, all vendors are either united under the OMG-CWM umbrella or have their own proprietary standard. The Metadata Coalition lead by Microsoft has joined the OMG-CWM standardization effort in 2000.

2.6.6 What other vendors support the OMG-CWM standard?
In addition to Oracle, the current list is:

• Hyperion 

• IBM 

• Microsoft 

• Unisys 

• SAS 

• Informatica 

• HP (in a supporting role) 

• SUN (in a supporting role) 

2.6.8 Is the repository a multi-user environment?
Yes, Warehouse Builder employs a sophisticated locking mechanism to allow users to work on the same project simultaneously. Locking occurs on the object level (for example, a table, a dimension or a mapping) to allow for maximum flexibility. One user will receive a read/write lock, the other will then have a read-only copy of the object. This read only copy is presented in a read-consistent view, which means that the object is not changed until a commit occurs in the session with read/write. Warehouse Builder provides a synchronize option to allow users to refresh their copy periodically. Furthermore, by using the security-oriented scripting features, it is possible to create users, define access rights on object and service basis in the metadata repository and to integrate a customized, user-defined security model. 

2.6.9 What is the Warehouse Builder�s security model?
The Warehouse Builder security model can define security policies on a user, permission and object granularity level if security is of primary consideration. The metadata repository administrator can create users, assign ETL specific permissions to them (viewing, editing, validating, generating and versioning) on the object level (i.e. for every table, view, dimension, mapping etc.). User defined PL/SQL security packages can also be integrated in the security framework by using PL/SQL call-outs.

2.6.10 Is there another way of viewing Warehouse Builder�s metadata than the client application?
Yes, Users can view their metadata through Warehouse Builder's Design Browser. The Design Browser provides users with a web based viewing medium on all of Warehouse Builder�s metadata and provides pre-defined metadata reports. The Design Browser can be used either through the local browser or by using Oracle Portal, a component of iAS.

2.6.11 Does Warehouse Builder provide Lineage and Impact analysis for metadata?
Yes, through the Warehouse Builder Design Browser,  users can view lineage and impact analysis diagrams.

2.6.12 Does the repository allow automatic discovery of database objects from database sources?
Yes, for Oracle data sources, the user supplies connection information (e.g. the user name, password, connection information), which will be used to create a database link in the database that hosts the Warehouse Builder repository.  Warehouse Builder uses this database link to query the source database�s data dictionary (catalog) and extract metadata that describes the tables, views, etc. of interest to the user.  Other sources accessed through Oracle's Transparent Gateways also look like an Oracle database to Warehouse Builder.  The user experiences this as a process of visually inspecting the source and selecting objects of interest. The integrator for SAP provides the same transparent capabilities.

2.6.13 Is it easy to create descriptive metadata?
Standard Oracle Warehouse Builder description components are used. Description fields can be populated to document definitions and meanings of fields and processes.  User-defined properties can be added in order to add specific descriptive metadata.  Warehouse Builder User Interface and its Browsers will automatically extend to show the values.

2.6.14 Can the metadata be accessed by other Oracle tools?
Warehouse Builder provides bridges for exporting metadata to both Discoverer and Oracle9i OLAP server.

2.6.15 Do I need iAS to run Warehouse Builder?
No, iAS is not required but can be used with Warehouse Builder.  For the Design Browser, a HTTP listener is required and the listener shipped with the enterprise edition database can be used.  For the Runtime Audit Browser, an OC4J engine is needed and it can run on iAS or locally.

2.6.16 Is the Warehouse Builder Metadata model extendible?
Yes. By using the scripting and/or API metadata access, the user can define additional attributes (properties) to the existing metadata objects. The future versions will allow the creation of new, user-defined objects.

2.7 Benefits
2.7.1 What are the main benefits of using Oracle Warehouse Builder?
The main benefits of using Oracle Warehouse Builder are:

• Because of the tight integration between Warehouse Builder and the Oracle database, it is guaranteed that Warehouse Builder will closely follow the evolution of the Oracle database in the business intelligence space. Therefore, it remains the best choice for Oracle oriented environments.

• Because of its metadata driven approach, Warehouse Builder creates solutions that are 100% reproducible. All code is generated from a metadata repository and therefore reproducible at any point. 

• Changes are incorporated in a fast and efficient way because the central point for changes is the repository. From there the changes can be propagated in an intelligent way to systems already deployed. This is lifecycle support. 

• Source and target system  reconciliation ensure that Warehouse Builder always provides the correct representation of the systems in the organization. 

• Warehouse Builder provides the users with data warehouse design, ETL design and life cycle management in one consolidated environment. 

• Warehouse Builder provides users with a highly graphical user interface, which reduces hand coding to a minimum. 

• Warehouse Builder offers an open platform with rich, customizable features accessible by scripting and Java APIs.

• Price performance: Warehouse Builder offers one of the best price performance ratios in the ETL market.

3.0 Integration with other Oracle products
   3.1 Oracle RDBMs
   3.2 Oracle Discoverer
   3.3 Oracle Express
   3.4 Oracle Designer
   3.5 Oracle Applications
   3.6 Oracle 9iAS
   3.7 Oracle Workflow
   3.8 Oracle Enterprise Manager (OEM)

3.1 Oracle RDBMs
3.1.1 How does Warehouse Builder integrate with the Oracle RDBMS?
Warehouse Builder integrates in a number of ways with the Oracle database. A few are noted below: 

• The Oracle database is the transformation engine for all extraction code generated from Warehouse Builder. 

• Warehouse Builder gives users the opportunity to tune the generated code using all the features the Oracle database provides. These include table partitioning, bitmap index creation, dynamic constraint management etc. 

• Warehouse Builder has a specific loading capability, called Partition Exchange Loading, utilizing key characteristics of the Oracle Database to improve loading into very large (fact) tables. 

• Warehouse Builder allows users to define the summary strategy for the warehouse using the Warehouse Builder client. The method used for summary management is the Oracle database's Materialized View feature. 

• The Oracle database hosts the Warehouse Builder repository which means it is covered by the database security and backup capabilities. 

3.2  Oracle Discoverer
3.2.1 Does Warehouse Builder 9.2 integrate with Oracle Discoverer?
Yes, Warehouse Builder allows the user to specify the Business Area characteristics for Discoverer. It allows the user to specify which data objects are included in a specific Business Area, and it lets the user specify, on an attribute level, what the Discoverer item characteristics will be. This virtually eliminates the need to use the Discoverer Administration edition for object creation and maintenance but it is needed for security. 

3.2.2 Which versions of Discoverer are supported with Warehouse Builder 9.2?
The Warehouse Builder export facility (the bridge) supports Discoverer 9.0.2.

3.2.3 Does Warehouse Builder automatically populate a business area for Discoverer?
Yes. Discoverer can get its metadata from Oracle9i Warehouse Builder. 

3.3 Oracle OLAP (Analytic Workspace)
3.3 Does Warehouse Builder 9.2 integrate with Oracle Analytic Workspace?
Yes, Warehouse Builder has a metadata bridge to Oracle Analytic Workspace (AW). It is possible to create and populate an AW by using Warehouse Builder. To fully take advantage of this functionality it is necessary to upgrade the database to the post 9.2.0.4 version that will contain the OLAP functionality. Please check the Oracle websites (metalink.oracle.com or /products/bi/olap/olap.html) for information regarding this upgrade.

3.4 Oracle Designer
3.4.1 Can Warehouse Builder extract definitions from Oracle Designer?
Yes, Oracle Designer 9.0.2.

3.4.2 Can Warehouse Builder  write back to Oracle Designer?
No. Warehouse Builder cannot write back to the Designer repository.

3.5 Oracle Applications
3.5.1 What is the integration strategy for Oracle Applications?
Oracle EDW is a warehouse out of the box for Oracle Applications. It is based on Warehouse Builder technology.  Oracle Applications can be treated as a regular Oracle data source when using Warehouse Builder.

3.6 Oracle9iAS
3.6.1 Can I use the HTTP server that comes with the database or should I use the one in 9iAS?
It does not matter which listener you use for the HTTP server - either the database version or the iAS version will work.

3.7 Oracle Workflow
3.7.1 Do I have to install Oracle Workflow in order to be able to use Warehouse Builder?
No, Oracle Workflow is the tool of choice for dependency management in Warehouse Builder. If you choose to use another tool, or scripts to do this, you do not have to install Oracle Workflow. 

3.8 Oracle Enterprise Manager (OEM)
3.8.1 Do I have to install Oracle Enterprise Manager (OEM) in order to be able to run Warehouse Builder?
No, however OEM is the tool of choice for scheduling in Warehouse Builder. If you choose to use another tool, or scripts to do this, you do not have to install OEM. 
Back to Top

4.0 Installation and Configuration
4.1 What client platforms are supported for Warehouse Builder 9.2?
Warehouse Builder is certified to run on Windows, Sun Solaris, Linux (August 2003), HP-UX (August 2003), and AIX (September 2003).

4.2 Where do I install Warehouse Builder 9.2?
The table below outlines the Oracle homes in which the different Warehouse Builder components should be located. The Warehouse Builder client, repository and runtime software are all installed in the same Oracle home which should be a different Oracle home than the Oracle database home.  Oracle Enterprise Manager, the Oracle database and Oracle Workflow can all be installed in the same Oracle home.


Back to Top

5.0 Packaging & Pricing
  5.1 Packaging
  5.2 Pricing
  5.3 Migration

5.1 Packaging
5.1.1 How is Warehouse Builder 9.2 packaged?
Warehouse Builder 9.2 will ship in the enterprise edition of the Oracle9i (9.2) database as a stand alone CD.  The Windows and Sun Solaris versions will be available in July 2003, the Linux and HP-UX versions in August 2003 and the AIX version in September 2003.

5.1.2 What other products do I need for Warehouse Builder 9.2?
Warehouse Builder  uses the Oracle database as its transformation engine. 

5.1.3 Where can I get more information on  Warehouse Builder?
Visit the Warehouse Builder  web site http://www.oracle.com/tools/owb.html for business and technical overviews, customer stories and demos.

5.2 Pricing
5.2.1 What is the price of  Warehouse Builder?
For all pricing questions please refer to the pricing information provided by Oracle at www.oracle.com.

5.2.2 Does the price for Warehouse Builder include the integrators for Applications or SAP R/3?
Integrators are components that are dedicated to extracting data from a particular type of data source.

• The integrators for Oracle databases and flat files are included for free with Warehouse Builder . 

• The Integrator for SAP is included for free 

5.2.5 Are any Oracle Transparent Gateways included in the price of Warehouse Builder?
No, the gateways must be purchased separately. However, generic connectivity via ODBC is a built-in feature of Oracle8.1.7 and Oracle9i.

5.3 Migration
5.3.1 What is the upgrade path from older versions of Warehouse Builder to the new release (9i)?
A Metadata Loader (MDL) file conversion utility is delivered with the product. This will convert MDL files from older versions of Warehouse Builder  to the new Warehouse Builder 9.2 format. The upgrade path for Warehouse Builder is a metadata upgrade into a new repository.

5.3.2 How do I convert the older versions to the new release?
In order to do this the user must perform the following steps: 

• Export the older version repository using the MDL export of that Warehouse Builder client version 

• Use the Warehouse Builder 9.2 client to import the converted MDL file into the newly created repository 

Back to Top