【oracle教程】Starting with Oracle Warehouse Builder 11g Release 1

Starting with Oracle Warehouse Builder 11g Release 1

Purpose

In this lesson, you use the setup files to configure the project environment for Oracle Warehouse Builder 11gRelease 1 (OWB 11gR1). You then create a Warehouse Builder user and log in.

Time to Complete

Approximately 30 minutes

Note: Instructions for downloading the OWB 11g SETUP scripts are provided later in this lesson. This tutorial and its setup scripts support OWB 11g Release 1, only. Earlier versions of this Oracle By Example tutorial are available for OWB 10g Releases 1 and 2.

Topics

This lesson discusses the following:

OverviewPrerequisitesReference MaterialWarehouse Builder 11g Architecture and Components

Setting Up the Project Environment

Introducing the OWB Program Group ComponentsLogging In to the Design CenterSummary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

In this lesson, you learn how to download and execute the setup files to configure the Warehouse Builder environment. You also use the OWB Repository Assistant to create a user for logging in to the Oracle Warehouse Builder repository database where your warehouse design metadata is stored.

Back to Topic List

Prerequisites

 

In order for this lesson to work successfully, you should have performed the following:

1.

Completed the installation of Oracle Database (Enterprise Edition) 10g Release 2 (patched up to 10.2.0.3 for OLAP support) or 11g Release 1 (11.1). It is suggested that you create a database namedorcl for this course. Otherwise, you need to substitute the Oracle Service Name of your database wherever you seeorcl mentioned in this course.

Note: This hands-on tutorial has been tested using OWB 11g Release 1 on an Oracle Database 10g Release 2 (10.2.0.3) instance. You can choose to run the hands-on using Oracle Database 10g Release 2 (10.2.0.3) or 11g Release 1.

To simplify the setup, this course assumes that the database and Warehouse Builder are on the same machine. For this course, you are strongly encouraged to install the database and Warehouse Builder on the same machine.

Note: If you are using Warehouse Builder 11g Release 1 with Database 10g Release 2, be sure to install Warehouse Builder to an empty Oracle Home, not the Oracle Home of the database.

 

2.

Completed the installation of Oracle Warehouse Builder 11g Release 1.

Note: Warehouse Builder is now packaged differently and comprised of four components as follows:

ETL Core features: The core ETL features are provided free with the database Standard Edition, Standard Edition One and Enterprise Edition. The aim of the ETL Core features is to provide core bulk ETL functionality as part of the database license. The core features that are provided free represent the functionality provided by the current 10g Release 1 version of Warehouse Builder with the addition of "Experts" feature.Enterprise ETL Option: The aim of the Enterprise ETL Option is to support large scale and complex deployments by improving the scalability and performance of Extract/Transport/Load (ETL) jobs. Some of the features included in this option are slowly changing dimensions type 2 and 3 support, transportable tablespaces, interactive lineage and impact analysis and user-defined objects.Data Quality Option: The aim of the Data Quality Option is to support the transformation of data into quality information on an ongoing basis.Connectors: Connectors allow customers to quickly and easily extract (and in some cases target) data to/from their CRM and ERP applications. The following connectors are available: SAP, Oracle EBusiness Suite, Peoplesoft 8/9, and Siebel.

For more information on OWB packaging, read the "Product Options and Licensing" section of theOracle Warehouse Builder User's Guide.

Back to Topic List

Reference Material

The following is a list of useful reference materials if you want additional information about the topics in this module:

Oracle Warehouse Builder 11g Release 1: Documentation LibraryOracle Technology Network (OTN): OWB Collateral Library

Oracle University (5 day Instructor Led course):Oracle Warehouse Builder 10g: Implementation Part 1 (3 day) andOracle Warehouse Builder 10g: Implementation Part 2(2 day)

Visit the Oracle University website, if you want to enroll for this course.

Back to Topic List

Warehouse Builder 11g Architecture and Components

Oracle Warehouse Builder is an information integration tool that leverages the Oracle Database to transform data into high-quality information. The Oracle Database is a central component in the Warehouse Builder architecture because the Database hosts the Warehouse Builder repository and the code generated by Warehouse Builder.

The diagram below illustrates the interaction of the major components of the Warehouse Builder software.

