OBIEE 11g: TopN and BottomN Functions are not Shipped to the Database (Doc ID 2008778.1)

Last updated on MAY 18, 2015

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

After migrating from OBIEE 10g to OBIEE 11g (11.1.1.7.x), it was observed that reports containing calculated columns with the TopN or BottomN function in the column formula were performing poorly. For example, a report with an execution time of 10 seconds in OBIEE 10g would now take 10 minutes to execute in OBIEE 11g.

In OBIEE 10g, the TopN/BottomN function was re-written to a RANK function that was shipped to / executed by the backend Oracle Database.

However, this did not occur in OBIEE 11g, although the TOPN_SUPPORTED and BOTTOMN_SUPPORTED database feature options were enabled / checked ON in the RPD; so that the Physical SQL returned a larger amount of data from the database that the BI Server would then have to filter / rank as per the TopN/BottomN function. 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms