OBIEE bypasses smaller aggregate table and queries largest aggregate table

707402

Hello,
Currently we are experiencing something strange regarding queries that are generated.

Scenario:
We have 1 detail table and 3 aggregate tables in the RPD. For this scenario I will only refer to 2 of the Aggregates.

Aggregate 1 (1 million rows):
Contains data - Division, Sales Rep, Month, Sales

Aggregate 2 (13 milliion rows):
Contains data - Division, Product, Month, Sales

Both tables are set at the appropriate dimension levels in the Business Model. Row counts have been updated in the physical layer in the RPD.

When we create an answers query that contains Division, Month and Sales, one would think that OBIEE would query the smaller and faster of the two tables. However, obiee wants to query the table with 13 million records completely bypassing the smaller table. If we make the larger aggregate inactive, then OBIEE queries the smaller table. We can't figure out why OBIEE wants to immediately go to the larger table.

Has anyone experienced something such as this? Any help would be greatly appreciated.

Edited by: gwb on Aug 19, 2009 7:45 AM

gerardnico

Have you try to change the sort order of the logical table source in your logical table ?

!http://gerardnico.com/wiki/_media/temp/obiee_logical_table_sources_sort.jpg!

Set the Aggregate 1 first.

Cheers
Nico

707402

Nico,
Thanks for the reply. I'm sorry I had failed to mention before that I had already ordered the aggregates in the way you have described. The smaller table is still bypassed.

720181

Can you check if all the columns are properly mapped to both aggregates?

One possibility might be the a column used in the report is only mapped to larger aggregate?


Manohar