Reporting Options
for Analysis Services Cubes: Microsoft Excel 2002
In this and subsequent lessons we will
explore what has become a popular topic, and which generates many e-mails each
week in my inbox, Reporting Options for Analysis Services Cubes.
Having connected to Analysis Services cubes with various business
intelligence tools, I have found that the steps for establishing connection to
the cube data source are similar between various products. We will examine
the use of MS Office in this and the next tutorial, and expose some of the
options that are available to most of us within the pervasive MS Office suite.
The integration of MS Office with the
MSSQL Server 2000 and Analysis Services components make this a comfortable
arrangement for many reporting needs. On the other hand, I find myself in
client scenarios where more "robustly specialized" or "enterprise-level"
reporting tools are mandated by management. We will address a prominent example
of this in a subsequent article of this group, Reporting Options for
Analysis Services Cubes: Cognos PowerPlay, as well as in another
article, where we will focus on accomplishing reporting from Analysis
Services cubes with a similar business intelligence solution. My
intent is to review the process of establishing connectivity and enabling reporting
capabilities for each of the examples, and not to compare the product features
themselves, to any significant extent. I will consider providing a
similar examination of other reporting products at a later time, based upon any
suggestions I receive from readers, if this turns out to be useful.
In this and the next articles,
we will explore features that integrate Analysis Services and MS Office to
provide a vehicle for client reporting and other business intelligence
capabilities. The central focus of the article will be a basic overview of the Excel
2002 PivotTable Report, while the next article will concentrate on the
FrontPage 2002 PivotTable List. In the context of the article, we will
examine each in its role of "client representative" of the Analysis
Server; other roles (example: the PivotTable Report's dual role as creator of,
and consumer for, local cubes) will not be examined in the current article, but
will be afforded appropriately scoped tutorials at a later date.
We will introduce the PivotTable
Report features that are available with Microsoft Excel 2002 in this
article. Our examination will include PivotTable Report options that provide
for creating robust and flexible reports, and will explore:
- Setting
up a connection to an OLAP cube;
- General
navigation of member information and cube data;
- Drilling
down to details
of reporting summaries;
- Venturing
beyond the " X-Y " dimensions, and making the PivotTable Report
truly multidimensional;
- Select
formatting options for our Excel 2002 PivotTable Reports.
Building an Excel
PivotTable Report on an Analysis Services Cube
MS Office 2000 witnessed the appearance
of robust new OLAP reporting features for the desktop user, most of which were
further evolved in the components of MS Office XP. When a PivotTable report
accesses a multidimensional cube, it receives data from a specified Analysis
Server via the PivotTable Service. As the first exercise in our tutorial, we
will create a PivotTable report that accesses the Sales
sample cube that comes along with Analysis Services. We will use the PivotTable
Wizard found in Excel 2002 in our initial efforts. The procedures we cover
are essentially the same for Excel 2000, although some of the terminology used
in the latter, as well as the appearance of dialogs / other objects, differ in
some cases.
The Wizard accesses the Microsoft Query
application in Excel to build a query file. Query file creation is a one-time event
for any given PivotTable report, and defines the connection between Microsoft
Excel and the Analysis Services cube.
» 
Comment and Contribute
