Installation of the database and OWB Part 2 (Oracle Warehouse Builder 11gR2)

Installing the OWB standalone software

If we are going to run the OWB client on the same computer as we just installed the Oracle database on, we don’t need any more installations. That is the configuration used in this topic. The OWB client software is now installed by default with the main database installation. We can verify that by checking the Start menu entry for Oracle. We will see a submenu entry for Warehouse Builder as shown in the following image:

If we want to run the OWB client on another computer on the network, or if we have an older version of the database already installed (10g Rel 2) and want to be able to use the Warehouse Builder software with it, we’ll need to continue here with the installation of the OWB client software. For all others, we can proceed to the next section on OWB — OWB components and architecture.

For the task of installing the standalone client, we’ll need to download the OWB client install file. So we will go back to the Oracle site on the Internet. The download page is at the following URL at the time of writing: http://www.oracle.com/ technology/software/products/warehouse/index.html. If that is not working, go to the main Oracle site and search for the Business Intelligence | Data Warehousing page where there is a link for the download of the OWB client.

Once again we’ll have to accept the license agreement before the download links will become active. So we’ll accept it and download the install file to the client computer on which we’ll be installing the software. The Windows ZIP file is about 930MB in size so we need to make sure we have enough room on our hard drive to store the file. We’ll need at least double that amount of space because the install files will take up that much space when unzipped.

When we have downloaded the ZIP file and unzipped it to our hard drive, run setup.exe in the top-level folder to run the Oracle Universal Installer. It should look familiar. Oracle is definitely correct in calling their installer "Universal". Every Oracle database product uses that installer, so we will become very familiar with it if we have to install any more Oracle products. It is universal also in the fact that it runs on every platform that Oracle supports, and so the same interface is used no matter where we install it. The installation steps are as follows:

1. The first step it goes through is asking us for the Oracle home details. It’s similar to what it asked at the beginning of the database installation as shown in the following image:

The installer will again suggest OraDb11g_home1 or something similar, but we’ll change it to OraOWB11g_home1 since it’s just the OWB installation and not the full database.

When installing the standalone OWB software, remember that it cannot be installed into the same ORACLE home as the database. It must reside in it own Oracle home folder. So if we have a database that’s already installed on the same machine, we’ll have to make sure the ORACLE_ HOME we specify is different. The installer will warn us if we try to specify the same one and won’t let us continue until it is different.

We need to verify the installation location for the home location also. The suggested name that it provides conforms to the OFA standard just as the database installation did, so we’ll want to just change the drive letter if needed. However, the bottom-most folder name can be changed if needed without violating the OFA standard. If it has a default of db_1, we can change it to OWB_1 just to be clear that it’s the OWB client.

2. The second step is that email prompt again for being notified about security issues that we saw earlier in the database software install. We can just continue and answer yes to the popup warning.

3. The next and final step is the summary screen. The OWB client installation is not as complex as a full database installation, so it does not need all the additional information it asked for during the database installation. The summary screen should look similar to the following:

This summary gives us an idea of the disk space it will need, as well as the products that will be installed. If we scroll down the list, we’ll see a number of other Oracle utilities and applications that it will install. We will also see items that are installed on the server as a part of the database install, but that will now be available to us on our client workstation. SQL*Plus appears there, which is the command line utility for accessing an Oracle database directly using SQL (Structured Query Language, the language used for accessing information stored in databases) among a host of other features.

Upon proceeding, the next screen will begin the installation and present us with the progress screen with a sliding bar moving to the right to indicate how far it has progressed. This is similar to what it did for the full database installation. An example of that screen is included next for reference:

Install results

The log files with the results of the installation are stored in the same location as they are for a full database install. The universal installer will use that same folder for all its installs.

When the installation is complete, we will be presented with the final success screen and an Exit button. And as if to remind us about the universal nature of the installer, it will pop up a confirmation box asking if we really want to exit, even though for this installation there is nothing else that would be available to do on that final screen if we said no.

OWB components and architecture

Now that we’ve installed the database and OWB client, let’s talk about the various components that have just been installed that are a part of the OWB and the architecture of OWB in the database. Then we’ll perform one final task that is required before using OWB to create our data warehouse.

