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:


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


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

3 thoughts on “Validating MDX Queries Without Running Them

  1. Great tip Chris, because I’ve previously tried to do this with command prepare statement… Just to find out it was still executing the query. :( We’ll test this one soon. Seems perfect!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s