Mastering OLAP Reporting: Reporting with Analysis Services KPIs
May 15, 2006
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:
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.
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: