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

June 20, 2005

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.







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers