Oracle Warehouse Builder 10g

Oracle Warehouse Builder 10g is a tool to enable the design and deployment of Business Intelligence applications, data warehouses and data marts. Warehouse Builder enables 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 Discoverer, Oracle Workflow and Oracle Enterprise Manager enable an integrated Business Intelligence solution with Warehouse Builder at the core.

Modeling a Business Intelligence system

Oracle Warehouse Builder is not merely an ETL (Extract, Transform, Load) tool. It is a tool allowing users to design their ETL processes, target warehouses, intermediate storage areas and the end user access layer.

Amongst the main components in any business intelligence solution are the source systems that the business intelligence system will report upon. Warehouse builder provides developers with an easy, wizard-driven way to capture the metadata for the source systems. Metadata can be captured by reading from a data dictionary or Oracle Designer repository but also industry standard Common Warehouse Metamodel (CWM) definitions are supported. Warehouse Builder 10g introduces a partner solution that enables support for more than 40 metadata files from third party vendors, such as CA Erwin, PowerDesigner and Business Objects.

After capturing this information, developers may use the metadata representations of the source to model the extraction processes. The supported source systems are:

• Oracle databases
• Flat Files
• SAP R/3
• DB2, Sybase, Informix, SQL Server and others via Oracle Transparent Gateways
• ODBC
• Mainframe

Warehouse Builder is tightly integrated with the Oracle Database platform. Therefore the main target system supported is the Oracle Database. However, Warehouse Builder also supports flat files as targets. The target systems supported are: 

• Oracle Database 10g Release 1 (10.1)
• Oracle9i Release 2 (9.2.0)
• Oracle8i Release 3 (8.1.7.4)
• Flat files

Designing the ETL processes

Once the metadata for the sources is captured and the target schema is designed, users can start to create the dataflows that define how data moves from sources to targets. These data flows, ETL processes, are called mappings in Warehouse Builder.

Warehouse Builder provides users with a graphical environment to model the ETL processes. The tool supports mapping multiple sources into multiple targets. It enables users to specify chained transformations on the data flow and apply complex PL/SQL transformations to the data. To ensure a high standard in usability, the mapping component allows for automated mapping between sources and targets. Users can create new mapping objects using auto mapping, or map by name or position to create mappings in a quick and efficient manner.To provide optimal flexibility, users can define their own inline expressions using a point and click Expression Builder with validation capabilities. To enable reuse of Legacy code as well as  newly developed code, a transformation library is available in which PL/SQL code is stored. This code can then be used within mappings or ran in pre or post mapping processes. Process flows also support the execution of these transformations.

The mapping component also enables you to perform common operations as joining, filtering, aggregating and ordering data. All of these operations are then transformed into generated PL/SQL code. More advanced operations as key lookup, set-based operations (union (all), minus, intersect), pivot and un-pivot are supported as well. Should the user choose a flat file as a source, Warehouse Builder will automatically detect this and generate the appropriate SQL*Loader code.

Once you have built a mapping you may want to run it using a set of test data. Warehouse Builder provides graphical debugging capabilities in the mapping editor. You can define which tables you want to use as source and target for the debug run, or create new tables, and perform the mapping step-by-step. Breakpoints and watch points enable you to view how the data flows through the mapping. The debug messages provide information about the status and any errors that occur during execution.

Data Quality

Data Quality plays a key role in the design of ETL processes. Warehouse Builder provides specific mapping operators to cleanse data upon loading. Complex tasks such as name and address cleansing as well as Match-Merge are also supported within the generic concept of Warehouse Builder.

The Name and Address solution identifies and corrects errors and inconsistencies in name and address source data by comparing input data to the data libraries supplied by the certified third-party name and address cleansing software vendors.

The Match-Merge operator first matches the data by determining through user-defined business rules which input records refer to the same data. It then merges records by consolidating the data from the matched records into a single record, again based on the user-defined business rules.

Designing the Process Flows

Having designed the ETL mappings the user can graphically record the dependencies between the mapping runs. The user-friendly interface offers graphical access to the most common features that a dependency engine supports. The user can design the complete process including email notifications etc. Code generation for Process Flow definitions consists of industry standard XML Process Definition Language (XPDL). Out-of-the-box the process flows will be deployed to an Oracle Workflow engine. Process flow definitions can contain a multitude of activities, including mappings, transformations, external processes and file-based activities such as FTP or file exists.

Deployment

After the design is completed, the actual code must be deployed to the database engine. Deployment Manager provides this functionality in Warehouse Builder. The Deployment Manager offers a unified interface for the deployment of objects, whether those are database objects, PL/SQL mappings or Process flows. In order to deploy the code, Warehouse Builder will generate code based upon the definitions in its metadata repository. The first step to create this generated code is to ensure all metadata is valid. Validation is a process that verifies the metadata and reports all defects in the logical model. If the model is valid, the code can be generated. Warehouse Builder enables users to deploy the specification to a file system and store it for later deployment.

