Flying Visit, and Managing OWB10gR2 Configurations

It's currently Sunday lunchtime over in the UK, and I'm here on a flying visit in between visits to Austria and Slovenia last week, and Sweden next. I ran the BI Masterclass last week in Vienna and Ljubljana (photos here, here, here, here and here) and spent the evenings working on the OWB ETL and data quality chapters for my book, eventually flying back to the UK early on Saturday morning.

Working on the OWB ETL chapter has certainly been an eye-opener. I'm positioning the book as being a sort of "developers' companion" rather than a manual re-write and therefore took the decision early on to try and base the examples on what you see in the real world, rather than what you knock up in thirty minutes for a quick demo. My OWB setup, therefore, consists of three separate databases; an OTLP database that contains most of the source data, a Repository database that contains the design metadata and the staging schema, and a Data Warehouse Database that contains the ODS and Analyic schemas. I knew up-front that I was going to need Control Centers on both the Repository and Warehouse databases, as even though the 10gR2 repository is now "unified" (the repository tables now contain both design and control center tables) you need to create one on each database that you deploy to.

Where it gets tricky though is when you get to the point where you need to start deploying mappings and process flows to the other, "secondary" Control Center on the data warehouse database. By default, the Control Center Manager only controls the Control Center you're currently putting design metadata in to, and to start working with the other one, you need to create a new Configuration (a new 10gR2 feature), associate it with the other Control Center, and then switch to this other configuration.

When you start working with this new configuration though, and start up the Control Center Manager, none of the deployment locations you need to work with - the ODS one, the Analyic one - are listed. I was eventually able to get them to appear in the (otherwise blank) list of available deployment locations just right-clicking on them within the Design Center and then selecting "Deploy", but afterwards I found that I could double-click on the alternate Control Center in the Connections Explorer and then shuttle them across to the "available" area and tick the "Target" box to make them available.

It was all a bit complicated actually, and not something that you'd work out by just playing around with the interface. Creating a new Control Center was straightforward enough, as well as adding it to the Connections Center, but the bit around having to create a new configuration just to be able to deploy to a second database - surely the most common scenario - was very, very confusing.

Still, I think I've worked out the correct workflow when setting up OWB10gR2 when you're planning to deploy the main warehouse tables to a database separate to the one containing your main Control Center:

1. Create a repository on the database you're going to use as your "OWB Repository" database, and another one on the other database that you're going to deploy to. Whilst in the Repository Assistant, create and register with the Control Center the schemas that you're going to deploy to (staging on the OWB repository database, ODS and Analytic on the warehouse database, in my case)

2. Start up Warehouse Builder 10gR2 and connect to the repository on your OWB repository database. This is where you'll create your design metadata. Using the Default Configuration and Default Control Center settings, create your source modules and the staging target module, together with their locations. This default configuration will then "own" those locations.

3. Register the other Control Center (repository) using the Connection Explorer (top right-hand part of the screen), then create a second configuration that uses this other Control Center.

4. Using this new configuration, create the ODS and Analytic modules together with their locations.

5. Now you've set all the configurations and locations up properly, switch back to the default configuration and go and create the tables, dimensions, mappings and so on required for the project.

6. When deploying to the staging module, use the default configuration and control center as normal. When deploying to the ODS and staging locations though, switch first to the other configuration and then deploy using the Control Center Manager. The ODS and analytic locations should then be visible in the Control Center Manager; if they're not, go back to the Design Center and enable them by double-clicking on the relevant control center in the Connections Explorer, and select the locations using the second tab of the dialog.

Anyway, that's how I think it's meant to work. Tonight I'll be going back to the project and re-creating the repositories, locations and modules to get it laid out just right for the book examples, and then hopefully I can move on from working out the examples to writing the chapter proper. Tomorrow, I'm working during the day and then going up to Heathrow for my flight out to Stockholm in the evening; when I come back on Wednesday I've got a couple of days back in the office to do some prep for a "data warehousing for business users" course I'm writing for one of our university clients. That's it for travel then for a while; I've been to some interesting places recently (Chester, in the photo at the top of the page and in this flickr group, being one of them), but it'll be nice to be back home for a while after being on the road for the best part of four weeks.