SQL Server Analysis Services

Deploying Multidimensional Models

This document guides you through the process of deploying your SQL Server Analysis Services (SSAS) multidimensional models from your development environment to a production or staging server.

Deployment Options

There are several methods to deploy your SSAS models:

• Using SQL Server Data Tools (SSDT): The most common method, directly from your Visual Studio project.
• Using XMLA Scripts: Manually execute Deployment Wizard generated or custom XML for Analysis (XMLA) scripts.
• Using PowerShell: Automate deployment tasks with SSAS PowerShell cmdlets.
• Using Tabular Editor: A powerful external tool for managing and deploying SSAS models.

Deploying with SQL Server Data Tools (SSDT)

This is the standard approach for most developers.

1. Build the Project: In Visual Studio, right-click on your Analysis Services project in Solution Explorer and select "Build". This compiles your model into deployable files.
2. Deploy: Right-click on the project again and select "Deploy".
3. Deployment Wizard: The Analysis Services Deployment Wizard will launch.
   • Server Name: Enter the name of your target Analysis Services instance.
   • Database Name: Specify the name for the new database on the target server, or select an existing database to overwrite.
   • Configuration Files: You can use default deployment configurations or specify custom ones for different environments (e.g., Development, Test, Production).
   • Process & Deploy: Choose whether to process the newly deployed database objects (load data) and to deploy them.
4. Review and Deploy: The wizard will show a summary. Click "Finish" to start the deployment process.

Understanding the Deployment Wizard and Configuration Files

The Deployment Wizard generates a solution file (`.asdatabase` or `.cube` for older versions) and an XML deployment configuration file (`.deploymentconfig`). These configuration files are essential for customizing deployments without rebuilding the entire project.

Customizing Deployment Configurations

You can modify these configuration files to change settings like server names, database names, or even specify different connection strings for data sources in different environments.

To edit deployment configurations:

1. In Visual Studio, right-click on your Analysis Services project.
2. Select "Properties".
3. Go to the "Deployment" tab.
4. Here you can create and manage different deployment configurations.

<!-- Example snippet from a .deploymentconfig file -->
<ConfigurationHolder>
  <DataSourceItems>
    <DataSourceItem>
      <DataSourceID>AdventureWorksDW2019</DataSourceID>
      <ConnectionString>Provider=SQLNCLI11;Server=YourProductionServer;Database=AdventureWorksDW2019;Integrated Security=SSPI;</ConnectionString>
    </DataSourceItem>
  </DataSourceItems>
  <DatabaseName>AdventureWorksDW_Production</DatabaseName>
</ConfigurationHolder>

Deploying using XMLA Scripts

After building your project in SSDT, you can generate an XMLA script that encapsulates the entire deployment process. This is useful for scripting deployments or using tools like SQL Server Management Studio (SSMS).

1. In SSDT, right-click the project and select "Properties".
2. Navigate to the "Deployment" tab.
3. Under "Deployment Options", select "Generate Deployment Script".
4. This will create a `.asdatabase` file (which is a compressed archive containing the model definition and XMLA) and a `deploymentscript.xml` file.
5. You can open the generated `.asdatabase` file in SSMS, or use the `deploymentscript.xml` with the `Invoke-ASDeployment` cmdlet in PowerShell or custom XMLA execution.

Processing Deployed Databases

After deployment, the data within your Analysis Services database needs to be processed. This loads the data from your source systems into the cubes and dimensions. You can choose to process objects during deployment or separately.

Processing methods:

• Full Process: Clears all existing data and reprocesses all objects from scratch.
• Process Default: Processes objects that have never been processed, or that require incremental processing.
• Process Incremental: Processes only new or changed data, typically for large fact tables.
• Process Add: Adds new data to existing partitions without affecting old data.

Deployment Best Practices

• Version Control: Store your SSDT projects and any custom scripts in a version control system.
• Environment Configurations: Use separate deployment configurations for development, testing, and production environments.
• Permissions: Ensure the account performing the deployment has the necessary administrative privileges on the target SSAS instance.
• Automated Deployments: For CI/CD pipelines, leverage PowerShell or other scripting tools to automate the deployment process.
• Testing: Always test deployments in a non-production environment before deploying to production.