Monthly Archives: February 2005


A bayon blog reader wrote me and asked if I had any additional information on scheduling ETL mappings and process flows. I had not fiddled too much with that part of the product; the Oracle provided run_my_owb_stuff.sql and SQL Templates for Enterprise Manager had usually been sufficient for my customers. There are some new scheduling features in Paris and without the reader request I probably would have just passed them up. So while this covers a somewhat smaller feature of the new version, it’s still interesting and makes a nice installation in my OWB Paris Early Review series.

To address scheduling needs there are entirely new OWB objects, appropriately called “Schedule Modules” and “Schedules.” Those current users of the product are well aware that in previous versions of the product, no such objects existed so it’s helpful to show how these are used. One creates a new Module which is merely a container to hold the actual Schedule objects. Note: I haven’t found anything particular interesting about the module definition in terms of deployment or configuration, but perhaps it’s used in the multi-configuration deployments.

Then one creates the schedule that will contain information on when to run an OWB Mapping Process flow. Note: I tested this feature with a Mapping but not a process flow. All the documentation I’ve seen from Oracle mentions that Schedules can be applied to either Mappings or Process Flows.

Now we see a wizard, which happens to be a conundrum. Wizards are usually helpful in providing the most common use cases with a honed interface, however the actual Schedule editor is far more intuitive than the wizard as you’ll see below. I think it a bit strange that the wizard is less helpful than the actual editor; any other testers out there have an opinion please email me!

We’ll define a start and end date for the Schedule which defines the bounds on which it will run. Perhaps you’ve built a Mapping that only runs for the next 3 months during an OLTP system migration so you can define when this schedule will no longer run.

And now we’ll set our recurrence… Don’t worry, you can see more details and have more control over the Schedule in the actual editor.

I’ve now created my schedule, LOAD_EMP_EVERY_2_MINUTES and have opened it in the editor. You can see this is a much better way of visually editing the Schedule. It projects the schedule in a hierarchical manner in the Schedule Preview window (you can flatten it if you so choose). It also allows you to set clauses here, so you can fine tune when it will execute. You might need to use this if you need to limit the Schedule so that it only runs on say Monday Thru Friday when the system is populating data.

Next step is to CONFIGURE the Mapping and let OWB know that we intend to use a certain schedule when we “Run” our Mapping (or flow). For this example, we’ll assume we’ve already created and tested an OWB mapping named “STAGE_EMP_TABLE” that’s been deployed and runs properly via the Control Center.

Now, when we head back into our Control Center panel we see a new Item that hasn’t been deployed named “STAGE_EMP_TABLE_JOB.” We deploy this object

We know see we have an available job in our Control Center. Remember that in Paris, nearly all (if not all) of the Control Center runs asynchronously now i.e. the client doesn’t block while deploying an application or running a mapping or process flow.

Now, according to OWB my Job has been scheduled and is running! It shows the job, when it last ran was, and what the return was of that run. In my example, the last run was sucessful and finished at 6PM on 2/25/2005.

I did manually verify the job was running, ensuring that STAGE_EMP_TABLE was executed properly each time. You can also see a list of the runs and which job they are associated with. The control center is showing me that it is currently running the job, and that it ran the job previously twice successfully. Notice the seperation of the time is two minutes apart, so it appears as if my schedule was about right. This is completion time, so don’t be worried if it doesn’t match up because while your job may start at the correct time, it may finish with varied elapsed times.

I do believe the only way to change the schedule, Start and Stop the Scheduled jobs is through the proper OWB Client. I took a quick look at the OWB Web Based browser and it does not appear as if you can make changes. My Paris copy mentions that the browser isn’t even going to be in the Beta release so I am probably just seeing some partial build with an incomplete version of the OWB Browser.

For those that this feature matters, this will be very helpful. OWB is really shooting to be a one stop shop for all your Data Warehouse/ETL needs and the scheduling feature is a nice little piece of that. Happy OWB scheduling to ya!

This blog is part of the OWB Paris Early Review series which reviews and comments on several new Paris features.


I recently did some training on OWB at my local PSOUG user group, which is not your “run of the mill” user group. Their focus on peer to peer education and quality, technical presenations makes this group stand out in my mind. If you are in the Puget Sound area (the greater Seattle, WA area for those not familiar with US regional geography) you should definitely check out the meetings. The handout from the meeting is here (PDF, Word). Watch for an email about an OWB course at the PSOUG lab!

I promised those in attendance that I would post some notes so that you can do the same excercise. Here are those promised notes, and I hope you find it useful to get you ready and in a state ready to “recreate” the excercise. Please email me with any questions you might have, or if you are planning a Data Warehouse or Data Mart using OWB. I’d love to help out by providing additional coaching, solution planning, or actual implementation.

Step 1 : Install Oracle Database 10g

