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 many tips and techniques for real-world use. For more
information on the series, as well as the hardware / software requirements
to prepare for the exercises we will undertake, 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 component in 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, 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 six-plus figures that many can shave
from their annual IT budgets represent a compelling sweetener to examining this
incredible toolset.
Basic
assumptions underlying the series are that you have correctly installed
Reporting Services, including current service packs, along with the
applications upon which Reporting Services relies, and that you have access and
the other rights / privileges required to complete the steps we undertake in my
articles. For details on the specifics of the adjustments necessary to quickly
allow full freedom to complete the exercises in this and subsequent articles,
as well as important assumptions regarding rights and privileges in general,
please see earlier articles in the series, as well as the Reporting Services Books
Online.
About the Mastering OLAP Reporting Articles …
As I have noted in many
articles and presentations, one of the first things that becomes clear to "early
adopters" of Reporting Services is that the "knowledgebase" for
OLAP reporting with this tool is, to say the least, sparse. (I recently
heard an internal "reporting guru" say, during a BI strategy session
with a major soft drink manufacturer in Atlanta, that "we didn’t evaluate Reporting Services
because it doesn’t do cubes …") As most of us are aware, minimal, if any,
attention is given to using Analysis Services cubes as data sources for reports
in the handful of books that have been published on Reporting Services to date.
All are written from the perspective of relational reporting, as if with
existing popular tools for that purpose. One Reporting Services book discusses
OLAP reporting with Reporting Services, and then performs illustrative
exercises with Office Web Components (OWC), instead. Another depicts an MDX
snippet at the end of the book, as if as an afterthought. All of the early
books focus entirely on relational reporting, and most make heavy use,
typically enough, of the Books Online and other scraps of documentation
that we already have anyway. (I could go on, but my overall opinion of the
technical book industry is already well known.)
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 the reporting solutions 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
An
important consideration, when designing a Business Intelligence system within
any environment, is a consideration for "where to put the intelligence"
among the various "layers" within the system. I have stated many
times in the past, in both articles and presentations, that "multi-layered
reporting solutions require multi-layered architects," and nowhere is this
truer than within the design and implementation of the Microsoft integrated
business intelligence solution. This article provides an excellent example of
such considerations: the option for placing a needed calculation at either the
Analysis Services level (within the cube structure) or the Reporting
Services level.
NOTE: For a summary of my opinions surrounding the importance
of thinking "multi-dimensionally" within the design and
implementation of a business intelligence system, see Multi-Layered
Business Intelligence Solutions … Require Multi-Layered Architects.
In
this session, we will examine the satisfaction of a business requirement within
the cube upon which a pre-existing report is based (we will modify a sample
OLAP report to save time and maintain the focus of the lesson as much as
possible). We will then add the same capability at the report level, comparing
and contrasting the steps as we do so. We will discuss, as we progress, some
of the considerations that arise in similar scenarios, where we have multiple
options for "where to put the intelligence."
While
we will be examining a relatively simple set of circumstances in the interest
of time and space, we should keep in mind that the concepts involved (the fact
that we do, indeed, have options, and where to put the capabilities among the
multiple layers of the architecture) can become extremely important in
enterprise-level design and implementation. Perhaps one of the most important
considerations I hope to make plain is that, as I have mentioned many times,
determining the placement of components among the layers requires far more than
expertise in Reporting Services alone, an excellent grasp of SQL or MDX, an
outstanding capability to generate relational reports from MSSQL Server, in
general, or mastery of any of the other discrete layers of the integrated BI
solution. Multi-level expertise is required to optimize a multi-level solution.
In
this article, we will:
-
Discuss a
common business need within the reporting environment, the requirement to
present percent of total values; -
Discuss two
options for meeting the business requirement, focusing upon the Analysis
Services layer as the home of one solution, and Reporting Services
as the layer containing the second; -
Prepare for
our practice session by creating copies of both an Analysis Services sample
cube and an existing sample OLAP report, to save time, while
preserving the respective original samples; -
Provide an
overview of the logic behind the percent of total calculation, as part
of a specification to create the capability for a hypothetical group of
information consumers; -
Create a calculated
member in the Analysis Services layer to generate the desired percent
of total value; -
Explain the MDX
that we employ at the cube level, within the Percent of Total
calculated member; -
Modify the
clone report to more closely meet the new presentation specifications of the
information consumers; -
Create a calculated
field in the sample report to present percent of total at the
reporting layer; -
Explain the expression
we assemble to support the calculated field we create within the sample
report; -
Add both calculated
member and calculated field to the streamlined report to demonstrate
approach; -
Discuss
considerations, throughout the article, surrounding which layer to select for
the construction of the percent of total calculation we create for the
information consumers; -
Preview the
report in Report Designer, to verify the operation of the calculations
that form the scope of our practice exercise.
