How to deploy using alter script in ssas

Question

Is it wrong if I create an alter script in the entire database in the analysis service in the SSMS of the development server and run this script on the SSMS of the production server instead of deploying via BIDS?

Answer 1

no, you really shouldn't use BIDS to deploy to prod. BIDS always overwrites the management settings (security and partition) of the target server.

The best option is to use the Deployment Wizard. It allows you to generate an incremental deployment script that updates the cube structures and dimensions. You can customize how roles and sections are handled. It uses the XML output files created when creating SSAS in BIDS as input files, and you can run several modes:

• Quiet mode (/ s): starts the utility in silent mode and does not display any dialog boxes.
• Answer file mode (/ a): Do not deploy. Modify only the input files.
• Output Mode (/ o): The user interface is not displayed. Create an XMLA script to be sent to the deployment targets. Deployment will not happen.

If you need full synchronization, you can use the Database Synchronization Wizard. It almost clones the database. When the destination database already exists, it performs metadata synchronization and incremental data synchronization. When the target database does not exist, the data is fully deployed and synchronized.

Answer 2

I believe that the main drawback of writing an entire database is that everything can be recycled. Also, if another team or team member is responsible for deploying the script, it can be much harder to reconsider and understand if everything will be rebuilt with each update.

I work at Red Gate and we recently introduced a free tool called SSAS Compare to help deal with this scenario. This will help you create a script containing only the changes you want to deploy.