Validating MDX Queries Without Running Them

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).