Aveek Das
Database Synchronization Completed

How to deploy SSAS cubes

April 9, 2020 by

In this article, I’m going to discuss the different ways in which we can deploy SSAS cubes. SSAS or SQL Server Analysis Services is an enterprise-grade analytical data engine which helps business to create semantic models that can be used for reporting and making better decisions. The semantic layer created by SSAS can be accessed by client tools like Power BI, or Excel and reports can be prepared from thereon. SSAS comes with a few different versions, which are as follows.

  1. SQL Server Analysis Services – This is the on-premise version that ships with SQL Server and can be installed on any server running Windows. This version supports both multidimensional and tabular data models
  2. Azure Analysis Services – This is the SaaS offering from Microsoft, which can be consumed as an Azure resource. Azure Analysis Services currently supports only Tabular models with a compatibility level of 1200 or higher. You can develop the tabular data models using Visual Studio and deploy it to the Azure Analysis Services
  3. Power BI Premium – This is currently in preview mode and has added support for Power BI Premium datasets, which have a compatibility level of 1500 or higher. It also provides extensibility by providing client libraries and APIs which can support the open-standard XMLA protocol

SQL Server Data Tools (SSDT) is the authoring tool that is used to develop and deploy SSAS cubes. This feature comes with a stand-alone installation until Visual Studio 2017. However, for Visual Studio 2019, it can be downloaded and installed as an extension from the Visual Studio Marketplace. This article focusses only on how to deploy SSAS cubes and does not emphasize on the development of SSAS cubes.

First, it is important to know the different artifacts that are being generated while building as SSAS cube. If you see the figure below, you can see that there are four files. These files are generated when you build any SSAS project.

SSAS Deployment Files

Figure 1 – SSAS Deployment Files

File Name

Purpose

ASDATABASE

This is the actual deployment file that contains data related to all the dimensions and cubes that you have developed using Visual Studio. All the objects are defined in this file

CONFIGSETTINGS

This file is specific to contain information regarding the data sources and other environment-related information. Although this information is already defined in the ASDATABASE file, it is overridden by the information from this file

DEPLOYMENTOPTIONS

This file contains information regarding whether to deploy and process all objects and how the objects should be processed

DEPLOYMENTTARGETS

This is a configuration file that contains the details of the server and instance name on which the project has to be deployed

Let us now understand different methods by which we can deploy SSAS cubes. I’ll be explaining the following three methods in this article.

  1. Using the Analysis Services Deployment Wizard
  2. Using the command-line interface with Analysis Management Objects (AMO)
  3. Using the Database Synchronization Wizard

Analysis Services Deployment Wizard

You can start up the Analysis Services Wizard by navigating to the directory mentioned below and run the Microsoft.AnalysisServices.Deployment.exe. For my PC, which has SQL Server 2017 installed, the default location is – “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe”.

Analysis Services Deployment Wizard

Figure 2 – Analysis Services Deployment Wizard

Once the wizard starts, you need to provide the path of the ASDATABSE file and proceed with the deployment process.

Database File

Figure 3 – Database File

In the next screen, the details of the target server and instance must be provided on which we want to deploy the SSAS cubes. Since I’m deploying it on the default instance, there is no instance name mentioned after the server name. Also, we need to provide the name of the database on which the project must be deployed. By default, it takes the name of the SSAS project as the default database name.

SSAS Installation Target

Figure 4 – SSAS Installation Target

In the following screen, you need to define the options pertaining to Partitions and Roles and memberships. In an ideal deployment scenario, we use the options “Deploy Partitions. Any existing partitions will be replaced.” and “Deploy roles and retain members. Roles will be deployed along with their members for new roles. Members for existing roles will be retained.

Deploy SSAS Cubes Partition and Roles

Figure 5 – Partition and Roles

In the following screen, you need to define the Data Source Connection Strings to the source of the SSAS cubes. Click on the button with three dots and define the connection. Also, select an appropriate Data Source Impersonation Information that can be used by the SSAS cube while processing the cubes.

Connection Properties

Figure 6 – Connection Properties

Finally, select the Processing Method as “Default Processing” and check the box for “Include all processing in a single transaction”. Hit Next and then Finish. The cube will be deployed and processed accordingly.

Set Processing Options

Figure 7 – Set Processing Options

The SSAS cube will be deployed, and you can browse the cubes using SQL Server Management Studio.

Analysis Management Object Command Line Interface

Navigate to the directory – “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe” and start the command prompt.

Start with the Microsoft.AnalysisServices.Deployment.exe and provide the path of the ASDATABASE file. Additionally, add the parameters “/d” and “/o” to generate a deployment script in XMLA format.

  • /d – Represents that the deployment is to be done is a disconnected mode
  • /o – The path of the deployment script

Microsoft.AnalysisServices.Deployment.exe
C:\temp\deploy-ssas-cubes\WideWorldImportersMultidimensionalCube.asdatabase
/d /o: C:\temp\deploy-ssas-cubes\DeploymentScript.xlma

Deploy SSAS Cubes using CMD

Figure 8 – Analysis Services Command-Line Deployment

Once the deployment script is generated, you can open the script in SQL Server Management Studio and execute it against the target server. It will deploy the SSAS cube to the target server as required.

XMLA Deployment Script

Figure 9 – XMLA Deployment Script

Database Synchronization Wizard

The Database Synchronization Wizard is another way to deploy SSAS cubes from one server to another directly. For example, this is useful when the SSAS cubes have already been tested in the test environment and are now ready to be deployed to the staging or production; we can leverage this method and deploy the SSAS cubes. The wizard compares the metadata between two instances of Analysis Services and then makes the target equivalent to that of the source by copying the metadata information from the source server to the target.

I have two instances of Analysis Services running – “SQLTEST01” and “SQLTEST02”. For this example, I’ll try to migrate the SSAS cube “WideWorldImportersMultidimensionalCube” from SQLTEST01 to SQLTEST02 using the Database Synchronization Wizard.

SSAS Instances

Figure 10 – SSAS Instances

Right-click on the Databases under SQLTEST02 and select Synchronize.

Deploy SSAS Cubes

Figure 11 – Synchronize Database

In the next screen that appears, select the Synchronization Source Server as the “SQLTEST01” instance and the Database as the “WideWorldImportersMultidimensionalCube”. Click Next once done.

Synchronization Source Database

Figure 12 – Synchronization Source Database

In the following screen, leave everything as default and click on Next.

Synchronization Location

Figure 13 – Synchronization Location

Finally, select the “Copy all” option for Security roles and members and click on Next. In the next screen, select Synchronize Now and click Finish.

Synchronization Options

Figure 14 – Synchronization Options

Depending on the size of the database, it might take a while to synchronize the databases. You can monitor the progress in the Database Synchronization Progress window.

Deploy SSAS Cubes Database Synchronization Progress

Figure 15 – Deploy SSAS Cubes using Database Synchronization

As you can see, now, the SSAS cube has been deployed to the SQLTEST02 instance as well.

Database Synchronization Completed

Figure 16 – Database Synchronization Completed

Conclusion

In this article, I have explained how to deploy SSAS cubes using three different methods. Although it depends on the environment that you’re trying to deploy the SSAS cubes, the most preferred way of deploying the cubes is by using Analysis Services Deployment Wizard as it has a GUI, which makes the task easier for a lot.

Aveek Das
4,538 Views