Migrating OBIEE Logical Models to use a Data Warehouse : Part 3
March 17th, 2008 by Mark Rittman
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.
March 18th, 2008 at 2:08 am
Mark,
How is your book on OBI coming along ?
I just placed an order for your book on Amazon.com, Is it true that your book is coming out on Mar 31st, 2008
Could you please provide the Table of Content for your book.
-Chenny
March 18th, 2008 at 9:14 pm
Mark:
It’s interesting how OBIEE’s use of the business model, and it’s insistence on mapping that layer as a star schema, eases the user through this process. In my experience, when reporting is done against an operation system, the proclivity seems to be to build “summary tables”, with the measures and dimensions all existing in the same table. Instead, OBIEE requires the “upfront” work that occurs when we build data warehouses, most notable, the conforming of dimensions. This makes the movement to a data warehouse seem almost painless, especially in contrast to a “Crystal Reports plus Summary Tables” approach that seems common with the clients I’ve had.
The other thing I couldn’t help but notice is how limitless the upside is for the Oracle BI stack. Several of the steps that you had to go through in these three parts are repetitive across the different pieces of the stack, but Oracle could go a long way to reducing that repetition in future releases. The first example is with the source-to-target mappings. In your examples, you basically had to do that process twice… in Parts 1 and 2. Once again, the business model to physical model mapping that occurs in OBIEE closely resembles what occurs with development in ETL tools, and I can envision a right-click option that says “Export as ODI mapping…” or “Export as OWB mapping…” in the business model explorer. The second example is with hierarchies, which can be one of the most cumbersome and difficult aspects in building a dimensional model. Since OBIEE requires this work to be done, Oracle could make these hierarchies exportable as dimensions for the database and dimensions/hierarchies for the accompanying ETL tool.
In my experience, every step taken down the path of reporting against an OLTP system was also a step taken away from building a data warehouse. You’ve made a strong case that the “quick win” need not be at the expense of the bigger picture. I also now feel obliged to apologize for calling this approach “philosophical” in one of my first postings, especially after seeing a complete real-world example.
March 18th, 2008 at 11:17 pm
Hi Stewart,
A very thoughtful reply, thank you. I especially like your observation about OBIEE encouraging the creation of a dimension model, vs. as you put it a series of summary tables with all the reporting data lumped in together.
Thanks for the comment about the approach not being so philosophical now - this was something I was aware of actually, i was due to present this approach at a user group event today and was conscious that it might come across as one of those “pie in the sky” architectural talks that had no real grounding in reality, hence my steps to put together a worked example. Glad it was of use.
regards - Mark
March 19th, 2008 at 3:31 pm
As I’ve given this a little more thought, there were a few more comments that I wanted to make, and at the expense of possibly monopolizing the posting, I decided to post them anyway.
I mentioned that the source-to-target mapping that occurs in the OBIEE business model is very similar to what occurs in ETL tool development. This coupling would actually break apart if the ETL were complex enough to require staging tables between the source tables and the target tables. The OBIEE business model would only map to whatever staging tables touched the data last… and the data lineage would no longer extend all the way back to the source.
Also, you mentioning having to give the SCD issue a little more thought. As Jon knows, I’m not a huge fan of the way OWB does SCD’s, but regardless, the extra columns generated (the level keys, the solved dimension records, etc.) are not columns used for reporting… instead, they are columns used for ETL, and I don’t think you analysis needs to say much more about them than it already does. The same can be said, for the most part, for the common SCD columns of CURRENT_INDICATOR, EFFECTIVE_DATE and EXPIRE_DATE. There are situations where these last three can be used in reporting, but these situations are more the exception than the rule. I have a similar opinion of the surrogate key versus natural key issue… the introduction of the surrogate in moving to a data warehouse is a logical implementation, and has no bearing on the analysis of measures across dimensions.
The existence of the additional SCD2 records changes the discussion slightly… instead of simply discussing FORM, we are now discussing CONTENT… what the different columns mean, instead of just a declaration of where they came from. If the OLTP system timestamps every record and maintains complete history across these changes, it’s conceivable that an OBIEE business model could actually show the data with Type 2 changes. As this type of scenario seems less likely without bona fide ETL, you may just want to leave the issue as a footnote about what the data means once we get to Part 3. A report detailing sales of blue sweaters in Georgia would look exactly the same regardless of whether we are reporting against the OLTP system or the data warehouse. However, what the report actually MEANS changes dramatically, especially if the OLTP system only stores the current address for each customer.
March 19th, 2008 at 3:58 pm
In the last paragraph, when I said the report would look the same… I meant the visual representation… column headers, number of rows, groupings, etc. The actual data (value of the SUM aggregation), however, would obviously disagree when run against the OLTP system and the data warehouse.
March 24th, 2008 at 9:56 am
Mark,
As always, it was wonderful reading your blog especially the posts on next generation OBIEE architecture.
Right now, am currently working on a project where the customer wants to consolidate around 30 odd marts across locations onto a OBIEE environment and this has to evolve over a period of time. Your thoughts are really valuable in that regard.
Having said that, my situation is a little different with the customer going in for packaged OBIEE datamarts on Financial/Sales/Procurement intelligence. Does your approach change (drastically) when packaged marts are considered? Can you pls. throw some light on this? Thanks.
Thanks again for your wonderful blog posts.
Regards
Karthik
March 27th, 2008 at 11:11 am
@Karthik
Our spam filter delayed your posting - I suspect it was trying to be too smart with words like customer and finance!
By “packaged marts” do you mean a bought in physical/logical/user OBIEE model for specific applications?
March 27th, 2008 at 9:50 pm
Hi Karthik,
Thanks for the kind words about the blog articles, it’s appreciated.
If you mean what i think (and Pete suggests) you mean, you’re looking at, for example, using the BI Apps to fast-track your BI development. This is a good idea in my view, I left this out of the presentation but really I should have added it in, to show how you can speed up the whole process of going from direct application access to a data warehouse, in this case by deploying the BI Apps (or any other packaged DW solution) rather than building your own. I think this is a sensible way to swap money for development time and deliver your future BI system faster.
regards, Mark