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

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

Client was on OBI 10.1.3.4.0 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
select
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

With
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]}))))))))))’
select
{[Measures].[Original Price]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [ASOsamp.Sample]
Cause

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.
Solution

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.