SSAS adding exisiting cube from one environment to another

Question

I would like to add cubes that are in production environment to Dev Environment. Dev has additional cubes and would like to know if there is a way to add additional cubes from prod without overwriting the cubes in dev environment.

Answer 1

You have several options...

1. Take a backup of the production SSAS database and restore it.
2. Generate and XMLA script from the production cubes and run it on dev.
3. Use the Synchronize data wizard in Management Studio (the caveat here is that the account running the SSAS service on the dev environment must have access to the SSAS database in the prod environment).
4. Import the cubes into a BIDS/SSDT project and deploy them to dev.
5. Analysis Services Deployment wizard

Here is a link to TechNet that explains all of your options.

Here is a bit of explanation for the first and fourth option.

1) Take (or ask your dba to take) a backup of the prod cubes and restore them to your dev environment. Information on how to do this can be found here. If you do this, you will be restoring an entire SSAS database rather than specific cubes. So you will end up with 2 SSAS databases on your dev server.

4) Open the production cubes in SSDT by creating a new project and choosing import from server. Then change the project settings to deploy that cube to the dev server. Enter the info for the production server and database.

Once you have the prod SSAS database in your project, change the Project properties so the target server is the dev server and deploy the cubes.