MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting - Page 2

At Last: A Genuine Integrated Approach

The major enterprise reporting application players (to whom I often refer as "the Big Sisters") have relatively recently begun to package and market their products from the perspective of the reporting life cycle (whereas, in their earlier literature they focused these efforts from what I like to call a "component perspective"). Reporting Services was designed within this integrated, life cycle perspective from its inception. It thus presents the paradigm of "one reporting tool," instead of presenting the implementer with a bundle of disparate product guides in an online library, from which one can get an integrated perspective only by milling through the set in a frustrating cross-referencing exercise. The obvious reason for the "component" approach of the Big Sisters was that they wanted to be everything to everybody, and to provide product combinations to meet the needs of their customers, while licensing each component separately as an overall marketing model.

Let's take an example: If you needed a relational report writer for general ledger and other transactional reporting, you might purchase an excellent tool like Cognos Impromptu to meet your organization's needs. If you wanted OLAP under the same vendor umbrella, you licensed a separate product, PowerPlay (which contained a cube-building component called Transformer). To get smooth and easy drill through from OLAP to its underlying transactional detail, you had to buy both components; although I have certainly accomplished drill through from PowerPlay without an Impromptu feed mechanism underneath it by creating workarounds, Impromptu is designed to optimally feed PowerPlay Transformer, with the two components working together to provide integrated security and other optimizations. When you needed to present your reports via web to enterprise consumers, the cost and complexity began to expand, as components had to be added for various functions.

This is a small example of the kinds of scenarios that can be avoided through the selection of a single, integrated model. It shouldn't be too taxing on the imagination to extrapolate the potentially huge savings in time and money that await adopters of Reporting Services.

The Phases of the Reporting Life Cycle

The Reporting Services literature breaks the phases of the reporting life cycle into the three primary stages, shown in Table 1.

We will overview each of the phases above in turn, beginning, in our next article, with the Authoring phase, then considering the Management and Access and Delivery phases in subsequent articles. In each of the overviews, we will examine the general steps contained from the perspective of the Reporting Services application, both as an introduction to later, more detailed articles, as well as to provide general, "high level" information. My hope is that this information will be useful in the coming months, to assist those leading the charge to the new reporting platform to evaluate the opportunity that awaits them. The strengths of Reporting Services that I have seen so far promise a migratory phenomenon that will be inspiring in its scope and magnitude, as OLAP moves toward a commodity market, and away from the highly specialized, expensive empire that has heretofore been ruled by a handful of dominant, proprietary players.

The Authoring Phase

The Authoring phase of the reporting life cycle witnesses the creation of a report definition. The definition is constructed on the client, and, once it meets the approval of the author, is published for general use on the report server. The query that underlies the report and its data source designation are combined in the definition, where we also do the layout design that determines the report's appearance. Virtually all content and presentation decisions are enacted in this phase, as we will discover in our next article.

Reporting Services leverages the Report Designer that it adds into Microsoft Visual Studio.NET (see Illustration 1), which means we have the added advantage of a single report design interface, regardless of the nature of the data source(s). This unifies the report writing process within a single tool that is consistent in look and feel (try finding a single writing environment for an OLAP report and a relational report with the products of the Big Sisters... your search will not be a productive one).

One of the most exciting aspects of report design within Reporting Services is the ease with which we can combine multiple data sources into a single report. One of my first challenges for Reporting Services was to create a report with an OLE-DB data source, based upon MDX (see a simple example in Illustration 2, based upon the sample FoodMart Warehouse cube supplied with MSSQL Server Analysis Services), and an ODBC data source based upon standard SQL (I used the sample NorthWind relational database that installs with MSSQL Server for the illustration). Reporting Services rose to the challenge, unlike the host of other reporting applications I have implemented for the last decade.

In contrast, let's take the Cognos Impromptu / PowerPlay combination we noted earlier: Cubes cannot be used as sources in Impromptu, to follow an earlier example, nor can relational report definitions, such as those produced by Impromptu, be pulled into a PowerPlay report, where cube data can be presented. Additionally, a catalog, upon which Impromptu is based, can only manage a single data source (I know, there are workarounds, but we're talking "out of the box," optimal functionality here). Again, the benefits of a single point of authoring are apparent to most Business Intelligence practitioners.

Underneath the Report Designer, Reporting Services generates Report Definition Language (RDL), which is XML-based, to support our authoring efforts. While we can embellish our designs with added functionality, due to the programming-capable environment provided by Visual Studio.NET, we can create sophisticated reports without going much further than the drag-and-drop capabilities to which many have become accustomed in enterprise reporting applications. The design environment also includes both local report processing and report rendering functionality, allowing us to conveniently preview our end report at various stages in its design.

We will explore many of the details of the Authoring phase in our next article in this series, The Authoring Phase.