Version And Environment Management Using OWB10g
July 20th, 2004 by Mark Rittman
One of our customers recently asked me about the best way to manage multiple
environments when using OWB. The customer had a development environment,
together with test, QA and of course production, and wanted to know how to set
up their design and runtime repositories. Do we create separate design and
runtime repositories for dev, test, QA and prod, do we have just one for all
environments, or is the answer somewhere in between?
The situation is complicated a bit by the fact that OWB’s architecture has
changed slightly over the previous few versions. Up until OWB 9.0.3, you had a
design repository to which your OWB client connected, and one or more OWB
runtimes, which you installed into every schema you loaded data in to. With OWB
9.0.4 this changed in that you now have one runtime repository per target
database, and this runtime repository contains all the packages, and stores all
the load results, for use by all the target schemas in a database. More details
on the new runtime architecture can be found in Architecture
Whitepaper for Warehouse Builder 10g and Server-side
component (runtime) in Oracle Warehouse Builder 10g on OTN.
In practice, we’ve found that different clients use different approaches to
solve this problem. Some of them have a single design repository, with it’s
contents effectively being the most current view of the OWB project definition.
This definition is then used to initially populate the dev warehouse
environment, and after that it’s used to populate the test and QA environments.
Once everyone’s happy that this is ok, it’s then used to load up the production
warehouse environment.
The problem with this is that the model within the design repository doesn’t
usually reflect what’s actually deployed in test, QA and prod. One way around
this is to make backup copies of the design repository (using the .MDL file
export routine) after each deployment, and then load these up whenever required
to see how the design repository looked when you last deployed to an
environment. Another way to deal with this is to have separate design
repositories for dev, test, QA and prod, and keep these in sync by exporting and
importing modules between the environments using the .MDL file export facility.
Yet another way is to use the snapshot facility in recent versions to store
copies of the repository for later reference.
You’ll be interested therefore to know that Oracle have recently posted a
series of OWB casestudies, one of which is specifically about version and
environment management using OWB. Entitled "Case
Study 9: How Do I Manage Multiple Versions of my BI Implementation?" it
discusses two scenarios where OWB is used to manage multiple target
environments, such as dev, test, QA and prod. According to the article
introduction:
"After a period of development and testing, one company implements
its BI system in production. The Production version of the system typically
changes as new features are incrementally implemented from Development, and as
Production bugs are discovered and fixed. At the same time, the Development
version of the system continues to evolve with new functionality. This company
now has several individually changing versions of the system and faces a
challenge familiar to all companies, regardless of how many BI environments
they maintain: how to best manage changes in different versions of the system.One version of this common scenario is depicted in Figure 9
July 20th, 2004 at 9:26 pm
Oh dear, oh dear, oh dear.
This has been a problem with all of Oracle’s development products since I’ve been using them (coming up for fifteen years now). They consistently fail to provide any half decent configuration management support. For instance, Forms 4.5 had a hook into PVCS that was so bad it was unusable.
I did have high hopes when the Designer team started building SCM in conjunction with Netherlands Consulting’s Headstart offerings, but the relocation to California and mothballing of this set of tools appears to have put paid to that. See http://www.orablogs.com/duffblog/archives/000247.html for another depressing story.
I also take issue with the categorisation of ‘in development’ and ‘in production’ mentioned in the white paper. What they are essentially saying is that you should put up with a load of time consuming, repetitive, trivial and error prone migration procedures during your development phase because it wont last long, honest. In a good (dare I say Agile) environment, you are always changing things and any impediment to this will slow your project down. You will need to make changes after you go live, and what do you do when you start another iteration of your project? Because then it is in development and production at the same time.
It’s even worse in Oracle Applications (or whatever it’s called this week) so don’t get me started on that.
I’m off for a lie down with a damp Radio Times, thanks for indulging me.
July 20th, 2004 at 11:12 pm
Hi Andy,
Good points. I thought the article was worth pointing out, as it’s the first I’ve seen from Oracle that sets out their suggested way of doing things.
Having said that - I’ve yet to actually work on a DW project that *is* agile - most of them are still quite formal in their dev>test>prod migration path, and OWB hasn’t really been an issue in these circumstances. However, I (and customers I speak to) are keen to use a more agile approach to DW development, so it’ll be interesting to see how the tools handle more ‘unstructured’ ways of deploying to the various environments.
cheers
Mark
thanks for the feedback
Mark
July 21st, 2004 at 4:01 pm
Mark, true, I’d say that BI projects tend to the Agile though as they usually have several phases each dedicated to delivering specific functionality and driven by specific, strong, requirements (often report definitions). Hopefully they are also driven by the business.
In terms of configuration management I guess my point was really that Oracle don’t really get it. “What, you have more than one environment? How quaint!” seems to be the usual message out of Redwood Shores. Thus the amount of work you have to do to migrate something simple like a model in OWB is completely out of proportion to the end result.
It’s because of limitations like this that many project end up avoiding any sort of migration whatsoever. Then before you know it you end doing system testing in your development environment (it’s quicker to fix bugs that way) and then it’s only a short step to run your acceptance test there as well - then you migrate everything to production and it all falls over in a stinking heap.
Not that I’m bitter or anything …
July 21st, 2004 at 9:31 pm
Andy, again good points, appreciate the feedback.
I take it you’ve worked on Oracle DW projects, and I was wondering if you’ve used OWB (or indeed Designer) - if so, what would you do differently if you could shape their version control features? Have you had experience with other ETL tool version control features - if so, are their version control features any better, or is this a situation common to all ETL tools?
Mark
August 5th, 2004 at 3:40 pm
What I would like to see, is an extension of the snapshot facility in OWB which would allow you to logically define you different environments (dev, test, etc). This would be done so that the design repository contained the different version of each module, transformation, and what not, for each environment.
This way I would safely be able to deploy a module to production, knowing that the repository would save the previous version of the module. I would then be able to quickly and easily revert to the previous version if, as you say, Mark, it all falls over in a stinking heap.
August 9th, 2004 at 12:57 pm
Correction: Andy. Not Mark. Sorry.