Overview
Data is not generally added / updated in SQL Server Analysis Services (SSAS) like database tables in the SQL Server relational engine. Mostly data is brought into SSAS database objects from external relational data sources. Data is updated either by processing SSAS database objects or by synchronizing databases from different servers / environments. We are going to discuss both options here.
Explanation
Processing SSAS Database Objects
Processing loads data from relational databases into SSAS objects. It is a series of tasks that read, aggregate, modify and load data into different SSAS database objects. There are 9 different types of processing options to add / update / remove data and/or structure of SSAS database objects. Below is a list of the options:
- Process Add
- Process Clear
- Process Clear Structure
- Process Data
- Process Default
- Process Full
- Process Index
- Process Structure
- Process Update
Depending upon the database object, different processing options would be applicable to each database object. Right-click the corresponding database object in the Object Explorer window of SSMS, and select the Process option. This would bring up a dialog box as shown in the below screenshot, which allows you to select the applicable processing options.
SSAS Synchronize Database Wizard
Database synchronization updates the data as well as metadata from source server to the target server. The SSAS Synchronize Database Wizard is used to synchronize data from the source to the target. One key advantage of the synchronization process is that while databases are being synchronized, databases still remain available and as soon as the synchronization completes, SSAS switches to the new copy of the database. To synchronize one SSAS database with another, on the target server, right-click the database folder in the object explorer window and select Synchronize. This should bring up the Synchronize Database Wizard. Select Next and it should bring up the screen as shown below. Select the source database on this screen.
The next screen shows the details of the objects being synchronized.
Click Next, and you should be able to see the Security and Compression settings. Generally while synchronizing from lower environments to production environments, security settings won’t be copied over. In that case you can ignore synchronizing any security related information.
Click Next, which should take you to the screen where you have the option to either start the synchronization process or save the synchronization script to a file.
Click Next, and you should be able to finally see the Summary screen which shows the details of synchronization. Click Finish to complete the synchronization.
Additional Information
- Processing options in analysis services is a detailed subject. Consider reading this article to understand it in more detail.
- There are a number of SSAS Wizards which are very useful for administrative purposes. Consider reading this article to get an overview of SSAS Wizards.
