SSAS Deploying Analysis Services in SQL Server 2008 and 2012

In this tutorial example i will explain the following deployment methods in SSAS 2008 2012 analysis services 
  1. Business Intelligence Development Studio
  2. Deployment Wizard
  3. XMLA Scripts
  4. Synchronize Database Wizard
  5. Backup and Restore
  6. Analysis Management Objects (AMO)
SSAS 2008 2012 Analysis Services Deployment Introduction
  1. Deploying Analysis Services can be performed through many methods. 
  2. Understanding the capability of each method will help you to determine the most effective deployment method to employ within your organization.
Analysis Services provides a number of methods to deploy Analysis Services objects. The following table describes the different methods available to perform a deployment.

1.Business Intelligence Development Studio
  1. Business Intelligence Development Studio is one method that can be used to deploy an Analysis Services project. 
  2. This method will prove useful as you develop your Analysis Services solutions to test the functionality of the solution. 
  3. This method builds a complete set of XML files in the output folder containing all of the necessary commands required to build all of the Analysis Services database objects in the project. 
  4. It will also validate the objects that are deployed.
  5. Prior to deploying the solution, you can configure a number of deployment options within the properties of the Analysis Services solution specific to the deployment.
  6.  These options can be accessed by right-clicking an Analysis Services project in Solution Explorer, and then clicking Properties. 
  7. Within the project Properties, click the Deployment page and configure the following settings:
  • Process Options. Determines whether the cube is process as it is deployed.
  • Transactional Deployment. Determines if the deployment is transactional and rolls back the deployment should it fail.
  • Deployment Mode. Specifies if all of the Analysis Services objects are deployed or only the changes are deployed.
  • Server. The name of the server to which the Analysis Services solution is deployed.
  • Database. The name of the database in which the Analysis Services solution is deployed.
2. Deployment Wizard
  1. The Deployment Wizard is a very useful feature for those database professionals who do not work within Business Intelligence Development Studio but need to deploy the Analysis Services solution. 
  2. This can include situations where a company’s infrastructure team is responsible for the deployment of the Analysis Services solution to the production SQL Server.
  3. The Deployment Wizard uses the XML files in the Output folder that has been created from within Business Intelligence Development Studio as the basis for the deployment.
  4. At the end of the wizard, you also have the option to generate an XMLA script of the Analysis Services objects and the deployment options that have been defined in the Deployment Wizard. 
  5. The Deployment Wizard can be run both interactively and at the command prompt.
  6. To access the Deployment Wizard interactively, click Start, point to All Programs, click Microsoft SQL Server 2008, point to Analysis Services, and then click Deployment Wizard.
The wizard consists of the following screens:
  • The wizard will first require the location of the *.asdatabase file, which, by default, is located in the bin folder of the Analysis Services project.
  • You are then prompted to specify the Server and database name of the Analysis Services database.
  • The third screen allows you to define any partitioning and security options that will be set as the cube is deployed.
  • You can then define properties for the components of the cube including configuration and optimisation settings.
  • The next screen allows you to specify the processing mode and whether the deployment is transactional.
  • The penultimate screen allows you to specify if you wish to generate a deployment script.
  • The final screen will start and complete the deployment.
Files used in the deployment of Analysis Services
When you build an Analysis Services project, Business Intelligence Development Studio generates XML files for the project. Business Intelligence Development Studio puts these XML files in the Output folder of the Analysis Services project. By default, output is out in the \bin folder. The following table lists the XML files that Business Intelligence Development Studio creates:
XMLA file


<project name>.asdatabase
Contains the declarative definitions for all the Analysis Services objects in the project.
<project name>.deploymenttargets
Contains the name of the Analysis Services instance and database in which the Analysis Services objects will be created.
<project name>.configsettings
Contains environment specific settings, such as data source connection information and object storage locations. Settings in this file override settings in the <project name>.asdatabase file.
<project name>.deploymentoptions
Contains deployment options, such as whether deployment is transactional and whether deployed objects should be processed after deployment.

3. XMLA Scripts
  1. XMLA scripts can be generated by using the Deployment Wizard that allows you to execute the script in SQL Server Management Studio, to recreate the database objects, which are defined within the script file.
  2. The XMLA script consists of settings that are used to create the Analysis Services objects. It also consists of the settings required to process the Analysis Services database and the objects found in the script.
  3. You can edit the XMLA script to add custom object through the XMLA language. This can be performed in any editor.
  4.  However, SQL Server Management Studio is a useful platform for making such changes, because the script is colour-coded and can be executed from within it to create the objects.
  5. After 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 Analysis Services. 
  6. Furthermore, after running the script and deploying the database, the newly created database must be processed before users can browse it.
  7. The XMLA script does not contain a password. If a password is specified in either the connection string for a data source or for impersonation purposes, you must add the password manually to the script before it executes or add it after the script executes.
4.Synchronize Database Wizard
  1. The Synchronize Database Wizard helps you to synchronize an Analysis Services database to another instance of SQL Server.
  2. This synchronization is one-way and cannot be performed on the same instance of Analysis Services.
  3. While the wizard synchronizes the data between the two databases, users can continue to query the destination database. 
  4. After synchronization finishes, Analysis Services automatically switches the users to the newly copied data and metadata, and drops the old data from the destination database.
5.Backup and Restore
  1. Analysis Services contains its own backup and restore utility.
  2. Using this method, you can back up an Analysis Services database and restore the database to a separate instance of Analysis Services.
6.Analysis Management Objects (AMO)
  1. AMO provides a programmatic interface to the complete command set for Analysis Services available to the developer. 
  2. Therefore, AMO can be used for deployment and also for the many administrative commands it otherwise supports.


Post a Comment