Using the SQL Server Analysis Services (SSAS) Deployment Wizard

By: Ray Barley   |   Updated: 2009-11-18   |   Comments (6)   |   Related: > Analysis Services Administration

Problem

I have to deploy my SQL Server Analysis Services project to a test server then to a production server after testing is completed.  For both of these deployments I need to provide an installation program.  I am not allowed to access either of these servers.  For my own testing I can deploy my project using Business Intelligence Development Studio (BIDS) and this works great.  How can I create an installer that my IT team can use to perform the deployment?

Solution

SSAS includes a tool called the Deployment Wizard which will do exactly what you need.  The Deployment Wizard uses the artifacts created by your SSAS project and can perform the deployment and/or generate an XMLA script that can be used to perform the deployment at a later time.  XMLA stands for XML for Analysis Services and it is the native protocol used by SSAS.  All commands sent to SSAS are in the XMLA format.  An XMLA script is an XML file.  The ability to generate the XMLA script allows the developer to provide the IT team what they need to perform the deployment.  In this tip we will review the BIDS artifacts, walk through the Deployment Wizard to create the XMLA script, then show the options to execute the script.

BIDS Artifacts

When you create an SSAS project using BIDS there are a number of files that contain the details about your project.  You can find these files in the bin folder within your project folder.  As you make selections and enter options in the various property dialogs in your project, those details are persisted in the files in the bin folder.  As an example I will use the SSAS project from my earlier tip Introduction to Dimension Security in SQL Server Analysis Services (SSAS) 2005.  The SSAS project name is dimension_security_intro.  The contents of the bin folder are shown below:

The Type shown above is pretty descriptive of the contents of each file.  The following descriptions of the files are found in the Books on Line page Understanding the Input Files Used to Create the Deployment Script:

Running the Deployment Wizard

You can launch the Deployment Wizard from the Analysis Services program group within the Microsoft SQL Server program group.  The wizard requires that you be able to connect to the target deployment server.  To get around this you have to launch the wizard from the command line and specify the options as shown below:

[PATH-TO-DEPLOYMENT-WIZARD]\Microsoft.AnalysisServices.Deployment [PATH-TO-ASDATABASE-FILE] /d /o:[SCRIPT-FILE-PATH]

Options:

• /d is for disconnected mode meaning you will not be connecting to the target deployment server

• /o allows you to to specify the full path to the XMLA script file to be generated; this is required when you use the /d option

When you run the wizard from the command line with the options shown above, the wizard does not step through a series of dialogs; rather it simply generates the XMLA script file based on the files in your project's bin folder as described above.  If you need to change anything in the generated script you can open it up in Notepad or your favorite editor.

To get the location of the Deployment Wizard, you can right click on it in the Microsoft SQL Server, Analysis Services program group, then select Settings from the menu to get the folder for the Deployment Wizard.  For a SQL Server 2005 32 bit default installation that folder is:

C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE

If you want to step through the wizard dialogs you can launch the Deployment Wizard from the Analysis Services program group (or from the command line without the /d and /o options) and you will be guided through the following steps:

Specify Source Analysis Services Database

Navigate to the [project name].asdatabase file in the bin folder of your BIDS project:

Installation Target

Specify the target deployment server and the name of the SSAS database on that server.  Remember you must be able to connect to the server.  If you cannot connect to the server you will have to specify something like localhost to get through the wizard.

Specify Options for Partitions and Roles

After initial deployment, an administrator may change the partitioning and/or roles and members.  When you deploy you have to decide whether or not to overwrite these settings based on what you have in your SSAS project.  The options shown below assume that you do not want to overwrite those changes.  However you can choose the options that will make the deployed database match what's in your project.

Specify Configuration Properties

Administrators may make changes to various configuration properties after deployment and you may need to preserve those settings rather than overwrite them with what's in your project.  In the case of data source connection strings, you make not even know the server names and credentials used to connect to them.  Optimization settings will likely change as the size and usage of the SSAS database increases over time.  Again the safest approach is to retain the settings rather than overwrite them.

Select Processing Options

You can specify whether to process the SSAS objects after deployment.  Default processing allows SSAS to decide what needs to be done; Full processing can be used to process all objects.  You can also choose not to process at all.  You can select whether to perform the entire deployment in a single transaction.  If this option is unchecked, each step of the deployment proceeds in its own transaction.

Confirm Deployment

Use this dialog to optionally specify whether to generate an XMLA script and save it in the folder of your choice.  After clicking Next on this dialog the wizard will perform the deployment.  It's a good idea to save the deployment script.

Deploying database

This dialog shows the results of performing the deployment.

Executing the XMLA Script

When you use the Deployment Wizard to generate the XMLA script for deployment, you can choose from the following options to execute the script:

• Use SQL Server Management Studio to connect to the target Analysis Services server, select File, Open from the menu to open the XMLA script file, then click on the Execute button (or Ctrl-E) to execute the script

• Create a SQL Server Agent job with a job step type of SQL Server Analysis Services Command; specify the SSAS server, put the XMLA script in the command text box, then execute the job or schedule it for execution at a later time

• Create a SQL Server Integration Services (SSIS) package, add an Analysis Services Execute DDL Task, specify an Analysis Services connection manager, and put the XMLA script in the task

• Execute the XMLA script with the ASCMD utility

ASCMD is a command line utility that is very similar to SQLCMD which is used to execute T-SQL scripts.  You can download ASCMD from the Codeplex page Readme for ASCMD Command-Line Utility (the download is a Visual Studio project that you have to compile).  ASCMD allows you to execute XMLA, MDX or DMX scripts from the command line which is simple and often desirable.  In addition ASCMD supports variable substitution just like SQLCMD; i.e. you can specify variables in your script and assign them a value on the command line.  For instance if you had a variable in your script such as $(database), you can execute the script and set the value of the database variable on the command line as follows:

 ASCMD -S servername -v database=mssqltips -i "c:\mssqltips\sample.xmla"

Command line options:

• -S servername - the name of the SSAS deployment server

• -v variable=value - you can assign values to one or more variables in your script; add additional variable=value statements to set a value for other variables

• -i scriptfile - the full path to the script to execute

Next Steps

• Take a look at Using the Analysis Services Deployment Wizard in Books on Line to get additional details on this tool.
• Keep in mind that the Deployment Wizard is a useful tool when you are not able to deploy your SSAS project directly from BIDS.
• By leveraging the Deployment Wizard and ASCMD, you can create a simple deployment command line that any administrator can easily execute from the Command Prompt.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips