Migrating OBIEE Logical Models to use a Data Warehouse : Part 3

In the final part of my series on migrating an OBIEE installation from direct application access to a data warehouse, I'm finally going to plug my new data warehouse into my OBIEE logical model. Previous to this, in the first posting I created a single logical model over three different application data sources, and in the previous posting I migrated the data from these data sources into a data warehouse, using Oracle Warehouse Builder, and then imported this data warehouse into my OBIEE physical model. Now, I'm going to edit my logical model and make it point to these new warehouse tables instead.

Before I start, I review the new data warehouse tables I've imported into my OBIEE physical model.

Notice all the alias tables in there - I'm going to use those later on when creating separate logical dimension tables for each of my fact table date hierarchies. Now, I use the physical database diagrammer to check that all of my new warehouse tables are joined together properly.

This is particularly important as I've imported the views that OWB creates over dimension tables in instead of the actual tables themselves; this is because OWB in versions 10gR2 upwards adds additional rows (with negative dimension keys) into dimension tables for levels above the bottom level, so that you can join to the dimension at any level. For what I'm doing though, these extra rows are just confusing for users and so I import the views in instead, which filter these rows out. The views though, of course don't have FK relationships with the fact tables, and so I have to use the OBIEE diagrammer to create these for me.

Doing this is fairly easy as each dimension table/view that OWB creates has the same column name, "dimension key" as the primary key column, and I join these back to the fact tables on the relevant fact table columns. You don't join on the surrogate key columns, you join on these dimension key columns instead, which is a bit confusing first of all but when you get the hang of it, it (sort of) makes sense.

Now that my physical model is set up correctly, it's time to re-map the dimensions using new logical table sources. To take an example, my Jobs dimension logical table has a business key of Job ID and a number of attributes. I leave this business key and attributes in place, but add a new column called "dimension key" which will hold the new dimension/surrogate key that the warehouse dimension table will use.

Then, as the Job ID business key will no longer be the primary key for this logical column, I delete it and create a new primary key that maps to the new dimension key column.

Finally, I map the columns from the warehouse job dimension table onto the logical job dimension table, which adds this new warehouse table as the logical table source for this logical table.

This process is then repeated for the other dimension tables until they're all swapped over to use the new data warehouse tables. Then, I repeat the process for the fact tables, starting with the Appointments Fact table, which initially has four business keys that link it through to its logical dimensions.

I edit the logical table definition and remove these business keys, as I now need the fact table to join using the dimension keys I created earlier.

I then create a new primary key for this fact table that uses the new dimension keys.

Finally, I map the warehouse physical columns on to these new logical columns, so that the logical fact table's logical table source now points through to the warehouse fact table.

In most cases, I don't need to re-join the logical fact tables to the logical dimension tables as I previously created these joins as complex (logical) joins, which don't specify actual logical columns and instead use the joins found in the physical layer, which now use the dimension key joins that OWB created.

I repeat the process for the other fact tables, replacing business keys in the logical fact table with the new dimension keys that the logical dimension tables now use, like this:

Once this is all done, I review the final model prior to testing it all out.

Now, the final test: do the reports I created right at the start, which ran against my single unified logical model that took data from the three different source system, still work now I've re-wired the model so that it now points to the data warehouse? I start off by trying the simple tabular report.

Bingo. The report still runs and returns the same data as before. What about the crosstab report?

Nice one. It runs the same, with no changes needed to the report, as the only real changes I've made are to the table keys, with the logical model adjusting automatically itself to the new datatypes as I mapped in the new logical table sources.

So, what do I conclude from all this? Well, firstly, it was a lot easier to re-wire the OBIEE logical model than I expected, principally because I'd built it independently of the original source systems first of all, then mapped them in and then afterwards switched this to the warehouse later on. The main change that I needed each time was to bring in the new dimension key that OWB created for each dimension, but this didn't affect any of the existing reports as I could still keep the business key for each table, and the reports themselves didn't display any key values, they just used them to join to other tables.

This approach should also work for slowly-changing dimensions - a dimension where we tracked attribute value changes (a Type 2 dimension) would just end up with more than one dimension key value for a given business key, but it'd still join back the same way and values for a particular dimension member would be aggregated as long as the dimension key wasn't included in the report.

So, I was fairly pleased with this. The most time-consuming part was putting the OWB data model and mappings together, if Oracle can automate the process of generating the data warehouse schema and mapping data to it, this'll make the whole process much much simpler. As it is though, it's eminently do-able which will give me a lot more confidence proposing it at my talk later in the week.