SSAS – Deployment Wizard

The Analysis Services Deployment Wizard is an often under utilized but important tool when moving your OLAP database throughout your environment.  Often I see developers deploy directly from BIDS to all Development, QA, and Production environment by changing the project properties to the different instances.  For multiple reasons this is a bad practice. 

  • With a sloppy developer like me I’m likely to accidently deploy to the wrong server.
  • You could unintentionally deploy over Production security
  • You could unintentionally overwrite a data source connection string
  • It doesn’t allow you to script out deployment

The better solution is to use the deployment wizard which will take care of all these problems for you. 

To get started open the deployment wizard by navigating to it in your start menu. Start –>All Programs->Microsoft SQL Server 2008 R2 (or your current version of SQL Server)->Analysis Services->Deployment Wizard.

After the wizard opens hit Next past the welcome screen.  It will then ask you for a database file.  This is a .asdatabase file which can be found in the file structure where you developed your Analysis Services project.  Just look under the /bin folder and you should see a file with the .asdatabase extension.  This will only appear if you’ve built your project, so if you don’t see it then do a build in Visual Studio.  Once you’ve selected the database file hit Next.

The wizard will then ask you which Analysis Server you wish to deploy to.  Simply tell it the server and what you desire the new database to be named.  Not that just because the database field is a dropdown box does not mean you cannot type a different database name on top of it.  Once this is complete hit Next.


The next screen allows you to control some of the objects that are deployed.  For example the partitions section allows you to either deploy the partitions that are part of current project or you can retain the existing partitions if you are deploying on top of an existing database.  This is useful if you have developed partitions on Production that accommodate a larger dataset than exists on Development.

You can also control security through the Roles and Members section.  For example it is likely that you have more stringent security on Production than Development.  This section will allow you to deploy all object either including or excluding the security.  It can even be more granular than that.  Maybe your security roles is the same but the membership is different.  You can optionally choose to just deploy membership changes.


The Specify Configuration Properties window will allow you to make just about any configuration change you would need to before deploying.  For example, when I deploy to Production the source database may be on a different server than it was on Development.  This screen will allow you to change the connection string of the Data Source.  It can also allow you to change where the data files will be stored.  So if you have everything stored on the C drive in Development but on Production you want them stored somewhere else this wizard will help with that.  You can also use it to change details of Actions.  So if you have a report action that uses a Reporting Services report on your Development server than you can change it to production here also.  This is a very useful screen.  When you’re happy with the changes click Next.


Next you will be asked to select processing options.  You can either do the actual processing of the Analysis Services database or just deploy metadata changes.  Click Next after deciding if you want to process or not.


The Confirm Deployment window is a little more helpful than it sounds.  Here you can click Next to begin the deployment or check Create deployment script and provide a file location to actually script out the entire deployment so it can be done later.  This is extremely helpful for those of you that must script out all deployment and hand it to Production DBAs.  If you want to do the actual deployment without scripting it out first just click Next without checking the box.


After hitting the final Next the Deployment Wizard will do the operation you’ve specified with the configuration requirements you provided.  The out put will be either a deployed cube or a .xmla file that has scripted the deployment for you to be done at a later time.


SQL PASS Summit 2011 Recap

This week has been an exciting and memorable week for me. The last PASS Summit I attended was in 2009 and I missed it so dearly. I meet a lot of people that I have only chatted with online but had never meet and others that were completely new to PASS and our community. For those of you that have never been to PASS I’m hoping hearing about my experience will get you excited to come next year. With obligations at home unfortunately I wasn’t able to stay through the final day of the event.


Arrived in Seattle around noon and meet up with the rest of the Pragmatic Works team that spend the weekend climbing Mount Rainier. Sounded like they had a fantastic time and I’ll join them next year when my babies get older! We got checked into our hotel then headed over to the convention center to get registered for the event. All the day one pre-cons were wrapping up so we went out to eat at a really great Moroccan restaurant. After dinner Brian and I spend the night focused on prepping for our pre-con on Tuesday.




This was a really exhausting but rewarding day. Brian and I delivered our pre-con on delivering a complete BI solution for the entire day. We had a group of about 120 in the room and build a complete solution based on NFL data. We even had an appearance from the Jaguars Defensive Coordinator, Mel Tucker. The group was very attentive and had a ton of great questions.

For dinner the we had a company dinner at Tap House.


Attended the key note session which had many great announcements like SQL Server 2012 to be released the first half of next year and final names for other tools like Crescent will now be called Power View (yes there’s a space there).

I also did my session on Common Analysis Mistakes and How To Avoid them. You can download the slides here. The session went great and I got a lot of good feedback.

I attended several sessions this day including one on the new SQL 2012 Reporting Services alerting and a session by Alberto Ferrari about many to many relationships in DAX. Both of these were really interesting and I learned a lot.

The day finished with the exhibitor party were the Pragmatic Works booth was consumed by people as you can see below. It helps when you have a booth right next to the food Smile.

SQL Pass
We also gave away almost 80 books away for free and had many of the authors sign them including myself, Brian Knight, Mike Davis, Mark Stacy, and Adam Jorgensen.

Book Signing

After the ever was over a group of people we meet during the event went to dinner at Gordan Biersch Brewery. Another great day!


Again attended the key note session were we saw more detailed demos of SQL Sever 2012, most of which we’ve already seen but still nice demos. Also, some of the new HP appliances were announced and Pragmatic Works was mentioned as one of a few companies that are leading in implementing these appliances and the architecture that goes with them.

After the key not I spend some time working our booth before we did a group picture. If snag the picture later.

I attend a few sessions today including one lightening round session that included speakers like Amy Lewis, Chris Webb, Grant Fritchey, and many others. All good stuff with a 5 minute time limit which makes things interesting.

Then I helped in a session submitted by Mike Davis called SQL Smackdown: SSIS vs TSQL. Mike and Adam Jorgensen battles for SQL supremacy while I was the refereed the match. There were plenty of jabs thrown on both sides including several at me from our buddy Bradley Ball as he photographed the event. Pictures to come!

Finished my week by taking down our companies booth and headed to the airport. Unfortunately I had to miss the final day of the event but as usual the summit never disappoints.