Setting up the Oracle Warehouse Builder Project

Purpose

In this tutorial, you setup and configure the project environment for Oracle Warehouse Builder 10g Release 2. You create a Warehouse Builder repository and log in to the Warehouse Builder Design Center.

Overview

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

This tutorial is a pre-requisite to the following tutorials

Prerequisites

Before starting this tutorial, you should:

1.	Have completed the installation of Oracle Database10g (Enterprise Edition). It is suggested that you set your Oracle home location to C:\oracle\product\10.2.0\db_1. 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. The SYSDBA username/password used throughout the course is sys/oracle. You may replace the password with your SYS account password wherever required. During the installation of the database, remember to include the sample schemas in the install and also towards the end of the install, ensure you unlock the three sample schemas: OE, SH and HR. Note: You may go through the tutorial, Installing Oracle Database 10g on Windows, to get step by step instructions on installing Oracle Database10g (Enterprise Edition). This hands-on has been tested using the Oracle Warehouse Builder 10g Release 2 on an Oracle Database 10gRelease 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.	Have completed the installation of Oracle Warehouse Builder 10g Release 2. You should install Warehouse Builder in a separate home location and not in the Oracle database home. It is suggested that you set the Warehouse Builder home name to owb10gr2 and location toc:\oracle\product\10.2.0\owb10gr2.
3.	Note: This is an optional prerequisite and only required if you plan to work on the tutorial, "Extract, transform and Load". Have installed Workflow 2.6.4. With the Oracle Database 10g Release 2 instance, you need to install Oracle Workflow 2.6.4. Oracle Workflow can be installed from the Oracle Database 10g Companion CD. During installation from the companion CD, in Select a Product to Install wizard page, make sure you choose the second option: "Oracle Database 10g Products 10.2.0.1" to install Oracle Workflow. Also, you need to install this in your Oracle Database home. Note: With Oracle Database 10g Release 1, you need to install Workflow 2.6.3. After installing Oracle Workflow in your Oracle home, run the Workflow Configuration Assistant to create theowf_mgr Workflow schema. When you run the Workflow Configuration Assistant, you need to specify the database connect string in the TNS Connect Descriptor box. Enter the following values: Accept the default for Install Option Workflow Account: (Accept the default) owf_mgr Workflow Password: owf_mgr SYS Password: <oracle or enter your SYS account password> TNS Connect Descriptor: localhost:1521:orcl Click Submit.
4.	Have downloaded the owbdemo_files.zip file by right-clicking here and selecting Save Target As from the pop-up menu. Direct the download to c:\ root directory on your computer's hard drive. Extract the files from the zip file in c:\ root path.

Preparing the Database Instance

To prepare your database instance for this OBE tutorial, you need to perform the following steps:

Run the SQL Scripts

Before you can begin working with the tutorials, you need to run the create_user.sql script and unlock.sql script from the owbdemo_files.zip file that you downloaded.

To run the SQL scripts, perform the following steps:

1.

The create_user.sql script creates a DQ_SRC user and grants connect, resource and create any view privileges to the user. In the next topic, you import data through a dq_src.dmp file into this dq_src schema/user. Log in to SQL*Plus as sys and run the create_user.sql script that you downloaded into the c:\owbdemo_files folder. To run the script, enter the following command at the SQL> prompt: @c:\owbdemo_files\create_user.sql

2

Incase you did not unlock the sample schema accounts as mentioned in the prerequisite 1 in this tutorial, run the unlock.sql script to ensure that all the required accounts are unlocked. This script also unlocks the Oracle Workflow user account, owf_mgr. If you did not install and configure Oracle Workflow, edit the unlock.sql script and remove the alter command for owf_mgr before running the script. Assuming you are still logged in as sys, to run the unlock.sql script, enter the following command at the SQL> prompt: @c:\owbdemo_files\unlock.sql Enter quit at the SQL> prompt to exit SQL Plus.

Import the dq_src.dmp File

To import the dq_src.dmp file, perform the following steps:

