MDX formatting and the changing toolset

Reporting Services includes a very useful visual MDX query tool, which I often start with when writing reports. It can add parameters and create calculated members for you which are both handy features. Very quickly though you realise that it can’t do more complicated MDX queries for you without you having to dive in and manually edit the query.

The first thing I tend to do is copy the auto-generated query into an MDX editor that will do some formatting.  I used to use Mosha’s awesome online MDX tool. He also had a downloadable MDX editor which was a bit like a code cop, not only showing you syntax errors but advising against some common poor query constructions. You might well know that Mosha was a driving force behind both the development of SSAS and the MDX language which subsequently became an industry standard. Usually Microsoft get blamed for convoluting open standards but here’s an example of them defining one and opening it up to competitors like Oracle and IBM.

Unfortunately Mosha’s tool is gone, and Moshas moved on from the MDX world a few years ago (genius never sits still) but this Microsoft provided tool works well http://formatmdx.msftlabs.com/

It seems with the trend to move towards Tabular modelling such as in PowerPivot and Tabular cubes, which only work with DAX and not MDX, that MDX might be going out of fashion. It’s not suprising given how convoluted it is to write and how easy it is to get it very wrong. The roots of DAX are in VB and Excel functions as well which makes it more palatable to business type users.

I don’t think my time delving down the rabbit hole of MDX was misspent though, it’s been fun in a masochistic kind of way.

 

Leave a Reply

Your email address will not be published. Required fields are marked *