Setting Up the Oracle Warehouse Builder 11g

Release 2 Tutorial Environment

Purpose

This tutorial covers the setup and configuration steps you must perform to set up the Oracle

Warehouse Builder 11g Release 2 tutorial environment. In this tutorial, you also create different

workspace owners to login to the OWB Design Client.

Time to Complete

Approximately 40 minutes

Overview

In this tutorial, 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 three

different workspace and workspace owners for logging in to the Oracle Warehouse Builder

Design Client. Using the Design Client, you then import predefined OWB projects.

In this tutorial, you define three different OWB Project environments for the following set of

tutorials. This tutorial is a prerequisite for the following tutorials:

1 . Improved User Interface, Usability, and Productivity With OWB 11g R2

2 . Handling Flat File and COBOL Copybook Sources in Mappings

3 . Using Data Transformation Operators with Source and Target Operators

4 . Working with Pluggable Mappings

5 . Examining Source Data Using Data Profiling

Note: The tutorials 2, 3, and 4 listed above use the same OWB project..

Prerequisites

Before starting this tutorial, you should:

1 . Have access to or have Installed Oracle Database 11g Release 2 on Linux platform.

Note: In this tutorial, you use the OWB that is installed with the Oracle Database 11g Release 2.

In Oracle Database 11g Release 2, OWB is integrated and is installed in the Oracle Database

home.

If you want to use the latest OWB 11g Release 2 with an older version of the database such as

the Oracle Database 11g Release 1 or Oracle Database 10g Release 2, you need to install the

standalone OWB software on the client and perform certain extra steps to create/upgrade the

OWBSYS repository on the database.

Downloading the Setup Files

To download the setup files, perform the following steps:

1 . Open folder owb_setup

Note that the owb_setup folder contains three .mdl files and a folder, dp_setup_files. In the

dp_setup_files folder, there are files required to setup the environment for the "Examining

Source Data Using Data Profiling" tutorial.

2 . Optionally, for easy access to the .mdl files when you import them, you may want to copy all the

three .mdl files to the [ORACLE_HOME]/owb/mdl folder.

Note: For this tutorial, the ORACLE_HOME is assumed to be

/u01/app/oracle/product/11.2.0/dbhome_1

3 . Create a desktop launcher for the OWB Design Center. Right-click on your Linux desktop and

select Create Launcher. Enter the launcher name as OWB Design Center. Browse to the

[ORACLE_HOME]/owb/bin folder and select owb.sh. Click OK.

4 . Browse to the [ORACLE_HOME]/jlib/ folder and delete the oracle_ice.jar file.

Note: Start Page bug: In the Oracle Warehouse Builder integrated installation that is included

with Oracle Database 11.2, links from the new Start Page included in the Design Center do not

function properly. This problem does not affect the Oracle Warehouse Builder standalone

installation. The online help bug and its workaround is mentioned in the OWB 11.2 Release

Notes. Deleting the oracle_ice.jar file will resolve the issue and you will be able to browse the

links in the Start page.

5 . Note: If you are using an older version of the database, you may skip this step for now.

Refer to the topic, "Using Standalone OWB 11.2 with Oracle Database 11g Release 1 or Oracle

Database 10g Release 2" for further setup instructions.

Unlock the OWBSYS user. Login as a sys as sysdba user. Open a terminal window and

enter the following commands:

sqlplus sys/[password] as sysdba

alter user owbsys identified by owbsys account unlock;

Note: If you are using OWB server installation that is integrated with the Oracle Database 11.2,

skip the following topic, "Using Standalone OWB 11.2 with Oracle Database 11g Release 1 or

Oracle Database 10g Release 2" and resume from the topic, "Creating Workspace and

Workspace Owners".

Using Standalone OWB 11.2 with Oracle Database 11g

Release 1 or Oracle Database 10g Release 2

If you want to use the latest Warehouse Builder software with an older version of the database,

you need to perform some extra steps to setup the OWB environment.

It is necessary to download the Oracle Warehouse Builder 11g Release 2 (11.2.0.1.0) Standalone

Software if you have any of the following needs:

You are installing OWB 11.2 and hosting an OWB Repository on one of: Oracle