1.

To import the dmp file, enter the following command at the DOS command prompt: <ORACLE_HOME>\db_1\bin\imp dq_src/dq_src@<ORCL or your database service name> file=<PATH>\dq_src.dmp full=y Substitute <ORACLE_HOME> with your Oracle home location and <PATH> with the location where you extracted the dq_src.dmp file. Also, ensure you enter the correct database service name.

Setting Up the Project Environment

To setup and configure the Oracle Warehouse Builder project environment, you need to perform the following steps:

Create a Repository Using the Repository Assistant

Using the Repository Assistant, you can define an Oracle database schema as a Warehouse Builder repository.

In Oracle Warehouse Builder 10g Release 2, you can have a single, unified repository. The Repository Assistant guides you through defining a unified repository with one repository owner and a separate repository user enabled as a deployment target.

In this topic, you create a unified repository using the Repository Assistant.

1.	Select Start > Programs > {your Oracle - OWB10gr2clientHome} > Warehouse Builder > Administration > Repository Assistant. The Welcome Page displays. Click Next on the Welcome Page.
2.	In the Install Type window, choose Advanced Setup. Click Next.Note: You could also choose Basic Install option. This option creates two users: a repository user that is enabled as a target for the ETL processes you design and a repository owner. Now, you look at the Advanced setup option. For the Advanced setup option, you need SYSDBA privileges.
3.	In the Connection Information window, enter oracle or <your SYS account password> as the SYSDBA password and the Oracle service name as orcl or the database service name you are using. Click Next.
4.	In the Choose Operation window, select the Manage a Warehouse Builder repository owner option. Click Next. Note: Observe the Reset OWBRT_SYS's Password (Optional) button. The OWBRT_SYS user stores system information regarding the runtime environment such as version information. This schema is managed by Warehouse Builder and upon installation of the repository, you can set the owbrt_sys password to conform to your security policy. Also note, next time you run the Repository Assistant on the same installation to create another repository, you will not get this button on the window.
5.	In the Manage Repository Owner window, select Create a new Warehouse Builder repository owner. Click Next.
6.	In the Repository Owner information window, enter owb/owb as the Repository owner username/password. ClickNext.
7.	In the Password Confirmation window, re-enter the password owb. Click OK.
8.	In the Select Tablespaces window, you can specify the tablespaces you are using for data, indexes, temporary data, and snapshots. For this course, you use the default tablespaces. Click Next.
9.	In the Select Languages window, accept the default. Click Next.
10.	In the Repository Users window, you can create a repository user or select an existing database user and register it as a Warehouse Builder user. This user has privileges to work in the design center as well as be able to deploy and execute objects through the Control Center. This user can also be enabled as a target schema. For now, because you don't intend to use a repository user, click Next to move to the next window. In the Summary window, verify the details and click Finish. An Installation Progress window displays. The installation of the repository owner takes several minutes. An Installation Successful window displays. Click OK. The Repository Assistant closes.

Import the Start.mdl File

You can use the Warehouse Builder Design Center to import metadata. To import objects from the start.mdl file, perform the following steps:

