SSAS Deployment Strategies

Introduction

Building a data warehouse and then a cube based on the data warehouse has become child’s play, thanks to Microsoft’s business intelligence tools. SQL Server Analysis Service is used to build cubes for slicing and dicing data. After the cube has been built we need to deploy it to the SSAS server in order to make it available to the client applications like Excel, SSRS and PPS 

This article focuses on best practices for the deployment of cubes.

How to deploy the cube?

The cube deployment can be carried out in the following ways:

1. Using the Visual Studios  BIDS
2. SSAS cube deployment wizard
3. Synchronization of the Cube

Using Visual Studio

Once the data warehouse is ready the cube can be deployed and processed from the SSAS project solution. We just need to right click on the project and click deploy. For the deployment we need to specify the SSAS server and the database name in the configuration properties. When the data warehouse is processed it creates a separate SSAS database for querying the cube. By default the storage mode is MOLAP which is optimized for the faster data retrieval.

 

The deployment can fail if the user doesn’t have administrative rights on the SSAS server. For this error to be resolved we need to add the user as the server administrator.

The account connecting to the data source must have permissions on the data source and preferably should be the credentials of the current user with appropriate administrative permissions.

Once the above issue has been addressed, there should not be any issue with the deployment and processing of the cube.

SSAS cube deployment Wizard

The cube deployment wizard gives you a lot of control while deploying the cube. The deployment wizard gives us the ability to choose the objects which need to be deployed on the server. Sometimes deploying the cube directly from the Visual Studio can result in the loss/overwriting of objects. This can be avoided if you use the deployment wizard. The deployment wizard can be opened from All Programs->Microsoft SQL Server 2012->Analysis Services-> Deployment wizard

Click on the deployment wizard and follow the steps:

1. Click Next.

2. Specify the Analysis services database file whose default location is the the bin folder of the Analysis Services project.

3. Specify the name of the SSAS server and database, if the database doesn’t exist  then a new database will be created.

4. Click next. The next window will allow us to specify the options to control the partitions and the roles of the cube. You can select the appropriate action and click next.

5. Specify the configuration for error logging at the different levels. If the impersonation mode is service account then it will throw a warning saying “It is not recommended that the credentials of the service account are used for data sources”. Click Next.

6. Set the processing mode.

7. If you want you can create an automated script which will be automatically generated once you check the create deployment script box. Specify the location of the script file.

Click Next and the deployment of the SSAS database starts 

Click Finish when the cube has been successfully deployed on the server.

Synchronization of the Cube

One best practice is to synchronize the production SSAS database with development database. The synchronize property is provided at the server level. The editions for synchronizing the databases on the server should be the same.

The advantage of using the synchronize method is that we don’t have to backup and restore the SSAS database manually. Though the manual intervention is inevitable as of now automated synchronization has not yet featured.

Tip: – It is better to have a domain user in place to carry out the synchronized deployment to avoid the permission issues for local administrator of the development server on the production server. This is because we need to have permissions for the development server administrator on the production server which can be a bit cumbersome task as the GUID’s of the administrators of the local machines are different.

Follow the steps below to start the synchronization wizard.

1. To start the synchronization wizard right click on the databases and click synchronise.

2. Specify the source server from where you want tosynchronise the database. Click Next.

3. Specify the storage location for the local partitions or you may keep the default settings and click Next.

4. Click Next.

5. Click next and the wizard will display the summary and then click Finish.

]]>