Database 10g Release 1, Oracle Database 10g Release 2, or Oracle Database 11g Release

1.

You are running the OWB 11.2 Design Client on a developer's computer.

You are installing OWB 11.2 on a computer that runs the Control Center Agent.

Note: OWB 11.2 is architected with an assumption that it is used with Oracle Database 11g

Release 2. This tight integration includes preseeding the OWBSYS schema in the default

database, and placing OWB in the same Oracle home as the database.

To use OWB 11.2 with Database 10g R2 or 11g R1, you must perform the following simple

steps.

Run a SQL script to create OWBSYS.

Run a SQL script to identify OWB home to Database 10g R2 or

11g R1.

Unlock OWBSYS and OWBSYS_AUDIT.

1 . First, run the cat_owb.sql to create the OWBSYS schema.

If using the Database 10g Release 2, you need to run a SQL script to create the OWBSYS

repository schema.

This script, <your OWB home>/OWB/UnifiedRepos/cat_owb.sql, installs the OWBSYS

database user that is needed by OWB 11.2. (OWBSYS is automatically created during the

installation of 11.2.)

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

@[OWB_HOME]/owb/UnifiedRepos/cat_owb.sql;

You will be prompted for a tablespace for the OWBSYS user. For this training, it is

recommended that you specify the USERS tablespace. Enter users.

IMPORTANT: If you are using Oracle Database 11g Release 1, it already contains the

OWBSYS schema which is the Oracle Warehouse Builder 11g R1 repository. You must clean it

before setting up the 11g R2 repository. To clean the OWBSYS repository, you must run

clean_owbsys.sql, followed by cat_owb.sql. The clean_owbsys.sql script is located in

[OWB_HOME]/owb/UnifiedRepos. The clean_owbsys.sql script drops the contents of any

existing OWBSYS schema installations, but leaves the schema otherwise intact. So, if it is

Oracle Database 11g Release 1, perform this step as shown below:

sqlplus sys/[password] as sysdba

@[OWB_HOME]/owb/UnifiedRepos/clean_owbsys.sql

@[OWB_HOME]/owb/UnifiedRepos/cat_owb.sql;

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 . Run a SQL script to identify OWB home to Database 10g R2 or 11g R1.

When you install the standalone OWB 11.2, it installs in a separate OWB home.

To ensure access to the Control Center on the database, run the reset_owbcc_home.sql script and

pass in the path to the OWB home.

Run the script as a system-privileged user such as SYS or SYSTEM.

Enter the following command at the command prompt, substituting in your own actual OWB

path:

@[OWB_HOME]/owb/UnifiedRepos/reset_owbcc_home <OWB_HOME>;

Note: If you do not provide the <OWB_HOME> path on the command line, you are prompted

for the full path of the OWB home for the OWB Control Center installation.

3 . Finally, unlock OWBSYS and OWBSYS_AUDIT by running the following commands:

alter user OWBSYS identified by <password> account unlock;

alter user OWBSYS_AUDIT identified by <password> account unlock;

Creating Workspaces and Workspace Owners

In this topic, you run the Repository Assistant to create three different workspace owners for

three different sets of the tutorials. The following table shows the workspace owners you create

for the respective tutorials. If you don't intend to do any one of the tutorials, you may skip the

creation of the particular workspace owner and the steps related with that workspace owner.

Workspace Owner

Names

Tutorials

eup_owner Improved User Interface, Usability, and Productivity With OWB 11g

R2

etl_owner Handling Flat File and COBOL Copybook Sources in Mappings

Using Data Transformation Operators with Source and Target

Operators

Working with Pluggable Mappings

dp_owner Examining Source Data Using Data Profiling

1 . Browse to the [ORACLE_HOME]/owb/bin/unix folder and double-click reposinst.sh to start

the Repository Assistant. Click Run to launch the Repository Assistant.

Click Next on the Welcome Page.

2 . In the Database Information window, enter the following values:

Host Name localhost <or your machine name>

Port Number 1521

Oracle Service Name <your database SID>

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 Name system

Password <your system user password>

Click Next.

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

Workspace Owner's User Name eup_owner

Workspace Owner's Password eup_owner

Workspace Owner's Password Confirmation eup_owner

