Using OWB10gR2 in a Production Environment … Revisited

A couple of weeks ago I posted an article on using Oracle Warehouse Builder 10gR2 in a production environment. Since then, I've had the opportunity to spend some time with customers doing just this, and I've come up with a few more observations that are worth adding to the original article.

In the original blog posting, I noted that most real-life customer deployments of Warehouse Builder involve more than one target database. This is probably still true, but it's also worth noting that many customers, especially when they first start out, have a much simpler architecture, and it's important not to over-complicate things too early on. In particular, I think now that the Multi-configuration feature in the Enterprise ETL Option is a bit of a red-herring, as in the vast majority of situations it's just not needed. To explain this, I'll firstly go through some typical customer deployments, and explain where all the various features come in.

The most typical deployment of Warehouse Builder 10gR2 is with a single database with both the repository and target schema in the same database, like this:

This kind of setup requires a single Control Center, which is contained within the repository used to hold the table and mapping designs.

A slightly more complicated setup is where you have a single repository, and a single target schema, but they are on different databases, like this:

In this case, you can still use a single Control Center, but it has to be the one on the database used by the target database. To do this, you install a new repository on the target database, register it in your project, and make this the default Control Center for your default configuration (right-click on DEFAULT_CONFIGURATION in the project, edit the properties and select the other control center, making sure you've registered the control center in the project beforehand.) Make sure you do this before you create your warehouse target module, then the location you set up will be owned by the correct Control Center. There's no need to use multi-configuration to get access to this Control Center, you can just enable it by changing the Control Center assigned to the default configuration.

When it comes to deploying these two approaches to production, assuming production has the same tablespace names and table storage parameters as development, you can just create a production repository, export the development repository to an MDL file, and then import it into the production repository to promote the code. Multi-configuration only becomes relevant if you need to store different physical object properties for each environment, but most places I've been to don't need this as development has the same tablespace names as the production environment.

A more complex setup, and one that I focused on in my original posting, was where you had two or more target databases that your project needed to deploy to. In my posting, I said that the only way you could do this was to use multi-configuration to re-point the Control Center Manager to each of the required Control Centers. Someone came back fairly quickly and reminded me that you could, as I've just explained, switch the control center associated with your default configuration using the Design Center GUI, and since then I've been working with clients on OMB*Plus scripting where this is even easier to do, like this:

OMBCONNECT username/password@host:port:sid

...

Again, therefore, multi-configuration, whilst it makes your life easier, is by no means a mandatory feature and you can deploy to more than one target database just as easily using standard, ETL Core features. Multi-configuration isn't really the issue here; sure it lets you easily work with two or more control centers, but this isn't the real use for it and you can achieve the same just as easy with standard features of the tool. Going from development to production is still the same case as before; you just export the project to an MDL file and import it into the production repository, then go through the same process to deploy to multiple control centers.

That said, most of the points I raised are still valid. You still need to be mindful of which control center owns your locations, as a given control center can only deploy and execute mappings against locations it owns, which must be on the same database as the control center. Versioning is still important, and OMB*Plus is the key to automating the move from development to production without introducing human error. There is a role for multi-configuration, and you can still use it as a way of using a single repository but storing deploying to different locations for development, test and production, but this isn't the usual approach and it's better to place the emphasis on what people actually do most of the time.

One thing that did come out of last week's work was a lot of work on OMB*Plus; it made a lot of the above clearer as it helps expose what OWB is doing under the covers when you deploy tables and mappings; it also makes handling more complex environments a lot more managable because you can script everything. I've got a couple of issues left on some scripts I wrote that I need to sort out, but once I do, I'll post a couple of articles on using OMB*Plus and how you can use it to help productionize an OWB deployment and work with these more complex setups. More on that in a few days, but I thought it worth going back to the original article as I think in retrospect it placed too much emphasis on multi-configuration, which as part of the Enterprise ETL Option isn't cheap and isn't actually a prerequisite when working with multi-database environments.