2 Introduction to Oracle Warehouse Builder

Use Cases for Oracle Warehouse Builder

Oracle Warehouse Builder 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 Oracle Warehouse Builder 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 Oracle Warehouse Builder add value in each of the use cases described in this section.

The most common use cases include:

• "Business Intelligence and Data Warehousing"

• "Master Data Management"

• "Data Migration, Conversion, and Modernization"

• "Data Profiling and Quality Management"

Business Intelligence and Data Warehousing

Oracle Warehouse Builder 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. Oracle Warehouse Builder can implement business intelligence applications and data marts.

Oracle Warehouse Builder can also be used to profile data sources and to develop or discover data rules. You can use data rules 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

Oracle Warehouse Builder 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.

You can use Oracle Warehouse Builder data quality features 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

You can use Oracle Warehouse Builder design to 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. You can use Oracle Warehouse Builder profile to 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

After you connect to your data sources in Oracle Warehouse Builder (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 Oracle Warehouse Builder or other ETL tools.

For customers who have selected solutions other than Oracle Warehouse Builder for data profiling and data quality, these can be applied independently of Oracle Warehouse Builder ETL and design features.

Before You Begin

Before you can use any of the Oracle Warehouse Builder client components, first ensure you have access to an Oracle Warehouse Builder workspace.

To begin using Oracle Warehouse Builder: 

1. Install the Oracle Warehouse Builder software and create the necessary workspaces as described in "Installing Oracle Warehouse Builder on the Server" in Oracle Warehouse Builder Installation and Administration Guide.

   If an administrator has previously completed the installation, contact that person for the required connection information.

2. Start the Design Center.

   On a Windows platform, from the Start menu, select Programs. Select the Oracle home in which Oracle Warehouse Builder is installed, then Oracle Warehouse Builder, and then Design Center.

   On a Linux platform, run Oracle Warehouse Builderclient.sh located in the Oracle Warehouse Builder/bin/unix directory in the Oracle home for Oracle Warehouse Builder.

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 Oracle Warehouse Builder 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.

Configure a Project in the Oracle Warehouse Builder Design Center

In this procedure, you configure your project and access source and target data.

To configure a project in the Design Center: 

1. 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".

2. 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 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 Oracle Warehouse Builder.

   For more information about locations see "Locations Navigator".

3. 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 Oracle Warehouse Builder.

   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 is 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 use to design the target warehouse.

4. 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.

5. 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, Oracle Warehouse Builder creates the DEFAULT_CONTROL_CENTER schema on the same database as the workspace.

   If you choose to use 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.

6. 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.

7. 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.

   As a new user, you may be interested in setting the "Environment Preferences" and the naming mode under "Naming Preferences". For information on all the preferences, see "Oracle Warehouse Builder Design Center and Runtime Preferences".

   Note:

   To change the locale, edit Oracle Warehouse Builder.conf or ide.conf and use AddVMOption to set the new locale. The locale setting specifies the language you want the client text to display.

   For example, the following entry in Oracle Warehouse Builder.conf sets the locale to Japanese:

   AddVMOption -Duser.language = ja

Import the Source Metadata

In this section, you create modules for each type of design object into which you intend to import metadata.

1. 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.

2. Import metadata from the various data sources: right-click the module and select Import to extract metadata from the associated location. Oracle Warehouse Builder 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.

3. 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.

Profile Data and Ensure Data Quality

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.

Design the Target Schema

In this section, you create and design the data objects for the Oracle target module. In previous steps, you may have imported existing target objects.

To design the target schema: 

1. 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 Schemas" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

2. 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. 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 .

3. 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, Oracle Warehouse Builder 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.

4. 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 Oracle Warehouse Builder.

   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.

Design ETL Logic

This procedure describes how to design mappings that define the flow of data from a source to target objects.

To design ETL logic: 

1. 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.

2. Manage dependencies between mappings. See "Designing Process Flows" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Deploy the Design and Execute the Data Integration Solution

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: 

1. Deploy objects from either the Design Center or Control Center Manager.

   In this step, you define the objects in the target schema.

   The simplest approach is to deploy directly from the Design Center by selecting an object and clicking the Deploy icon. In this case, Oracle Warehouse Builder deploys the objects with the default deployment settings.

   Alternatively, if you want more control and feedback on how Oracle Warehouse Builder 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.

2. 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.

Monitor Quality and Report on the Data System

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.

Although the Control Center Manager displays histories for both deployment and execution, the Repository Browser is the preferred interface for monitoring and reporting on Oracle Warehouse Builder operations.

The Oracle Warehouse Builder Repository

A major feature of the architecture in Oracle Warehouse Builder is the single, unified Oracle Warehouse Builder Repository for the database instance, which is pre-seeded with a schema and database objects. The run time environment and the design environment reside in this single repository. The repository schema, named Oracle Warehouse BuilderSYS, gets created when you install Oracle Database. After the database is installed, you must not perform additional actions, other than unlocking the Oracle Warehouse BuilderSYS and Oracle Warehouse BuilderSYS_AUDIT accounts.

Workspaces

To start using Oracle Warehouse Builder, you create at least one, new workspace. Users access their respective workspaces, instead of the repository as a whole. Thus, if you are Oracle Warehouse Builder 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, you can use the Repository Assistant in Oracle Warehouse Builder to manage existing workspaces or to create new ones.

Control Center Service

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.

Control Center Agent (J2EE Runtime)

The Control Center Agent (CCA) runs on the Oracle Containers for J2EE (OC4J) server. Some capabilities of Oracle Warehouse Builder 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 Run time. For some heterogeneous data access scenarios, you must install a standalone Java Run time on hosts where there is no Oracle database installed.

You start the Control Center Agent with ccastart from the command line. Oracle Warehouse Builder provides the cca_admin utility to enable dynamic changes to Control Center Agent settings, without the requirement to shut down and subsequently restart the run-time environment.

Target Schemas

The data in your Oracle Warehouse Builder 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 contains 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.

Help Menu

The Help menu available from the Design Center contains these menu items:

• Search. Provides a shortcut to the search facility for the online Help Center.

• 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.

• Dynamic Help.

• Start Page. Accesses the Start Page after the first time you have run Oracle Warehouse Builder.

• Extensions. A link to Oracle Warehouse Builder Utility Exchange on OTN. The purpose of the Oracle Warehouse Builder 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.

• Training. Provides a shortcut to Oracle University where you can find out about training for Oracle Warehouse Builder.

• Discussion Forum.

• Oracle Technology Network. Provides a shortcut to Oracle Warehouse Builder on OTN for this release.

• Check for Updates. Checks for Oracle Warehouse Builder product updates. When grayed out, no updates are available.

• 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.

• About. Displays version information about the current software release.

Help Center

The Help Center contains the complete Oracle Warehouse Builder documentation set in HTML format, available for online reading and searching. The Help Center opens with the Contents tab active. Click the plus symbol to expand the contents. Use the Search facility to enter topics on which to search.