Edit

Work with Analysis Services Projects and Databases in Production

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

After you have developed and deployed your SQL Server Analysis Services database from your SQL Server Analysis Services project to an SQL Server Analysis Services instance, you must decide how you wish to make changes to objects in the deployed database. Certain changes, such changes related to security roles, partitioning, and storage settings, can be made using either SQL Server Management Studio or SQL Server Data Tools. Other changes can only be made using SQL Server Data Tools, either in project mode or in online mode (such as adding attributes or user-defined hierarchies).

As soon as you make a change to a deployed SQL Server Analysis Services database using either SQL Server Management Studio or SQL Server Data Tools in online mode, the SQL Server Analysis Services project that was used for deployment becomes out of date. If a developer makes any changes within the SQL Server Analysis Services project and attempts to deploy the modified project, the developer will be prompted to overwrite the entire database. If the developer overwrites the entire database, it must also be processed. This issue becomes compounded if the changes made directly to the deployed database by the production staff were not communicated to the development team because they will not understand why their changes no longer appear in the SQL Server Analysis Services database.

There are several ways in which you can use SQL Server SQL Server Analysis Services tools to avoid the problems inherent in this situation.

  • Method 1: Whenever a change is made to a production version of an SQL Server Analysis Services database, use SQL Server Data Tools to create a new SQL Server Analysis Services project based on the modified version of the SQL Server Analysis Services database. This new SQL Server Analysis Services project can be checked into the source control system as the primary copy of the project. This method will work regardless of whether the change was made to the SQL Server Analysis Services database using SQL Server Management Studio or SQL Server Data Tools in online mode.

  • Method 2: Only make changes to the production version of an SQL Server Analysis Services database using SQL Server Management Studio or SQL Server Data Tools in project mode. With this method, you can use options available to you in the SQL Server Analysis Services Deployment Wizard to preserve changes made by SQL Server Management Studio, such as security roles and storage settings. This ensures that the design-related settings are kept in the project file (storage settings and security roles can be ignored) and the online server is used for storage settings and security roles.

  • Method 3: Only make changes to the production version of an SQL Server Analysis Services database using SQL Server Management Studio or SQL Server Data Tools in online mode. Since both tools are only working with the same online server, there are no possibilities of getting different version out of sync.