Process Analysis Services Objects with Integration Services
February 13, 2006
About the Series ...
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.
Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:
To successfully replicate the steps of the article, you also need to have:
Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.
In this lesson we revisit an approach to automated / scheduled processing of an Analysis Services database (and related objects), a subject that we undertook in my article Process Analysis Services Cubes with DTS (Database Journal Introduction to MSSQL Server Analysis Services series), published in May of 2005. In the earlier article, we learned that, among several tools that Microsoft SQL Server 2000 Analysis Services offered to assist us in maintaining our cubes in a state of readiness, one of two custom Data Transformation Services ("DTS") tasks that accompanied the installation of Analysis Services 2000, the Analysis Services Processing task, provided us a prefabricated mechanism for performing Analysis Services processing functions. The DTS Analysis Services Processing task, we noted, was specifically designed for the purposes of handling processing of Analysis Services databases, dimensions, cubes, or other objects that we could manually process from Analysis Manager, but which we could not easily manage using "home made" (or other pre-packaged) DTS tasks.
In this article, we will examine the processing of an Analysis Services 2005 cube via another of the integrated MSSQL Server 2005 components, Integration Services ("SSIS"). Virtually anyone who works with MSSQL Server in an RDBMS context, and especially within a data warehouse or mart design perspective and related functions, will probably interact at least occasionally with SSIS if only as an Import / Export utility. Just like its predecessor, DTS, SSIS is best known by early adopters primarily as a set of ETL (Extraction, Transformation and Load) utilities that accompany the integrated Microsoft BI Solution of which MSSQL Server 2005, Analysis Services, and Reporting Services are component parts. While SSIS does, indeed, perform well within the context of all of the stages of data transformation (examples include type conversions, scrubbing and validation, among others, to varying degrees), it also maintains a particularly high-profile role within the creation and maintenance of data warehouses, marts, or other such sources for business intelligence and organizational reporting, among many new additional features.
The flexible utility of SSIS packages is perhaps nowhere more apparent than within the realm of automation: they can be used to perform all manner of actions, including the execution of scripts and programs written in other languages, to help us to accomplish virtually any requirement necessary to meeting objectives of data warehousing, and far, far beyond.
In this article, we will overview the Analysis Services Processing task. As a part of our examination of this useful task, we will:
Process an Analysis Services Database with Integration Services
Overview and Discussion
As we stated in Process Analysis Services Cubes with DTS, the recurring need to process our cubes is one of the most critical administrative tasks surrounding an Analysis Services implementation. 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 either the Business Intelligence Development Studio or the SQL Server Management Studio (at least initially, in the design and development of our cubes and other Analysis Services objects). We can use this prefabricated task to automate our processing cycles, and thus keep our cubes updated to reflect the latest data in their underlying sources, all with minimal manual intervention.
As most of us know, processing is the step or steps that turn data into information for business analysis. This is true for all objects that we process, although the processing steps differ among various object types. Analysis Services 2005 requires that we deploy to the server the objects or projects that we design and build locally before we can perform processing. When deployment takes place, the re-creation and execution of our project occur thanks to a script that is sent to the targeted Analysis Services instance. While deployment processes all database objects by default, we have the option to change several facets of post-deployment processing, including the objects that are processed. The Analysis Services components to which we can apply our Integration Services-driven capabilities include those detailed, hierarchically, in Table 1.
Table 1: Component Selection Options for the Analysis Services Processing Task
Cascaded processing occurs when we select and process higher-level objects that contain subordinate objects. As an illustration, if we select a given cube for processing, execution will cause processing of the dimensions, measure groups and partitions that are contained within the cube.
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
NOTE: Properties for the Analysis Services Processing task can be set using the SSIS designer (this approach with which this article concerns itself) or programmatically.
When we process multiple objects at the same time, we can define settings that apply to the processing of all the objects in the batch, as we shall see. When working with batches of this sort, we can process the constituent objects sequentially (in series) or in parallel (assuming that order of processing of the objects is not a consideration). Processing batch members in parallel offers speedier processing in general, and the Analysis Services Processing task affords us the flexibility of directing the number of objects that can process simultaneously.
The Analysis Services Processing task offers us more options than its Analysis Services 2000 predecessor, even when we process sequentially. We can arrange grouping of all objects within a batch as a single transaction, or establish each as a separate transaction. Moreover, anytime we undertake to process Analysis Services objects, we can take advantage of the option to process objects that depend upon our selection, in addition to the selected objects themselves.
Additional options that the Analysis Services Processing task provides include allowing us to manage error handling within dimension keys (an "ignore" or "stop processing" choice). We can substitute custom error configurations that we construct, whereby we can direct task error conditions and handling, as well as specifying an error log path, for default error configurations. We often employ the Analysis Services Processing task in conjunction with one or more other Integration Services tasks whereby we perform some or all of extraction, transformation and loading processes to populate the data warehouse / mart upon which a cube is based. An example might be a case where we use a Data Flow task (or group of tasks) to populate the tables of our star schema using data from an online transactional (OLTP) database, and then execute the Analysis Services Processing task to process a cube we have constructed that uses the star schema as a data source.
We will gain some hands-on familiarity with the Analysis Services Processing task in the Practice section below. As a part of walking through the addition of an Analysis Services task to an Integration Services package we create, the configuration of the task, and the subsequent execution of the Integration Services package we construct to house it, we will comment on various settings and approaches as we encounter them.
Considerations and Comments
For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.
The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references, as well.