Reporting Options for Analysis Services Cubes: MS FrontPage 2002

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