Executing an MDX query with XMLA

This question comes up  very occasionally and I have some sample scripts kicking around, but it always takes me a few minutes to find them, so I figured why not post a short sample here and let the magic of Google/Bing help me out next time.

I actually had a need of this last week, a colleague in another team was attempting to setting up some tests in cucumber (which uses Ruby) using the Olap4r library. By looking at the results returned from an XMLA command he was able to see the “structure” of the cellset that was returned which gave him enough hints about how the results are structured to get the tests working.

It’s also interesting (at least to me) to see the difference in the size of the response when you request a Cellset (which is the default for most OLAP clients like Excel) compared to a Flattened response (which is what you get when using the ADO connection in Reporting Services). You can see the “flattened” version of the response by taking out the commented section in the XMLA properties below.

Note that this format is not how the response normally travels over the “wire” to the client, see Mosha’s classic post for more detail about how the XMLA responses are transmitted over TCP/IP.

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
        <Command>
            <Statement>
                SELECT [Measures].[Internet Sales Amount] ON 0,
                [Product].[Product Category].[Product Category].Members ON 1
                FROM [Adventure Works]

            </Statement>
        </Command>

        <Properties>
            <PropertyList>
                <Catalog>Adventure Works</Catalog>

<!--
                <Format>Tabular</Format>
                <Content>Data</Content>

-->
            </PropertyList>
        </Properties>

    </Execute>
</Body>
</Envelope>

Print | posted on Wednesday, February 19, 2014 6:45 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)