Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS - Page 2
May 9, 2005
DTS' Analysis Services Processing Task
Overview and Discussion
One of the most critical administrative tasks surrounding an Analysis Services implementation is the recurring need to process our cubes. Processing is essential to keeping our OLAP data sources in sync with the data sources that they summarize. The Analysis Services Processing task exists to perform the processing options that we typically perform manually from within Analysis Manager, at least initially, in the design and development of our cubes. We can use it to automate our processing cycles, and thus keep our cubes updated to reflect the latest data in the cubes' underlying sources, all with minimal manual intervention.
Because some Analysis Services processing capabilities, including the processing of databases, dimensions, cubes, or other objects we can manually process from Analysis Manager, could not be easily managed using other DTS tasks, we are provided with a means of performing these activities with a task specifically designed for that purpose. The Analysis Services components to which we can apply our DTS-driven capabilities include those detailed, hierarchically, in Table 1.
Table 1: Component Selection Options for the Analysis Services Processing Task
We also have the options depicted in Table 2 with regard to the manner in which we perform processing.
Table 2: Processing Options and the Components to Which They Apply
Other considerations affect the options that are available, as well. An example is illustrated in the selection of multiple objects in the tree structure provided within the Analysis Services Processing task dialog; anytime we select two or more objects within the nodes of the tree, full processing / rebuild is our only selection option.
As we note in the tables above, the Analysis Services Processing task allows us the flexibility to leverage the time savings that accompany incremental updates. While, as I mentioned in the last paragraph, we are forced to do full processing in a scenario where we select a cubes folder to process multiple cubes, we can perform an incremental update on a cube group if we create an individual task for each cube (which we can then "chain" together within a DTS package). The same is true when we select a dimensions folder: we are forced to do a complete rebuild of these dimensions, or to create a separate task for each one. (In general, we can meet any processing sequencing need not specifically "built in" to the Analysis Services Processing task through chaining tasks in a larger DTS package.)
NOTE: For more information on processing cubes and dimensions in general, see my other articles in my Database Journal series, Introduction to MSSQL Server Analysis Services, indexed at
We will gain some hands-on familiarity with the Analysis Services Processing task in the Practice section below. In walking through the creation of an Analysis Services task, and the subsequent execution of the DTS package we construct to house it, we will comment on various settings and approaches as we encounter them.