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.

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

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

  1. 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.

  1. 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.

  1. Set the
    processing mode.

  1. 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.

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

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

  1. Click Next.

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




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |