Simple batch script to generate XMLA and deploy SSAS DB

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):

—————SCRIPT START———————-

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 . . .

PAUSE Completed

—————SCRIPT END———————-

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:

I also referred to Vidas Mitalis’s FAQ creating the same script from SSAS 2005 here while constructing my SSAS 2008 script:


March Presentations Uploaded on SkyDrive

I presented at three events in the past 10 days. 

  • Magenic Lunch & Learn – SSRS Training – Overview of SQL Server Reporting Services functionality.
  • Minnesota SQL Server User Group – SQL Azure – a good discussion on SQL Azure, it’s capabilities, and cost effectiveness for our solutions.
  • Techfuse 2011 – Performance Monitoring and Tuning with SSAS – a look at the query execution workflow and the tools to monitor and tune Analysis Services.

I hope these provide value to you.  Feel free to comment here with questions related to any of these presentations.

Minnesota gets a Microsoft Technology Center

Microsoft Technology Center

Last week, I got a look at the new Microsoft Technology Center in Minneapolis.  Very cool place.  I am glad to see one here that we will be able to use to help our customers with their POCs.  They have a training room, a number of POC areas, and server farm you can leverage for your POCs. 

The Grand Opening for the MTC is Tuesday, April 5.  If you are able take time to check this out as it as cool new facility I encourage you to do so.  You can find out more at

Magenic Guys at the MTC Partner Event


Installing PowerPivot on an Existing Farm

At my current customer, we are putting together a sandbox MSBI environment with SharePoint as well.  We installed and configured SharePoint, SQL Server, Analysis Services, and integrated Reporting Services.  I was preparing to put together a PowerPivot demo in SharePoint and started to install it.  So here is where the fun began.

First, you need to use the SQL Server 2008 R2 install to

Next, you need to LOGIN using your Farm admin account and from what I could piece together, that user MUST have the following privileges.

  • SysAdmin on SQL Server
  • Local and Domain Administrator
  • SharePoint Farm Administrator

Of course, all of this violates any concept of minimum privileges for users.  While this level of access may not have been absolutely necessary, it is definitely where I ended up to get this working.  (The moral of this story is to install PowerPivot on new farms only?)

Here are some of the links I used to get me pointed in the right direction.  I would be interested in hearing if anyone else has had this issue and resolved it differently.

Now that it is running I can put the rest of my demo together.  This sure seemed more painful than it had to be.


February 2011 MN SQL Server User Group Meeting

 February PASSMN Meeting & Newsletter

Sponsored by Digineer


8300 Norman Center Drive, 9th Floor, Bloomington, MN  55437


February 15th, 2011


3:00 – 5:00

Please click here for meeting details and to RSVP 

High Availability & DR Options for SQL Server

Tim Plas, Virteva

A comparison of SQL HA & DR options, by a practitioner who has implemented & managed all the SQL HA and DR approaches (& various combinations thereof). Tim is an operational DBA, charged with keeping SQL servers up & running & optimized, for managed-services customers. We will compare trade-offs between the various SQL HA & DR options: for complexity, usability, hardware requirements, licensing, failover speed, initial costs, ongoing support costs, staff skill requirements, etc.

Also, as you may be aware, Microsoft has announced a set of very powerful “AlwaysOn” features for the upcoming version of SQL (“Denali”), features popularly referred to as “HADRON”  (“High Availability Disaster Recovery always ON”).  We’ll provide a brief overview of those features now, and will have a full presentation on that later in the year.


Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: