Tagged ‘Oracle Warehouse Builder‘

Book Review – Oracle Warehouse Builder 11G Getting Started – OWB for beginners

Bob Griesemer’s Oracle Warehouse Builder 11G – Getting Started is the first and so far (as of Oct 2009) only book published on Oracle Warehouse Builder.

It aims to introduce data warehousing in general and Oracle Warehouse Builder in particular, to absolute beginners in the field.

In chapter 1 the author guides us through the installation process for OWB 11G. A typical installation of Warehouse Builder in a client/server environment is explained. More complex architectures with the Control Center service on a different server are not explained.

In chapter 2 we are introduced to the data model of the source system for the data warehouse. We are shown how to import or create the metadata for our source system. What I found useful here is that the author explains how we can use a non-Oracle RDBMS (in this case SQL Server) as a source system via heterogenous services. This can be a pain to set up so it is helpful to have a step by step walkthrough for this.

Chapter 3 gives an introduction to the basics of data warehouse design. It then explains how we can implement such a design as a target structure in Oracle Warehouse Builder.

In chapter 5 the author gives an overview on the basics of ETL processes and introduces us to some of the more important OWB operators that will allow us to implement ETL process in Warehouse Builder. I found that more space should have been given to explain the operators in detail. Some more detailed examples for each would have been useful also.

Chapters 6 and 7 then show us how we can bring the source metadata from chapter 2, the target metadata from chapter 3, and the operators from the previous chapter together in a Warehouse Builder mapping to extract, transform, and load data from source to target. What is completely missing here though is an introduction to process flows. Process flows are fundamental to glue ETL mappings in Warehouse Builder together and should have been part of an introduction to the subject.

Chapter 8 gives an overview on the deployment and execution of objects via the OWB Control Center. It also includes a good troubleshooting section with regards to deployment. As expected from a beginner’s book, more advanced topics such as deployment via OMB+ and TCL scripts is not explained. What is also missing is an explanation on how to schedule objects once they have been deployed.

In chapter 9 the author introduces us to various OWB features. The book gives a good overview on version management via snapshots and export/import of metadata.

The book does pretty much what it says on the tin and gives a good introduction to novices in the area of data warehousing and Oracle Warehouse Builder. So if you have never used Oracle Warehouse Builder this book is for you. If you have used OWB before you will not learn anything new here. Also this book can only be a starting point for your OWB career. A lot more OWB features than are outlined in this book need to be learned to become a master in the area (if you think about it the OWB user manual in PDF format has about 1000 pages and in some areas only scratches at the surface). Also the timing of the publication of the book is a bit unfortunate as only recently OWB 11GR2 was released with a lot of important new features and a redesigned User Interface. Hopefully there will be a 2nd edition soon that addresses this shortcoming.

Run Procees Flow from Command Line or SQL+

You can run a process flow from the command line. This is useful if you want to call a process flow from another application, e.g. if you want to use a 3rd party scheduler like AT or Cron etc. or if you want to script the execution of your process flows.

sqlplus user/pwd@connect_identifier C:\oracle\products\10.2.0\owb_1\owb\rtp\sql\sqlplus_exec_template.sql <rep_owner>
<location process> <flow name> <process flow> <sys params> <custom params>

You need to call the sqlplus_exec_template.sql from sqlplus and pass five parameters to the script as in the example above:

rep_owner: repository owner
location: for a process flow this is the location for the Oracle Workflow Manager as defined in the OWB Design Center
process flow: Just pass in PROCESSFLOW as arg
name process flow: The name of your process flow
sys params: “,” if you don’t have any
custom params: P_ENABLE_TRACE=0 as an example

Automate OWB Metadata export with OMB+

I regularly make exports of Warehouse Builder Metadata to files. I do this at least once a day. When there are a lot of changes to OWB objects I make the export multiple times a day. I prefer this over snapshots as these exports to file do not fill up the OWB repository. Regular exports are useful to re-import changed objects and to keep track of changes to your objects.

I have written a script to automatically export Warehouse Builder Metadata. The script can be scheduled via Windows AT command.

Adapt the following to your environment and save as OWB_export.tcl

OMBCONNECT owb rep_owner/pwd@host:port:service
set OMBLOG c:\\temp\\log.txt
set systemTime [clock seconds]
set systemTimeFormat [clock format $systemTime -format %Y%M%d-%H%M%S]
OMBEXPORT MDL_FILE 'c:\\temp\\my_project_$systemTimeFormat.mdl' \
OUTPUT LOG 'c:\\temp\\my_project_$systemTimeFormat.log'

Now open a command prompt and type:

AT 23:30 /EVERY:m,t,w,th,f C:\oracle\products\10.2.0\owb_1\owb\bin\win32\OMBPlus.bat c:\OWB_Export.tcl

This will schedule to run the script every weekday at 23:30.

Global Variables OWB

Several global variables can be accessed in a process flow. How this is done is not documented well. In the example below I have created two variables and assigned them values of the global variables Audit_ID and Number_of_Errors.

Step 1:

Create Process Flow and add two variables: l_audit_id and l_number_of_errors. For both variables select Integer as the data type.
owb audit_id

Step 2:

Deploy process flow and execute. After execution the Output Parameters will have the values for the audit_id and the number_of_errors global variables. The variables could be assigned to output parameters of the process flow, input parameters to transformations, mappings or other process flows etc.

owb audit_id