Validating MDX Queries Without Running Them

Reposted from Chris Webb's blog with the author's permission.

Here's something interesting I came across while looking over some old documentation the other day: a way of checking whether an MDX query is syntactically correct without actually having to run it. You can do this by setting the Content connection string property. The default value for this property is:

Content=SchemaData

And this runs your queries as normal. For example, take the following query on the Adventure Works cube:

select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

On a cold cache I can see lots of activity on Profiler when it's run, as I'd expect:

However, with the connection string set as follows (see here for how to do this in SQL Server Management Studio; but beware - this bug is still around in 2012) :

Content=Schema

When I rerun the query on a cold cache I can see the MDX Script being evaluated but nothing happening for the query:

In SQL Server Management Studio the query is executed successfully but no results are returned; notice that in the Resource Usage event all the values are zero too.

If I modify the query to include an error, however:

select {[Measures].[Internet Sales Amount]} on 0,
blah blah blah
[Date].[Calendar Year].members on 1
from [Adventure Works]

I do see an error in SQL Server Management Studio:

This functionality could be useful in situations where you wanted to test the syntax of an MDX query or indeed just a calculation - it would allow you to do this without actually running the query and then killing it (and some queries don't die immediately when they're cancelled, as you might know).

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .