Friday, June 15, 2018

MS Analysis Services:How to sync SSAS Cube?

When business users accessing a SSAS cube by connecting via excel or any other front end tool and at the same time if you process the cube then user can be affected badly. User simply can't access the cube if you are processing the same.

To avoid the above scenario, we can have two different SSAS server, one for Processing and other for Query. Business user should connect the Query server. So while you processing the cube it will update the Processing server; so user  can't be affected while you are processing the cube since user will be connected with Query server.

Today's writing will cover how to sync cube from one server to other.

Step 1: Find the Query server where you want to sync.

Click at the database of the server and then click Synchronize as like below fig 1


Fig 1: Sync to the Query server

Step 2: Synchronize wizard

At this step it explains what do you with the wizard

Fig 2: synchronize wizard

Step 3:  Choose the server from where you would like to sync which can be your processing server and Destination server will be automatically chosen.

Fig 3: Choose source and Destination to Sync

Step 4: Specify location for the partition, you can leave as default

Fig 4: location for partition

Step 5: Choose security roles and members

You need to take this part as per your need, if you don't want to copy any role and security from the processing server then you can choose third option 'Ignore all'. And if you want role need to copied but not members then choose 2nd option.

Fig 5: Choose what to Copy from processing Server

Step 6: Save script or Synchronize

At this last step, you can either start synchronize or generate the script to save in a file for later uses.
Fig 6: Last step of synchronization

You may choose Synchronize now, however; you can copy the script so that you can add the script to a SQL Server Agent job. 

Why do you create SQL Server Agent job?

If you need to sync the cube from Processing server to Query server time to time then creating job will be the best solution. Hence, the script saved from step 6 can be used to create a job. And whenever you need to sync the cubes you can run the job.