In SSAS we build what’s known as a UDM, or Unified Dimensional Model, over the top of a database schema, usually a data warehouse. The UDM consists of several objects designed to aid analysis of the data. The UDM is built in BIDS (Business Intelligence Development Studio) and in terms of SSAS is synonymous with the more common term cube.
As the UDM is built and developed it needs to be deployed to an SSAS service for testing. Ultimately it will need to be deployed to production. Once the objects have been deployed, the resulting cube needs to be processed.
There are actually three stages, first the UDM needs to be built, then deployed and finally processed. Building and deploying are closely coupled operations but processing is likely to happen on an independent schedule.
1. Build.
When we are developing a UDM or Cube in BIDS it is possible to be in one of two modes; connected mode or project mode. In connected mode a connection is established directly with an Analysis Services Service and objects are edited “in situ”. The more familiar project mode is where objects are edited in the BIDS environment on a development machine and then deployed to testing or production.
In BIDS from the build menu – select the build option (or right click on the project in the solution explorer).
The build process will create four xml files in the bin subfolder of the project folder (this location can be overridden by right clicking the project and selecting properties).
As the names hint at, the .asdatabase file is the main object definition file and the three other files contain configuration and deployment metadata.
2. Deploy.
Deployment is the process of attaching the objects defined in the build file to a runnning Analysis Services service. This can be done in a number of ways.
In BIDS simply select deploy from the build menu. Deployment options can be configured by right clicking on the project, selecting properties and clicking on the deployment tab. Deployment via BIDS will overwrite the destination database management settings – so is not recommended for production deployment.
A more controllable option is the deployment wizard, available in interactive or command line mode.
Run the wizard from Start>All Programs>Microsoft SQL Server>Analysis Services>Deployment Wizard
1. Browse to the .asdatabase file created by the build.
2. Connect to the target server.
3. Configure how partitions and role should be deployed.
4. Specify how configuration settings are deployed.
5. Specify if objects should be processed as part of the deployment.
6. Finally, choose whether to deploy instantly or to create an XMLA command script for later deployment. The script will be created in the same location as the .asdatabase file.
Deployment can also be done using the Synchronize Database Wizard. This involves deploying to a target server by synchronising metadata from a designated source server/database.
Deployment is also possible using the .net analysis management objects (AMO) API.
I’m not going to cover either of these methods here.
3. Process.
Processing means populating our freshly deployed SSAS database with data from the data source (most likely a data warehouse of some sort attached to a SQL Server database engine). It is essentially the process of executing a number of select statements against the source database and storing the results in the cube. Aggregations and other materialised objects are also created or rebuilt at process time. A detailed description of SSAS processing is given in this whitepaper.
Processing can be done from BIDS, in SSMS either with the GUI or scripted in XMLA or through SSIS tasks. In BIDS the process option is available in the build menu or by right clicking the project node in the solution explorer. In SSMS, right click the database. Processing can be done at the database, cube, partition or dimension scope. Both methods result in a similar dialog.
The processing options for OLAP objects are:
Process Default: SSAS dynamically chooses from one of the following process options.
Process Full: Drop all object stores, and rebuild the objects. This option is required if any metadata changes have occurred.
Process Update: Only apply dimension member updates, inserts or deletes.
Process Add: Only apply new data.
Process Data: Process data only, do not rebuild indexes or aggregations.
Process Index: Rebuild indexes and aggregations without processing data.
Unprocess: Delete data from the object.
Process Structure: Drop the data and perform process default on all dimensions.
Processing can be done in parallel, and can be wrapped in a transaction to enable an aborted or failed process to be rolled back. Like most other SSAS management tasks, processing is implemented via an XMLA command script built in the background. This script also gives the ability to batch processing commands. For details of building these scripts look at the whitepaper linked above.
The Impact Analysis button produces a list of object interdependencies with their dynamically chosen process options preselected. You have the option here to choose to process dependant objects.
Proactive caching allows SSAS to dynamically execute processing tasks in response to changes in the data source. That is another story for another day.
