Processing Analysis Services Objects

As an administrator, you keep the Microsoft SQL Server Analysis Services objects in the production databases current by processing them. Processing is the step, or series of steps, that populate Analysis Services objects with data from relational data sources. Processing is different depending on the type of object and the selection of processing options.

While the processing job is working, the affected Analysis Services objects can be accessed for querying. The processing job works inside a transaction and the transaction can be committed or rolled back. If the processing job fails, the transaction is rolled back. If the processing job succeeds, an exclusive lock is put on the object when changes are being committed, which means the object is temporarily unavailable for query or processing. During the commit phase of the transaction, queries can still be sent to the object, but they will be queued until the commit is completed. For more information about locking and unlocking during processing, see Locking and Unlocking Databases (XMLA). For more information about performance issues related Analysis Services processing, see the section “Tuning Processing Performance” in the SQL Server 2005 Analysis Services Performance Guide.

Deployment and Processing

Before you can process an Analysis Services object, you must deploy it by sending it to the server. During deployment, an XMLA script is sent to an Analysis Services instance, and there the XMLA script executes one or more of the following actions:

  • A CREATE procedure if an Analysis Services database does not exist.

  • An ALTER procedure to update the database metadata.

  • A PROCESS procedure to process the database.

By default, deployment includes processing all the objects in the database. However, you can change the options for post-deployment processing. For more information about deployment, see Deploying Analysis Services Projects.

Project Mode and Online Mode

Working in Business Intelligence Development Studio, you design a database in project mode. When you want to analyze the data, you deploy the project to an Analysis Services instance. The deployment process sends project metadata from Business Intelligence Development Studio to the Analysis Services instance and creates an Analysis Services database. Changes to the Business Intelligence Development Studio project do not affect the Analysis Services database until the project is redeployed.

When you connect to an Analysis Services database from Business Intelligence Development Studio in online mode, changes to objects in Business Intelligence Development Studio directly affect the Analysis Services database without the need to redeploy.

In both project mode and online mode you have to process the Analysis Services objects to analyze or browse data. For more information about project and online mode, see Working with Analysis Services Projects and Databases in a Production Environment.

Objects Processed

Processing affects the following Analysis Services objects: measure groups, partitions, dimensions, cubes, mining models, mining structures, and databases. When an object contains one or more objects, processing the highest-level object causes a cascade of processing all the lower-level objects. For example, a cube typically contains one or more measure groups (each of which contains one or more partitions) and dimensions. Processing a cube causes processing of all the measure groups within the cube and the constituent dimensions that are currently in an unprocessed state. For more information about processing Analysis Services objects, see Processing (Analysis Services - Multidimensional Data).

During a processing job, whether an object is processed, and how it will be processed, depends on the processing option that is set for that object. For more information about the specific processing options that can be applied to each object, see Processing Options and Settings.

Starting Jobs to Process Objects

To process Analysis Services objects, you have many options. You can use SQL Server Management Studio from the Object Explorer, you can use Business Intelligence Development Studio from Solution Explorer, or you can run an XML for Analysis (XMLA) script. For more information, see How to: Process an Analysis Services Object.

If you prefer a programmatic approach, you can use Analysis Management Objects (AMO) to start to process jobs. For more information, see Programming AMO OLAP Basic Objects.

Using any one of these methods, you can fully or incrementally process all the objects that make up a cube, or you can specify processing for only those objects that require processing.

Impact Analysis

Before you process an Analysis Services object, you can analyze the effect on related objects by clicking Impact Analysis in one of the Process Objects dialog boxes. Unless explicitly specified otherwise, objects that depend on another object are not processed when the object to which they relate is processed. Therefore, if the dimension that you process is used in several cubes, those cubes will be in an unprocessed state when you process the dimension. This means that the cubes must be processed separately before they can be queried. In such a case, you can take advantage of batch processing settings to process both the dimension and the related cubes at the same time. For more information about processing related objects together, see Batch Processing in Analysis Services.

Reprocessing Objects

Cubes that contain unprocessed elements have to be reprocessed before they can be browsed. Cubes in Analysis Services contain measure groups and partitions that must be processed before the cube can be queried. Processing a cube causes Analysis Services to process constituent dimensions of the cube if those dimensions are in an unprocessed state. After an object has been processed the first time, it must be reprocessed either partially or in full whenever one of the following situations occurs:

  • The structure of the object changes, such as dropping a column in a fact table.

  • The aggregation design for the object changes.

  • The data in the object needs to be updated.

When you process objects in Analysis Services, you can select a processing option, or you can enable Analysis Services to determine the appropriate type of processing. The processing methods made available differ from one object to another, and are based on the type of object. Additionally, the methods available are based on what changes have occurred to the object since it was last processed. If you enable Analysis Services to automatically select a processing method, it will use the method that returns the object to a fully processed state in the least time. For more information, see Processing Options and Settings.

Batch Processing

You can process multiple Analysis Services objects in a batch. Using batch processing, you control the objects that are processed and the order in which the processing occurs without having to perform a full processing operation. For example, you might want to process a certain partition in a measure group and the dimensions that affect that partition. Instead of processing at a database or cube level, you can select only the objects you want to process. For more information, see Batch Processing in Analysis Services.