XMLA script to deploy MDX Calculations

At a current project I needed to create a deploy script for the MDX calculations of a cube.

SQL Server Management Studio makes it very easy for you to create XMLA-scripts to create or alter cubes, dimensions, etc… But it’s not possible to use Management Studio to create an alter-script that only contains the MDX calculations. Because I needed to resolve a bug in the calculations but the rest of the cube was not ready to be deployed to production yet, I had to create an alter script just for the MDX.

Of course I could have started learning the XMLA-syntax to write a script for this, but I decided to go for the easy way: the BIDSHelper has an option to deploy the MDX to a cube without having to deploy the entire cube.

If you don’t know BIDSHelper, or don’t already have it installed, go to http://www.codeplex.com/bidshelper. This is a must have add-in for all SSAS and SSIS developers!

But as I was allowed to use Management Studio to makes changes to the cubes in the development environment only. So I needed to capture the XMLA script the BIDSHelper executes on the cube, to deploy the same script to the production environment.

That what the profiler is for, not? J

Open the SQL Server Profiler, create a new trace and connect to the analysis services instance. Use a blank template and switch to the tab “Events Selection”. The only event we want to capture here is “Command Begin”.

Click Run.

Now the profiler is ready to capture the XMLA-script, so switch back to Management Studio and use the BIDSHelper to deploy the MDX calculations. When the deploy is finished you can stop the trace.

clip_image002

The profiler should have captured 2 events. The event with subclass “Alter” is the one we need.

clip_image004

Copy the script from the profiler to a new XMLA script in Management Studio.

clip_image006

The last 3 lines (PropertyList) can be deleted.

clip_image008

In the node “ObjectDefinition” you will find your script.

Enjoy!

Share
  1. May 1st, 2012 at 18:00 | #1

    Great post! This really helped me out in a pinch! Some things you just need SQL Profiler for.

  2. Les R
    Jun 29th, 2012 at 09:00 | #2

    This is great, but how do you swithc BIDS to offline mode? These BIDS Helper options don’t appear in online mode, so I’m stuck.

  3. Jul 10th, 2012 at 13:42 | #3

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>