About the Series …
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.
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 2005 Reporting Services
Server 2005 Database Services
AdventureWorks sample databases
Server 2005 Analysis Services
AdventureWorks OLAP cube
Internet Explorer 6.0 with scripting enabled
Intelligence Development Studio (optional)
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
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
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.
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.
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.
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,
Create a new Analysis
Services Project within a Business Intelligence Development Studio solution;
of the Relational Data Source within the Analysis Services Project;
Deploy the Analysis
Add a new Reporting
Services Project to the solution;
of the Analysis Services Shared Data Source;
sample OLAP Report to work with our Cube;
Values to the Report;
Indicators with KPI Values in the report;
appropriate junctures, the results obtained within the development techniques
that we exploit throughout our practice session.