To instantiate the warehouse and the ETL processes the following types of code are generated:

• SQL DDL for warehouse creation
• PL/SQL for ETL processes from relational sources
• SQL*Loader control files for flat file sources
• ABAP to extract from SAP R/3 sources
• XML Process Definition Language (XPDL) code to deploy processes to Workflow

• Set-based or row-based
• PL/SQL bulk processing
• Foreign key constraint manipulation
• Faster loading from remote tables using inline views
• Partition exchange loading
• External tables
• Table functions
• Multi table insert
• Merge statement

Metadata Management

Warehouse Builder 10g fully exposes the version management capabilities. At any point in time the user can take a snapshot of the design object(s). The snapshot is stored in the database and can be used for future comparison purposes or to restore from in the case of unintended changes. The Change Manager user interface provides access to the snapshot related features.

Low-level security and user-based auditing are also available in Warehouse Builder. The architecture to manage users and privileges is there and available to use.

The metadata repository is multi language enabled. Business names and descriptions can be recorded in multiple languages enabling metadata to be exchanged in different languages. For example you can create a Discoverer end user access layer in English as well as in French or German, provided the translated labels have been recorded.

The Warehouse Builder metadata repository is extensible. Users can define their own user-defined properties to record information that cannot be stored in the existing definition of Warehouse Builder objects. These properties are fully supported in both the UI and the reporting capabilities. As such business users can also benefit from the information recorded in the user defined properties.

In some cases you may require back-end access into the metadata repository. Oracle Warehouse Builder provides a fully documented public Java API that exposes all functionality of the product. On top of that, a TCL-based scripting language provides access to all functionality. Both solutions may be very appealing for mass-updates (e.g. prefix all table names) and precision updates (e.g. increase the precision of all numeric ID columns from 12 to 15).

While creating the metadata for the warehouse design, designers can use Warehouse Builder's strong metadata reporting facilities to share this information with business users. Warehouse Builder provides a large number of pre-built metadata reports. Sophisticated reports such as data lineage from target objects and impact analysis reports are among the standard reports provided. To enable easy access to this information, all the reports are created for (but do not require) Oracle Portal and accessible through an Internet browser.

Managing the Business Intelligence system

Once the code is deployed in the target system the day-to-day activities include scheduling the ETL processes and verifying whether these processes completed successfully. All this processing takes place in the Warehouse Builder runtime environment.

Oracle Enterprise Manager (OEM) provides the scheduling in an Oracle database environment. This is a DBA tool that Oracle bundles with the database or the Application server. Whether the user registers a job in OEM to run just a mapping or to run a whole process flow, the runtime platform will take care of the execution and auditing. Users can also run mappings or process flows from the deployment manager or via the command line. The command line option enables integration between Oracle Warehouse Builder and third-party schedulers.

Since errors can occur while loading data, Warehouse Builder provides an HTML-based application called the Runtime Audit Browser to verify the results of running the jobs. Runtime Audit Browser also enables access to the deployment history information the deployment manager uses to define the default deployment action on an individual object. The Warehouse Builder 10g includes capabilities to manage the runtime platform in a multi-node real applications cluster (RAC).

A Business Intelligence system is an ever-evolving system. Sources may be added, existing sources may change, the target must be adapted to new business questions and so on. To allow the warehouse to grow and evolve, Warehouse Builder has life-cycle management capabilities. Besides the abilities to handle source object changes, Change Management allows Warehouse Builder to adapt the warehouse to changes in the logical model. To deploy these changes, the user simply chooses the "upgrade" action in the deployment manager. The user can then choose to deploy these changes. All this is done without jeopardizing the data in the warehouse environment.

Integration

Oracle is one of the few vendors covering the entire BI space with an end-to-end integrated solution. Warehouse Builder, as a Business Intelligence and ETL tool, is at the center of this integrated stack.

Oracle Database 10g: The Oracle database is the runtime engine for Oracle Warehouse Builder. As a tool that generates code, Warehouse Builder generates optimally tuned code for execution on the Oracle platform. All ETL processes are therefore tuned for the deployment platform and users do not have to worry about the scalability of external ETL engines. Because Warehouse Builder is so closely aligned with the database, it is able to leverage the ETL enhancements added to the database. Therefore complex PL/SQL routines can now be replaced with SQL statements, which push the complexity into the database engine. The result is that Warehouse Builder is able to generate less complex code for a superior performance. Warehouse Builder also leverages full capabilities of the Oracle Database 10g OLAP server, including Analytic Workspaces.

