SSAS: Clearing the MDX Script for a session in SSMS

Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you  may even have design changes in dev that have not yet been deployed that would cloud the issue.

There is a solution to this using the connection technique from my previous post. If you are a server or database administrator you can use the CLEAR CALCULATIONS command. This command effectively clears the MDX script for your current session. So what I usually do is to run the following:

CLEAR CALCULATIONS;

GO

CALCULATE;

As noted by joschko in the comments on the previous post you can use the GO keyword between statements to run multiple statements at a time in SSMS. Personally I don't tend to use this with SCOPE statements as "GO" is not an MDX keyword, it's only understood by SSMS, so you can't copy and paste it into your MDX Script and I like to keep the SCOPEs that I'm testing in a form that I can directly paste into the cube script.

Print | posted on Saturday, February 2, 2013 8:52 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)