OBIEE 11g: TopN and BottomN Functions are not Shipped to the Database
Last updated on MAY 18, 2015
Applies to:Business Intelligence Suite Enterprise Edition - Version 18.104.22.168.0 and later
Information in this document applies to any platform.
After migrating from OBIEE 10g to OBIEE 11g (22.214.171.124.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.
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