MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total – Two Perspectives

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