The Design Center is the user interface for designing, managing, scheduling, and deploying ETL processes for moving and transforming data. All metadata associated with the work done in the Design Center is stored in the Oracle Warehouse Builder Repository. The repository is hosted on an Oracle Database and you can use the Repository Browser to report on the metadata in the repository. Also hosted on an Oracle Database is the Target Schema to which Warehouse Builder loads data resulting from the ETL processes that you execute through the Control Center Service.

Design Center

The Design Center provides the graphical interface for defining sources and designing targets and ETL processes.

Control Center Manager

Also in the Design Center client is the Control Center Manager from which you can deploy and execute ETL processes. The Control Center Manager is a comprehensive deployment console that enables you to view and manage all aspects of deployment.

Target Schema

The target schema is the target to which you load your data and the data objects that you designed in the Design Center such as cubes, dimensions, views, and mappings. The target schema contains Warehouse Builder components such as synonyms that enable the ETL mappings to access the audit/service packages in the repository. The repository stores all information pertaining to the target schema such as execution and deployment information.

Notice that the target schema is not a Warehouse Builder software component but rather an existing component of the Oracle Database. As such, you can associate multiple target schemas with a single Warehouse Builder repository. You can have a 1 to 1 relationship or many target schemas to a single repository.

Warehouse Builder Repository

The repository schema stores metadata definitions for all the sources, targets, and ETL processes that constitute your design metadata. In addition to containing design metadata, a repository can also contains the runtime data generated by the Control Center Manager and Control Center Service.

As part of the initial installation of Warehouse Builder, you use the Repository Assistant to define the repository in an Oracle Database. You can host a Warehouse Builder 11g repository either on Oracle Database 10g R2 (10.2) or 11g.

About Workspaces

In defining the repository, you create one or more workspaces, with each workspace corresponding to a set of users working on related projects. A common practice is to create separate workspaces for development, testing, and production. Using this practice, you can allow users such as your developers access to the development and testing workspaces but restrict them from the production workspace.

Later in the implementation cycle, you also use the Repository Assistant to manage existing workspaces or create new ones.

Repository Browser

The Repository Browser is a web browser interface for reporting on the repository. You can view the metadata, create reports, audit runtime operations and perform lineage and impact analysis. The Repository Browser is organized such that you can browse design-specific and control center-specific information.

Control Center Service

The Control Center Service is the component that enables you to register locations. It also enables deployment and execution of the ETL logic you design in the Design Center such as mappings and process flows.

Back to Topic List

Setting Up the Project Environment

In order to partially prepopulate your repository for this course, perform the following steps:

Download the Setup ScriptsIf Using DB10gR2, Create OWBSYS User and Enable Access to Control Center and WorkspacesCreate the Design Center User and RepositoryRun the Setup Scripts

Back to Topic List

Download the Setup Scripts

1.

Download the owbdemo_files.zip file by right-clicking here and selecting Save Target As from the pop-up menu. Direct the download to any directory on your computer's hard drive.

 

2.

Open the owbdemo_files.zip file, select all files, and unzip them to a root level, such asc: or d:. If you unzip to your computer's d:\ drive, your file locations will match the screenshots in the following lessons.

Note: If you extract the files using WinZip, select the "Use folder names" check box.

If you unzip this way to the d:\ drive, the resulting file locations will be:

• D:\newowbdemo\create_users.sql
• D:\newowbdemo\createlocs.tcl
• D:\newowbdemo\createprj.tcl
• D:\newowbdemo\cube_sales.tcl
• D:\newowbdemo\dim_channels.tcl
• D:\newowbdemo\dim_customers.tcl
• D:\newowbdemo\dim_products.tcl
• D:\newowbdemo\dim_promotions.tcl
• D:\newowbdemo\dim_times.tcl
• D:\newowbdemo\load_channels.tcl
• D:\newowbdemo\load_customers.tcl
• D:\newowbdemo\load_products.tcl
• D:\newowbdemo\load_promotions.tcl
• D:\newowbdemo\load_sales.tcl
• D:\newowbdemo\loadall.tcl
• D:\newowbdemo\loadrolapsales.tcl
• D:\newowbdemo\loadsalesmaps.tcl
• D:\newowbdemo\loadxsales.tcl
• D:\newowbdemo\owbdemoinit.tcl
• D:\newowbdemo\sequences.tcl
• D:\newowbdemo\sourcefiles\export.csv
• D:\newowbdemo\sourcefiles\expense_categories.csv

