Updating Analysis Services Solutions

This document outlines the procedures and considerations for updating existing Analysis Services (SSAS) solutions. Updating a solution can involve deploying new versions of your multidimensional or tabular models, applying schema changes, or migrating to a new instance.

Understanding the Update Process

When you update an SSAS solution, you are essentially replacing the existing deployed database with a new version. This process can be achieved through several methods, including:

Deploying a new project from SQL Server Data Tools (SSDT).
Using XMLA scripts for programmatic updates.
Leveraging tabular model deployment wizards or APIs.

Prerequisites and Considerations

Before initiating an update, ensure the following:

Backup: Always perform a full backup of your existing Analysis Services database before deploying any updates. This is crucial for disaster recovery.
Downtime: Plan for potential downtime. While some updates can be performed with minimal disruption, especially for tabular models, multidimensional model updates might require significant processing and can impact query availability.
Permissions: Ensure you have the necessary administrative privileges on the Analysis Services instance to deploy and manage databases.
Compatibility: Verify that the new version of your solution is compatible with the target Analysis Services server version.
Dependencies: Understand any external dependencies, such as ETL processes or reporting services, that rely on the Analysis Services database.

Methods for Updating Solutions

1. Deploying from SQL Server Data Tools (SSDT)

This is the most common method for developing and deploying SSAS solutions.

Open your Analysis Services project in SSDT.
Make the necessary modifications to your model (e.g., add new dimensions, change measures, modify tables).
Right-click on the project in Solution Explorer and select Deploy.
In the SSAS Deployment Wizard, specify the target server and database name.
Choose to Replace the existing database.
Review the deployment summary and click Finish.

For tabular models, SSDT often handles delta deployments more efficiently than full replacements.

2. Using XMLA Scripts

XML for Analysis (XMLA) provides a powerful way to script deployments and updates.

You can generate XMLA scripts from SSDT or write them manually. Key commands include:

Alter Database to modify database properties.
Deploy Database to deploy a new or updated database.
Process Database to process data after deployment.

Example of an XMLA script to deploy a new version:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Create Database="MyCubeDatabase" />
    <Update>
        <DatabaseInfo>
            <Name>MyCubeDatabase</Name>
            <Source>
                <File>C:\Path\To\Your\NewModel.asdatabase</File>
            </Source>
        </DatabaseInfo>
    </Update>
    <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2010/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2010/engine/2/2" TargetError="Ignore500" Continue="Continue" ProcessFull="true" ObjectProcessingKind="Default" RestartDependentObjects="false" AllowImplicit AffectStructures="false" >
        <Object>
            <DatabaseID>MyCubeDatabase</DatabaseID>
        </Object>
    </Process>
</Batch>

Manually crafted XMLA scripts require a deep understanding of SSAS object model and syntax.

3. Scripting with Tabular Editor

For tabular models, tools like Tabular Editor offer advanced scripting capabilities using C# and TOM (Tabular Object Model) to automate complex updates and migrations.

Post-Deployment Steps

After a successful deployment, the following steps are usually necessary:

Data Processing: The newly deployed or updated database often needs to be processed to load data from your sources. This can be done manually, via XMLA, or through scheduled jobs.
Testing: Thoroughly test the updated solution by running queries, verifying reports, and checking data integrity.
Monitoring: Monitor server performance and query response times after the update.

Ensuring all data partitions are correctly processed and visible after an update is critical for business continuity.

Rollback Strategy

Have a clearly defined rollback strategy in place. This typically involves restoring the database from the backup taken before the update. Ensure your rollback procedure is tested.

MSDN Documentation