Explain deployment of SSAS cubes in MSBI

In the following article, I’m going to elaborate on the multiple ways in which we can deploy SSAS cubes. SQL Server Analysis Services is an enterprise-level analytical data engine. It helps businesses to create different logical models useful for reporting and making better decisions. The logical layer created by SSAS is accessible for different client tools like Power BI or Excel. Moreover, the reports are designed from thereon.

It comes with multiple versions, as follows:

SQL Server Analysis Services:

This version is on-premise and continues with SQL Server and can be installed on any Windows server. Moreover, it supports both multidimensional and tabular data models as well.

To get in depth knowledge on Microsoft business intelligence, enrich your skills on Msbi online training 

Azure Analysis Services (AAS):

This is one of the other SAAS offerings from Microsoft consumable as an Azure resource. The Azure Analysis Services presently supports Tabular models only with a compatibility level. Anyone can develop these tabular models using Visual Studio and implement it to the AA Services.

SQL Server Data Tools or (SSDT) is the major tool useful to create and deploy SSAS cubes. This feature includes a stand-alone installation until Visual Studio 2017 version.

Thus, this article focuses only on the deployment of SSAS cubes and does not focus light on its development.

At first, we need to know the different artifacts generated while developing an SSAS cube. The following files will generate while building any SSAS project.

ASDATABASE

It is the actual file useful for deployment that includes data related to all the dimensions and cubes that we develop using Visual Studio. Besides, all the objects of files are defined in this file only.

DEPLOYMENTOPTIONS

The file contains information relating to the deployment and processing of all objects and how to process the objects as well.

CONFIGSETTINGS

The following file specifically contains information regarding the data sources and other environment-related data. However, this information is already in the ASDATABASE file, it is just overridden by the information.

DEPLOYMENT TARGETS

It is a configuration file that includes the details of the server & instance name on which the project has to be deployed.

Different Options for Deploying a Cube

Let us understand the multiple methods through which we can deploy SSAS cubes. The following are the three methods described as well:

a)      using the Analysis Services Deployment Wizard method

b)      using the command-line interface with Analysis Management Objects (AMO) method

c)      using the Database Synchronization Wizard method

By using the Analysis Services Deployment Wizard

We can start the Analysis Services Deployment Wizard by navigating to the directory the file exists and run the Microsoft.AnalysisServices.Deployment.exe. The following image describes the same:

In the below image, the details of the target server and the instance are given on which we want to implement the SSAS cubes. This deployment is on the default instance, there is no instance name specifically given after the server name. Moreover, we also need to provide the details, like the name of the database on which the project it is to deployed. By default, it takes the name of the SSAS project as the database name in default. Learn more from Msbi online course

 

In an ideal deployment feature, we use the following options:

A.    Deploy Partitions.

B.     Existing partitions will get replaced

C.     Deploy roles and retain members.

The roles will be deployed along with the members for new roles. Moreover, the members for existing roles will be retained.

Finally, we need to select the Processing Method as “Default Processing”. Now, hit- Next and then click Finish. Now the cube will get deployed and processed.

By using the command-line interface with Analysis Management Objects

Here, let us start with the Microsoft.AnalysisServices.Deployment.exe & provide the path of the ASDATABASE file. In addition to this, we need to add the parameters “/d” and “/o” to generate a deployment script in XMLA format.

·        /d–Represents a disconnect mode in which the deployment is to be done.

·        /o–Represents the path of the deployment script.

After generating the deployment script, we can open the script in SQL Server Management Studio and execute it against the target server. Besides, it will deploy the SSAS cube to the target server as per requirement.

Database Synchronization Wizard

The Database Synchronization Wizard is another way to deploy cubes from one server to another directly. For instance, this is useful when the SSAS cubes have already been tested in the test environment. These are now ready to implement to the production. Moreover, we can use this method and deploy these cubes up to the max level. Further, the wizard compares the metadata between two instances of Analysis Services. Then it makes the target similar to the source by copying the metadata from the source server to the target.

Automate SSAS cube deployment

Here, we use MSBUILD as an instance. We will understand how to use MSBUILD to automate the build and deployment of an SSAS Cube. This solution is based on calling Visual Studio (devenv.exe) from within MSBUILD to first build the Cube. And then we use the SSAS deployment wizard from the command line to implement the Cube easily.

The first thing we need to understand is that an Analysis Services project doesn’t follow the MSBUILD standards. Therefore, it cannot be developed directly through MSBUILD. Thus, in the place of MSBUILD script, we call the Visual Studio code to build the Cube.

Furthermore, we are going to understand how to setup / configure the SSAS project and run the MSBUILD script automatically.

1)    Set up the project

Creating different project configurations/setup for each target ecosystem, (such as; DEV, TEST, PROD) in the SSAS project is the key to make the work successful. For each ecosystem we can define values for the target Analysis Services server, database, build path, and Data Sources connection strings as well.

Automating the Build

The first thing here to do within the MSBUILD script is to define a few properties. The following properties are passed through the command line while calling the MSBUILD script.

·        env. Relates to the project configuration name for the ecosystem as an example.

·        solutionPath. The path to the BI solution of the project

·        ssasVersion. There are different versions of the SSAS Cube generally built. This affects the version of Visual Studio used to build the Cube.

Later, we need to define a Target to build the SSAS cube.

Finally, the (Exec), target runs a command line. This command-line uses the devenv.exe version specified within the devEnvTool. And it builds the Cube project specified by the CubeProjectFiles, for the solution and project configuration specified within the env. property as well.

2)    Automating Cube Deployment

The automation of cube deployment uses the SSAS cube deployment wizard that executed from the command line through Microsoft.AnalysisServices.Deployment.exe.

Again, it is important to correctly set up the deployment options in the SSAS cube project before implementation. These settings implemented into the files developed by the build and make it possible to deploy the cube to a server silently.

·        Processing Option

·        Server Mode

·        Server

·        Database

Final words

Configure the SSAS cube project with proper security and encryption of passwords. It needs to set the password first before executing the files. Thus, to learn more about cubes and their deployment practically, go through MSBI Online Training Hyderabad.