Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the SQL Server Analysis Services (SSAS) Deployment Wizard


By:   |   Last Updated: 2009-11-18   |   Comments (6)   |   Related Tips: > 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:

XMLA File Description
[project name].asdatabase Contains the declarative definitions for all SSAS objects
[project name].deploymenttargets Contains the name of the target SSAS instance and database
[project name].configsettings Contains environment specific settings such as data source connections and object storage locations.  These settings override what's in [project name].asdatabase.
[project name].deploymentoptions Contains options such as whether deployment is transactional and whether objects should be processed.

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.


Last Updated: 2009-11-18


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 27, 2014 - 8:42:51 AM - Raymond Barley Back To Top

It's a good idea to save the deployment script so that you know exactly what you specified in the deployment wizard and if you want to rerun it.  For instance you may want to deploy to multiple servers.


Saturday, January 25, 2014 - 11:53:50 AM - Gene Back To Top

It is "either or" and not "and" on Confirm Deployment screen - either deploy or save the script and do not immediately deploy. Thus if you checked off "Create Deployment script" option, the deployment will not take place. The screen and your comments "It's a good idea to save the deployment script" are somewhat misleading, since if you decided to save it, you need to repeat deployment process second time w/o checking off this option, or run the saved script outside of the Wizard. 


Thursday, November 14, 2013 - 1:26:43 AM - Namrata Back To Top

Thanks .That was very helpful.Just one doubt..if you use the deployment wizard instead of command line then you cannot deploy unless you have access to the target server?? where is the XMLA script stored after deploying it thru the wizard??


Saturday, August 10, 2013 - 2:24:21 PM - Ray Barley Back To Top

My best guess is that the error is related to your data source setup; see the Impersonation Information where you can specify:

- use a specific windows user name and password

- use the service account

- use the credentials of the current user

- inherit

I think use the service account is the default but whichever you choose that account has to at least have read permission on the database that you're using to populate the cube; e.g. if you're using a SQL Server database then put the account in the db_datareader database role for the database.  If SSAS and the SQL Server database are on different machines then the SSAS service account probably should be a domain account.


Saturday, August 10, 2013 - 10:47:32 AM - Jason Back To Top

Thanks for the article. I'm new to SSAS and this really helped my understanding of the deployment process. However, when executing the script via SSMS, it builds the cube successfully but appears to bomb during the Processing part. I get a "Login denied: null password..." error. Do I need to update the .configsettings file before running the script? Any ideas?


Friday, February 15, 2013 - 5:25:35 AM - fred t Back To Top

Most useful article, thank you.


Learn more about SQL Server tools