Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More …

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