1.	First, you need to log in to the Design Center. Select Start > Programs > {your Oracle - OWB10gr2clientHome} > Warehouse Builder > Design Center. The Design Center Logon window displays. Enter owb as username and password. If not showing the connection details, click Show Details. Select the Connection details option, if not selected already, and specify the connection details. Enter host as localhost, port as 1521, and service name as orcl or <your database service name>. Click OK.
2.	The Design Center displays.Note: The Design Center is the main client application of Warehouse Builder. It provides easy-to-use graphical interfaces that enables you to design, deploy, create, and monitor business intelligence systems. The Design Center is divided into three panels: Project Explorer, Connection Explorer, and Global Explorer, as shown in the screenshot below. The Project Explorer on the left contains one project: MY_PROJECT, an empty project created when Warehouse Builder is installed.
3.	To start the import mdl process, from the Design menu, select Import > Warehouse Builder Metadata. The Metadata Import window displays. Note: If you had made changes to the repository metadata before running the import utility, the Warehouse Builder Warning dialog is displayed. Click Save to save changes or Rollback to revert to the previously saved version. If you have not made any changes to the repository metadata after last saving the design, Warehouse Builder displays the Metadata Import dialog.
4.	In the Metadata Import window, click Browse to specify the filename for the metadata file. In the Open dialog box, select the start.mdl file from c:\owbdemo_files\ or from the location you saved the file. Note or modify the path of the Log file in case you need to review the log later.Note: Whenever you export or import repository metadata, the MDL writes diagnostic and statistical information to a log file. You can specify the location of the log file when you invoke MDL using the Metadata Loader utility. The log file enables you to monitor and troubleshoot export and import activities in detail. The import log file contains information about the object types imported, and displays the total number of objects added, replaced, skipped, or deleted. Accept the default for the Object Selection, Import Option, and Match By options. Click Import.
5.	After the import is completed, click Show Details. It shows the message log of the metadata imported. Scroll in the window to verify that there is no error reported. Click Close.
6.	The migrated project HANDSON is added in the Project Explorer panel. Expand Handson > Databases > Oracleand examine the two imported modules, DQ_TGT and DP_TGT. From Design menu, select Save All and click Yes in the Warehouse Builder warning dialog box to commit the changes.

Create Target Users

You noticed the two modules, DQ_TGT and DP_TGT inside the HANDSON project that you imported. Every target module must be mapped to a target user schema. This target schema physically stores your target objects on deployment. Also, each target module references a target schema by an assigned location.

To create DQ_TGT and DP_TGT target schema users, perform the following steps:

1.	In the Global Explorer panel, expand the Security node. Right-click the Users node and select New. Note: You must save or revert your changes before creating a new user. To save previous changes, select Save All from the Design menu. The Register Warehouse Builder Users Wizard is launched. Click Next on the Welcome page.
2.	On the Select DB user to register page, you can select from the available list of database users or create a new one that is automatically registered as Warehouse Builder target user.Click on Create DB User to create a new target user.
3.	To be able to create a new target user, you need to have SYSDBA privileges. In the Create Database User dialog box, enter sys as username and oracle or <your sys account password> as the password.Enter DQ_TGT/DQ_TGT as the username/password for the new DB user. Accept the defaults for the tablespace properties. Click OK.
4.	To create another target schema user, DP_TGT, on the Select DB user to register page, click Create DB Useragain. In the Create Database User dialog box, enter sys as username and oracle or <your sys account password> as the password. Enter DP_TGT/DP_TGT as the username/password for the new DB user. Accept the defaults for the tablespace properties. Click OK. Observe both DQ_TGT and DP_TGT has been added to the Selected Users list. Click Next.
5.	On the Check user as target schema page, ensure that Used as target schema is selected for both DQ_TGT and DP_TGT user. Click Next. The Target User Password dialog box launches. Re-enter the same password for DQ_TGT and DP_TGT users that you specified in the Create Database User dialog box for new DB user. Click OK. On the Summary page, examine the details and click Finish. The Register Users Progress window shows the task progress.
6.	Examine the Users node. You find the users, DQ_TGT and DP_TGT added to the list of existing target users. Also, in the Connection Explorer panel, expand the Oracle Locations node and note that new locations, DQ_TGT_LOCATION and DP_TGT_LOCATION have been added. Note: Locations define information about the database schema or target tool where you deploy objects. Locations are specific to a type of module, such as Oracle Database, SAP, or flat file. From Design menu, select Save All. In Warehouse Builder warning dialog box , click Yes to commit the changes.

Register the Oracle Workflow user

Note: This is an optional step and only required if you installed and configured Oracle Workflow with an intention to complete "Extracting, Transforming and Loading Data" tutorial.

You need to grant specific roles to the Oracle Workflow(OWF) user, owf_mgr, for it to have the privileges to execute a process flow in the Control Center.