Make sure that export.csv and expense_categories.csv files are in a "sourcefiles" subfolder of the "newowbdemo" folder. Make a note of thec: or d: or other directory location where you place these files.

 

3.

You can optionally download the xsales.zip file fromhere.

Save the xsales.zip file in any directory on your computer's hard drive. Extract the xsales.dmp file from the xsales.zip file.

Note: This download is not required or mandatory for the hands-on exercises. This is made available to you in case you want to explore XSALES table data on your own.

Back to Topic

If Using DB10gR2, Create OWBSYS User and Enable Access to Control Center and Workspaces

Note: If you are using Oracle Database 10gR2 instead of Database 11g with this tutorial, you must perform the following steps.
If you are using Database 11g, skip ahead to the subtopic "Create the Design Center User and Repository".

1.

If using DB 10gR2, you need to run a SQL script to create the OWBSYS repository schema that automatically comes with the install of DB11g, and which is needed by OWB 11g.

This script, <your OWB home>/OWB/UnifiedRepos/cat_owb.sql, installs the OWBSYS database user that is otherwise automatically created when installing DB 11g.

Note: The method by which you invoke SQL Plus is important. Do not invoke SQL Plus from Start > Programs > Oracle database home folder or Warehouse Builder folder! Instead, invoke SQL Plus from Start > Run > cmd.exe. Type thePath command and press Enter. You want to check that Warehouse Builder is near the front of the path statement, so that the SQL Plus session will invoke from the Warehouse Builder installation. If not, you may need to temporarily set the path, such as Path=<Warehouse Builder home>\bin. Then invoke SQL Plus asSYS with SYSDBA privileges. For example, at the command line you might type:sqlplus sys/<sys password> as sysdba.

Connect to SQL Plus as sysdba and issue the following command to create OWBSYS:

@<your OWB home>/owb/UnifiedRepos/cat_owb.sql;

Press Enter. You will be prompted for a tablespace for the OWBSYS user. For this training, it is recommended that you specify the USERS tablespace. Typeusers and press Enter.

When this command finishes successfully, you will be prompted "If you are NOT using an OWB installed in the Oracle database home, please now run reset_owbcc_home".

 

2.

Earlier, you were instructed to install Warehouse Builder in an Oracle home other than thedatabase Oracle home. To ensure access to the Control Center on the 10g R2 database, run the script UnifiedRepos/reset_owbcc_home.sql and pass in your Oracle home for Warehouse Builder. Run the script as a system privileged user such as SYS or SYSTEM. For example, type the following command at the command prompt:

SQL> @c:\oracle\OWB_home_11g\owb\UnifiedRepos\reset_owbcc_home;

Press Enter. You will be prompted for the full path of the Oracle home for the OWB Control Center install. First read the following note.

Note: In the following example, observe the required use of forward slashes "/", even when using a Windows machine. Also please note that your response with the full path must becase-sensitive! Be sure to type the drive letter in uppercase, such as "C:". And follow the case sensitivity of the folder names in the path of the Oracle home for OWB. Your response should appear somewhat like the following example:

C:/oracle/OWB_home_11g

Press Enter.

 

3.

If the strong password option is enabled on the Database, then after creating OWBSYS, you need to unlock the OWBSYS account and unexpire its password. Proceed with the 2nd and 3rd SQL Plus commands to unlock the OWBSYS account and assign a password also named OWBSYS:

alter user OWBSYS account unlock;

alter user OWBSYS identified by OWBSYS;

The requirements explained above are also mentioned in the OWB 11g Installation Guide documentation, in the section titled �Hosting the Repository on Oracle Database 10g Release 2�.

 

4.

For each Warehouse Builder client installation, enable access to the workspaces hosted on your 10gR2 database.

By default, Warehouse Builder 11g Release 1 (11.1) clients are set to connect to workspaces hosted on an Oracle 11g Database. That is, the Warehouse Builder repository is assumed to be hosted on an Oracle 11g Database.

To enable access to a 10gR2 repository and its workspaces, alter the file< owb home> /owb/bin/admin/Preference.properties on your machine. If the file does not exist, you can create it based on the example file available in the same directory, Preference.properties.tmp.

In the preference properties file, add a property REPOS_DB_VERSION_ALLOWED and set its value to:Oracle 10g, Oracle 11g. (This property may already be present.)

