【oracle教程】Starting with Oracle Warehouse Builder 11g Rel
Starting with Oracle Warehouse Builder 11 g Release 1 Purpose In this lesson, you use the setup files to configure the project environment for Oracle Warehouse Builder 11 g Release 1 (OWB 11 g R1). You then create a Warehouse Builder user
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 11g Release 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:
Overview | ||
Prerequisites | ||
Reference Material | ||
Warehouse Builder 11g Architecture and Components | ||
Setting Up the Project Environment |
||
Introducing the OWB Program Group Components | ||
Logging In to the Design Center | ||
Summary |
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 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.
|
||||||||
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:
For more information on OWB packaging, read the "Product Options and Licensing" div of the Oracle 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 Library | ||
Oracle 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 Scripts | ||
If Using DB10gR2, Create OWBSYS User and Enable Access to Control Center and Workspaces | ||
Create the Design Center User and Repository | ||
Run 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 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:
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.
|
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 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, 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= Connect to SQL Plus as sysdba and issue the following command to create OWBSYS: @ 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".
|
2. |
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.
|
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 div 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:
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:
Click Next. |
||||||||||
7. |
In the Workspace Owner (New) window, enter the following values:
Click Next. |
||||||||||
8. |
In the OWBSYS Information window, enter the following values:
Click Next. |
||||||||||
9. |
In the Select Tablespaces window, accept all of the defaults and click Next. |
||||||||||
10. |
In the Select Languages window, accept the defaults and click Next. |
||||||||||
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 workspace user. Click Create New User. |
||||||||||
12. |
In the Create New Database User window, enter the following values:
Click OK. |
||||||||||
13. |
The Workspace Users (Optional) window reappears, with the new rep_user listed in the Selected 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, 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. 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 DataNote: 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
|
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 these variables to match the values in your computer's environment: set owbclient rep_owner 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.
|
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 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.
|
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:
|
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
|
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.
|
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 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
Place the cursor over this icon to hide all screenshots.