Due to the security model, you do not have to embed the password for the Control Center in database-links owned by the OWF user. The Control Center user is highly privileged and its password is tightly controlled.

To register the OWF user, you use the Register Warehouse Builder Users wizard from the Security > Users node in Global Explorer panel. To get access to the security node, ensure you log in to the design center as the repository owner.

To register the owf_mgr user, perform the following steps:

1.	In the Global Explorer panel, expand the Security node. Right-click the Users node and select New. Note: You must save or revert your changes before creating a new user. To save previous changes, select Save All from the Design menu. The Register Warehouse Builder Users Wizard is launched. Click Next on the Welcome page.
2.	On the Select DB user to register page, you can select from the available list of database users or create a new one that is automatically registered as Warehouse Builder target user.Select owf_mgr from the Available DB Users list and click > to move it to Selected Users list. Click Next.
3.	On the Check user as target schema page, uncheck the Used as target schema option. You need not make OWF user, a target user. Click Next. On the Summary page, examine the details and click Finish. The Fix Database Default Role for Users dialog box displays.
4.	In the Fix Database Default Role for Users dialog box, enter oracle or <your SYS account password> in SYSDBA Password field in the Fix Now section. Click OK. Notice that owf_mgr user gets added inside the Security > Users node, in the Global Explorer panel. From Design menu, select Save All. In Warehouse Builder warning dialog box , click Yes to save the changes.

Set Security Preferences

To set security preferences, perform the following steps:

1.	From the Tools menu, select Preferences.
2.	In the Preferences window, select Security Parameters. In the right panel, check Persist location password in metadata. Also, check Share location password during run time, as shown in the screenshot. Click OK. From Design menu, select Save All. In Warehouse Builder warning dialog box , click Yes to save the changes.

Connect the Modules with the Target Schema Locations

When you imported the start.mdl, you had the DQ_TGT and DP_TGT modules already created for you and then you followed steps to create two target users, DQ_TGT and DP_TGT referenced by two locations, DQ_TGT_LOCATION and DP_TGT_LOCATION respectively. Now, you need to link the target modules with each of their respective locations, so that whatever you design in a module, on deployment those objects physically exist on the target schema the location points to.
To connect the modules with the locations, perform the following steps:

1.	First, you test your locations to verify the connection. In the Connection Explorer panel, expand Locations > Databases > Oracle. Double-click DP_TGT_LOCATION. In the Edit Oracle Database Location window, enter DP_TGT in the Password field. Note, the Host field should be set to localhost. If the Host field is set to your machine name, enter localhost. Verify other connection details and click Test Connection. If connection is successful, click OK. If the connection is not successful, check the connection details and try again. Similarly, test DQ_TGT_LOCATION. In the Edit Database Location window, enter DQ_TGT in the Password field. Again, verify the Host field. Note, the Host field should be set to localhost. If the Host field is set to your machine name, enter localhost. Click Test Connection. If connection is successful, click OK. If the connection is not successful, check the connection details and try again.
2.	In the Project Explorer panel, expand Databases > Oracle. Double-click DP_TGT. In the Edit Module window, select Metadata Location tab. From the Location drop down list, select DP_TGT_LOCATION. Now, select the Data Locations tab. Select DP_TGT_LOCATION from the Available locations list and click > to move it to the Selected Locations list. Click OK.
3.	Similarly, you connect the DQ_TGT module with DQ_TGT_LOCATION. Double-click DQ_TGT. In the Edit Module window, select Metadata Location tab. From the Location drop down list, select DQ_TGT_LOCATION. Now, select the Data Locations tab. Select DQ_TGT_LOCATION from the Available locations list and click > to move it to the Selected Locations list. Click OK.
4.	You also need to configure the module to pick the correct data location. Right-click DP_TGT and select Configure. In Configuration Properties window, for Location property, select DP_TGT_LOCATION(Default) and click OK.
5.	Similarly, right-click DQ_TGT and select Configure. In Configuration Properties window, for Location property, select DQ_TGT_LOCATION(Default). Click OK. From Design menu, select Save All. In Warehouse Builder warning dialog box , click Yes to save the changes.

