| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
Oracle Warehouse Builder is a business intelligence tool that provides an integrated solution for designing and deploying enterprise data warehouses, data marts, and business intelligence applications. It solves the complex problem of data integration between dispersed data sources and targets. In addition, Warehouse Builder provides all the necessary functionality to maintain the life cycle of the system you develop.
The chapter is divided into the following sections:
Warehouse Builder is a complete design and implementation tool for building and managing data warehouses and business intelligence systems. It combines the key components of both an extraction, transformation, and loading (ETL) tool and a design tool into one product. In addition, Warehouse Builder leverages Oracle database technology. It is the central point of integration of the Oracle Business Intelligence tools suite and provides integration with ad-hoc query tools as well as OLAP and relational database features.
The architecture of Warehouse Builder is comprised of two components, the design environment and the runtime environment. Each of these components handles a different aspect of the system. The design environment manages the metadata, while the runtime environment handles the physical data.
The Warehouse Builder design component consists of a highly scalable metadata repository that is stored in an Oracle database and a set of client design and reporting tools written in Java or HTML. Using these tools, metadata can be viewed and manipulated.
Creating metadata is a design activity that uses editors in the client tool to design objects, processes and jobs. This interactive way of creating metadata is typically used to design a new system. Warehouse Builder supports the design of relational database schemas, multi-dimensional schemas, ETL processes and End User tool environments through the client.
Source systems play an important role in any ETL solution. Instead of creating metadata manually, Warehouse Builder provides integrated components that import the relevant information into its repository.
One of the strengths of the architecture is that it supports life-cycle management which enables metadata to be updated based on changes in the source systems. Warehouse Builder then facilitates propagating these changes to the ETL processes and the target systems.
To ensure the quality and completeness of the metadata in the repository Warehouse Builder provides extensive validation within the repository. Validation helps to keep a complex system that is created by multiple users in an accurate and coherent state.
To further aid in the development and evaluation of the metadata, a web based metadata reporting environment is available. The reporting environment enables developers and business users to browse and investigate system elements without using the design tools. A very important component of this reporting environment is the Impact Analysis capabilities allowing the identification of the impact of changes throughout the system before they are made. Impact Analysis reporting enables you to have better control on changes and better planning for the implementation of these changes. The opposite capability, tracing back where data originated from, is called Data Lineage reporting and is also provided in Warehouse Builder.
Once the user has designed the ETL system on a logical level, he needs to move it to the physical database environment. Before this can be done, information about the database environment is added to the logical design when the target is configured for deployment. After the configuration is complete, code can be generated.
Warehouse Builder generates extraction specific languages for the ETL processes and SQL DDL statements for the database objects. The generated code is deployed, either to the file system or into the database.
Performing the ETL functions means running the deployed code in the database. This can be done using the Warehouse Builder Deployment Manager or from an external tool such as Oracle Enterprise Manager. The ETL process then pulls the source data into the target database . This can be a staging area, an operational data store, a warehouse or any other schema. The code sections external to the Oracle database are executed in their respective environments. For example, ABAP code to extract from SAP systems is run in the SAP environment.
To allow reporting on data loads, the code generated by Warehouse Builder contains audit routines. These routines write information about the ongoing load into the Warehouse Builder runtime tables. Information captured while running the code can include the number of rows selected, inserted and updated. If an error occurs while transforming or loading data, the audit routines report the errors into the runtime tables. To allow easy access and convenient reporting on this runtime information, Warehouse Builder provides the Runtime Audit Browser.
Dependency management and scheduling is provided by a close integration with specific Oracle tools. Oracle Enterprise Manager is Oracle's scheduling and database management tool. Warehouse Builder creates jobs in the Oracle Enterprise Manager repository, which can be scheduled and monitored along with other database activities. Through the interaction with Oracle Workflow (OWF) the Warehouse Builder user can create full-blown processes for dependencies between the ETL processes including notifications.
Creating a business intelligence application is a complex process. It involves various steps and phases, that may span a great number of systems, resources, and functional areas. Warehouse Builder reduces this complexity as it enables you to manage these tasks from a single interface while ensuring scalability, reliability and flexibility by leveraging the latest Oracle database technology.
Key Warehouse Builder functions include:
Importing source data definitions.
Designing and creating target database schema.
Defining data movement and transformation between sources and targets.
Assigning dependencies between ETL processes.
Managing and updating source definitions.
Deploying, upgrading, and managing target schemas.
Designing and creating an ad-hoc query tool environment.
Designing and creating an OLAP environment.
Warehouse Builder is organized into the following key components:
The Warehouse Builder client application provides an easy to use graphical interface that enables you to define, design, and deploy business intelligence systems. Many components contribute to each part of the process. The code generator and Deployment Manager are the components of the client application that control the creation and management of the systems you create.
This component generates scripts such as DDL and PL/SQL based on the metadata in the repository. The generator is designed to utilize the Oracle8i and 9i database features. The generated scripts provide optimal performance for Oracle database systems.
This component manages all aspects of deployment and deployed objects. You can select objects for deployment and determine how you want those objects deployed. You can then execute deployed objects. You can also manage the lifecycle of your system through immediate access to deployment history.
The Runtime Platform Service is the server-side component of Warehouse Builder software that provides execution and deployment services. In order for you to be able to run these services, the Runtime Platform Service must be active.
The Runtime Platform Service manages the execution of mappings and process flows from within Warehouse Builder and ensures that all execution and deployment audit data is stored in a Runtime Repository. For remote executions, it connects to Oracle Enterprise Manager's Management Server. The Runtime Platform Service is invoked through a database job that is automatically started when the database is started and shut down when the database is shut down.
The Design Repository, installed in an Oracle database, stores the metadata definitions for all of the objects used in Warehouse Builder. This is where all of the design information is stored for the target systems you are creating. You can access metadata stored here using the client user-interface, or through OMB Plus, the Warehouse Builder scripting utility.
The runtime repository, installed in an Oracle database, stores all of the deployment data as well as data from executed mappings and process flows. This is where the target environment information is stored for the business intelligence systems you create. This includes connection information for all of the deployment locations.
This reporting browser enables you to view your deployment and execution audit information from a web-based application. The reports are based on data stored in the Runtime Repository.
This reporting browser enables you to view your metadata stored in the design repository from a web-based application and provides information to larger audiences. The information is organized into a format targeted for business-oriented users. The reports are based on data stored in the Warehouse Builder Design Repository.
Warehouse Builder contains a catalog of objects known as First Class Objects. These include objects that can be imported, designed, and deployed.
Warehouse Builder First Class Objects include the following:
Advanced Queues
External Tables
Connectors
Cubes
Dimensions
Files
Locations
Mappings
Materialized Views
Process Flows
Sequences
Tables
Transformations
Views
First Class Objects include the set of objects that can be run through Warehouse Builder services. Figure 1-1 shows the First Class Objects along the top of the diagram and the available Warehouse Builder services on the bottom.
When you design targets in Warehouse Builder, you have a wide variety of deployment targets from which to choose. Depending on your business intelligence needs you can decide how you want to deploy your design. You can use Warehouse Builder to deploy to any of the following systems:
Relational Databases
OLAP Databases
Oracle Enterprise Manager
Oracle WorkFlow
Oracle BI Beans
Oracle Discoverer
CWM Bridges
Warehouse Builder offers the following benefits for data integration and business intelligence system deployment:
Rapid Design: Warehouse Builder reduces the design time by providing easy to use visual editors, wizard-driven user interfaces, and a library of predefined transformations.
Centralized Design: All information about the design of the system is stored and managed in one central place, the Warehouse Builder design repository. This is a single source of the entire design and eliminates the extra cost of inconsistencies caused by reducing duplication of metadata.
Reduce Time to Incorporate Changes: Advanced Life Cycle management features based on the single repository enable a smooth maintenance process.
Error-Free Code: Because it is generated from a design stored in one place, the code is not only error-free, but also easy to re-create, upgrade, and maintain.
Leverage Technology Investments: By using the Oracle database as both a transformation engine and storage facility, Warehouse Builder leverages Oracle scalability, performance, security and high availability.
