Essbase ASO reporting issue with SmartView

I haven't posted anything for almost a year because my projects shifted from Oracle EPM to Microsoft BI and so I had nothing to write about in this blog, but recently I came back to the world of Essbase and SmartView for the sake of a small but interesting project. To cut the long story short, I think I've bumped into a nasty Essbase bug.

You may have already known that SmartView uses MDX queries (when reports are assembled with Query Designer or Smart Query) on Essbase cubes that you can trace with the TRACE_MDX option since 11.1.2.2, if you haven't then see the details here.

Guess the difference between these traces:

===============================================================

Following MDX query executed at Sat Jul 27 15:32:41 2013

===============================================================

SELECT NON EMPTY 

 { CROSSJOIN( { [Original Price] , [Price Paid] , [Returns] , [Items per Package] , [No. of Packages] } , CROSSJOIN( {(LEAVES([Geography]))} , CROSSJOIN( { [Under 20,000] } , CROSSJOIN( {(LEAVES([Payment Type]))} , {(LEAVES([Age]))} ) ) ) ) }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME] ,[GEN_NUMBER],[LEVEL_NUMBER]   ON ROWS,  

 { {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER]  ON COLUMNS  

from [asosamp].sample 

WHERE  {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )}  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [12.158] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:09 2013

===============================================================

SELECT NON EMPTY  

 { CROSSJOIN( { [Original Price] , [Price Paid] , [Returns] , [Items per Package] , [No. of Packages] } , CROSSJOIN( {(LEAVES([Geography]))} , CROSSJOIN( { [Under 20,000] } , CROSSJOIN( {(LEAVES([Payment Type]))} , {(LEAVES([Age]))} ) ) ) ) }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME] ,[GEN_NUMBER],[LEVEL_NUMBER]   ON ROWS,   

 { {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER]  ON COLUMNS   

from [asosamp].sample  

WHERE  {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )}  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [0.001] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:30 2013

===============================================================

SELECT NON EMPTY 

 { CROSSJOIN( { [Original Price] , [Price Paid] , [Returns] , [Items per Package] , [No. of Packages] } , CROSSJOIN( {(LEAVES([Geography]))} , CROSSJOIN( { [Under 20,000] } , CROSSJOIN( {(LEAVES([Payment Type]))} , {(LEAVES([Age]))} ) ) ) ) }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME] ,[GEN_NUMBER],[LEVEL_NUMBER]   ON ROWS,  

 { {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER]  ON COLUMNS  

from [asosamp].sample 

WHERE  {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )}  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [12.465] seconds ===================

===============================================================

Following MDX query executed at Sat Jul 27 15:33:36 2013

===============================================================

SELECT NON EMPTY  

 { CROSSJOIN( { [Original Price] , [Price Paid] , [Returns] , [Items per Package] , [No. of Packages] } , CROSSJOIN( {(LEAVES([Geography]))} , CROSSJOIN( { [Under 20,000] } , CROSSJOIN( {(LEAVES([Payment Type]))} , {(LEAVES([Age]))} ) ) ) ) }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME] ,[GEN_NUMBER],[LEVEL_NUMBER]   ON ROWS,   

 { {DESCENDANTS([MTD],[MTD].DIMENSION.LEVELS(0))} }  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME],[GEN_NUMBER],[LEVEL_NUMBER]  ON COLUMNS   

from [asosamp].sample  

WHERE  {( [Digital Cameras] , [No Promotion] , [004118] , [Sale] , [Current Year] )}  PROPERTIES [MEMBER_ALIAS],[MEMBER_UNIQUE_NAME]

=== MDX Query Elapsed Time : [0.001] seconds ===================

Let me point out that all of them are the same but it took 12 000 times longer to execute the 1st and the 3rd than the other 2.

Let me also point out that the queries contain stored leaf members only so there is no aggregation whose performance could be different using different formulas or cache settings or aggregate views. In fact the 4 query were fired in a short period of time and the result of them were an empty set since the built in ASOSample.Sample application contains no sample data for the particular part of the cube.

Then what could cause the 12 000 times longer execution?

Well the 1st and the 3rd was started from SmartView while the 2nd and the 4th were from EAS Console. It makes even weird that it does not matter how many times I run the test I always got similar results.

Ok you could say that the connection that SmartView and EAS Console use to Essbase server are different and so can be the reason for the discrepancy. Well SmartView and EAS console was running on the same client desktop and since the result set is empty (so the amount of data transferred back to the clients were almost nothing) it could not result such a big difference in my opinion.

If I include more leaf members in the query the difference gets even worse (while the result set remains empty).

I've tested it on 11.1.2.2 and 11.1.2.3. Both versions behaved the same way, while 11.1.2.1 crashed on the query...

I simply can't explain so raised an SR to Oracle...

UPDATE: Oracle has accepted this issue as a bug.