MDX Formatter

Have you ever tried to export some MDX from SSRS, Excel or some other tool into SSMS to better try to read the code? Well if you have, you will know that it doesn’t format the MDX for you and places everything into a single row. This is very frustrating for developers because you have to spend the extra time formatting it.

However, if you do some google searching you can find some very useful tools that will do the formatting for you to make it easier to read without having to put the extra time and effort into it.

Here’s one that I use personally but others exist out there as well. I also like this one because it will also format DAX.

http://formatmdx.azurewebsites.net/

Need Help Capturing MDX Queries? There’s an App for that.

Occasionally you might to capture the MDX query that’s being used for an excel pivot table. You might need this for performance tuning or maybe even just another option for learning MDX or how the MDX is being created behind the scenes in excel.

Now you have multiple options here. You could use the built in SQL Profiler tool to do this but you will need the appropriate permissions to use it. It can also be challenging just finding the query in the trace. Luckily for us we have third party options, either free or not. The one I’m going to be using here is the OLAP Pivot Table Extensions that can be found on CodePlex for free. Now the excel add-in can do more than just capture the MDX query but in this blog we will focus just on capturing the MDX that’s used for the pivot table.

Here’s a link where you can go download it along with other instructions on how to use some of its other features. http://olappivottableextend.codeplex.com/

Once you have the above add-in installed, go open an existing pivot table or create a new one. Now right click somewhere in the pivot table and select OLAP Pivot Table Extensions. It should be the very last option in the window.

Once you have selected this the OLAP Pivot Table Extensions Add-in will open. You will see several tabs but if you click on the MDX tab it will display the MDX query that’s used to return the results of the pivot table.

I hope you found this useful and informative.