Query Failed: [nQSError: 42043] An external aggregate is found in an outer query

Creating a table view with two levels of headers
May 18, 2009
How to reset/change the ‘Administrator’ password in OBIEE, BI Publisher and BI Scheduler
June 2, 2009

I thought this might be useful for someone struggling with Essbase.

Client was on OBI QF 170 and Oracle’s Hyperion Essbase 9.3.1 and running a report in Answers based on ASOSample Essbase cube which is generating an error.

In Answers Customer has created a report with a measure column (selected twice) which is being filtered with 2 different values using the filter function as below:

Add the 1st column
a. In Answers, Navigate to the Criteria Tab,
b. Select the column “Original Price”
c. On the column, click ‘edit formulae’,
d. in the edit formulae box, click the ‘Filter’ button
e. Insert a filter for the column, by selecting from the selection pane
column “Products”.”Product SKU” = ‘Boomboxes’.
Add the second column
Repeat the above steps a-d
In step e, Insert a filter for the column, by selecting from the selection pane column “Products”.”Product SKU” = ‘DVD’

The report can also be generated by directly entering the following SQL in Advanced Tab
Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘Boomboxes’ ),Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘DVD’ )
from ASOSamp

When the report is run the error message that is generated is as below

Query Failed: [nQSError: 42043] An external aggregate is found in an outer query

The MDX generated is

set [Products5] as ‘{[Products].[Boomboxes], [Products].[DVD]}’
set [Axis1Set] as ‘crossjoin ({[Age3]},crossjoin ({[Attribute Calculations2]},crossjoin ({[Geography5]},crossjoin ({[Income Level2]},crossjoin ({[Payment Type2]},crossjoin ({[Products6]},crossjoin ({[Promotions2]},crossjoin ({[Stores4]},crossjoin ({[Time5]},crossjoin ({[Transaction Type2]},{[Years2]}))))))))))’
{[Measures].[Original Price]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [ASOsamp.Sample]

By default in the OBI rpd, the Aggregation rule is set to “External Aggregation”. As per Engineering this needs to be change, so OBI EE knows what those rules are.

Filter By clauses are not supported by MDX (or Essbase), so OBI EE has to perform those internally. If the Aggregation rule is set to “External Aggregation”, then OBI EE doesn’t know what those rules are and hence throws this error.

It is possible to fix the error by doing the below

1. Changing the OBI Aggregation rule in both the Physical and BMM layer to the corresponding rule in Essbase, would stop the error.

2. Alternatively, using OBI Answers filter clauses would allow the BI Server to re-write the MDX to push down the filter to Essbase and may be more efficient anyway.

There was an enhancement raised within Oracle to address this matter in a future release, as the client stated that changing the aggregation rule in the rpd is not a suitable option.


  1. Do you know how many cases like this you will find? Literally dozens.

    And don’t count on Oracle. They won’t have a solution since I pestered them with this for the first time back in September ’08.

    What you CAN do however (and that’s highly useful) is to write MDX inside of an EVALUATE wrapper to get what you want.

    Fire me an email and I’ll get back to you with the statement.


  2. Andriy Yakushyn says:

    Thank you for the comment Christian! I tried to leave a comment at http://hekatonkheires.blogspot.com/2009/05/vendor-client-relatioship.html#comments but for some reason the link “Leave a comment” isn’t active.

  3. Hi Andriy,

    I only allowed commenting for registered users to quell the spam flood 😉
    Google, WordPress, LiveJournal, OpenID etc. is all fine.

    Otherwise shoot directly at christian DOT b DOT berg AT gmail DOT com.


Leave a Reply