OBIEE Step by Step Guide

April 20, 2010

Migrating BO Aggregate Awareness Functionality to OBIEE

First of all, as of this writing, lets make sure that we are on the same page that there is no automated tool that was developed to move a BO universe and convert that into a OBIEE repository. With this premise set, we need to walk through various features that are supported in Business Objects and lets see how we can convert them into OBIEE.

Are you worried on how to replicate the use of BO XI function “aggregate awareness” in OBIEE..

Let me assure you that its “No worries”.

Lets look at what aggregate awareness does in Business Objects. The use of this function lets you use materialized views for quick query execution. The syntax for this is

@Aggregate_Aware(sum(agg_table1.column), sum(agg_table2.column1))

This syntax will force the use of agg_table1 for summarization else use agg_table2 for summarization.

Also, the aggreagte awareness function also lets you choose compatible measures and incompatible measures. This way, if the measure is found to be a compatible measure, it tries to use the aggregate tables and for incompatible objects, it directly goes to the base tables.

Steps involved in enabling aggregate awareness are (credits to Complete Reference Business Objects Reference by Cindi Howson) are

1. Work with the DBA to create aggregate fact tables.
2. Map the star schema to understand which data columns do not work with the aggregate tables.
3. Add aggregate awareness to measure objects.
4. Specify which objects are incompatible
5. Test and View SQL statements.

There is also debate whether materialized views should be tool independent and should be dealt with in the database for any incoming query. Checkout this link for the discussion.

Now, lets move on to OBIEE and see how it is dealt here. Can you replicate the same comcept in OBIEE like how it is dealt in BO. Simple and straight answer is “Yes”.

How does it work in OBIEE? Well, have you heard the term “Aggregate Persistence” wizard in OBIEE. This is a new feature in OBIEE 10.1.3.2 and later. Yes, I could start talking about this from the very start but thought its better I redirect you to this beautiful post by Mark Rittman. Also, dont forget to read the comments section below which enlightens you a lot.

Here is the link.

Now, you might ask how should I mark my incompatible measures in OBIEE like I did in BO. Read carefully and you would come to a conclusion that OBIEE creates aggregate tables for the measures you are interested in and not for all measures and its really not using the materialized views like we did in BO. It takes a slightly different approach as is mentioned Mark Rittman’s blog.

Until next time, kudos to all OBIEE evangelists out there and also don’t forget this other blog on conversion of BO Filters and Prompts to OBIEE filters and prompts. Follow this link.

Advertisements
Older Posts »

Create a free website or blog at WordPress.com.