Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 29, 2003

ETL Prototype using Oracle Warehouse Builder - Part 3 - Page 2

By DatabaseJournal.com Staff

Building the prototype -- Initial steps

In this article, we will go through the some of the steps involved in building a prototype for the Extraction, Transformation and Loading (ETL) stage of the Data warehouse lifecycle.

The initial intent was to develop a "complete" ETL process design that including multiple type transformations, disparate source and target modules and a well-defined process flow scheduled for execution by the Oracle workflow server /OMS.

However, due to the scope of the article and because of the many emails I receive with questions on "how a specific task can be achieved", the focus eventually turned to "using a step-by-step" approach scenario on a per article basis, rather than taking a "complete" scenario. In addition, to cover all possible combinations and cases is beyond the scope of a single article.

Considering that Oracle rolls out umpteen versions, each requiring a "patch" for some reason (like Microsoft having their bulky product updates every now and then), you are bound to run into a set of problems depending upon the version you have (database, warehouse builder, etc.) and the feature you are trying to use (Like I did too!).

As you probably will experience, installation is one of the relatively "painful" tasks. However, following the steps below will help you avoid some common pitfalls in installation.

This prototype was done on a machine with following configuration. For the specific system requirements please use Oracle Warehouse Builder Installation and configuration guide.


1.       RAM 512 MB

2.       HDD 120GB

3.       CPU 667 MHZ (pretty old one in the days of GHz!)

The database was created with the Oracle provided "data warehouse" template. In this case the data warehouse is created with a DB_BLOCK_SIZE =8k and enqueque_resource parameter= 892.

Oracle recommends a DB_BLOCK_SIZE=16k and enqueque_resource parameter= 3000 for Oracle Warehouse Builder installations.

At the time of this prototype, the defaults were used.

Software versions:

1.       Oracle

2.       OWB 9.2

Following are the guidelines only, not "detailed " steps for installation as these can be obtained using the Oracle Server installation manual and Oracle Warehouse Builder installation manual.


1.       Install the database server.

2.       Choose the Data warehouse template and use the default settings.

3.       Install Oracle Warehouse Builder version 9.2 (I chose to install as a separate component and not a bundled component that is available with 9iDS suite).

4.       Use a separate ORACLE_HOME for OWB.

5.       Technically, you should install the Oracle warehouse repository (design time and run time, both) in a separate database, but for this prototype, I have chosen to use the same data warehouse that was created in step 1 above.

6.       Installation and implementation of a complete business solution using the Oracle Management Server, the workflow server and the Oracle9i Application Server are not within the scope of this article. Oracle Warehouse Builder can be configured to work with these components, thereby delivering a total effective, efficient, highly scalable, and manageable business intelligence solution.

7.       Create the Oracle Warehouse Builder repository using the Repository Assistant. As shown below

8.       Follow the Repository Assistant wizard prompts (see fig. Below)

9.       Using this repository owner, you will later access the Oracle Warehouse Builder client through which will you will be able to design, develop and implement the complete data warehouse. (Make sure you note down all of the user names and passwords; it can be confusing between design time and runtime repository users for first time users)

10.   The next step is to create the Runtime repository where your runtime objects will be stored/deployed. (See figure below)

11.   Follow the runtime repository assistant wizard prompts .After providing the SYS user name and password and database connection details, you will see the window as shown:

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM