Microsoft Docs

Deploying Analysis Services Solutions

This document provides a comprehensive guide to deploying SQL Server Analysis Services (SSAS) solutions. Deployment involves moving a developed SSAS project from a development environment to a production or test environment. This process typically uses the deployment wizard or command-line utilities.

Deployment Methods

There are two primary methods for deploying Analysis Services solutions:

• SQL Server Management Studio (SSMS) Deployment Wizard: This is the most common and user-friendly method. It guides you through the deployment process step-by-step.
• Command-Line Deployment: For automated deployments or integration into build processes, you can use command-line tools like AnalysisServices.exe or PowerShell cmdlets.

Using the SSMS Deployment Wizard

To deploy a solution using the SSMS Deployment Wizard:

1. Open SQL Server Management Studio.
2. Connect to the SQL Server Analysis Services instance where you want to deploy the solution.
3. In Solution Explorer, right-click on your Analysis Services project and select "Deploy".
4. The "Analysis Services Deployment Wizard" will launch.
5. Introduction: Click "Next".
6. Deployment Source: Select "Deploy the Analysis Services project".
7. Source Project: Browse to and select your SSAS project file (.dwproj).
8. Configuration File: You can optionally select a deployment configuration file (.deploymentconfig) if you have pre-configured settings.
9. Server: Specify the target Analysis Services server name.
10. Database: Specify the target database name for the deployment. You can choose to create a new database or deploy to an existing one.
11. Deploy Objects: Select which objects (cubes, dimensions, etc.) you want to deploy.
12. Process Objects: Choose how to process the deployed objects. Options include:
    • "Process default" (retains existing processing state)
    • "Full data" (processes all data)
    • "Clear data" (clears existing data without processing)
13. Connection Strings: Review and modify connection strings for data sources if necessary.
14. Parameters: Configure any deployment parameters defined in your project.
15. Custom Code: Specify any custom deployment scripts or code if applicable.
16. Summary: Review your selections.
17. Deployment Progress: The wizard will execute the deployment. Monitor the progress and check for any errors.
18. Results: The final screen will show the deployment results.

Command-Line Deployment

For automated deployments, the AnalysisServices.exe command-line utility or PowerShell cmdlets are invaluable. These tools use the same deployment configuration file (.deploymentconfig) and project file (.dwproj).

Using AnalysisServices.exe

The basic syntax for AnalysisServices.exe is:

AnalysisServices.exe /Project:C:\Path\To\YourProject.dwproj /ConfigurationFile:C:\Path\To\YourConfig.deploymentconfig /Server:YourServerName /Database:YourDatabaseName

Using PowerShell

PowerShell provides more flexibility. You can use the Microsoft.AnalysisServices.PowerShell.Cmdlets module.

Import-Module SqlServer
Invoke-ASDatabaseDeployment -SourceDatabaseFile "C:\Path\To\YourProject.asdatabase" -TargetServer "YourServerName" -TargetDatabase "YourDatabaseName" -DeploymentConfiguration "C:\Path\To\YourConfig.deploymentconfig"

Note that when deploying from a project file via PowerShell, you often export the project to an .asdatabase file first.

Post-Deployment Steps

After a successful deployment, consider the following:

• Processing: Ensure all necessary objects are processed correctly, especially if you chose "Process default" or "Clear data" during deployment.
• Security: Verify that database roles and permissions are set up as intended.
• Testing: Conduct thorough testing of the deployed solution to ensure data integrity and query performance.
• Automation: Integrate deployment into your CI/CD pipeline for repeatable and reliable deployments.

By following these guidelines, you can ensure efficient and effective deployment of your SQL Server Analysis Services solutions.