By: Siddharth Mehta
Overview
Generally in development environments, development teams develop SSAS solutions including a SSAS database and database objects on the development server. Once the development is complete, the solution is processed and deployed. Processing a database objects directs SSAS to read data from the data source, calculate and aggregate the data, and store it in the cube structure as per the design. Deploying the database results in the database objects structure being deployed on the SSAS server. In this chapter we will briefly look at both of these aspects of SSAS multidimensional database deployment.
Explanation
In general, there are five standard methods of deploying SSAS databases which are mentioned below.
1) XMLA Scripts: Deployment using scripts is one of the most common methods used by developers as well as admins in the SQL Server world. In SSAS instead of T-SQL Scripts, XMLA scripts gets generated for database objects. The same scripts can be used for deployment.
2) Deployment Wizard: As a standard development practice, the SSAS solution is generally version controlled using Team Foundation Server (TFS). All the solution objects are generally checked into TFS and an admin is also provided access to the same. Using the files contained in the bin folder of the SSAS solution, the deployment wizard can be used to deploy the SSAS database. A detailed explanation of using SSAS Deployment Wizard can be read from this tip.
3) Synchronize Wizard - This wizard facilitates the synchronization of database objects from one server to another.
4) Backup / Restore - As with any database environment, backup and restore is a standard and routine process, and can be used as a deployment option.
5) AMO Automation - Highly experienced developers / administrators can use Analysis Management Objects (AMO) (which is a SDK interface) to programmatically administer SSAS and deploying SSAS databases.
SSAS Solutions are deployed when the structure or design of the database object changes, but these database objects are often processed to refresh incremental or the entire data set from the data source. Depending on the requirements, the appropriate processing options are used. SSAS provides the following processing options for database objects - Process Default, Process Full, Process Clear, Process Data, Process Add, Process Update, Process Index, Process Structure and Process Clear Structure. You can read more about processing a multidimensional model from here.
While deploying database objects, one important consideration is to define storage modes and partitioning of measure groups. The same can be done from the Partition tab in the cube designer. We intentionally did not cover this topic in the previous chapter, as it's a more appropriate fit for deployment purposes. SSAS Multidimensional solutions can be configured in three types of storage modes - Multidimensional OLAP (MOLAP), Real-time OLAP (ROLAP) and Hybrid OLAP (HOLAP). Each of these storage modes has its benefits and limitations. Consider reading more about partition and storage modes in multidimensional model from here.
Additional Information
- Consider exploring each processing option in detail as it's almost an everyday activity for SSAS solutions.
