Process Analysis Services 2005 objects with an
Integration Services package. Architect Bill Pearson leads hands-on
exploration of one means of automating object processing in Analysis Services
About the Series ...
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:
Server 2005 Database Engine
Server 2005 Analysis Services
Server 2005 Integration Services
Intelligence Development Studio
Server 2005 sample databases
The Analysis Services
Tutorial sample project and other samples that are available with the
installation of the above.
successfully replicate the steps of the article, you also need to have:
within one of the following:
- the Administrators local group on the Analysis
- the Server role in the instance of Analysis
Read permissions within any SQL
Server 2005 sample databases we access within our practice session, if
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.
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,
Introduce the Analysis
Services Processing task, and discuss its uses;
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;
configure, an Analysis Services Processing task within the package;
package to ascertain its effectiveness.
Process an Analysis Services Database with Integration Services
Overview and Discussion
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
Use This Option to:
Process (full or
default) all cubes, measure groups, partitions, dimensions,
mining models and mining structures within the selected Analysis
Perform various types
of processing (see Table 2 below for details) with the selected cube(s).
Perform various types
of processing (see Table 2 below for details) with the selected measure
Perform various types
of processing (see Table 2 below for details) with the selected partition(s).
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
Perform processing (default,
full or unprocess) with the selected Mining Structure(s).
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.
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
Use This Option to:
... Mining Model
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
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.
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
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
The topics "Running
Setup to Install AdventureWorks Sample Databases and Samples" in SQL
Server Setup Help or "Installing AdventureWorks Sample Databases and
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.
Let's get some
hands-on practice with a basic Analysis Services Processing task. To
prepare, we will create a new Integration Services project, and, simultaneously,
a new Integration Services package, to which we will add our Analysis
Services Processing task, and within which we will both configure and
execute the task. We will accomplish the steps of our practice
session within the SQL Server Business Intelligence Development Studio.
Create an Integration Services Package within the SQL Server Business Intelligence
begin, we will launch the SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my server appears as depicted in Illustration 1.
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
--> New from the main menu.
... from the cascading menu, as shown in Illustration 2.
Project dialog appears.
Select Business Intelligence
Projects (the default) in the Project types pane of the dialog, if
Services Project, a Visual Studio installed template, within the Templates
pane of the dialog.
In the Name
box of the dialog, type the following name:
provide a path for the new project within the Location box.
Leave the Solution
box at its default (ANSYS044_SSAS_Processing).
remaining settings of the dialog at default.
The New Project
dialog appears, with our settings, similar to that depicted in Illustration
Illustration 3: The New
Project Dialog, with Our Settings
Click OK to
create the project.
new Integration Services project appears in Solution Explorer, as
shown in Illustration 4.
Illustration 4: The
Project Appears within BI Development Studio Solution Explorer
now prepared to add the Analysis Services Processing task to the package
that has been automatically created within our new project. We will do
so in the section that follows.
Add the Analysis Services Processing Task to the Integration Services Package
need an Integration Services package within our new project before we
can add the Analysis Services Processing task (which forms the focus of
our lesson). While there are several ways to create a package within
the Business Intelligence Development Studio, we will not have to create
a package separately, in our present case SSIS automatically
added an empty "starter" package within the project when
we created it. It is good to keep in mind, however, that, should we have
already had a solution in place, containing an existing package (such as
an ETL package) or packages, and we decided to combine the Analysis
Services Processing task with the existing package, for reasons
similar to those we noted in our introductory comments, we would have to create
and add an Integration Services package to the solution before adding
our Analysis Services Processing task.
While we are adding a single task
to the package for our present purposes, packages typically
contain several tasks, with each task set to run within the
context of the package control flow. Workflows that run in
response to run-time events, called event handlers, can also have tasks.
To add functionality to a package, we
typically build the control flow and data flows, add connection managers, and
implement other Integration Services features that a given package
requires. In our present case, the steps involved will be minimal. We
will begin by giving the automatically created package a more meaningful
Within the Solution
Explorer, expand the SSIS Packages folder, if required.
the Package.dtsx file that appears underneath the folder in the tree.
from the context menu that appears, as depicted in Illustration 5.
Illustration 5: Select
Rename from the Context Menu ...
following in place of the now-highlighted package name:
NOTE: Be sure to include the .dtsx file extension.
appears, with the proposed name, in Solution Explorer, as shown in Illustration
Illustration 6: Renaming
the Package in Solution Explorer ...
the caption box, to "set" the new name.
message box appears, asking if we wish to rename the package object as
well, as depicted in Illustration
Illustration 7: Message
Box Asks If We Wish to Rename Package Object, As Well ...
to rename the package object, too, and to dismiss the message box.
Designer opens, defaulted to the Control Flow tab.
Click the Control
Flow tab, if it does not already appear, to move to it.
Flow tab bears a message telling us to add objects, and related matters,
from the Toolbox, as shown in Illustration 8.
Illustration 8: Control
Flow Tab, and Message Regarding Object Addition and Related Matters
will add the Analysis Services Processing task to the package at this
--> Toolbox from the main menu, as depicted
in Illustration 9.
Illustration 9: Opening
the Toolbox ...
appears. In the upper half, we see Control Flow Items. It is here that
we will obtain the Analysis Services Processing task object, one of two Analysis
Services objects that appear within the Control Flow Items group
atop the Toolbox.
Click and drag
the Analysis Services Processing Task item from the Toolbox to the design surface of the Control
Flow tab, as
shown in Illustration 10.
Illustration 10: Drag the
Analysis Services Processing Task to the Control Flow Design Surface
Services Processing Task item appears on the design surface as depicted in
Illustration 11. We note that a red circle, containing a white "X,"
appears on the right side of the box. Passing the mouse pointer over the item
produces a rollover caption that reads "No connection manager is
specified," as depicted in Illustration 11.
Illustration 11: The Task
Appears and Reminds Us to Add a Connection Manager ...
add an Analysis Services Connection Manager to the package in the
section that follows.
Add an Analysis Services Connection Manager to the Integration Services Package
An Analysis Services Connection Manager enables our
package to connect to the server that runs the Analysis Services
database upon which (or upon whose member objects) we wish to use our Analysis
Services Processing task. (We can also use the Connection Manager
to connect to an Analysis Services project that provides "BI-development-Studio-only"
access to cube and dimension data, to establish connections between the package
and the server / database to which we later deploy the package).
At run time, the Connection Manager is resolved as
an Analysis Services connection. As part of the creation process, Integration
Services sets the properties (one of which, ConnectionManagerType,
is set to MSOLAP90), adding it to the package's Connections
collection. We will add and configure the Analysis Services Connection
Manager by taking the following steps.
Right-click a point within the Connection
Managers pane, located below the Control Flow tab in the SSIS
2. Select New
Analysis Services Connection ... from the context menu that appears, as
shown in Illustration 12.
Click for larger image
Illustration 12: Initiating
the Creation of a New Analysis Services Connection in the Package
Right-click a point within the Connection
Managers pane, located below the Control Flow tab in the SSIS
Click the Edit button on
the Add Analysis Services Connection Manager dialog that immediately
appears, as depicted in Illustration 13.
The Connection Manager dialog appears next.
In the Server or file name box,
enter the server (or server / instance) appropriate to your environment.
appropriate radio button in the Log on to the server section of the Connection Manager dialog.
In the Initial catalog
selector, select Adventure Works DW.
Click the Test Connection
button to ascertain connectivity.
A Connection Manager
message box appears, indicating that the test connection has succeeded, as
shown in Illustration 14.
Illustration 14: Testing
Positive for Connectivity to the Analysis Services Database
to dismiss the message box, and to return to the Connection Manager dialog.
Manager dialog appears, with our input, similar to that depicted in Illustration
Illustration 15: The
Connection Manager Dialog ...
Click OK to
accept settings and close the dialog.
We return to the Add
Analysis Services Connection Manager dialog, which appears similar to that
shown in Illustration 16.
Illustration 16: The Add
Analysis Services Connection Manager Dialog ...
Click OK to accept and
dismiss the Add Analysis Services Connection Manager dialog.
arrive once more at the Integration Services Designer, where we see the
new Connection Manager within the Connection Managers pane as
depicted in Illustration 17.
Illustration 17: The New
Connection Manager Appears ...
--> Save All from the main menu, as depicted in
Illustration 18, to save our work to this point.
Illustration 18: Saving
Our Work to this Point ...
now established and tested the Connection Manager we have added to the Analysis
Services package. We will begin configuration of the Analysis
Services Processing task we have added to the package in the next
Configure the Analysis Services Processing Task within SSIS Designer
earlier that we can set properties for an Analysis Services Processing task
through the SSIS Designer or programmatically. In this article, we will
be working within the SSIS Designer to configure the Analysis
task with which
we are working. We will set up the Analysis Services Processing task to
process the Adventure Works cube. Once we have configured the task,
and then created an Integration Services package to contain it, we will
be ready to execute the task via the package, and to verify its
On the Control Flow tab
within the SSIS Designer, right-click the Analysis Services
Processing task that we added earlier.
Select Edit... from the
context menu that appears, as shown in Illustration 19.
Illustration 19: Select
Edit from the Context Menu ...
Services Processing Task Editor appears, defaulted to the General
Expand the General
item in the right pane of the Editor by clicking the "+"
sign to its left, if necessary.
and Description rows, General page of the Analysis Services Processing Task Editor appear.
the box containing Name to highlight the box to its right, where the
default name of the Analysis Services Processing Task currently
default name with the following:
ANSYS044_Adventure Works Full_Processing
manner, replace the wording in the Description box with the following:
SSAS_Processing Task Example
The General page
of the Analysis Services Processing Task Editor appears, with
our modifications, as
depicted in Illustration
Illustration 20: The General
Page of the Analysis Services Processing Task Editor ...
In the left hand pane of
the Analysis Services Processing Task Editor, click Analysis Services
to move to the Analysis Services page.
Services page opens, and we see the Connection Manager we added
earlier displayed in the box titled Analysis Services connection manager, atop
the page. It is underneath this box, within the Object list (in the
upper half of the Processing configuration section of the page), where
we specify the analytic objects to process (as well as Process Options
and Settings, as we shall see) within the Analysis Services
Click the Add
button under the Object list.
Analysis Services Object dialog appears, at this stage in our procedure, as
shown in Illustration 21.
Illustration 21: The Add
Analysis Services Object Dialog
Expand the Cubes
folder, by clicking the "+" sign to its immediate left.
Expand the Adventure
Expand the Internet
Sales measure group within the cube.
have "drilled down" on the various levels within a cube object
to examine them in general. The results appear in Illustration 22, which
represents the relevant components of the cube structure upon which we can
perform processing within an Analysis Services Processing task.
Illustration 22: The
Processing Objects within a Cube
purposes of our practice exercise, we will process the entire cube through this
but it is important to reflect upon the wide range of choices that are
available with regard to available processing objects. We can see most of
those choices in the illustration above, including Analysis Services
databases, cube, measures groups and partition
objects. We can also configure the task to process dimensions
and mining models and mining structures, as well.
Select the Adventure
Works cube object, by clicking the checkbox to its immediate left (and thereby
placing a check within the box), as depicted in Illustration 23.
Illustration 23: Selecting
the Adventure Works Cube for Processing ...
to accept our choice, and to close the Add Analysis Services Object dialog.
return to the Analysis Services Processing Task Editor Analysis Services page,
where we see our selection appear in the Objects list. Having selected
the object we wish to process, we have the option, within the Objects list,
of specifying Processing Options, which vary somewhat between the types
of objects we select for processing (as we saw in Table 2 above). While
we will leave the selection at Process Full for the purposes of our
example, we have numerous options from which to select for processing a cube
object, as is evidenced by clicking the selector button for the Processing
Options field within the Objects list, as shown in Illustration 24.
Illustration 24: Possible
Processing Options Selections for a Cube Object
The Analysis Services Processing
Task Editor Analysis Services page appears, with our input, as
depicted in Illustration 25.
Illustration 25: The
Analysis Services Processing Task Editor with Our Input
also significant to note that the flexibility for customization of the Analysis
Services Processing task is further enhanced by the last page in the Analysis Services Processing Task Editor, the Expressions page.
In the left hand pane of
the Analysis Services Processing Task Editor, click Expressions
to move to the Expressions page.
The Expressions page
opens, and appears as shown in Illustration
Illustration 26: The
Expressions Page of the Analysis Services Processing Task Editor
Expressions page allows us edit property expressions and to
access the Property Expressions Editor and Property Expression Builder, both useful
tools in creation and maintenance of property expressions within Integration
Services tasks. A property expression is
an expression that is assigned to a property to enable dynamic
update of the property at run time. Expressions themselves can
range from simple (consisting of a single constant, variable, or function) to
complex (where they might use multiple operators and functions, and / or
reference multiple columns and variables, and so forth). Expressions are
a combination of symbols (identifiers, literals, functions, and operators) that
yields a single data value. Many Integration Services elements, in
addition to properties (where a common use for expressions
is to set a property value) can use expressions.
Property expressions can be updated in many different ways. While
they are beyond the scope of this article, Property expressions
afford us a means of reaching far beyond the "standard" options
offered by the Analysis Services Processing task to enable us to achieve
the specific business needs of our employers and clients. A good understanding
of the use of Property expressions can be valuable when we come
across requirements for which a simple Analysis Services Processing task cannot
Click Analysis Services
in the left hand pane of the Analysis Services Processing Task Editor,
to return to the Analysis Services page once more.
We can click the Impact
Analysis button underneath the Object list, to perform impact
analysis on any object we add to the list. While we have no need of this
feature within our practice session, the Impact Analysis button allows
us to display the Impact Analysis dialog, with which we can optionally
process dependent objects that are affected if the objects listed are
We can also make any
necessary modifications to the Batch Settings, if necessary. We will
leave the settings, which are grouped into two types, Processing Options
and Dimension Key Errors, at default, but this provides a convenient
means of making batch settings modifications (such as Processing Order
of parallel or sequential, or whether the default or a custom error
configuration is used when dimension key errors are detected, both of
which we touched upon earlier) across the entire processing batch.
at the bottom of the Analysis Services page, to finish editing the new Analysis
Services Processing task.
Services Processing task dialog closes, and we are returned to the SSIS
Designer, Control Flow tab.
--> Save All from the main menu, once again.
completed the configuration of the Analysis Services Processing task within the new Integration
Services package we have created, we are ready to test the package.
We will execute the package to verify its operation in the section that
Execute the Integration Services Package Containing the New Analysis Services Processing Task
Let's test drive our new Integration Services package from within the development environment, and ensure that the task we have assembled performs as expected.
1. On the Control Flow tab, right click the new ANSYS044_Adventure Works Full_Processing task.
2. Select Execute Task from the context menu that appears, as depicted in Illustration 27.
Illustration 27: Select Execute Package from the Context Menu
The ANSYS044_Adventure Works Full_Processing task box on the Control Flow tab turns yellow as execution (of the task as well as its parent package, SSIS_Processing.dtsx) begins.
3. Click the Progress tab (shown in Illustration 28), which appears once execution starts, to monitor the events encountered within the operation of the ANSYS044_Adventure Works Full_Processing task.
Processing events are logged (we can click on any of these to access amplifying information, if desired) and appear on the Progress tab as partially depicted in Illustration 29.
Illustration 29: Partial View of Events Logged on the Progress Tab
Once processing successfully completes, the ANSYS044_Adventure Works Full_Processing task box turns green on the Control Flow tab.
4. Click the link below the Connection Managers pane, which tells us "Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu," as shown in Illustration 30.
Illustration 30: Stop Debugging via the Link Shown Circled Above
We can, as we noted earlier, string multiple tasks together within our package, as well as provide for notification of completions / failures via e-mail and other ways, from an appropriately assembled Integration Services package. Moreover, Integration Services packages can be called from other processes, scheduled and so forth, to allow for automation of much of the essential cube refreshment process. (For more information on the steps required to deploy the project containing our package, see the MSSQL Server 2005 Books Online).
5. Select File -->Exit to leave the Business Intelligence Development Studio, saving if prompted, when desired.
In this article, we examined the processing of an Analysis Services cube via the Analysis Services Processing task in Integration Services, one of two Analysis Services tasks that accompany the installation of Analysis Services. After introducing this prefabricated Integration Services task, we discussed multiple processing operations that we can use it to perform, at multiple levels of an Analysis Services database structure.
After preparing a project within which to create an Integration Services package, we began a practice exercise, adding the Analysis Services Processing task, and then a Connection Manager, to the package. We discussed various processing options as we configured our Analysis Services task to specify full processing of a sample cube. Along the way, we discussed other considerations in taking advantage of the options that the task makes available to us. Finally, we executed the Integration Services package containing our Analysis Services Processing task to verify its operation.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.