We assume you know how to install Oracle software and how to use DBCA to create a database. Download the Oracle DB 10g release 1 from OTN here. Follow the instructions to install the product onto your machine. During the process (or after using DBCA) create a database using the Data Warehouse Template, and make sure and install the sample schemas.

Step 2 : Install Oracle Warehouse Builder 10g

There’s not a lot to do at this point. Download OWB from OTN, and follow the instructions on the screen and install OWB into a SEPERATE ORACLE HOME.

Step 3 : Create an OWB Design Repository

Run the following program from your OWB installation ($ORACLE_HOME/owb/bin/unix/ (or if you’re on windows run %ORACLE_HOME\owb\bin\win32\reposinst.bat).

Click “Next”

Enter your sys username, password, Service name, and port number

Select “Create a new Warehouse Builder Repository”

Create and install into a new user

Call it OWB_REP and give it an easy password

The default tablespaces are usually fine, but if you have installed your db differently feel free to change these.

American English is fine in most circumstances and for this demo

Review the information for correctness and then click “Finish”

Hopefully you’re watching this work and

get one of these. You’ve installed an OWB DESIGN REPOSITORY into your database.

Step 4 : Create an OWB Runtime Repository

Run $ORACLE_HOME/owb/bin/unix/ (or corresponding file for windows)
Enter in your sys information (sys/password/singapore/1521/

Select Runtime Repository

Select Create New Warehouse Builder Repository

Create and install into a new schema

Enter in owb_rt and an easy password twice

The warnings are nothing major… click OK

Default Tablespaces are fine

Install a new runtime access user

Enter in owb_user and an easy password twice

Port number is fine (very few people actually use the name/address cleansing and it costs extra)

Review and Click Finish

Click “Yes” to create a target schema

Select Target Schema

Create a new OWB Target Schema

This is the Runtime Repository we just created, owb_rt/password

Create and install into New user schema

owb_tst/easy password twice

Click OK again on the warning

Default tablespaces are fine

Review and click Finish

You’ve successfully created a runtime repository and a target schema to deploy your test OWB objects to!

Step 5 : Install OWF 2.6.3

Download OWF Server from OTN here.

Click “Next”

This is really important… You must use your database 10g home for installation

Select “Oracle Workflow”

Review and Click Install

Now run $ORACLE_HOME/wf/install/wfinstall.csh (or corresponding .bat if windows)
Install into OWF_MGR, Install, your connection info (bdev, singapore, (LocalName TNSNames, hostname, JDBC string)

Using the SYSDBA account grant execute any procedure to OWF MGR

SQL> grant execute any procedure to owf_mgr;
Grant succeeded.

Step 6 : Unlock the SCOTT account by running the following script as SYSDBA

SQL> alter user scott account unlock;
User altered.

Step 7 : Allow OWB_TST to select from SCOTT tables

grant select on emp to PUBLIC;
grant select on dept to PUBLIC;
grant select on salgrade to PUBLIC;.

Step 8 : Get the demo materials

Download the following items which were used during the demonstration:

OWB Paris Webcast

I recently gave a presentation at NWOUG on the new features of Paris and thought it worthwhile to post a similar presentation on my blog.

This is my first time producing a rich media presentation and did so rather quickly with the help of a tool called Camtasia Studio. Consider yourself duly warned: the audio is not that great, I didn’t do any editing, and it’s just me chatting over some screenshots of the product. You’ll need flash (1024×768 too), and I’ve split it up by segments for easy viewing. If you’re already familiar with OWB consider skipping ahead to OWB 10gR2 Overview and continuing on from there. They are each a few minutes, and are not in great depth but give a sense for the feature and it’s applicability.

I do hope it’s useful for those eager to get some new information on the Paris release of OWB. Feel free to drop me a line if you have Q’s about OWB Paris or otherwise.

Why OWB?
OWB ETL Leader
OWB Demo
OWB Applicability
OWB Concepts
OWB Installation
OWB 10gR2 Overview
OWB Improved UI
OWB Pluggable Mappings
OWB Data Profiling 1
OWB Data Profiling 2
OWB Data Profiling 3
OWB Data Correction
OWB Right Time BI
OWB Change Data Capture
OWB Experts
OWB Impact Analysis
OWB Misc
More Information


I’ll be giving a lecture at the upcoming PSOUG meeting on the 23rd of this month. Those locally that are interested in a free “crash course” in OWB should attend this meeting. It’s always free and usually has an excellent program aimed at technical users. Hope to see you at Oracle’s Bellevue office 108th Ave NE, Suite 1300 from 5:30 to 7pm.

NWOUG Presentation on OWB Paris

On Monday I gave a presentation at the Northwest Oracle Users Group (NWOUG) Winter conference. This was the first NWOUG conference I’ve attended, and was grateful to the NWOUG board for accepting my presentation.
I’ve included the presentation slides here (Powerpoint and PDF) for those readers interested.
As many of you know, I’m writing a series of articles on the new features of Paris which I’m hoping to add more to shortly.