Microsoft BI Implementation - Cube back up and restore using XMLA command

Current BI implementation require to deploy the cube in multiple location to make it available  locally in each location to improve the performance. Cube development happening in one location say X but since the company is having presence in around the world and so business users sits in different locations X, Y and Z. Internet connectivity between X, Y and Z are not good and accessing the cube deployed in X from Y and Z is very slow and it impact the end user productivity.

To overcome this issue, cube has to be deployed in Y and Z locally and upload the connection in their local SharePoint as mentioned below.



Fig 1

Now let see how we can automate all these process from taking back up of the cube database to restore it in each location.

Follow the below mentioned steps to automate back up and restore of cube database.

- select the back up option by right click on the cube database to be backed up and generate the XMLA script after providing necessary properties







Fig 2

and the script looks like below.

Fig 3

- Create SSIS package and include this script in Analysis service Execute DDL task. Here in this task we need to mention the server name and the database to be backed up.

Fig 4

- Now the back up part is automated and the next level is restore this back up to another location. right click on the same cube database and select restore button. Provide all the necessary properties and generate restore XMLA command and we can include this command in our SSIS package.

Fig 5

and the script looks like this

Fig 6

- Include this script in SSIS package Analysis service Execute DDL task. This can be repeated for each location and create separate task in SSIS for each location.

- Once the SSIS package is finished with back up and restore commands, schedule this command using SQL agent, windows Scheduler or any other compatible tools.