Oracle Warehouse Builder is composed on the client of the Design Center (including the Control Center Manager) and the Repository Browser. The server components are the Control Center Service, the Repository (including Workspaces), and the Target Schema. New for release 11gR2 is the Control Center Agent which is used by the new Code Template Mappings to communicate with a non-Oracle database. We’ll be covering that in more detail in topic 10. A diagram illustrating the various components and their interactions follows:

Client and server

The previous diagram depicts a client and server, but these are really just logical notions to indicate the purpose of the individual components and are not necessarily physically separate machines. The client components are installed with the database as we’ve seen previously and, therefore, can run on the same machine as the database. This configuration is assumed throughout the topic.

The Design Center is the main client graphical interface for designing our data warehouse. This is where we will spend a good deal of time to define our sources and targets, and describe the extract, transform, and load (ETL) processes we use to load the target from the sources. The ETL procedures are what we will define to carry out the extraction of the data from our sources, any transformations needed on it and subsequent loading into the data warehouse. What we will create in the Design Center is a logical design only, not a physical implementation. This logical design will be stored behind the scenes in a Workspace in the Repository on the server. The user interacts with the Design Center, which stores all its work in a Repository Workspace.

We will use the Control Center Manager for managing the creation of that physical implementation by deploying the designs we’ve created into the Target Schema. The process of deployment is OWB’s method for creating physical objects from the logical definitions created using the Design Center. We’ll then use the Control Center Manager to execute the design by running the code associated with the ETL that we’ve designed. The Control Center Manager interacts behind the scenes with the Control Center Service, which runs on the server as shown in the previous image. The user directly interacts with the Control Center Manager and the Design Center only.

The Target Schema is where OWB will deploy the objects to, and where the execution of the ETL processes that load our data warehouse will take place. It is the actual data warehouse schema that gets built. It contains the objects that were designed in the Design Center, as well as the ETL code to load those objects. The Target Schema is not an actual Warehouse Builder software component, but is a part of the Oracle Database. However, it will contain Warehouse Builder components such as synonyms that will allow the ETL mappings to access objects in the Repository.

The Repository is the schema that hosts the design metadata definitions we create for our sources, targets, and ETL processes. Metadata is basically data about data. We will be defining sources, targets, and ETL processes using the Design Center and the information about what we have defined (the metadata) is stored in the Repository.

The Repository is a Warehouse Builder software component for which a separate schema is created when the database is installed—OWBSYS. This is the schema we talked about unlocking during the installation discussion previously as one of the final steps in the database creation process. This will be created automatically by the 11g install, but is installed separately using scripts if we want to host the Repository on an Oracle 10g database. The explanations in this topic all assume that the Repository is hosted on an Oracle 11g database. The Oracle Warehouse Builder Installation and Administration Guide found at the following URL: http://download.oracle. com/docs/cd/E118 82_01/owb.112/e1713 0/toc.htm discusses the procedure for installing the Repository schema on an Oracle 10g release 2 database if needed. It also contains more detailed information about the various other configurations that can be installed along with more detailed diagrams of the various options.

The Repository will contain one or more Workspaces as shown in the previous diagram. A Workspace is where we will do our work to create the data warehouse. There can be more than one workspace defined in the Repository. A common example of how multiple workspaces can be employed is to use different workspaces corresponding to sets of users working on related projects. We could have one workspace for development, one for testing, and one for production. The development team could be working in the development environment separately from the test team that would be working in the test environment. For our purposes at the ACME Toys and Gizmos Company, we will be working out of one workspace.

This concept of the workspace is new in the 11g releases of OWB. The Repository is created in the OWBSYS schema during the database installation. So setting up the Repository information and workspaces no longer requires SYSDBA privileges for the user to install the Repository. SYSDBA is an advanced administrative privilege that is assigned to a user in an Oracle database. This allows the user to perform tasks affecting the database and other database users that ordinary user accounts cannot do (or for that matter, other administrative accounts without SYSDBA). For security reasons, we want to restrict user accounts with SYSDBA privilege to a minimum. So it is good that we don’t have to use that privilege when we install the Repository.

One final OWB component to consider is the Repository Browser on the client. It is a web browser interface for retrieving information from the Repository. It will allow us to view the metadata, create reports, and audit runtime operations. It is the only other component besides the Design Center and the Control Center Manager that the user interacts with directly.

We will have a chance to visit each one of these areas in much more detail as we progress through the design and build of our data warehouse. However, first there is one more installation step we have to take before we can begin using the Warehouse Builder. The Repository must be configured for use and a workspace must be defined.