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.

Note: Current Service Pack updates are assumed for the operating system, along
with the applications and components listed above and the related Books
Online
and Samples. Images are from a Windows 2003
Server
environment, but the steps performed in the articles, together with
the views that result, will be quite similar within any environment that
supports MSSQL Server 2005 and its component applications.

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.

NOTE:
For detailed information about Analysis Services 2005 KPIs, see my
introductory article Mastering
Enterprise BI: Introduction to Key Performance Indicators
, a part
of the Introduction to MSSQL Server Analysis Services series at Database Journal.

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