Analysis Services · MDX

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

4 thoughts on “Validating MDX Queries Without Running Them

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Thanks Steve, that’s useful to know.

  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!

  2. This works in SSMS, but when I add it to a connection string in Excel, that I use to run MDX queries, it accepts Content=SchemaData, but throws an error when Content=Schema.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.