About the Series …
This is the eleventh article of the
series, Introduction to MSSQL Server 2000 Analysis Services. As
I stated in the first article, Creating Our First Cube, the primary
focus of this series is an introduction to the practical creation and
manipulation of multidimensional OLAP cubes. The series is designed to provide
hands-on application of the fundamentals of MS SQL Server 2000 Analysis
Services ("Analysis Services"), with each installment
progressively adding features 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.
In addition to MSSQL
Server 2000 and MSSQL Server 2000 Analysis Services, of which we
have made repeated use in the previous articles of the series, additional
application considerations apply for this tutorial because it introduces
another MS Office component, FrontPage 2002. For those joining the
series at this point because of a desire to work with Analysis Services and its
components from a FrontPage perspective, it is assumed that Analysis Services
is accessible to / installed on the PC, with the appropriate access rights to
the sample cubes (which are provided in a Typical installation of
Analysis Services). In addition, we will assume that we are performing all
steps on a Windows 2000 – family PC, although the steps will be quite similar
with later operating systems.
For
more information on the hardware/software requirements to prepare for this
tutorial, see our last article, Reporting
Options for Analysis Services Cubes: MS Excel 2002.
Introduction
While the majority of the
articles of our series to date have focused upon the design and creation of
cubes within Analysis Services (see Articles One
through Nine of the Introduction
to MSSQL Server 2000 Analysis Services series), we began in Article
Ten to discuss reporting
options for our cubes. This comes in response to the expressed need of several
readers for options in this regard – options beyond the mere browse
capabilities within Analysis Services.
In our last lesson, Reporting
Options for Analysis Services Cubes: MS Excel 2002, we presented an
introduction to using Excel PivotTable Reports to retrieve and display
information from an OLAP cube. We exposed PivotTable Report features that are
available with Microsoft Excel 2002 (most of which were available in Excel 2000)
for creating robust and flexible reports. We explored setting up a connection
to an OLAP cube, creation of the PivotTable Report, and the general navigation
of member information and cube data. We practiced drilling down to (and
zooming up from) details of reporting summaries, then exposed the use of
intersected dimensions to make the PivotTable Report truly multidimensional.
We discussed a few formatting options at relevant junctures in our exploration
of the Excel 2002 PivotTable Report.
In this
article we will focus our exploration on Office PivotTable Lists; we
will design the reporting mechanism from the ground up, using Microsoft
FrontPage as the design environment. We will expose various options available
to the PivotTable List designer to control the capabilities afforded to the
information consumer, specifically through placing restrictive setpoints in
design mode, and enforcing those setpoints in the browser through which
information consumers access the PivotTable List.
Reporting Options
for Analysis Services Cubes: The Office PivotTable List and FrontPage 2002
In this lesson we will continue our
exploration of reporting options for Analysis Services cubes. This has
become a popular topic, which, as I mentioned in Article
Nine, generates many e-mails
each week in my inbox. As I have mentioned before, my focus is to offer
options for obtaining reports from Analysis Services cubes within a
range of similar business intelligence capabilities. My intent is
therefore to review the process of establishing connectivity and enabling reporting
capabilities for each of the options, and not to compare the product features
themselves, to any significant extent. As I also stated in the last article,
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 tutorial, we will extend our exploration into the use of MS Office
applications for cube reporting to an examination of some of the options offered
by the Office PivotTable List (the FrontPage "equivalent"
of the Excel PivotTable Report) for report building with Analysis
Services cubes. As we did with the Excel PivotTable Report in the first of our
Reporting Options articles, we will present an introduction to designing
PivotTable Lists in FrontPage 2002, to retrieve and display information
from our cubes, first discussing the steps needed to define our data source,
and to establish a connection to the cube. Next, we will expose the layout of
the PivotTable List and its navigation, and explore its use in browsing and
reporting our cube data. We will discuss the nesting of dimensions in
PivotTable Report axes to achieve multidimensional reporting within the
classical two-dimensional presentation of print media and PC screens, as well
as general formatting considerations at relevant points in the tutorial.
Our objectives in the
article include:
-
An introduction to the features that are available within Office
PivotTable List, and the options and capabilities therein that provide for
creating robust and flexible reports; -
A practical walkthrough of the
setup of Microsoft FrontPage
2002 connectivity and other preparatory steps; -
Exploration of additional facets
of the basic use and navigation of the various components that comprise
PivotTable List functionality; -
A focus on designing a
PivotTable List, "from the ground up," to provide flexibility in
information delivery to meet business needs; -
A discussion of some of the
options for the incorporation of controls over the capabilities afforded to
information consumers.