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.