Back to Topic

Create the Design Center User and Repository

1.

When logging in to the OWB Design Center for the first time, you can create a Warehouse Builder user with which to log in. You create a new workspace, a new workspace user, and install the Warehouse Builder repository.

Select Start > Programs > {your Oracle - OWB11gR1clientHome} > Warehouse Builder > Design Center.

The Design Center Logon screen appears.

If the Workspace Management button is not visible, click Show Details. Click Workspace Management . The Welcome screen of the Repository Assistant Wizard appears. Click Next.

 

2.

In the Database Information window, enter the following values:

Host Namelocalhost <or your machine name>Port Number1521Oracle Service Nameorcl <or your database service name>

Click Next.

 

3.

In the Choose Operation window, click Manage Warehouse Builder workspaces.

Click Next.

4.

In the Choose Workspace Operations window, click Create a new Warehouse Builder workspace.

Click Next.

5.

In the New or Existing User window, click Create a workspace with a new user as workspace owner.

Click Next.

6.

In the DBA Information window, enter the following values:

User NamesystemPasswordoracle

Click Next.

7.

In the Workspace Owner (New) window, enter the following values:

Workspace Owner's User Namerep_ownerWorkspace Owner's Passwordrep_ownerWorkspace Owner's Password Confirmationrep_ownerWorkspace Namemy_workspace

Click Next.

8.

In the OWBSYS Information window, enter the following values:

User NameOWBSYSPasswordowbsys

Click Next.

9.

In the Select Tablespaces window, accept all of the defaults and clickNext.

10.

In the Select Languages window, accept the defaults and clickNext.

11.

The Workspace Users (Optional) window allows you to optionally select existing database users or create a new database user to serve as a workspace user.

In addition to the workspace owner that you specified earlier in this wizard, you will create a new database user to serve as a workspaceuser. Click Create New User.

12.

In the Create New Database User window, enter the following values:

User Namerep_userPasswordrep_userRe-enter Passwordrep_userDBA User NameSYSTEMDBA User Passwordoracle

Click OK.

13.

The Workspace Users (Optional) window reappears, with the new rep_user listed in theSelected panel.

Click Next.

14.In the Summary window, examine the information and click Finish.

15.An Installation Progress window appears. The installation of the OWBSYS user, repository owner will take several minutes.

16.

An Installation Successful window appears. Click OK . The Repository Assistant closes.

If the Design Center Logon window is still open, click Cancel to close it. In the Warehouse Builder Warning window, clickYes to quit Warehouse Builder. You will be logging in later in this lesson.

Back to Topic

Run the Setup Scripts

1.

Before you can begin working with the lessons, you need to run the scripts from theowbdemo_files.zip file that you downloaded.

Note: The following directions and screenshots assume use of the D: drive. If you are using the C: drive, substitute accordingly.

Log in to SQL*Plus as sys and run the create_users.sql script that you downloaded into the D:\newowbdemo folder.

To run the script, enter the following command at the SQL> prompt:

@d:\newowbdemo\create_users.sql;

Exit SQL*Plus.

Note: The create_users.sql script will create two users: xsales and eul_from_owb. If you plan to work optionally with xsales source data, as mentioned earlier in this lesson, you need to import the xsales.dmp file that you downloaded and extracted from the xsales.zip file.

You Can Optionally Import xsales Data

Note: The step of importing the xsales.dmp file is optional and only required if you want to use the xsales source data to explore your own examples.

If you wish to import the optional dmp file, enter the following command at the DOS command prompt:

<database ORACLE_HOME>\bin\imp xsales/xsales@<orcl or your database service name> file=<PATH>\xsales.dmp full=y

Substitute <ORACLE_HOME> with your Oracle database home path and <PATH> with the location where you extracted the xsales.dmp file.

 

2.

Now you will run a set of tcl scripts to create a predefined OWB project. First, you need to edit theowbdemoinit.tcl script that defines and sets variables used by the other tcl scripts. Theowbdemoinit.tcl script is provided to you with the following variables;edit these variables to match the values in your computer's environment:

set owbclient rep_owner
set owbclientpwd rep_owner
set sysuser sys
set syspwd oracle
set host localhost
set port 1521
set service orcl
set project OWB_DEMO
set sourcedir d:/newowbdemo
set dataspace USERS
set indexspace USERS
set tempspace TEMP
set snapspace USERS
set sqlpath d:/oracle/10.2.0.3/db_1/BIN
set sid orcl
set workspaceOwner rep_owner
set workspaceName my_workspace

