Process Analysis Services Objects with Integration Services

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.

Introduction

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:

• Introduce the Analysis
  Services Processing task, and discuss its uses;

• Prepare for
  our practice exercise by creating an Integration Services project within
  Business Intelligence Development Studio;

• Create an Integration
  Services package to house our Analysis Services Processing task,
  using SSIS Designer;

• Add, and
  configure, an Analysis Services Processing task within the package;

• Execute the
  package to ascertain its effectiveness.

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.

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.

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.