OWB 11gR2 and BI EE - Creating a Business Model - Part 2

In the last blog entry here, i had covered the automatic derivation of BI EE business model from OWB 11gR2. Though it works out of the box, in most cases, users will like to have more control on the business model like logical table sources, level specification etc. OWB does provide a means to manually create the business model from scratch or update the derived business model. Either way, this is an excellent option which provides us with more control on the business model layer.

Lets take the OE schema this time to model in warehouse builder.

The first step is to create the business model and the corresponding logical tables that we need in the business model. In the OE schema we require 3 dimensions(Dim Order, Dim Product and Dim Customer) and a fact table containing 2 measures(Order Quantity and Order Volume). So, lets create the skeletal structure first for these 5 tables. Every logical table creation requires a corresponding source table to be provided. For example, if you require a logical table source containing the inner join of Orders and Order_items table, include both of them while creating the logical table(only related tables in the physical source can be chosen). OWB automatically recognizes the relationship between the tables and then creates an inner join. But unfortunately we cannot specify outer joins here (if the joins in the physical source are driven through constraints) which is possible through the BI EE Admin

Also OWB currently lacks the ability to create multiple logical table sources which is somewhat very limiting. Once all the logical tables have been created, the next step is to create the joins between the table sources. The good point here is that the joins options are comprehensive and more or less provides all the options that one will expect in the BMM layer.

The joins between the logical table sources can be snowflaked or can strictly follow a star. But what is not possible though is the ability to create multi-column primary-foreign key relationships (as i had explained in the previous blog). The rollups(or default aggregation) can be specified for all the columns. But only the lowest granular table rollup will work once the repository is created.

The next important aspect of the manual business model creation is the ability to create hierarchies or drill-paths. Hierarchy creation is quite straightforward though there are certain aspects that seem to be missing. I will list them here (based on my first impressions)

1. No option to create Grand Total levels
2. No option to specify Drill keys (not level keys)
3. No option to specify level based measures
4. No out of the box way to create multiple hierarchies and share them (though possible)

In our case, lets create a simple hierarchy for our customer dimension as shown below

As you see i have created 2 levels. Each level has a provision to choose the logical table. Then for each level, the columns can be assigned to that level from the chosen logical table. The next step allows us to specify the level keys for a level (no drill keys) as shown below

Once this is done, just create the subject area or presentation catalog and deploy the repository again. Remember, if you have an existing repository and you would like to add the repository objects created by OWB, you will have to use the Merge option within the BI EE Admin tool to merge repositories.