Workspace Name my_workspace1

Click Next.

8 . In the OWBSYS Information window, enter the following values: (this will not appear in the

subsequent runs of the Repository Assistant)

User Name OWBSYS

Password owbsys

Click Next.

9 . In the Select Tablespaces window, accept all of the defaults and click Next. (This will not

appear in the subsequent runs of the Repository Assistant).

10 . In the Select Languages window, accept the defaults and click Next. (This will not appear in the

subsequent runs of the Repository Assistant)

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.

You will not create any additional users; you will log in using the workspace owner that you

specified earlier in this wizard. Click Next.

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

You see a progress bar. The seeding of the OWBSYS user and the installation of the workspace

owner will take several minutes (it takes more time in the first run).

An Installation Successful window is displayed. Click OK . The Repository Assistant closes.

13 . Create another workspace owner, etl_owner.

Note: Refer to the table at the beginning of this topic to know if you need to create this

workspace owner in relation to the tutorials that depend on it.

Repeat steps 1 to 6. In the Workspace Owner (New) window, enter the following values:

Workspace Owner's User Name etl_owner

Workspace Owner's Password etl_owner

Workspace Owner's Password Confirmation etl_owner

Workspace Name my_workspace2

Click Next to remaining wizard pages. Click Finish.

An Installation Successful window is displayed. Click OK. The Repository Assistant closes.

14 . Create the third and the last workspace owner, dp_owner.

Note: Again, refer to the table at the beginning of this topic to know if you need to create this

workspace owner in relation to the tutorials that depend on it. If you don't intend to do the

"Examining Source Data Using Data Profiling" tutorial, you can skip this step.

Repeat steps 1 to 6. In the Workspace Owner (New) window, enter the following values:

Workspace Owner's User Name dp_owner

Workspace Owner's Password dp_owner

Workspace Owner's Password Confirmation dp_owner

Workspace Name my_workspace3

Click Next to remaining wizard pages. Click Finish.

An Installation Successful window is displayed. Click OK . The Repository Assistant closes.

Logging In and Importing the .Mdl Files

In this topic, you log in to the Warehouse Builder Design Center using the workspace owner

credentials and then you import pre-populated projects by using the .mdl files. Perform the

following steps:

1 . Log in to the Design Center. Double-click the OWB Design Center launcher on your desktop.

Note: If you get a window regarding migrating settings, click No. If you did not create a desktop

launcher, then browse to the [ORACLE_HOME]/owb/bin folder and double-click owb.sh.

In the Logon dialog box, enter eup_owner/eup_owner as the username/password. Also, enter

the connection details as shown in the screenshot below. Click OK.

2 . The Design Center opens. From the File menu, select Import > Warehouse Builder Metadata.

3 . The Metadata Import dialog opens. Click Browse. The Open dialog is displayed. Select the

gui_usability_productivity.mdl file and click Open. The Metadata Import dialog is displayed.

Accept all of the defaults and click Import.

The Metadata Import Progress window is displayed.

When the import finishes, click OK. You should now see a project named BIDEMO_112 in the

upper left corner of the OWB window.

From the File menu, click Exit. (If prompted to save or revert your work, save it.) In the Exit

Confirmation dialog, click Yes.

4 . Assuming you created the etl_owner workspace owner(with an intention to work on the related

tutorials), log in to the Design Center using etl_owner/etl_owner username/password.

If you entered the connection details earlier, you need not enter it again. Click OK.

5 . The Design Center opens. From the File menu, select Import > Warehouse Builder Metadata.

The Metadata Import dialog opens. Click Browse. The Open dialog is displayed. Select the

etl_project.mdl file and click Open. The Metadata Import dialog is displayed. Accept all of the

defaults and click Import.

The Metadata Import Progress window is displayed. When the import finishes, click OK.

You should now see a project named ETL_PROJECT in the upper left corner of the OWB

window.

From the File menu, click Exit. (If prompted to save or revert your work, save it.) In the Exit

Confirmation dialog, click Yes.

Setting Up the Data Source and the Predefined Project for

the Data Profiling Tutorial

If you plan to do the "Examining Source Data Using Data Profiling" tutorial, perform the

