When Micro strategy is used as a front end tool with BW queries acting as a Data provider, there is a probability that you might run into an error “MDX results contains too many cells (more than 1 million)”.
This happens when a BW query set results in more data than an MDX (Multidimensional expressions used by Micro strategy) can handle, 1 million cells (999999 to be exact)
The main reason for this issue is that the fields TUPLE_ORDINAL, COORDINATES and CELL_ORDINAL are defined as NUMC data types with length set at 6.
To eliminate this issue we have to use the NEW BAPI’s as opposed to the older ones.
RSR_MDX_CREATE_OBJECT (corresponds to BAPI_MDDATASET_CREATE_OBJECT)
RSR_MDX_GET_AXIS_INFO (corresponds to BAPI_MDDATASET_GET_AXIS_INFO)
RSR_MDX_GET_AXIS_DATA (corresponds to BAPI_MDDATASET_GET_AXIS_DATA)
RSR_MDX_GET_CELL_DATA (corresponds to BAPI_MDDATASET_GET_CELL_DATA)
RSR_MDX_GET_FLAT_DATA (corresponds to BAPI_MDDATASET_GET_FLAT_DATA)
RSR_MDX_GET_FS_DATA (corresponds to BAPI_MDDATASET_GET_FS_DATA)
These New BAPI’s come in as part of Support Package 02 for SAP NetWeaver BI 7.01 (SAPKW70102)and Support Package 00 for SAP NetWeaver BI 7.11 (SAPKW71100)
Well, the glitch here is that SAP strongly recommends using older BAPI’s and doesn’t recommend MDX Interface for mass data retrievals/access.
In order to Cross check if Microstrategy calls the old BAPI’s or the new Ones, an RSTT trace has to be performed, Once performed, you can you can check in transaction RSTT ,In the area Traces or the area trace collection, choose ‘display’ for the newly recorded trace. In the tab ‘Recorded Program Objects’ it will be shown which BAPIs / Function Modules have been called.
How to run MDX query in BW environment
Step1: run the T-code MDXTEST
Step 2a: you can copy the SQL statement produced in the Microstrategy in the right window as mentioned in the below screen and run it.
Step2b: here, there are two things that need to be understood. In the screen below, Catalog is analogous to CUBE/Multiprovider in BW and Cube is analogous to Query in BW.
Select the catalog and the Cube and click on the run the query