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”.
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.
The profiler should have captured 2 events. The event with subclass “Alter” is the one we need.
Copy the script from the profiler to a new XMLA script in Management Studio.
The last 3 lines (PropertyList) can be deleted.
In the node “ObjectDefinition” you will find your script.