MDX Debugging

MDX is a very powerful language for analyzing data and generating sophisticated reports. This power comes with a price; complexity and a steep learning curve. The icCube MDX Debugger allows users to better understand multidimensional queries besides simplifying finding and fixing errors that may occur.

The debugger shows you how the request has been parsed and executed by the OLAP kernel, decomposing the MDX queries into a tree like structure of MDX parts. Each one of this MDX parts has a list of parameters and a return a value. All this it's a bit abstract, so let's work with a simple example :

Troubleshooting: TopCount()

Our idea, is getting the highest sales month for the continent [America]. So we enter the current query :

        SELECT
            [Geography].[Geo].[America] on 0,
            TopCount( [Time].[Calendar].[Month].members, 1) on 1
        FROM
            [sales]

This shows an empty cell for [Jan 2010] month. This was not expected, so let's use the debugger to check :

As we can see in the debugger tree, topCount method does not evaluate any tuples and returns the first member of the level's members. Let's check on the documentation for TopCount() : we can see that when no [Measures] is specified TopCount() is equivalent to the Head() function. This explains why no tuple has been evaluated and we're getting an unexpected result.

Let's fix our query and introduce the [Measure].

        SELECT
            [Geography].[Geo].[America] on 0,
            TopCount( [Time].[Calendar].[Month].members, 1, [Measures].[Amount]) on 1
        FROM
            [sales]

The following picture is showing the difference in the TopCount() function call evaluation. We now can see all the tuples being evaluated to select the top month. You might notice as well the value of the tuple being calculated within the TopCount evaluation: 1023. This is not the same result shown in the pivot table result, why ? TopCount() is being evaluated when computing the rows axis and therefore is not evaluated within the context of the selected member in the columns ([America]). Therefore, TopCount() is using the default of the [Geography].[Geo] hierarchy which is the [All] member.

To confirm that, we can have a look to the cell computation and check the tuple value. Indeed, we can see that [May 2010] as result of the TopCount() evaluation is used; [America] being selected on the columns axis.

This is still not matching our initial request, we are not sure to get [America] top months. In order to get this we will need to use this new MDX request :

        SELECT
            [Geography].[Geo].[America] on 0,
            TopCount( [Time].[Calendar].[Month].members, 1, ([Geography].[Geo].[America],[Measures].[Amount]) ) on 1
        FROM
            [sales]

Next chapter: MDX Profiling is describing how to profile a MDX query.