Forget SQL Management Studio, Stick to BIDS

Ok, so lately there has been a decent bit of frustration from our crew about how to deploy a Analysis Services Project from BIDS to XMLA without going into SQL Management Studio and going through that whole mess.  Well my fellow coworker Xander got a solution figured out for this.  One of the other key side effects is that one doesn’t actually need the database/cube itself physically on the box once this is done, but simply the project itself (it helps to have a cube to push to).

First just setup the actual XMLA build process.  To do this click on Tools and then External Tools.

Click on Add and in the Title, Command, Arguments, and Initial Directory enter the following;

• Title:  Build XMLA
• Command: Microsoft.AnalysisServices.Deployment.exe
• Arguments: “-enter project name here-.asDatabase” /d /o:..\..\Project.xmla
• Initial Directory: $(ProjectDir)\bin
• Check the “prompt for arguments” option.
• Check the “output window” option.

When the prompt comes up during execution of the Build XMLA you must remember to enter your project name exactly so that the database name matches.

Select OK and then click on Tools again.  You will now see a Build XMLA option on the Tools menu.

Now open a new project.  Since it is the standard template example, I’m going to open up AdventureWorks. 

Make sure just before executing this that you have done a clean build of the project.  Without a build the necessary *.aspDatabase file won’t be available.  Once you have opened and built the solution then click on the Tools -> Build XMLA option.  The argument prompt will appear.  Enter the project name as shown in the image.  Now you should see the output window display the status.

After execution navigate to the project root and you will find the “Project.xmla” file.  Now you’re good to go without fighting with SQL Management Studio all the time, nor needing a physical installation of your database/cube.