The cube synchronization in SQL 2008 is vastly improved compared to SQL 2005 (much faster). On the target (or production) server you have the option to run the Synchronization Wizard within SQL Server Management Studio (SSMS) to pull data from a source (or staging) server, but what if you want to run the ETL and process the cube on a staging server and then push the changes to the production server once all processing is complete? This can be done by simply adding a step at the end of your SQL Server Agent job that runs on the staging server and executing a SQL Server Analysis Services Command.
In the screenshot below, the “Load Data” step of the SQL Server Agent job runs the ETL to load the data warehouse and processes the cubes on the staging server. By using this approach instead of processing the cube on the production server, the production server can be queried by end users without interruption while the cube is processed on the staging server.
The second “Cube Sync” step is used to sync all changes to the cube on the production server by pushing the changes from staging. This is done with the following command.
Notes
In the screenshot below, the “Load Data” step of the SQL Server Agent job runs the ETL to load the data warehouse and processes the cubes on the staging server. By using this approach instead of processing the cube on the production server, the production server can be queried by end users without interruption while the cube is processed on the staging server.
The second “Cube Sync” step is used to sync all changes to the cube on the production server by pushing the changes from staging. This is done with the following command.
Notes
- Since we are syncing to the production server once the cube is processed on the staging server the users experience little to now downtime when querying the production server during the sync.
- You can replace TARGETSERVER and SOURCESERVER with the relevant server name or IP.
- More than likely, you will need to setup the Cube Sync step to run as a domain user with access to both the staging and production servers. The SQL Server Agent Service Account will more than likely not work since you are moving data between two different servers.
- You can choose to include or exclude syncing the security information when executing the command. In this example, I am not moving the security by adding the “SkipMembership” text to the <SynchronizeSecurity> tag.
