Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 21, 2006

Mastering OLAP Reporting: Prototype KPIs in Reporting Services

By William Pearson

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.


In Mastering OLAP Reporting: Reporting with Analysis Services KPIs, a member of my Database Journal MSSQL Server Reporting Services series, we introduced an exciting new feature within MSSQL Server 2005 Analysis Services ("Analysis Services"), Key Performance Indicators ("KPIs"). In that article, we focused upon using Analysis Services KPI structures within Reporting Services. Our practice exercises centered upon preparing a scenario within which we used KPIs that existed in an Analysis Services cube, presenting them within a report we created within Reporting Services.

NOTE: For detailed information about Analysis Services 2005 KPIs (from an Analysis Services, versus Reporting Services, perspective), 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 response to the article, I received several e-mails that, in varied ways and with diverse objectives, touched upon a common thread: the need to generate KPIs at the presentation layer without creating KPI structures within Analysis Services. The reasons ranged from a need to generate KPIs from relational data sources to the requirement for presenting KPIs based upon an Analysis Services 2000 cube, where KPI capabilities, per se, do not exist. Moreover, I come across the need to prototype KPIs on a regular basis for prospective clients, as well as for clients who have yet to implement them (as an organization in general, or for various organizational units that do not currently make use of KPIs). At times, client representatives wish to develop the concepts behind prospective KPIs on the fly – when they may not have the capability to make ad hoc modifications within a cube that is currently in place – with the intent of backfilling the structures in Analysis Services once the concepts are tested and perfected.

Other reasons exist for a desire to create "quick and dirty" KPIs at the presentation layer, and so I suspect that an introduction to an easy approach for creating them within Reporting Services might be useful. The concepts behind the steps we take to do so in this article can be extrapolated to many similar strategies. While the best place for KPI support is likely to ultimately be the Analysis Services layer (or perhaps the relational database layer) of the integrated BI solution, the highly flexible Reporting Services environment lends itself to prototyping in this regard, as we shall see.

As always, performance optimization and other (often competing) factors need to be weighed in deciding "where to put the intelligence," and, for this reason, a "reporting specialist," an "Analysis Services expert," an "MSSQL DBA," or other "single-layer practitioner," no matter how experienced, might not serve as the best architect for an overall BI solution. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

In this article, we will focus upon the generation of "prototype" KPIs, housed solely within Reporting Services, to meet the business requirements of a hypothetical client. As a part of our examination of the steps involved in prototyping KPIs within Reporting Services, we will:

  • Create a new Analysis Services Project within a Business Intelligence Development Studio solution, so as to make our entire solution freestanding (perhaps to ease further independent exploration);
  • 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;
  • Add an Analysis Services Shared Data Source;
  • Create a new, basic OLAP Report to work with a sample cube;
  • Add measures and associated KPIs to the report;
  • Verify the correct operation of our prototype KPIs within the report;
  • Discuss, at appropriate junctures, the results obtained within the development techniques that we exploit throughout our practice session.

Prototyping KPIs in Reporting Services

Objective and Business Scenario

As I discuss in Mastering Enterprise BI: Introduction to Key Performance Indicators and in Mastering OLAP Reporting: Reporting with Analysis Services KPIs, a Key Performance Indicator ("KPI") is a performance measure that specifies an emphasis intended to produce a desired result. As quantifiable measurements that gauge business success, KPIs can be diverse in nature, but they typically represent values, statuses, trends, or goals. KPIs often combine two or more of these into a single indicator. Because their purpose is to present high level, summary information to executives or other managers of the enterprise, who then use the information to steer an integrated organization, we typically limit the number of these vital measures to only a few.

KPIs are usually graphic in nature, and are collected into a dashboard, scorecard, or other central point of reference, where they can be easily accessed by the management team, to obtain a quick and accurate summary of business success, or of progress toward success. KPIs make it possible for management to make decisions and take action. Based upon the information the indicators convey, managers can pull the processes and activities that the KPIs represent into alignment with strategic goals and objectives. KPIs can range from simple to complex, and they are often evaluated over time. These indicators can be used alone or in combination to support rapid management decisions and actions, and to provide an indication of progress toward the accomplishment of intended strategic outcomes.

Our focus in this article is the generation of ad hoc KPIs in Reporting Services. While we would ultimately design and implement KPI support in Analysis Services (or even elsewhere), we will do so within Reporting Services within this session to illustrate an approach to prototyping KPIs in a scenario that supports a hypothetical client need. The concepts we apply will work equally well within an environment whose data source is Analysis Services 2000 or Analysis Services 2005, in combination with Reporting Services 2000 or Reporting Services 2005. The basic ideas can even be extrapolated to relational data sources, the use of MS Excel as a presentation medium, and elsewhere, with only a minimal amount of imagination.

Let's say that representatives of the Controller's Group of our client, the Adventure Works organization, have asked us to consult with them to meet a new reporting requirement. Management, having become excited by the sheer volume of information in the recent media about KPIs, has asked that the Controller's Group investigate the use of KPIs in general, and that they present a working prototype, based upon existing OLAP data, to show that they can support the Controller with KPIs. We ask the group members for the details, and note the requirements.

Members of the Controller's Group explain that they wish to be able to present two basic KPIs for Fiscal Year 2004. They would like to generate, based upon existing data, a Gross Profit Margin KPI and a Growth in Customer Base KPI for each respective Territory Sales Country. Instead of presenting any of the common images for the indicators (which include everything from Smiley faces in various stages of apparent happiness, to meters, gauges and so forth with "readings" ranging from "low" - or "bad" - to "high," "good," etc.), the requirement for the Controller is simple. Based upon his highly traditional background, the Controller wishes simply to see a small square, containing one of three colors, for each KPI for each country.

For the Gross Profit Margin KPI, the specification is for a Gross Profit Margin of less than 41 percent to be represented by a red indicator; for a Gross Profit Margin equal to, or greater than, 42 percent to be represented by a green indicator; and for a Gross Profit Margin between the lower and upper thresholds (equal to, or greater than, 41 percent, but less than 42 percent) to be represented by a yellow indicator.

The Growth in Customer Base KPI works in a similar fashion, only with the growth factor the supporting measure. The specification is for a Growth in Customer Base value of less than 4 to be represented by a red indicator; for a Growth in Customer Base value equal to, or greater than, 5 to be represented by a green indicator; and for a Growth in Customer Base value between the lower and upper thresholds (equal to, or greater than, 4, but less than 5) to be represented by a yellow indicator.

Detailed discussion with the client representatives reveals that, although we can access a cube (as a report data source) that contains the data to support the KPIs requested, we cannot modify the existing cube, which is used for critical reporting support, in any way. We realize that this means no KPI support at the cube level (via KPI structures, calculated members, etc. in Analysis Services), and that the entire prototype solution must be based within Reporting Services. Because we have found the flexible Reporting Services environment to be ideal for such efforts in similar situations in the past, we agree to prototype the two proposed KPIs, once we have ascertained the existence, in the cube, of the data we need to serve as the bases for our KPI calculations. Moreover, we agree that, once we have successfully prototyped the required KPIs, we will offer advice to the team about a more optimal approach for their creation and maintenance, within Analysis Services.

Hands-On Procedure

We will begin by creating a new Report Server Project, within the SQL Server Business Intelligence Development Studio, wherein we will establish a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as in another of my Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the Studio features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM