| |
 |
|
Oracle Warehouse Builder (OWB) vs. Manual data loading
Oracle Tips by Burleson Consulting |
Question: I'm new to Oracle data warehousing and I'm
wondering about the relative benefits of using Oracle Warehouse
Builder as opposed to writing a custom solution with dbms_scheduler.
should we use the new Oracle 9i OWB or just load Oracle 9i and
create a warehouse type database from within that? I've looked
for some guidance from Oracle but I can't find any best practices
documents or hardware configuration recommendations (We're probably
going to go with a single Sun server). Thank you.
Answer:
Oracle Warehouse Builder (OWB) is a software framework to allow
you to chain-together complex jobs for performing data warehouse
activities (data loading, aggregation with materialized views,
etc.), and you have many options. Essentially, OWB is a
teleprocessing monitor (a TP monitor like CICS, IDMS/DC or Oracle's
dbms_scheduler) where you can define job streams, perform branching
based on return codes and manage complex data transformation.
The benefit of OWB is that the framework requires standard API,
but the downside is that a custom-written solution may be more
flexible and powerful. There is no standard "best practice"
for using OWB vs. custom solutions, it depends on the skills of your
staff and your requirements. To measure server load, take a
look at the book "Oracle Database 10g Application Server Handbook"
by Oracle Press.
For alternatives to OWB, you can choose from many options:
- Use a TP monitor - TP monitors such as Tuxedo can
manage complex processing streams.
- Roll your own with dbms_scheduler or
cron - Experienced DBA staff are well-versed in writing
data load routines within Oracle (using the dbms_scheduler
package) or externally using a UNIX/Linux cron with shell
scripts.
For references, I recommend these books and services:
And these pages:
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of my favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
