|Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)
Part Number E10581-02
This section provides an introduction to Oracle Warehouse Builder (OWB), gets you started using OWB, and discusses the architectural components and objects that you create.
This section contains these topics:
Oracle Warehouse Builder (OWB) is a full-featured data integration, data warehousing, data quality and metadata management solution designed for the Oracle database. OWB is an integral part of Oracle Database 11g Release 2 (11.2) and is installed as part of every database installation (other than Oracle Database XE).
The major feature areas of OWB include:
Extraction, Transformation, and Load (ETL)
Data profiling and data quality
Business-level integration of ERP application data
Integration with Oracle business intelligence tools for reporting purposes
Advanced data lineage and impact analysis
OWB is also an extensible data integration and data quality solutions platform. OWB can be extended to manage metadata specific to any application, and can integrate with new data source and target types, and implement support for new data access mechanisms and platforms, enforce your organization's best practices, and foster the reuse of components across solutions.
See Also:Oracle Database Licensing Information for complete information about options for Oracle Warehouse Builder
OWB can be used in a wide range of scenarios, centered on Oracle Database, and adds value as a solution for data integration, data movement, and data quality. The data systems you create with OWB are driven by rich metadata about sources and targets, and tight integration with, and awareness of, core features in Oracle Database. The ETL and data quality features provided by OWB add value in each of the use cases described in this section.
The most common use cases include:
Business Intelligence and Data Warehousing
OWB can be used in the design of relational objects for your operational data store, and dimensional objects for the data warehouse performance layer. You can implement ETL processes for loading warehouses, including smart operators that simplify loading dimensional objects, even for complex loading processes required for slowly changing dimensions. OWB can implement business intelligence applications and data marts.
OWB can also be used to profile data sources and to develop or discover data rules. Data rules can be used to measure data quality, monitor, and enforce quality requirements during loading, or as an out-of-band process. Data cleansing logic can be incorporated into the warehouse loading process.
Master Data Management
OWB application adapters (or connectors) enable access to data stores representing critical business entities such as customers and products at a logical, rather than physical, level. This simplifies the design of data movement, data quality and data cleansing, and enrichment processes.
OWB data quality features can be used to discover, audit, and enforce the contents of your master data stores and their compliance with your data rules. Automated data cleansing and enrichment processes are easy to implement.
Data Migration, Conversion, and Modernization
OWB can be used to design the target for any migration or conversion process and can implement data movement processes. Data quality features offer high value in such scenarios as well. Data profiling of the source systems can reveal data quality issues before they are introduced into the new system. OWB can be used to profile source data, design the target system, and to implement and orchestrate complex data movement, transformation and cleansing processes without requiring custom code.
Data Profiling and Quality Management
Once you connect to your data sources in OWB (including Oracle databases, sources accessed through gateways, and flat file sources) you can apply full-featured data profiling to generate statistics about data quality, and to discover complex patterns, foreign key relationships, and functional dependencies. You can then design complex data rules and create data auditors to monitor compliance with those rules in any source or target system in your landscape, regardless of whether those sources are loaded using OWB or other ETL tools.
For customers who have selected solutions other than OWB for data profiling and data quality, these can be applied independently of the OWB ETL and design features.
Note:Depending on how you utilize OWB, you may require licenses for additional database options and technologies. Refer to Oracle Database Licensing Information for complete details about OWB options.
See Also:These topics in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux:
Oracle recommends that you start with the following steps to learn about using OWB:
Note:Standalone software for OWB is available with Oracle Database. Use the OWB standalone software to host the OWB repository on an earlier release of Oracle Database. Also, use the standalone software to install OWB on client computers. See "Working with the OWB Standalone Install Package" in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux
To begin using OWB:
Install the OWB software and create the necessary workspaces as described in "Installing Oracle Warehouse Builder on the Server" in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux.
If an administrator has previously completed the installation, contact that person for the required connection information.
On a Windows platform, from the Start menu, select Programs. Select the Oracle home in which OWB is installed, then OWB, and then Design Center.
On a Linux platform, run
owbclient.sh located in the
owb/bin/unix directory in the Oracle home for OWB.
Use the Projects Navigator to manage design objects for a given workspace. The design objects are organized under a project, which provide a means for structuring the objects for security and reusability. Each project contains nodes for each type of design object that you can create or import.
Use the Connections Navigator to establish connections between the OWB workspace and databases, data files, and applications.
Use the Globals Navigator to manage objects that are common to all projects in a workspace and to administer security.
Note:The Security node is visible to users who have an administrator role.
See Also:"Managing Security" in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux
In this procedure, you configure your project and access source and target data.
To configure a project in the Design Center:
In the Projects Navigator, identify the project to be used.
If you are satisfied with the single default project, MY_PROJECT, continue with the next step.
Alternatively, you can rename MY_PROJECT or define more projects. Each project you define is organized in the same fashion with nodes for databases, files, applications, and so on. See the procedure "To create a project" For a different organization, consider creating optional collections as described in "Collections".
Create locations in order to connect to source and target data objects.
To create a location, right-click the appropriate node and select New. Fill in the requested connection information and select Test Connection. In this step, you merely establish connections to sources and targets. You do not move data or metadata until subsequent steps.
In the Connections Navigator, establish these connections by defining locations. Expand the Location node and the nodes within it to gain a general understanding of the types of source and targets you can access from OWB.
For more information about locations see "Locations Navigator".
Identify the target schema.
Although you can use a flat file as a target, the most common and recommended scenario is to use the Oracle Database as the target schema.
To define the target schema, begin by creating a module. Modules are grouping mechanisms in the Projects Navigator that correspond to locations in the Connections Navigator. The Oracle target module is the first of several modules you create in OWB.
In the Projects Navigator, expand the Databases node. Right-click Oracle and select New. The Create Module Wizard appears. Set the module type to Warehouse Target and specify whether the module will be used in development, quality assurance, or production. This module status is purely descriptive and has no bearing on subsequent steps you take.
When you complete the wizard, the target module displays with nodes for mappings, transformations, tables, cubes and the various other types of objects you utilize to design the target warehouse.
Create a separate Oracle module for the data sources. (Optional)
At your discretion, you can either create another Oracle module to contain Oracle source data or proceed to the next step.
Identify the execution environment.
Under the Connections Navigator, notice the Control Centers node. A Control Center is an Oracle Database schema that manages the execution of the ETL jobs you design in the Design Center in subsequent steps.
During installation, OWB creates the
DEFAULT_CONTROL_CENTER schema on the same database as the workspace.
If you choose to utilize the default execution environment, continue to the next step. Alternatively, you can define new control centers at any time. For more information and procedures, see "Deploying to Target Schemas and Executing ETL Logic" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Prepare development, test, and production environments. (Optional)
Thus far, these instructions describe the creation of a single project corresponding to a single execution environment. You can, however, reuse the logical design of this project in different physical environments such as testing or production environments.
Deploy a single data system to several different host systems or to various environments, by creating additional configurations. See "Managing Configurations" in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux.
Adjust the client preference settings as desired or accept the default preference settings and proceed to the next step.
From the main menu in the Design Center, select Tools and then Preferences.
In this section, you create modules for each type of design object into which you intend to import metadata.
In the Projects Navigator, select a node such as Files.
For the selected node, determine the locations from which you intend to ultimately extract data.
Then create a module for each relevant location by right-clicking on the node and select New.
Import metadata from the various data sources: right-click the module and select Import to extract metadata from the associated location. OWB displays a wizard to guide you through the process of importing data.
For an example and additional information on importing data objects, see "Importing Warehouse Builder Data into Business Intelligence Applications" in Oracle Warehouse Builder Sources and Targets Guide.
For the metadata you imported, profile its corresponding data. (Optional)
The next step uses the Data Profiling Option to ensure data quality as described in "Overview of Data Profiling" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Data can only be transformed into actionable information when you are confident of its reliability. Before you load data into your target system, you must first understand the structure and the meaning of your data, and then assess the quality.
Consider using the Data Profiling Option to better understand the quality of your source data. With the Data Profiling Option, you can correct the source data and establish a means to detect and correct errors that may arise in the loading of transformed data.
In this section, you create and design the data objects for the Oracle target module. In previous steps, you may have already imported existing target objects.
To design the target schema:
To create data objects, you can either start the appropriate wizard or use the Data Object Editor. To use a wizard, right-click the node for the desired object and select New. After using a wizard, you may want to modify the object in the editor. In that case, right-click the object and select Open Editor.
For additional information, see "Designing Target Schemas" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
As you design objects, be sure to frequently validate the design objects.
You can validate objects as you create them, or validate a group of objects together. In the Projects Navigator, select one or more objects or modules, then click the Validate icon.
Examine the messages in the Validation Results window. Correct any errors and try validating again.
To redisplay the most recent validation results at a later time, select Validation Messages from the View menu.
For additional information, see "Validating Data Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Configure the data objects.
To configure a data object, select the data object in the Projects Navigator and click the Configure icon. Or right-click the data object in the Projects Navigator and select Configure.
Configuring data objects specifies the physical properties of the object. You must not generate and deploy data objects without specifying the physical property values.
When you create data objects, OWB assigns default configuration property values based on the type of object. In most cases, these default values are appropriate. You can edit and modify the configuration property values of objects according to your requirement. For example, you configure a table to specify the name of the tablespace in which it is created.
When satisfied with the design of the target objects, generate the code.
In the Projects Navigator, select one or more objects or modules, then click the Generate icon. Examine the messages in the Generation Results window. To redisplay the most recent generation results at a later time, select Generated Scripts from the View menu.
Alternatively, in the Data Object Editor, you can generate code for a single object by clicking the Generate icon.
You can save the generated script as a file and optionally deploy it outside OWB.
Generation produces a DDL or PL/SQL script to be used in subsequent steps to create the data objects in the target schema. For more information about generation, see "Generating Data Objects" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
This procedure describes how to design mappings that define the flow of data from a source to target objects.
To design ETL logic:
In the Projects Navigator, expand the Oracle target module, right-click the Mappings node and select New.
The Mapping Editor enables you to define the flow of data visually. You can drag-and-drop operators onto the canvas, and draw lines that connect the operators. Operators represent both data objects and functions such as filtering, aggregating, and so on.
See detailed procedures in "Defining Mappings" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide, concluding with generating the code for the mapping.
Manage dependencies between mappings. See "Designing Process Flows" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Deployment is the process of copying the relevant metadata and code you generated in the Design Center to a target schema. This procedure is necessary to enable the target schema to execute ETL logic such as mappings.
To deploy and execute the generated code:
Deploy objects from either the Design Center or Control Center Manager.
In this step, you define the objects in the target schema. You need do this only once.
The simplest approach is to deploy directly from the Design Center by selecting an object and clicking the Deploy icon. In this case, OWB deploys the objects with the default deployment settings.
Alternatively, if you want more control and feedback on how OWB deploys objects, from the Design Center menu select Tools, then Control Center Manager.
Whether you deploy objects from the Design Center or the Control Center Manager, be sure to deploy all associated objects. For example, when deploying a mapping, also deploy the target data objects such as tables that you defined and any associated process flows or other mappings.
For more information, see "Deploying to Target Schemas and Executing ETL Logic" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Execute the ETL logic to populate the target warehouse.
In this step, you move data for the first time. Repeat this step each time you want to refresh the target with new data.
You have two options for executing the ETL logic in mappings and process flows. You can create and deploy a schedule as described in "Defining Schedules", or you can execute jobs manually as described in "Starting ETL Jobs" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
It is essential to ensure the quality of data entering your data warehouse over time. Data auditors enable you to monitor the quality of incoming data by validating incoming data against a set of data rules and determining if the data confirms to the business rules defined for your data warehouse.
See Also:"Monitoring Quality with Data Auditors and Data Rules" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide
Although the Control Center Manager displays histories for both deployment and execution, the Repository Browser is the preferred interface for monitoring and reporting on OWB operations.
See Also:"About the Repository Browser" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide
Oracle Database provides Oracle Warehouse Builder (OWB) as part of the standard software when the database is installed. OWB is an integral part of Oracle Database. OWB runs on all versions (Standard Edition, Standard Edition One, Enterprise Edition) and typically all platforms that Oracle Database is certified on and ported to.
The basic OWB architectural components on the server side are:
The main OWB components on the client or desktop side, which are discussed in Chapter 3, "User Interface Tour", are:
Figure 2-1 illustrates the components that comprise OWB and where they reside and run on clients and servers.
Figure 2-1 OWB Components
See Also:"Overview of Installation and Configuration Architecture" in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux for diagrams of additional configurations
A major feature of the architecture in OWB is the single, unified OWB Repository for the database instance, which is pre-seeded with a schema and database objects. The runtime environment and the design environment reside in this single repository. The repository schema, named OWBSYS, gets created when you install Oracle Database. Once the database is installed, you do not need to perform additional actions, other than unlocking the OWBSYS and OWBSYS_AUDIT accounts.
OWB release 11g stores all repository objects in the OWBSYS schema, which is created as part of every Oracle release 11g database. The OWBSYS database user is also registered as an OWB user. Administrators and developers generally will register other database users and assign them required privileges, rather than using the OWBSYS account directly.
You can create multiple repositories if you prefer to separate the runtime and design environments; however, this is not recommended.
See Also:These topics in Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux:
To start using OWB, you create at least one, new workspace. Users access their respective workspaces, instead of the repository as a whole. Thus, if you are the OWB administrator, instead of granting users access to a repository, you grant them access to one or more workspaces. Because all workspaces are stored in a single repository schema, creating workspaces is simplified.
In defining the repository, an administrator creates one or more workspaces, with each workspace corresponding to a set of users working on related projects. For example, a common practice is to create separate workspaces for Development, Testing, and Production. This practice provides team focus in addition to security. Users such as developers can have access to the Development and Testing workspaces, and can be restricted from the Production workspace. Later in the implementation cycle, the Repository Assistant in OWB can be used to manage existing workspaces or to create new ones.
Each workspace has a default Control Center that points to itself, and it is started and stopped with its corresponding Control Center Service. A Control Center stores detailed information about every deployment and execution, which you can access either by object or by job.
You can use the default Control Center to deploy to the local system, or you can create additional Control Centers for deploying to different systems as needed. Only one Control Center is active at any given time, and this is the Control Center associated with the current active configuration.
The Control Center Agent (CCA) runs on the Oracle Containers for J2EE (OC4J) server. Some capabilities of OWB related to accessing non-Oracle data, such as Code Templates and Web services, depend on Java code that executes outside the database, in an OC4J server called the Java or J2EE Runtime. For some heterogeneous data access scenarios, you may also need to install a standalone Java Runtime on hosts where there is no Oracle database installed.
You start the Control Center Agent with
ccastart from the command line. OWB provides the
cca_admin utility to enable dynamic changes to Control Center Agent settings, without the need to shut down and subsequently restart the run-time environment.
Note:A Code Template (CT) contains the knowledge required by Oracle Warehouse Builder to perform a specific set of tasks against a specific technology, system, or set of systems. You must start the Control Center Agent before you deploy Code Templates.
Refer to "About Code Templates" in Oracle Warehouse Builder Sources and Targets Guide and "About Prebuilt Code Templates Shipped with Warehouse Builder" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide
The data in your OWB project is stored in a target schema within the server. This data is in the form of data objects such as tables, views, and dimension and cube objects. In a traditional data warehousing implementation, there is typically only one target schema, which is the data warehouse target. You can design both relational and dimensional target schemas. To design a target schema, you first create the target module that will contain all the data objects. A target module is a container that holds the metadata definitions of all your data warehouse objects. Each target module corresponds to a target location that represents the physical location where the objects are stored.
In addition to context-sensitive help available with the F1 key, OWB provides a Help Menu with links to utilities, training, the discussion forum, Oracle Technology Network and more. The Help Menu also contains the Help Center, the online version of the complete OWB documentation library.
Table of Contents. Opens the Help Center with the Table of Contents selected.
Help Favorites. Opens the Help Center with your favorites selected, if you have configured favorite Help topics.
Start Page. Accesses the Start Page after the first time you have run OWB.
Extensions. A link to Oracle Warehouse Builder Utility Exchange on OTN. The purpose of the OWB Utility Exchange is to provide the user community with a forum where utilities, code samples, and tips and tricks can be exchanged. The utilities posted here are not part of any Oracle production release and are, therefore, free-of-charge and not supported.
Session Properties. Displays information about the current workspace session such as workspace owner and name; connection properties like host name, service name and database version; and roles granted to the workspace owner.
Oracle Warehouse Builder provides the documentation described in Table 2-1.
Table 2-1 Oracle Warehouse Builder Documentation Library
|Title||Description and Use|
You will use "Part I: Installing and Configuring Oracle Warehouse Builder" to perform any necessary installation tasks and to configure the OWB repository. "Part II: Administering Oracle Warehouse Builder," starting with the chapter "Managing Configurations" provides detailed procedures for configurations, the Control Center and Repository, Control Center Agent, managing content, using the Metadata Loader, and managing security.
The release notes contain any late-breaking information about corrections, troubleshooting, and known issues. You can scan through the release notes during the set up processes to see the last-minute notes about this release.
Similar to Oracle Database Concepts, this book provides a high-level explanation of the architecture, user interface, and components within OWB. It provides a user interface tour chapter and overviews of the processes and steps used to perform typical tasks within OWB. This book provides links to more detailed information and procedures within the other books.
This book provides comprehensive procedures for designing target schemas, performing data transformations, generating code, and doing all the tasks for optimizing and managing data quality.
This book lists all of the supported sources and targets and provides procedures for importing from sources and deploying to targets.
This book is part of the Oracle Database 2 Day + series and provides a good starting place to understand the data warehousing features offered with the database in addition to OWB.
This book describes the scripting language available with OWB and provides a complete language reference.
Oracle Warehouse Builder Help (Only available as online help within OWB.)
The comprehensive help system that provides online versions of the complete documentation library, and context-sensitive help for all UI objects.