KB208436: An MDX statement run in MicroStrategy 9.4.1 returns different results than the same MDX statement run directly in Oracle Essbase 9.x or newer.

• Strategy

Article outlines why a MicroStrategy report run against an Oracle Essbase database and executing the same MDX directly in Oracle Essbase (e.g. via Essbase Administration Services) return different result sets.

SYMPTOM:
Executing a Strategy report against an Oracle Essbase database and executing the same MDX directly in Oracle Essbase (e.g. via Essbase Administration Services) return different result sets.
CAUSE:
MDX queries are treated differently depending on the Essbase “MDX mode”.
Essbase has a particular proprietary way of treating levels in a hierarchy: generations are top-down (numbered from the root), starting from 1; levels are bottom-up (numbered from the leaves), starting from 0:

(Image source: http://docs.oracle.com/cd/E12825_01/epm.111/esb_dbag/frameset.htm?dinconc.htm#dinconc15657).
This is in contrast with the XMLA standard way of treating levels with a top-down approach in a hierarchy, starting from 0.
Strategy follows the XMLA approach for MDX. In Essbase 9.x or newer this is supported, as MDX statements are supported in an XMLA-compliant way. However, in Oracle Essbase’s own MDX tools, the MDX is executed by default in a non-XMLA-compliant way. Therefore, the MDX generated in Strategy will still be executed, but it will return data from different levels.
ACTION:
This is by design and does not depend on Strategy.
WORKAROUND:
A possible workaround could be to manually modify the MDX statement prior to execute it directly in Essbase. In order to have the same results returned as in Strategy, all levels need to be re-numbered. For example, if a dimension has 5 levels, the mapping would be as follows:

Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150
Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150	Execution Time PARAM_QUERY_ROWSET_SIZE = 32768 rowsPARAM_QUERY_ROWSET_SIZE = 1048576 rowsSpark ODBC24 minutes, 10 seconds2 minutes, 11 secondsImpala ODBC15 minutesThis driver currently does not support a large ROWSET size. Strategy is working with the vendor to resolve this issue. Get Started Prepare Data ⟩ Create Cards ⟩ Share Cards ⟩ Administer ⟩ XMLAEssbase051423324150

Note: this is a simplification. In the case data is missing or ragged hierarchies are present, then users may still obtain different results when executing the MDX directly in Essbase.

Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

Comment

0 comments

Details

Knowledge Article

Published:

May 24, 2017

Last Updated:

May 24, 2017