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.
Note: Current Service Pack updates are assumed for MSSQL Server 2000, MSSQL
Server 2000 Analysis Services, and the related Books Online
and Samples. Images are from a Windows 2003 Server
environment, within which I have also implemented MS Office 2003, but
the steps performed in the articles, together with the views that result, will
be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis
Services" or "MSAS"). Our focus in this article is Microsoft
Excel 2003 in conjunction with Analysis Services, which will require that Microsoft
Excel 2003 be installed on the PC we use to undertake the steps of the
practice exercise.
Through, and together with, Microsoft Excel 2003, we
will use Microsoft Query to create an Excel PivotTable report
based upon an OLAP cube as a data source. Microsoft Query provides the
capability for establishing connections to our cubes, among other functions.
Because it is an optional Microsoft Office component, we will
need to ascertain the existence of Microsoft Query on our PCs. If this
is the first time Microsoft Query is being accessed on the machine, it
may be a good idea to consult the appropriate Office 2003 online
documentation for installation instructions.
We will also need the Microsoft OLAP Provider,
included in a typical Excel 2003 installation, which consists of the Data
Source Driver and the client software needed to access cubes created
by Microsoft SQL Server 2000 Analysis Services.
About the Reporting Options for Analysis Services Cubes Articles …
When I began the Reporting
Options for Analysis Services Cubes in March, 2003, I set out to
explore what had become a popular topic, as well as a subject of countless e-mails
and forum questions: what were the options for reporting from Analysis
Services cubes? Since then, the field of options has widened considerably,
with the number of applications that can access our cubes increasing in
response to Analysis Services’ success in the market. I noted in my
introductory article,
Reporting
Options for Analysis Services Cubes: MS Excel 2002 that, having connected to Analysis Services cubes with various business
intelligence tools, I had found that the steps for establishing connection to
the cube data source are similar between various products. We then examined the use of MS
Office (Excel 2002 in the introductory article, and Microsoft FrontPage
2002 in a subsequent article), exposing some of the options that were
available within the pervasive MS Office suite.
As I
noted then, the integration of MS Office with the MSSQL Server and
Analysis Services components make this a comfortable arrangement for
many reporting needs. Moreover, having examined Microsoft Excel 2002 as
a reporting option for Analysis Services cubes, I continue to receive
questions regarding the use of Excel 2003 for the same purpose. While the
general concepts in my previous article still apply, and changes to the
appearance of the various dialogs and displays involved have been minimal, I
thought it might be useful to address specific settings using Excel 2003
to report from an Analysis Services cube. The widespread use of Excel compels
me to keep my readers up to date with the most recent version, and so I have
revisited its use in accomplishing similar objectives to that of the previous
article. This article will serve as that update, therefore, "synchronizing"
the features of Excel 2003 with the process of establishing connectivity and
enabling reporting capabilities that we considered in the Excel 2002 article,
all within an independent article.
Introduction
In
this article, we will explore features that integrate Analysis Services
and MS Office Excel 2003, 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 2003 PivotTable Report. Within the
context of the article, we will examine the role of Excel 2003 as the "client
representative" of the Analysis Server; other roles (for instance, the PivotTable
Report’s dual role as creator of, and consumer for, local cubes)
will not be examined in this article. In addition, we will provide a brief
summary of another Excel 2003 OLAP Reporting option, the Microsoft Office Excel Add-in for
SQL Server Analysis Services.
We
will examine the PivotTable Report features that are available
with Microsoft Excel 2003 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 2003 PivotTable Reports; -
Using the Excel Add-in for SQL Server
Analysis Services as
another fully featured, reporting and analysis option for Excel 2003 and
Analysis Services cubes.
