Starting with Oracle Warehouse Builder 10g Release 2

In this lesson, you use the setup files to configure the project environment for Oracle Warehouse Builder 10g Release 2 (OWB 10gR2). You then create a Warehouse Builder user and log in.

Approximately 15 minutes

Topics

This lesson discusses the following:

Setting up the Project Environment

Introducing the OWB Program Group Components
Logging in to the Design Center

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

 

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

1.

Completed the installation of Oracle Database10g (Enterprise Edition). It is suggested that you create a database named orcl for this course. Otherwise, you need to substitute the Oracle Service Name of your database wherever you see orcl mentioned in this course.

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

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.

 

2.

Completed the installation of Oracle Warehouse Builder 10g Release 2

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, iInteractive lineage and impact Analysis and user-defined objects.

For more information, read the Packaging and Licensing section of OWB FAQ on OTN.

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 10g Release 2: Documentation Library
Oracle Technology Network (OTN): OWB Collateral Library

Oracle University (5 day Instructor Led course): Oracle Warehouse Builder 10g: Implementation Part 1 (3 day) and Oracle 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

Setting Up the Project Environment

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

Download the Setup Scripts
Create the Design Center User and Repository

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 preferably to your computer's d:\ drive, so that your file locations 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 the d:\ or other directory location where you place these files.

 

3.

You can optionally download the xsales.zip file from here.

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. This is made available to you in case you want to work on XSALES tables data for your own hands-on examples.

 

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 user, a new, highly privileged repository owner, and install the Warehouse Builder repository.

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

The Design Center Logon screen appears.

Move your mouse over this icon to see the image

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

Move your mouse over this icon to see the image

 

2.

In the Install Type window, choose Basic Install. Click Next.

Move your mouse over this icon to see the image


3.

In the Repository User and Connection Information window, create a user named rep_user, by entering the following values:

Repository User Name rep_user
Repository User Password rep_user
SYSDBA User Name sys
SYSDBA Password <your SYSDBA password>
Host Name localhost <or your machine name>
Port Number 1521
Oracle Service Name orcl <or your database service name>

Move your mouse over this icon to see the image

Click Next.

 

4.

In the Password Confirmation window, re-enter the password rep_user, and click OK.

Move your mouse over this icon to see the image

 

5.

In the Repository Owner Information window, if no existing owner is detected, Warehouse Builder offers a default owner name (such as OWB_10_1_2_3_31) on the basis of the Warehouse Builder version installed).

Instead, you can also provide your own owner name. The repository owner is a highly privileged Warehouse Builder user with access to additional security features. Enter rep_owner as username and password. Click Next.

Move your mouse over this icon to see the image

Note: If you have already created another repository owner before this course, click Cancel to exit the Repository Assistant and create rep_owner now by invoking the Repository Assistant as follows:

  1. Select Start > Programs > {your Oracle - OWBclientHome}> Warehouse Builder > Administration > Repository Assistant. Click Next on the Welcome Page.
  2. Choose Advanced Setup and follow the prompts to create a new repository owner, naming it rep_owner. Then rerun steps 1 through 5.

The scripts in the following section assume the existence of rep_owner.

In the Password Confirmation window, re-enter the password rep_owner and click OK.

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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, click Yes 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 the owbdemo_files.zip file that you downloaded.

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;

Move your mouse over this icon to see the image

Exit SQL*Plus.

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

To import the dmp file, enter the following command at the DOS command prompt:

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

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

Note that the step of importing the xsales.dmp file is optional and only required of you want to use the xsales source data for your own examples.

 

2.

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

set owbclient rep_owner
set owbclientpwd rep_owner
set sysuser sys
set syspwd sys
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/db_1/BIN
set sid orcl

You need to set all the values shown in bold above, as per your database and Warehouse Builder setup.

Note: The scripts are written to run on default tablespaces. It is advised that you use default tablespaces for this hands-on. If you intend to run them 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, select OMB Plus from the Window menu. Or select Start > Programs > [Oracle - OWB10gR2clientHome] > Warehouse Builder > OMB Plus.

On the OMB+> prompt, enter the cd command as shown below: (it is case sensitive):

cd d:\\newowbdemo\\

The cd command changes the working directory to the folder where you have unzipped the setup tcl scripts. After this command, enter the source command on the OMB+> prompt as shown below. The source command executes the loadall.tcl script that in turn runs all the other tcl scripts in desired sequence.

source loadall.tcl

Move your mouse over this icon to see the image

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

Introduction to the OWB Program Group Components

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

1.

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

Move your mouse over this icon to see the image

 

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

 

Start/Stop OWB Browser Listener

Facilities for starting and stopping the Repository Browser Listener for standalone local browser

Design Center

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

Documentation This 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>. Click OK.

Move your mouse over this icon to see the image

 

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, and OWB_DEMO, a partially defined project that you created with the setup scripts.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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 lessons
Create the OWB Design Center User and install the Repository
Log in to the OWB Design Center and open an OWB project

Back to Topic List