Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS

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 adding features and techniques designed to meet
specific real – world needs. For more information on the series, as well as the
hardware / software requirements to prepare for the exercises we will
undertake, please see my initial article, Creating Our First Cube.

Introduction

In this article, we will examine the processing of an
Analysis Services cube via another of the integrated MSSQL Server
components, Data Transformation Services ("DTS").
Virtually anyone that works with MSSQL Server in an RDBMS context, and
often within a data warehouse or mart design perspective and related functions,
has probably interacted in some way with DTS – if only as an Import /
Export utility. Best known as the set of ETL (Extraction,
Transformation and Load) utilities that accompany the integrated Microsoft
BI Solution as a part of MSSQL Server, DTS 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. DTS also maintains a particularly high-profile role within the
creation and maintenance of a data warehouse, mart, or other such source for
business intelligence and organizational reporting.

A significant part of DTS’ power within the Microsoft BI
solution, among other combinations, is its inherent integration with the
Microsoft Universal Data Access and ActiveX technologies. The resulting "expanded
access" means that DTS works equally well in extracting, transforming and
loading data from ODBC- and OLE DB compliant sources. DTS is the tool of choice
for many other data "movement and manipulation" needs, and I like to
think of its uses as belonging to either these sorts of activities, or the
running of programs, scripts, etc., to act as an agent of automation of some
sort or other – which will actually be the kind of thing we examine in this
article.

There are, of course, many things that DTS can be used to
accomplish that do not necessarily fit into neat classifications: For example,
I used DTS in another article of this series, Introduction
to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two
Perspectives, whereby I show how to perform cube drillthrough
activity via DTS-mobilized MDX. I suggest the article as one of many uses of
MDX that might not readily occur to the "casual user." Moreover, the
flexible utility of DTS 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 one of two custom DTS tasks
that accompany the installation of Analysis Services, 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;

• Perform a
  practice exercise, whereby we conduct full processing of a sample cube with the
  Analysis Services Processing task;

• Discuss
  options that are available to us within the Analysis Services Processing task;

• Note the
  benefits of using the Services Processing task, particularly in
  combination with scheduling.