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.

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:

  • Microsoft SQL
    Server 2005 Database Engine

  • Microsoft SQL
    Server 2005 Analysis Services

  • Microsoft SQL
    Server 2005 Integration Services

  • Business
    Intelligence Development Studio

  • Microsoft SQL
    Server 2005 sample databases

  • The Analysis Services
    Tutorial sample project and other samples that are available with the
    installation of the above.

To
successfully replicate the steps of the article, you also need to have:

  • Membership
    within one of the following:

    • the Administrators local group on the Analysis
      Services
      computer
    • the Server role in the instance of Analysis
      Services
      .
  • Read permissions within any SQL
    Server 2005
    sample databases we access within our practice session, if
    appropriate.

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.

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.

Component

Use This Option to:

Database

Process (full or
default) all cubes, measure groups, partitions, dimensions,
mining models and mining structures within the selected Analysis
Services
database.

… Cube

Perform various types
of processing (see Table 2 below for details) with the selected cube(s).

…. Measure
Group

Perform various types
of processing (see Table 2 below for details) with the selected measure
group(s)
.

….
Partition

Perform various types
of processing (see Table 2 below for details) with the selected partition(s).

… Dimension

Perform various types
of processing (see Table 2 below for details) with the selected dimension(s).

… Mining Model

Perform various types
of processing (see Table 2 below for details) with the selected mining
model(s)
.


…. Mining
Structure

Perform processing (default,
full or unprocess) with the selected Mining Structure(s).

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.

Component

Use This Option to:

Database

  • Process
    Default

  • Process Full

  • Unprocess
  • … Cube

  • Process
    Default

  • Process Full

  • Process Data

  • Process
    Structure

  • Unprocess

  • Process
    Index

  • Process
    Incremental

  • Process
    Script Cache
  • …. Measure
    Group

  • Process
    Default

  • Process Full

  • Process Data

  • Unprocess

  • Process
    Incremental

  • Process
    Index
  • ….
    Partition

  • Process
    Default

  • Process Full

  • Process Data

  • Unprocess

  • Process
    Incremental

  • Process
    Index
  • … Dimension

  • Process
    Default

  • Process Full

  • Unprocess

  • Process Data

  • Process
    Index

  • Process
    Update
  • … Mining Model

  • Process
    Default

  • Process Full

  • Unprocess

  • …. Mining
    Structure

  • Process
    Default

  • Process Full

  • Process
    Structure

  • Process
    Clear Structure

  • Unprocess
  • 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.

    William Pearson
    William Pearson
    Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

    Latest Articles