OWB 11gR2 and BI EE – Creating a Business Model – Part 2
September 16th, 2009 by Venkatakrishnan J
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.
September 16th, 2009 at 10:49 pm
Hi Venkat
For the comment ‘ability to create multiple logical table sources’ what exactly do you mean here? You can create a logical table from multiple physical or multiple logical tables, I didn’t quite get what you meant.
Although the wizard provides a fairly dumbed down drill path creation in the editor there are additional properties for setting in the property inspector. So for example you should be able to set a drill level as a ‘Grand Total Level’ (see the Property Inspector), don’t map it to a logical table, and set its business name to a user-friendly name.
For drill keys is this not just the ‘Use For Drilldown’ property (use Property Inspector again) on the OWB Drill Level Key object?
Cheers
David
September 17th, 2009 at 7:00 pm
@David – Multiple logical table sources are used within a single logical table in many cases like Fragmentation. You would find that within the sources tab of a logical table. I dont mean multiple logical tables or a logical table from multiple physical sources. Instead, i mean multiple logical table sources used for a logical table.
Thanks for the comment on the Grand Total though. I missed it (it should typically have been available while creating the level though).
I dont see a property called “Use for Drilldown” in the property inspector? Are you sure its there in the property inspector?
-Venkat
September 18th, 2009 at 6:16 pm
Hi Venkat
On the level keys tab when you select a key the PI has a ‘Use for drilldown’ this should switch the use of the key from level key to drill key.
On the multiple logical tables, you can build a (complex) logical table from multiple logical tables, is this what you are after?
I think it’d be better if the wizard provided some richer functionality so you didn’t have to go fishing around after, agreed.
Cheers
David