OBIEE Aggregate Navigation (not hitting?)

Often you will find people complaining that they have setup aggregate navigation, but when they run report, OBIEE is not hitting the right fact table.

Common suggestions you will find to resolve this are:

• Go to Content tab of the Logical Table Source and check if Logical Level are set correctly set.
• Go to General tab of Dimension Level and check the value in textbox "Number of elements at this level"

In this post, I am just going to show that even after following above, the query may not hit aggregate table if you are not following modeling practice right.

Let's take following data as example. There are two sales fact tables and a time dimension. DAILY_SALES has day level sales data. WEEKLY_SALES has sales data pre-aggregated at week level.

Here's repository created on top of above physical data. The Logical Level is set correctly for WEEKLY_SALES.


Even the number of elements are set correctly for Week and Day level of Time dimension.




Let's execute following query which fetches sales by week. Since we have setup aggregate navigation, it is expected that the query should fetch data from WEEKLY_SALES table.

SELECT "Time"."WEEK_DESC", "Sales"."SALES_AMT" FROM SALESCATALOG

Now have a look at NQQuery.log. The query didn't hit WEEKLY_SALES table, in fact it is grouping data form DAILY_SALES table instead.


So what went wrong?
Our query has two columns WEEK_DESC and SALES_AMT. If you see the earlier repository screen carefully, you will see that WEEK_DESC attribute is not associated with any level of the Time dimension, so OBIEE assumes it to be most granular level attribute i.e. day level. When we query data by WEEK_DESC, OBIEE goes to fact table with day level granularity (i.e. DAILY_SALES) and fetches the data.

Let's add WEEK_DESC to Week level of the time dimension as shown below and execute the query again.


Now let's see NQQuery.log again. Bingo! it just hit the table we wanted it to.



Alternative approach with Snow-flake:
In the example I have given, the Time dimension is snow-flake, so it is also possible to set the logical level for invidual source table to make aggregate navigation work. Setting logical level "Time Week" for source table TIME_WEEK and "Time Detail" for source table TIME_DAY will do the work.

I hope this post will give a different perspective to your troubleshooting effort when you face that aggregate navigation problem again.