MSDN Documentation

Deploying SQL Server Analysis Services Projects

This guide outlines the essential steps and considerations for deploying SQL Server Analysis Services (SSAS) projects from development to production environments. Successful deployment ensures your multidimensional or tabular models are accessible and performant for your end-users.

Deployment Methods

SSAS projects can be deployed using several methods, each suited for different scenarios:

  • SQL Server Data Tools (SSDT): The primary tool for development, SSDT also provides a straightforward deployment wizard.
  • Analysis Management Objects (AMO): A .NET library for programmatic deployment and administration of SSAS.
  • XMLA Scripts: Executing XML for Analysis (XMLA) scripts directly against the SSAS instance.
  • SQL Server Management Studio (SSMS): Allows direct deployment of `.asdatabase` files.

Prerequisites for Deployment

Before deploying your SSAS project, ensure the following prerequisites are met:

  • SSAS Instance: A running instance of SQL Server Analysis Services must be available.
  • Permissions: The account performing the deployment needs appropriate administrative privileges on the target SSAS instance. Typically, membership in the Server Administrators role is required.
  • Firewall Rules: Ensure that necessary ports (default is 2383 for TCP) are open between the client machine and the SSAS server.
  • Data Source Connectivity: The SSAS server must be able to connect to the underlying data sources used by your models. This often involves configuring service accounts or providing credentials.

Step-by-Step Deployment using SSDT

This is the most common and user-friendly method:

  1. Open your SSAS project in SSDT.
  2. Build the project: Navigate to Build > Build Solution. This generates the deployment files.
  3. Deploy the project: Right-click on the project in Solution Explorer and select Deploy.
  4. Deployment Wizard:
    • Server: Enter the name of your SSAS server instance (e.g., SERVERNAME\INSTANCE).
    • Database: Specify the name for your new SSAS database or select an existing one to overwrite.
    • Configuration: Choose the correct deployment configuration (e.g., Debug, Release). This is crucial for managing data source connections and other settings.
    • Credentials: Review and configure data source credentials. You can specify impersonation options or provide specific Windows/SQL Server credentials.
  5. Review and Deploy: The wizard will show a summary. Click Deploy to initiate the process.

Example Deployment Configuration (app.config or .deployment file)

<?xml version="1.0" encoding="utf-8" ?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="14.0">
  <PropertyGroup>
    <SSASConnectionString>Provider=MSOLAP;Data Source=SERVERNAME\INSTANCE;</SSASConnectionString>
    <Configuration>Release</Configuration>
  </PropertyGroup>
  <ItemGroup>
    <DeploymentConfigurationFiles Include="bin\Release\YourModel.asdatabase" />
  </ItemGroup>
  <Target Name="Deploy">
    <CreateDatabase ... />
    <DeployProject ... />
  </Target>
</Project>

Post-Deployment Steps

After a successful deployment, consider the following:

  • Process the Database: Ensure all dimensions, cubes, or tabular models are processed to load data. This can be done via SSDT, SSMS, AMO, or scheduled jobs.
  • Security Roles: Configure and assign users to the security roles defined within your SSAS database.
  • Testing: Thoroughly test the deployed model from client applications (e.g., Power BI, Excel PivotTables) to verify data accuracy and performance.
  • Automation: For production environments, automate the build and deployment process using CI/CD pipelines (e.g., Azure DevOps, Jenkins).

Important Note on Credentials

Managing credentials securely is paramount. Avoid hardcoding sensitive information directly in deployment scripts. Utilize secure credential stores or impersonation settings provided by SSAS.

Tip for Large Deployments

For very large databases, consider deploying model metadata first, then processing and syncing data separately to minimize downtime and ensure data integrity.

Troubleshooting Common Deployment Issues

  • Connection Errors: Verify server name, instance name, and firewall settings. Check SSAS service account permissions.
  • Permissions Errors: Ensure the deployment account has sufficient rights on the SSAS server.
  • Data Source Errors: Confirm that the SSAS server can reach the data sources and that credentials are correctly configured and valid.
  • Model Corruption: If the deployed model is unstable, try cleaning and rebuilding the project, or redeploying from a known good build.