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 3

By DatabaseJournal.com Staff

12.   Now, the first step is to create the Runtime repository and the runtime repository owner (the wizard creates it for you, simply follow the prompts). The wizard also prompts to create a Runtime Access user. Oracle recommends accessing the runtime repository by logging in to the runtime repository as this user.

13.   After creating the runtime repository, the repository owner and the runtime access user, the wizard prompts to create the Target schema. This is the schema where the warehouse objects you have designed will be deployed and the processes will be executed. It is very important to follow the sequence because the wizard will grant security privileges to each of the users. The required privileges have to be rechecked again, if you get any "system privilege" messages during Deployment. The privileges for each user are documented under the Oracle Warehouse Builder release manual.

14.   Now you are all set to start building a prototype.

15.   Go to the start menu / Oracle Warehouse Builder / OWB client tool. (See figure below)

16.   Login to the OWB client using the Design repository owner user name that you specified in Step 8. (See below):

17.   Make sure you provide the server connection details by clicking on the "Connection Info." button (see below).

18.   When you login you will see a default project created for you in the repository as shown below:

19.   Click on the "+" sign to expand the node. (See below). Under the PROJECT node, you will see all the components discussed in the previous article.

20.   Under DATABASE, you can define the source/target modules. The source can be either Oracle or any other system including APPLICATIONS like SAP or Flat FILES. However, the target can only be an Oracle schema. You will also see the PUBLIC TRANSFORMATIONS that include CUSTOM or PRE-DEFINED transformations. Designed objects can then be deployed by using Runtime Repository that can be configured. You can define the process flows by choosing the PROCESS FLOW node.

21.   Some of the PRE-DEFINED transformations that can be used are for e.g.: the WB_LOOKUP_NUM (See below for description)

22.   You can also access Oracle tools such as workflow server, OMS etc through OWB client as follows:

23.   You can important /export Repository meta data to other tools such as Oracle Discoverer, Oracle Designer using Files/ Bridges as shown below:

24.   From the above figure, you will observe we can create a NEW PROJECT as well.

Building the prototype - Continued

The above were quick steps to get started with a simple "prototype." In my next article, that will follow shortly , we will see how to define three types of sources, namely, flat file, external table and a Oracle schema source table, target module, register locations, Map flat file definitions to target objects, use mapping editor, object editors and deploy these objects and initiate a data load.

OWB can be used to design dimensions, facts, and staging tables, ETL processes and generate mappings using complex transformations such as ADDRESS cleansing, table functions, look up transformations etc. in a relatively easy way and also handle SCDs. As already mentioned in previous articles, OWB can also handle multiple disparate sources and deploy objects to multiple target environments.

In addition, OWB can be configured with OMS or Workflow Server for automatic execution of ETL processes and import/export meta-data repository. Finally, OWB can be used in a multi-user environment seamlessly.

In subsequent articles, we will cover each of the above-mentioned points in relatively more details, followed by a complete "real-life" case scenario.

Useful Links

The Oracle Warehouse Builder group to discuss and share your views, opinions and experiences and ask any questions. Oracle Warehouse Builder Group

The Oracle Streams group to discuss and share your views, opinions and experiences and ask any questions. Oracle Streams Group

» See All Articles by Columnist Nandeep Nagarkar

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