Importing Source Metadata in Source Modules

In Warehouse Builder, you create modules for various sources, such as for Oracle-based relational table source metadata. In the subsequent tutorials that you may take up to perform the hands-on, you may need to create source modules pointing to a Oracle database sample schema such as OE or the DQ_SRC schema where-in you imported the dq_src.dmp file in the previous topic.

Note: Depending on the tutorial, you plan to work through, follow the topics below to create the relevant source module. When you create a source module, you also import the required metadata using the Import Metadata Wizard.

If you plan to work on Examining Source Data Using Data Profiling and/or Matching and Merging Records tutorial, perform "Importing Metadata in DQ_SRC Module" topic. If you plan to work on Extracting, Transforming and Loading Data and/orUsing Experts to Automate Warehouse Builder Tasks tutorial, perform "Importing Metadata in OE Module" topic. Obviously, if you want to work through all the four tutorials, you need to complete both topics, mentioned below:

To import metadata in DQ_SRC module, perform the following steps:

1.	First you need to create the DQ_SRC module, before you can import metadata into it. Assuming that you logged in as owb user, in the Project Explorer panel, select the HANDSON project, expandDatabases. Right-click Oracle and select New. The Create Module wizard launches. Click Next to skip the Welcome page.
2.	In the Name and Description page, enter DQ_SRC as the name of the module. Accept Development as the module status and select Data Source as the module type. Click Next.
3.	In Connection Information page, you specify the location specifications of the source data. Observe that Warehouse Builder gives a default name, DQ_SRC_LOCATION1, to the location. Click Edit to specify the details.
4.	The Edit Oracle Database Location dialog appears. Provide the following information to create a fully qualified location: Click Test Connection to test the connection. If the connection is not successful, check the connection details. Otherwise, click OK. Ensure that the "Import after finish" option is checked. Click Next. In the summary page, examine the details and click Finish. The Import Metadata Wizard launches. Click Next on the Welcome page.
5.	In the Filter Information page, you select the object types you want to import. Uncheck all boxes, except Table andView and click Next.
6.	In the Object Selection page, hold shift and select Tables and Views, and click > to move all the tables to the Selected Objects list. Click Next.
7.	In Summary and Import page, examine the import details and click Finish. The Import Progress dialog shows the import progress. Click OK on the Import Results window. From Designmenu, select Save All. In Warehouse Builder warning dialog box , click Yes to save your work.

Importing Metadata in OE Module

To import metadata in OE module, perform the following steps:

1.	First you create the OE module, before you can import metadata into it. Assuming that you logged in as OWB user, in the Project Explorer panel, select the HANDSON project, expandDatabases node. Right-click Oracle and select New. The Create Module wizard launches. Click Next to skip the Welcome page.
2.	In the Name and Description page, enter OE as the name of the module. Accept Development as the module status and select Data Source as the module type. Click Next.
3.	In Connection Information page, you specify the location specifications of the source data. Observe that Warehouse Builder gives a default name,OE_LOCATION1, to the location. Click Edit to specify the details.
4.	The Edit Oracle Database Location dialog appears. Provide the following information to create a fully qualified location: Click Test Connection to test the connection. If the connection is not successful, check the connection details. Otherwise, click OK. Ensure that the "Import after finish" option is checked. Click Next. In the summary page, examine the details and click Finish. The Import Metadata Wizard launches. Click Next on the Welcome page.
5.	In the Filter Information page, you select the object types you want to import. Accept the default selection. ClickNext.
6.	In the Object Selection page, select Tables and click > to move all the tables to the Selected Objects list. Click Next.
7.	In Summary and Import page, examine the import details and click Finish. The Import Progress dialog shows the import progress. Click OK on the Import Results window. From Designmenu, select Save All .In Warehouse Builder warning dialog box , click Yes to save the changes.