Oracle 10g Application Server: The Application Server is the deployment platform for the reporting tools in Oracle's integrated BI solution. Within Warehouse Builder application developers can design the End User Layer (EUL) for Oracle Discoverer and then export that information to the EUL schema. This shortens the development time of the entire solution and ensures all metadata is essentially stored in one common repository. To report on the contents of both the design repository and the runtime repository, Warehouse Builder offers a solution that uses another Application Server component, Oracle Portal. Using the proven Portal technology and Single Sign On, all users (from developers to business users) can be given privileges to view the BI metadata as stored in the Warehouse Builder metadata repository. Out-of-the-box lineage reports enable business users to track all the transformations done on a specific target record, enabling them to judge the correctness and accuracy of the data. System administrators can view the deployment history as well as metrics of ETL and process jobs that have run. The reports can also be run outside Portal environment.

Third Party vendors: Oracle is one of the driving partners of the OMG CWM (Object Management Group, Common Warehouse Metamodel) metadata standard. Warehouse Builder is built based on that standard and is capable of exchanging metadata with all third party vendors supporting the OMG CWM standard. The benefits of supporting this open standard are:

• A powerful object model
• Spanning the complete spectrum of metadata related to ETL and analysis
• Utilizing XML Metadata Interchange (XMI)

Summary

Oracle Warehouse Builder is a core component of Oracle's Business Intelligence strategy, tightly integrated with the entire stack of products Oracle offers to customers. A summary of the key characteristics and benefits: Warehouse Builder is a design tool for Business Intelligence.

• Design and deployment of business intelligence schema's, source-to-target mappings and process flows
• Fully leverages the Oracle database and other Oracle products
• Life-cycle management capabilities
• Runtime data and design time metadata sharing and reporting

 

KEY FEATURES
Integration Oracle Database 10g Oracle Application Server 10g Oracle Warehouse Builder 10g bridges: - OMG CWM  - Oracle Designer - OLAP Server - OLAP Analytical Workspaces - Oracle Discoverer - Oracle Express - more than 40 metadata sources via a partner solution	ETL functionality Graphical ETL design Numerous operators to create ETL processes: - Pivot/un-pivot - Table functions - Key lookup - Union/Minus/Intersect - Joiner - Splitter - Filter - Aggregator - Inline Expressions - Transformations - Surrogate key handling - Pre and Post mapping processes - External processes Incremental code generation within the mapper Graphical data flow debugger Auto mapping between sources and targets SAP Integrator provides seamless extraction from SAP R/3 source system on any platform An XML tool kit is supplied with a set op OWB embedded transform functions Process flow editor
Supported sources Oracle - Relational tables - External tables - Advanced queues SAP R/3 Flat Files ODBC DB2, Sybase, Informix, SQL Server (via Oracle Transparent Gateways) Mainframe	Supported targets Oracle Database 10g Release 1 (including RAC support) Oracle9i Release 2 (including RAC support) Oracle8i Release 3 (8.1.7.4) Flat files
Extensibility Public views on both design time and runtime environment Public Java APIs to access any of the product's functionality Tcl-based scripting language to access any of the product's functionality	Target design capabilities Wizard driven and highly graphical data stores, marts and enterprise Business Intelligence systems Relational models Multi-Dimensional models
Standards conformance OMG CWM: - Open standard - Utilizes XML Metadata Interchange (XMI) - Powerful object model - Spans spectrum related to ETL and analysis XPDL code generation for process flows	Data Quality Name and Address checking Open architecture for data quality vendors Match-Merge
Lifecycle Management Source metadata Reconcile: - Re-import existing source objects - Reconcile with current definitions Impact analysis Create/Drop/Add/Rename Objects Impact Analysis report Generate upgrade scripts Store intermediate data if required for change	Reporting Support for multiple Portlets within the Warehouse Builder Browser component Metadata Impact Analysis Reporting Metadata Lineage Reporting Portlet based technology Secure framework HTML-based Design and Runtime Audit reporting
Flat file handling Character delimited and fixed length Single or multiple record type files Graphical Expression builder Graphical Transformation Editor Transformation library to store and share transformations	Metadata Management Repository security model Advanced validation framework Multiple User Environment Advanced locking and name checking Archive and Restore mechanism Multi Language Support (MLS) Fully exposed version management of any object Extensibility through user-defined properties
RELATED PRODUCTS AND SERVICES Oracle Warehouse Builder 10g is the center of Business Intelligence design and integrates with a number of Oracle products:  Oracle Database 10g Enterprise Edition: - Deployment target - ETL functionality - OLAP server - Workflow engine - Oracle Enterprise Manager scheduling Oracle Application Server 10g: - Metadata reporting Oracle Discoverer Oracle Business Intelligence Beans	GETTING STARTED To get started using Oracle Warehouse Builder 10g:  Install Oracle Database 10g Enterprise Edition Install Oracle Warehouse Builder 10g The product is available on Windows 32-bit (NT, 2000, XP) in May 2004. The product will be released soon after on other platforms.