OWB 11gR2 and BI EE – Deriving the Business Model – Part 1

One of the most anticipated features(atleast for all BI EE users) of the recently released OWB 11gR2 is its ability to create a BI EE repository directly from its table/view/dimensional objects. This feature to derive BI specific objects was one of the very popular ones when a similar such integration was introduced for discoverer 3 or 4 releases prior to this(a long time ago). Mark has blogged about his first impressions here and actually points out to some of the issues that the current integration has. I agree with Mark on all the points and i will try to cover the details of the integration in 2 parts.

From my perspective, this integration is something that is very unique and actually warrants more study since this is the first time Oracle has released a tool which creates a repository automatically for a relational source. Prior to this the discoverer to BI EE migration utility was the one that came close to this though there are quite a few differences between both of them. There are 2 main aspects of this integration

1. Deriving or automatically creating the business model and then deploying the repository
2. Creating a business model manually and then deploying to a repository

I will cover the first approach in this blog entry. I will be using the standard SH schema to test this out. The steps involved in creating a BIEE repository from OWB 11gR2 is given below

Import the necessary physical tables into the OWB database module.

If constraints are not set at the database level, create them manually within OWB. There is no need for deploying these constraints back to the database as the derive module uses only the OWB metadata. The constraints determine the physical layer joins.

Create a BI EE specific module and then set the location. The location is nothing but the physical directory to which OWB would generate the UDML file of the repository. OWB provides 3 ways of creating the file. One is via the file system, the other is through a ftp (provide the url for ftp) and the third is through http. All the methods are supported.

Derive the imported database objects into the BI EE module created above.

Create a presentation catalog

Deploy the BI module. The deployment process is pretty straight forward. It generates UDML for every individual step and will then write-append to the same file. From the control center one can look at the individual udml code.

Use nqudmlexec utility to create a new repository and if required, merge this repository to the main repository

The steps above are pretty straight forward. Lets take a look at the repository created by the derive module. One of the things that struck me first was the way BMM joins were getting created. Generally in Business Model layer, it is a common practice to use complex joins  and drive everything else through logical table sources and physical joins. Logical foreign key based implementation is not used much as that constrains only one column to be part of a logical key. Strangely though OWB uses the logical foreign key based approach instead of complex joins.

So what this means is the fact that OWB uses the non-star approach to modeling the business model layer. The logical diagram would basically show you a snowflaked business model (notice the customer dimension)

Though the logical foreign key approach has some advantages the biggest disadvantage as stated above is one cannot use multiple column based keys. So, in order to test whether OWB automatically recognizes such issues, let us change the physical primary key of the countries dimension to include both the country id and the country name instead of the country name alone.

Lets derive the business model again and deploy the repository. Now, the business model fails the consistency check phase.

I was expecting OWB to be context aware and then switch over to complex joins in the BMM layer. Unfortunately, the rules seem to be more or less hard coded. But to OWB’c credit, the integration has opened up a completely different modeling perspective.

So, being a consultant one of the things that i wanted to get out of this exercise was to find out where the above integration can be put into practical use. So far, the concept and even the implementation to an extent is very promising. But again, as with any first release (BI EE – Essbase integration to cite an example), this requires the presence of a consultant or a developer who has a good knowledge on the BI Server modeling to use it in a production scenario(which is not bad as it works neverthless). For simple scenarios this works really well and hence can be recommended where a BI EE implementation is required without any seperate repository modeling time. One more positive aspect about this is, it reduces the BI EE implementation timelines to an extent as it does most of the grunt work.

I will cover the manual model creation (which is even more interesting and useful) next.