As I was working through some issues with my cube design, I realized that I wanted to deploy my SSAS databases using XMLA in SSMS. This can be easily scripted from SSMS, but I had not deployed the database yet. As a result, I created a batch file to build the XMLA used to deploy a database. This script can be used for automated builds or even automated deployments if that fits into your model. I don’t write a lot of batch files so you may want to update the syntax as needed.
The script I used is here (I saved this as a .bat file on my desktop):
REM Use this batch file to build and create the database XMLA script
ECHO Build started . . .
”C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe” “<< location of your SSAS solution or project>>” /build development /out ssasbuild.log
ECHO Build completed . . .
ECHO XMLA Script generation started
Microsoft.AnalysisServices.Deployment.exe “<< project path >>\bin\<<ProjectName>>.asdatabase /d /o:”<<target location and file name>>.xmla”
ECHO XMLA Script generation complete . . .
The two key executables are devenv.exe and Microsoft.Analysis Services .Deployment.exe.
Devenv.exe may not be in the same location as mine. This is the Visual Studio executable. If you have multiple versions of Visual Studio installed, be sure to pick the correct one. The parameters used are:
- The location of the SSAS solution (.sln) or project (.dwproj). Keep in mind that if you build the solution, you will build all of the projects in your solution.
- /build selects which configuration you are planning to build. This is a part of the project properties. In my case, I picked the development build.
- /out specifies the destination of the log file. This log file will log the warnings and errors that occur during the build process.
The next executable is the actual deployment executable – Microsoft.AnalysisServices.Deployment.exe. Once the database has been built it results in an asdatabase file which this process will generate an XMLA script from. If you built multiple projects in the previous step, you will need to repeat this step for each database you wish to create an XMLA script for. The parameters used are:
- The location of the .asdatabase file. You will usually find it in the bin folder of your project after you have built the project.
- /d specifies that the deployment executable not connect to the target server during the build of the XMLA which is what we want in this scenario.
- /o specifies the file location and name of the XMLA output.
(NOTE: The ECHO and PAUSE statements were used to make this more friendly for my use and are not required to build or deploy the XMLA script.)
Once you have the XMLA file, you can open it in SSMS and choose the target SSAS instance you want to deploy to. You can also make changes required prior to the deployment such as data source connection strings.
MSDN reference for the deployment utility: http://msdn.microsoft.com/en-us/library/ms162758.aspx
I also referred to Vidas Mitalis’s FAQ creating the same script from SSAS 2005 here while constructing my SSAS 2008 script: http://www.ssas-info.com/analysis-services-faq/29-mgmt/104-how-create-a-script-that-generates-xmla-script-from-solution-project.