Using OBIEE against Transactional Schemas Part 1: Introduction

What’s the pervasive myth surrounding the Oracle Business Intelligence Enterprise Edition (OBIEE) product suite?

OBIEE is only useful for reporting against star schemas.

It’s true that the Business Model and Mapping (BMM) layer in OBIEE always presents a dimensional model, or star schema, to report developers, but our imagination, plus some real chops in metadata development, are the only ingredients required for reporting off any kind of model. The best practice in business intelligence delivery is always to build a data warehouse. Even with the rise of game-changing technologies such as in-memory analytics beasts like Exalytics, or database monsters like Exadata, this simple best practice hasn’t changed for the most part. All things equal, a data warehouse is the best location for important business data to drive analytics. The landscape is certainly shifting though, and the power of engineered systems from Oracle combined with metadata-induced BI Tools like OBIEE 11g should give us pause when considering future architectures. Are we now primed to see a return to strict transactional reporting?

Maybe. Pure transactional reporting is problematic. There are, of course, the usual performance issues. Equally troublesome is the difficulty in distilling a physical model down to a format that is easy for business users to understand. Dimensional models are typically the way business users envision their business: simple, inclusive structures for each entity. The standard OLTP data model that takes two of the four walls in the conference room to display will never make sense to your average business user.

So why would we want to report against transactional schemas? One reason is a simple lack of conviction that all the hard work required to deliver a conformed data warehouse can deliver the transformational power that the business seeks. Perhaps we just want to get our feet wet before diving head-first into a full-blown data warehouse delivery project. Budgetary restrictions are another common reason. Similarly, it’s hard to wash the stench off of failed data warehouse projects, and CIO’s are hesitant to undertake another expensive BI project when a previous failure is still in the rearview mirror. The simplest reason, however, is that we can. The power of the OBIEE 11g semantic layer gives us the opportunity to hide the dirty business going on underneath our complex metadata layer. We should feel empowered to make the leap and deliver reports to users quickly, without iterations and iterations of ETL. Besides, the OBIEE product has impressive roots in delivering transactional reporting, which Mark has described in-depth before.

Over the next few posts, I'll describe some techniques for OLTP reporting using OBIEE 11g. I've blogged about transactional reporting before (in a way), as a part of the ExtremeBI Agile methodology I've championed over the last few years. But I didn't go into a lot of detail about specific techniques, so that's what I really want to focus on with this new series of posts. For those of you who have seen me speak before, or read any of my white papers or other blog entries, you know I have a hard time getting through any of these without mentioning the Oracle Next-Generation Reference Architecture. This blog post is no different. If we were to use GoldenGate to stream our transactional changes to a foundation layer instead, as Michael Rainey described in his series of blog posts on the subject, we would be able to do a lot more interesting things with our transactional schema. However, for the purpose of this series, my demonstrations will be directly against source systems.

Speaking of source systems, I wanted to give a teaser for the transactional systems I'll be using as examples for these posts. I settled on using a few of Oracle’s public domain pre-canned Application Express (APEX) applications and the accompanying schemas, which used to be available for free as a download, but they don't seem to be available anymore. The first I'll use is called Customer Tracking, which is a very simple CRM of sorts which has just the right amount of challenges with a degree of difficulty that would make for a hearty introduction. It uses a third-normal form (3NF) data model for most purposes, and this is exactly the type of schema that most of us find ourselves trying to model in OBIEE when we are modeling transactional schemas.

I'll be using another of the public domain Oracle APEX applications to describe some other techniques when the application schema isn't really normalized at all: the Ask the Expert application, which many of you will recognize as the application used to power the AskTom website. I couldn't resist putting "Ask Stewart" at the top: every Oracle Database junkie has dreamed of seeing their name at the top of that site.

So keep a lookout for Part 2, where we'll jump immediately in some of the best practices and techniques.