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.
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.
Now you are all set to start
building a prototype.
Go to the start menu / Oracle
Warehouse Builder / OWB client tool. (See figure below)
Login to the OWB client using
the Design repository owner user name that you specified in Step 8. (See
Make sure you provide the
server connection details by clicking on the "Connection Info." button (see
When you login you will see
a default project created for you in the repository as shown below:
Click on the "+" sign to
expand the node. (See below). Under the PROJECT node, you will see all the
components discussed in the previous
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.
Some of the PRE-DEFINED
transformations that can be used are for e.g.: the WB_LOOKUP_NUM (See below for
You can also access Oracle
tools such as workflow server, OMS etc through OWB client as follows:
You can important /export
Repository meta data to other tools such as Oracle Discoverer, Oracle Designer
using Files/ Bridges as shown below:
From the above figure, you
will observe we can create a NEW PROJECT as well.
Building the prototype - Continued
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
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.
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.
subsequent articles, we will cover each of the above-mentioned points in
relatively more details, followed by a complete "real-life" case scenario.
The Oracle Warehouse Builder group
to discuss and share your views, opinions and experiences and ask any
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