A First Look at OWB11gR1

I finally got around to installing Oracle 11g earlier this afternoon, and I was pleasantly surprised to notice that Oracle Warehouse Builder gets installed by default:

For someone like myself who makes most of his living from either consulting in, or training in, Warehouse Builder, this is of course good news as it gets the tool into lots more people's hands. If I remember correctly back to Jean-Pierre Dijcks's presentation at ODTUG Kaleidoscope 2007, Oracle 11g comes with a pre-installed repository and Workflow already installed and configured, so hopefully the "out of the box" experience should be pretty good and all of the Warehouse Builder components should work right away. My recollection is that OWB11g uses a single repository, and uses the Oracle database "workspace" feature to create versions of it that are used by different OWB projects - these workspaces are the equivalent to the repositories that we create now. As well as this, the 11gR1 version also has a number of bug fixes, support for LOG ERRORS (which was back-ported to the 10.2.0.3 version as well), and a Siebel connector. So, let's fire it up and take a look.

Starting up the Design Center, the logon dialog has had a slight change - it now says "Workspace Management" on the button that previously said "Get Started", which launched the Repository Assistant in 10gR2. Workspaces are obviously the equivalent of repositories, except they're all stored in a single schema called OWB_SYS. I press the Workspace Management button, which launches the Repository Assistant (perhaps this should be renamed "Workspace Assistant"?)

On the first page of the wizard, you're prompted for the database hostname, port and service name. I enter this and move on to the Choose Operation page.

On this page, I can either manage a workspace, manage a workspace user, add languages to the repository or register a RAC instance. Now I think that what's going on here is that I can add multiple workspaces to the same, single repository, add a language to this single repository, and make it work with RAC, so I choose the first option and manage a warehouse builder workspace. The next page gives me the option to create a new workspace or drop an existing one, so I choose the first option and create a new workspace.

The next page gives me the option to create a new workspace with a new user to own it, or to create a workspace and have an existing user own it. As I said, what I think is happening here is that a logical sub-repository is being set up in the central repository, and an Oracle account is being set up to own this sub-repository. In a second I'll take a look at the user account that's been created, I suspect it'll be otherwise empty and will just have permissions on the new repository workspace.

The next page asks me to enter the username and password of a DBA account (I use SYSTEM) and then asks me for three bits of information : the new user account's username and password, and the name of the workspace (a repository in old terms, but stored as a sub-repository in 11g's central OWB repository). I call the new user OWB_REPOS_11g and give the workspace the same name.

On the next page, I get asked for the password for the OWBSYS schema - this must be where the master repository is, which is then carved up to provide all the workspaces that then get created. I guess the rationale here is to make it such that OWB is ready-installed and configured in your Oracle database, the repository data is all in the same, single place (like it is with all the other tools and add-ons for the database) and it's just all "ready to roll". I put in the password "password", which is what I've set all my other passwords to be during the database install process, but it doesn't let me in .... what could the password be?

As a workaround, I go into SQL*Plus and issue the command

alter user OWBSYS identified by password;

which resets it to "password" for me, but it's a bit annoying as I set all my passwords to "password" earlier on. Anyway, this time it works, and I'm then prompted for the usual language selection for the repository, whether I want to create any target user accounts to get created along with the repository (sorry, workspace) and then the wizard goes off and creates it for me.

Watching the progress bar, it looks like the wizard is actually installing the OWBSYS schema now, I wonder if it waits for the first person to run this wizard before it puts the repository tables into OWBSYS? I'll have to create another workspace later on, see if it goes through the same steps.

At the end of the process, I get a message saying that the workspace installation is successful. It's still a bit confusing, so I go and create another one (this one I'll call OWB_REPOS_11g_PROD) and see what happens.

Ah, this time round it only take a couple of seconds to create a new workspace. So, the first time around it obviously installs the main OWBSYS central repository tables, subsequent workspace installs after that just create the logical container. If I take a look at what's in the OWB_REPOS_11G_PROD schema, it's actually empty:

SQL> conn owb_repos_11g_prod/password@ora11g
Connected.
SQL> select table_name from user_tables;
no rows selected
SQL>

which suggests that this account just owns the workspace in OWBSYS, rather than it containing repository tables itself. I wonder if the repository table structure in the OWBSYS schema is any different to the tables in a 10gR2 repository schema, or whether it's the same, it's just "partitioned" into separate virtual repositories using the database workspace feature, and is otherwise the same? I'll have to ask JP when I see him on Sunday morning, at the ODTUG BI&DW SIG meeting.

Anyway, let's start her up and see what's changed.

Well not much, actually, although this was never meant to be the release that has all the new "talked about" 11g features - logical mappings, real-time mappings, ODI Knowledge Module integration, SOA integration and so on - they're all coming in 11gR2, or a later release. In this version though, there's a new connector, through to Siebel (Siebel CRM I presume, rather than Siebel Warehouse or Siebel Analytics), and at the top of the screen it shows your logged in name and the Workspace name as well.

One thing I couldn't find was any pre-integration with Workflow - from poking around the schema names on the 11gR1 database, it doesn't look as if Workflow has been pre-installed (something I heard might happen in 11g), which still means I guess that I'll need to download the Companion CD and install it off of there, and run the Workflow Configuration Assistant afterwards. Ah well, you can't have everything.

In terms of other new features, I'll have to take a look through the docs, but I get the impression this release is more about 11g database compatibility, the pre-installed repository now held in a single schema, the Siebel Connector and a few features and bug fixes (DML Error Logging, SCD2 handling working properly) that are more a case of finishing off what was delivered as part of 10gR2.