Multidimensional model solution deployment

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

After you have completed the development of an SQL Server Analysis Services project, you can deploy the database to an Analysis Services server. Analysis Services provides six possible deployment methods that can be used to move the database to a test or production server. The methods are listed here in order of advantage: AMO Automation, XMLA, Deployment Wizard, Deployment Utility, Synchronize Wizard, Backup and Restore.

Deployment methods

Method Description Link
Analysis Management Objects (AMO) Automation AMO provides a programmatic interface to the complete command set for SQL Server Analysis Services, including commands that can be used for solution deployment. As an approach for solution deployment, AMO automation is the most flexible, but it also requires a programming effort. A key advantage to using AMO is that you can use SQL Server Agent with your AMO application to run deployment on a preset schedule. Developing with Analysis Management Objects (AMO)
XMLA Use SQL Server Management Studio to generate an XMLA script of the metadata of an existing SQL Server Analysis Services database, and then run that script on another server to recreate the initial database. XMLA scripts are easily formed in SQL Server Management Studio by defining the deployment process, then codifying it and saving it in an XMLA script. Once you have the XMLA script in a saved file, you can easily run the script according to a schedule, or embed the script in an application that connects directly to an instance of SQL Server Analysis Services.

You can also run XMLA Scripts on a preset basis using SQL Server Agent, but you do not have the same flexibility with XMLA Scripts as with AMO. AMO provides a larger breadth of functionality by hosting the complete spectrum of administrative commands.
Deploy model solutions by using XMLA
Deployment Wizard Use the Deployment Wizard to use the XMLA output files generated by an SQL Server Analysis Services project to deploy the project's metadata to a destination server. With the Deployment Wizard, you can deploy directly from the SQL Server Analysis Services file, as created by the output directory by project build.

The primary advantage of using the SQL Server Analysis Services Deployment Wizard is convenience. Just as you can save an XMLA script for use later in SQL Server Management Studio, you can save Deployment Wizard scripts. The Deployment Wizard can be run both interactively and at the command prompt via the Deployment Utility.
Deploy model solutions by using the Deployment Wizard
Deployment Utility The Deployment utility lets you start the Analysis Services deployment engine from a command prompt. Deploy model solutions with the Deployment Utility
Synchronize Database Wizard Use the Synchronize Database Wizard to synchronize the metadata and data between any two SQL Server Analysis Services databases.

The Synchronize Wizard can be used to copy both data and metadata from a source server to a destination server. If the destination server does not have a copy of the database that you want to deploy, a new database is copied to the destination server. If the destination server already has a copy of the same database, the database on the destination server is updated to use the metadata and data of the source database.
Synchronize Analysis Services Databases
Backup and Restore Backup offers the simplest approach to transferring SQL Server Analysis Services databases. From the Backup dialog box, you can set the options configuration, and then you can run the backup from the dialog box itself. Or, you can create a script that can be saved and run as frequently as required.

Backup and restore is not used as frequently as the other deployment methods, but is a way to quickly complete a deployment with minimal infrastructure requirements.
Backup and Restore of Analysis Services Databases

Deployment considerations

Before you deploy an SQL Server Analysis Services project, consider which of these questions apply to your solution and then review the related link to learn ways of addressing the issue:

Consideration Link to more information
How will you deploy related objects that are outside the scope of the SQL Server Analysis Services project, such as Integration Services packages, reports, or relational database schemas?
How will you load and update the data in the deployed SQL Server Analysis Services database?

How will you update the metadata (such as calculations) in the deployed SQL Server Analysis Services database?
Deployment Methods in this topic.
Do you want to give users access to SQL Server Analysis Services data through the Internet? Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0
Do you want to deploy objects in a distributed environment by using linked objects or remote partitions? Create and Manage a Local Partition (Analysis Services), Create and Manage a Remote Partition (Analysis Services) and Linked Measure Groups.
How will you secure the SQL Server Analysis Services data? Authorizing access to objects and operations (Analysis Services)

See also

Deploy model solutions by using XMLA
Deploy model solutions by using the Deployment Wizard
Deploy model solutions by using the Deployment Utility