following steps to setup the data source and the OWB pre-populated project:

1 . In SQL*Plus, connect as sys as sysdba user. Run create_user.sql. It creates a user, dq_src and

grants it the required privileges.

Note: The create_user.sql file is located in the ../owb_setup/dp_setup_files folder.

Open a Linux terminal window. Enter the following commands:

sqlplus sys/[password] as sysdba

@/home/oracle/owb_setup/dp_setup_files/create_user.sql

Note: Substitute the sys user password and the exact location of the create_user.sql script.

2 . Run unlock.sql to unlock the sample schemas. Enter the following command:

@/home/oracle/owb_setup/dp_setup_files/unlock.sql

3 . Open a terminal. Change to the folder where you saved the dq_src.dmp file.

Import the dq_src.dmp file using the following command on the terminal:

cd <folder location>/owb_setup/dp_setup_files

imp dq_src/dq_src@orcl file=dq_src.dmp full=true

Note: Substitute the <folder_location> with the correct location of the dp_setup_files folder.

4 . Assuming you created the dp_owner workspace owner(with an intention to work on the related

Data Profiling tutorial), log in to the Design Center using dp_owner/dp_owner

username/password.

If you entered the connection details earlier, you need not enter it again. Click OK.

Note the new Recent Logon field. Because you have logged in already with two different user

credentials, it retains them. Ignore it for now and log in as a new user, dp_owner.

5 . The Design Center opens. From the File menu, select Import > Warehouse Builder Metadata.

The Metadata Import dialog opens. Click Browse. The Open dialog is displayed. Select the

dp_handson.mdl file and click Open. The Metadata Import dialog is displayed. Accept all of

the defaults and click Import.

The Metadata Import Progress window is displayed. When the import finishes, click OK.

You should see a project named HANDSON in the upper left corner of the OWB window. Do

not exit the Design center yet.

6 . Create and register a target schema, dp_tgt.

Navigate to the Globals Navigator panel. Expand Security node. Right-click Users and select

New User. Click Next on the welcome page.

7 . Click Create DB User. In the dialog box, enter the details as shown in the screenshot.

Username/password: dp_tgt/dp_tgt. Click OK. Click Next.

8 . In the Check to create a location page, deselect the "To Create a location" option because this

location is already created.

Click Next. Click Finish.

9 . In the Design Center, from the Tools menu, select Preferences. In the Preferences window,

expand the OWB node and select Security Parameters. Check the Persist Location Password

in Metadata option.

Note: This persists the passwords that you enter for the locations. You need not enter the

password each time sources or targets locations are accessed.

Click OK.

10

.

Setup the locations. Navigate to the Locations Navigator (just besides the Projects Navigator).

Expand Locations > Databases > Oracle. Double-click DP_TGT_LOCATION (or right-click

it and select Open).

Enter dp_tgt as the password. Verify the other existing connection details. Check that the service

name is correct.

Change the version to 11.2.

Click Test Connection to test. Click OK.

Click OK again to close the Edit Location dialog box.

Do the same for DQ_SRC_LOCATION and OE_LOCATION.

Click Save ALL on the toolbar to save the changes. Click Yes in the confirmation dialog box.

Exit the Design Center.

11

.

You need to grant select privileges on the source tables to the dp_tgt user. Log in as sys user and

run grant_priv.sql located in the ../owb_setup/dp_setup_files folder.

Quit SQL*Plus.

Summary

After completing all the steps described in this tutorial, you will be able to run through the

following Oracle By Example (OBE) tutorials:

Improved User Interface, Usability, and Productivity With OWB 11g R2

Handling Flat File and COBOL Copybook Sources in Mappings

Using Data Transformation Operators with Source and Target Operators

Working with Pluggable Mappings

Examining Source Data Using Data Profiling

In this tutorial, you have learned how to:

Download the setup files

Create workspace and workspace owners using the Repository Assistant wizard

Setup the Warehouse Builder project environment for the tutorials

Import source metadata using the .mdl files

Resources

OWB on OTN

OWB Documentation

Oracle University OWB Courses:

Data Integration and ETL with Oracle Warehouse Builder: Part 1

Data Integration and ETL with Oracle Warehouse Builder: Part 2