Mastering OLAP Reporting: Reporting with Analysis Services KPIs

About the Series …

This
article is a member of the series MSSQL Server Reporting Services.
The series is designed to introduce MSSQL Server Reporting Services ("Reporting
Services"), with the objective of presenting an overview of its features,
together with tips and techniques for real-world use. For more information on
the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I
have stated since the charter article of the series, published about the time Reporting
Services was first publicly released, my conviction is that Reporting
Services will commoditize business intelligence, particularly in its role
as a presentation component within an integrated Microsoft BI solution.
Having been impressed from my first exposure to this exciting application, when
it was in early beta, my certainty in its destiny grows stronger by the day, as
I convert formerly dominant enterprise business intelligence systems, such as Cognos,
Business Objects / Crystal, MicroStrategy, Hyperion, and
others, to the Reporting Services architecture. I receive constant
requests to conduct strategy sessions about these conversions with large
organizations in a diverse range of industries – the interest grows daily as
awareness of the solution becomes pervasive. Indeed, the five- to six-plus
figures that many can shave from their annual IT budgets represent a compelling
sweetener to examining this incredible toolset.

Note: To follow along with the steps we
undertake within the articles of this series, the following components, samples
and tools are recommended, and should be installed / accessible, according to
the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

• Microsoft SQL
  Server 2005 Reporting Services

• Microsoft SQL
  Server 2005 Database Services

• The
  AdventureWorks sample databases

• Microsoft SQL
  Server 2005 Analysis Services

• The
  AdventureWorks OLAP cube

Client Requirements

• Microsoft
  Internet Explorer 6.0 with scripting enabled

• Business
  Intelligence Development Studio (optional)

Sample Files

For purposes of the
practice exercises within this series, we will be working with samples that are
provided with MSSQL Server 2005. The samples with which we are
concerned include, predominantly, the Adventure Works DW database. This
database and companion samples are not installed by default in MSSQL Server
2005. The samples can be installed during Setup, or at any time
after MSSQL Server has been installed.

The topics "Running
Setup to Install AdventureWorks Sample Databases and Samples" in SQL
Server Setup Help or "Installing AdventureWorks Sample Databases and
Samples" in
the Books Online (both of which are included on the installation CD(s), and
are available from www.Microsoft.com and other sources, as well),
provide guidance on samples installation. Important information regarding the
rights / privileges required to accomplish samples installation, as well as to
access the samples once installed, is included in these references.

About the Mastering OLAP Reporting
Articles …

One of
the first things that become clear to "early adopters" of Reporting
Services is that the "knowledgebase" for OLAP reporting with this
tool is, to say the least, sparse. As I stated in my article, Mastering
OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting
subset of my Reporting Services series is to focus on techniques for using Reporting
Services for OLAP reporting. In many cases, which I try to outline in my articles at
appropriate junctures, the functionality of well-established, but expensive,
solutions, such as Cognos PowerPlay, can be met in most respects by Reporting
Services – at a tiny fraction of the cost.

The
vacuum of documentation in this arena, to date, represents a serious "undersell"
of Reporting Services, from an OLAP reporting perspective. I
hope to contribute to making this arena more accessible to everyone, and to
share my implementation and conversion experiences as the series evolves. In
the meantime, rest assured that the OLAP potential in Reporting Services
will be yet another reason that the application commoditizes business intelligence.

For
more information about the Mastering
OLAP Reporting articles,
see the section entitled "About the Mastering OLAP Reporting
Articles" in my
article Ad Hoc
TopCount and BottomCount Parameters.

Overview

Among the many powerful
new features that appear within MSSQL Server 2005 Analysis Services ("Analysis
Services"), Key Performance Indicators ("KPIs") are
yet another of the "gap closers" between the Microsoft integrated
business intelligence solution (consisting of MSSQL Server Database Engine,
Analysis Services, Integration Services, and Reporting
Services) and the formerly dominant enterprise BI suites (such as Cognos,
BO, MicroStrategy, etc.). As we shall see, we are not limited to using KPIs
within Analysis Services, and can thus enjoy even more flexibility with
these highly customizable components.

In this article, we will focus largely upon the use of Analysis
Services KPIs within Reporting Services. We will discuss the
general use of KPIs, and then move directly into preparing a scenario within
which we use KPIs that exist in an Analysis Services cube,
presenting them within a report we create within Reporting Services. As a part of our examination of
the steps involved in making KPIs work within Reporting Services,
we will:

• Create a new Analysis
  Services Project within a Business Intelligence Development Studio solution;

• Ascertain Connectivity
  of the Relational Data Source within the Analysis Services Project;

• Deploy the Analysis
  Services Project;

• Add a new Reporting
  Services Project to the solution;

• Ascertain Connectivity
  of the Analysis Services Shared Data Source;

• Modify a
  sample OLAP Report to work with our Cube;

• Add KPI
  Values to the Report;

• Drive Graphical
  Indicators with KPI Values in the report;

• Discuss, at
  appropriate junctures, the results obtained within the development techniques
  that we exploit throughout our practice session.