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

Setting Up the Project Environment

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

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" div of the Oracle Warehouse Builder User's Guide.

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.

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.

Open the owbdemo_files.zip file, select all files, and unzip them to a root level, such as c: 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 the c: or d: or other directory location where you place these files.

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 exercises. This is made available to you in case you want to explore XSALES table data on your own.

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, /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 the Path 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=\bin. Then invoke SQL Plus as SYS with SYSDBA privileges. For example, at the command line you might type: sqlplus sys/ as sysdba.

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

@/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. Type users 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".

Earlier, you were instructed to install Warehouse Builder in an Oracle home other than the database 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 be case-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.

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 div titled ?Hosting the Repository on Oracle Database 10g Release 2?.

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.

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.

In the Database Information window, enter the following values:

Click Next.

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

Click Next.

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

Click Next.

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

Click Next.

In the DBA Information window, enter the following values:

Click Next.

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

Click Next.

In the OWBSYS Information window, enter the following values:

Click Next.

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

In the Select Languages window, accept the defaults and click Next.

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 workspace user. Click Create New User.

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

Click OK.

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

Click Next.

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.

Before you can begin working with the lessons, you need to run the scripts from the owbdemo_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 with your Oracle database home path and with the location where you extracted the xsales.dmp file.

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

To run the tcl scripts, start the OMB Plus environment. There are two ways to start OMB Plus. Within the Design Center, you could select OMB Plus from the Window menu. However, for this example, start OMB Plus the other way: select Start > 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.

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

Examine the menu choices:

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 , port as 1521, and service name as orcl or . Click OK.

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.

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.

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.