You need to set all the values shown in bold above, as per your database and Warehouse Builder setup. The "set sqplpath" statement should point to the BIN directory of your OracleDATABASE home.

Note: The scripts are written to run on default tablespaces. It is advised that you use default tablespaces for this hands-on activity. If you intend to run the scripts on your own defined tablespaces you need to set them in this script as well as in multiple other scripts.

 

3.

To run the tcl scripts, start the OMB Plus environment. There are two ways to start OMB Plus. Within the Design Center, you could selectOMB Plus from the Window menu. However, for this example, start OMB Plus the other way: selectStart > Programs > [Oracle - OWB11gR1clientHome] > Warehouse Builder > OMB Plus.

On the OMB+> prompt, enter the cd command as shown below to change the working directory to the folder where you have unzipped the setup tcl scripts: (it is case sensitive):

cd d:\\newowbdemo\\

After this command, enter the source commands on the OMB+> prompt as shown below. The first source command executes the owbdemoinit.tcl script that defines and sets variables used by the other tcl scripts.The second source command executes the loadall.tcl script that in turn runs all the other tcl scripts in the desired sequence.

source owbdemoinit.tcl

Press Enter.

source loadall.tcl

Press Enter.

The scripts should finish with the statement "Disconnected" and return the OMB+> prompt. Exit the OMB Plus environment.

The scripts have created a partially defined OWB project, OWB_DEMO, in which you will do your work.

 

Back to Topic

Introducing the OWB Program Group Components

You will now examine the components that constitute the OWB installation.

1.

Select Start > Programs > [Oracle - OWB11gR1clientHome] > Warehouse Builder > Administration. This displays the range of components that make up the OWB product.

 

2.

Examine the menu choices:

Administration

Repository Assistant

Facility for creating and managing OWB repositories and users

 

Start/Stop Control Center Service

Facilities for starting and stopping the OWB Control Center

Design Center

The main OWB client in which you design sources, targets, ETL mapping and transformations

DocumentationThis will redirect you to Oracle Warehouse Builder documentation on Oracle Technology Network

OMB Plus

The scripting tool you use to execute tcl scripts

Repository Browser

The reporting environment for examining repository design and control center metadata.

Back to Topic List

Logging In to the Design Center

To start the OWB Design Center, perform the following steps:

1.

Select Start > Programs > [Oracle - OWBclientHome] > Warehouse Builder > Design Center.

The Design Center Logon window appears. Enter rep_owner as username and password. By default,Connection details option is selected.

If you cannot see Connection details option, click Show Details.

Enter host as localhost or <name of your computer>, port as 1521, and service name as orcl or <your database service name>. ClickOK.

 

2.

The Design Center appears. The Design Center is divided into three panels: Project Explorer, Connection Explorer, and Global Explorer.

The Project Explorer on the left side contains two projects: MY_PROJECT, an empty project created when OWB is installed, andOWB_DEMO, a partially defined project that you created with the setup scripts.

 

3.

A project is a container to manage your design work. After you create a project, you can create all the other Warehouse Builder objects.

Warehouse Builder contains wizards, object editors, property sheets, and object finding tools that assist you in designing your business intelligence system.

In the Project Explorer panel's navigation tree, expand the OWB_DEMO project. Various object types appear in the tree: Databases, Files, Applications, Data Profiles, and so on.

Expand the Databases node, and you see various object types that can be included in design of your warehouse: Oracle databases, non-Oracle databases, and transportable modules.

Expand the Oracle node, and you see two modules predefined by the setup scripts:SALES_WH and XSALES. Modules are logical groupings of source or target definitions.

 

4.

Expand the SALES_WH module. The various object types appear in the tree: mappings, transformations, dimensions, cubes, tables, and so on. Expand some of these nodes to see the objects that have been predefined by the setup scripts.

 

Back to Topic List

Summary

In this module, you should have learned how to:

Download and execute the setup files for the remaining Warehouse Builder lessonsCreate the OWB Design Center User and install the RepositoryLog in to the OWB Design Center and open an OWB project

Back to Topic List

Place the cursor over this icon to hide all screenshots.