Working with Analysis Services Projects and Databases in a Production Environment
After you have developed and deployed your Analysis Services database from your Analysis Services project to an 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 Business Intelligence Development Studio. Other changes can only be made using Business Intelligence Development Studio, 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 Analysis Services database using either SQL Server Management Studio or Business Intelligence Development Studio in online mode, the Analysis Services project that was used for deployment becomes out of date. If a developer makes any changes within the 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 Analysis Services database.
There are several ways in which you can use 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 Analysis Services database, use Business Intelligence Development Studio to create a new Analysis Services project based on the modified version of the Analysis Services database. This new Analysis Services project can be checked into the source control system as the master copy of the project. This method will work regardless of whether the change was made to the Analysis Services database using SQL Server Management Studio or Business Intelligence Development Studio in online mode.
Method 2: Only make changes to the production version of an Analysis Services database using SQL Server Management Studio or Business Intelligence Development Studio in project mode. With this method, you can use options available to you in the 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. For more information, see Using the Analysis Services Deployment Wizard.
Method 3: Only make changes to the production version of an Analysis Services database using SQL Server Management Studio or Business Intelligence Development Studio 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.
See Also