It's here ... A New Paradigm for
Enterprise Reporting. Author Bill Pearson kicks off a new series surrounding
the exciting new functionalities that debut with MSSQL Server Reporting
About the Series ...
This is the first of several articles of a new series, MSSQL
Server 2000 Reporting Services. The series is designed to introduce
MSSQL Server 2000 Reporting Services ("Reporting Services"), with the
objective of giving a preview of its features, as well as sharing my conviction
in its role as a new paradigm in enterprise reporting. As I advise clients on a
more and more frequent basis these days, this is the future in a big way. I hope
you will consider my input valuable, and that you will investigate closely the
savings and advanced functionality that will soon be available to anyone with
an MSSQL Server 2000 (and beyond) license.
screen shots that appear in this series were taken from a Windows 2003 Server
upon which I have implemented Reporting Services Beta 2. The final release may
differ in appearance, as well as in the details of operation and general
functionality. Any Microsoft Office components that appear in the series are
members of the Office 2003 suite.
I became a beta-tester of Reporting Services early in its
development, and rapidly concluded that this new MSSQL Server 2000 add-on would
literally change the face of enterprise reporting, as we know it today. Not
only does Reporting Services provide an integrated, end-to-end set of tools for
creating, managing, and viewing / delivering reports, but it does so with a
scalable engine that supports server-based hosting and processing of reports.
This is enterprise reporting at its finest, with several impressive advantages
over the current offerings in the enterprise business intelligence arena. Moreover,
the potential savings that await the implementing organizations could rank
right up there with those promised by the recent fads of outsourcing, among
other "follow the leader" activities so prevalent in business today.
As a recovering Certified Public
Accountant, who also holds credentials as a Certified Management Accountant and
Certified Internal Auditor, I spent several years dealing with reporting
systems from the perspective of an information consumer. I am now nearing ten
years in experience as a data architect and implementer of business
intelligence for many Fortune 500 organizations; I have, for most of that time,
worked daily with large enterprise reporting applications such as Cognos,
Business Objects, Crystal Enterprise and Crystal Analysis, MicroStrategy, and
divers other applications / combinations of applications.
the Reporting Services model paints a bright future for all roles in the
reporting life cycle, not only because an organization gets the entire solution
in one package, but because the solution is open and extensible, allowing
report authors, managers, and users at every level to benefit from familiar
tools and systems that are already in place. The solution is a part of the
Microsoft BI framework, and is the latest entry to a powerful family of tools that
includes a relational database (SQL Server), a powerful ETL tool (Data
Transformation Services), an OLAP engine for cube production (Microsoft
Analysis Services), and a formidable data-mining component, among other substantial
functionality. Needless to say, integration with Microsoft Office components
is a given.
this series of articles, I will share some of my insights and discoveries as I
work with Reporting Services, in hopes of shedding more light on the practical
realities I find. I will do this from the viewpoint of a practitioner who has
worked closely and repeatedly with many BI products, comparing functionalities
between some of these, and the new Reporting Services offerings. To kick the
effort off, we will begin with a discussion of the phases of the reporting
cycle as presented in the Reporting Services model, and then continue our
coming articles with an overview of Reporting Services from the perspective of
each of these phases.
At Last: A Genuine Integrated Approach
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.
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.
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.
Table 1: Primary
Phases of the Reporting Life Cycle
Creation of the report definition, via an authoring
The published report definition is saved on a
report server, and managed with Report Manager by the report server
Access and Delivery
The generated report is viewed via an application,
or is routed to a delivery target where it is accessed by consumers.
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
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.
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).
Illustration 1: It All Starts Here - Defining a
Reporting Project in Visual Studio.NET
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.
Illustration 2: OLAP and Relational Data Sources in
the Same Report: Dataset 1 depicts an MDX Query against the FoodMart Warehouse
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
The Managing Phase
Central management of the enterprise reporting function is a common and natural requirement in most organizations. Reporting Services again leverages its integration within MSSQL Server, and across the spectrum of the Microsoft applications that are pervasive in our enterprises. Reporting management functions, as many of us know, include a wide range of activities. Among these are security, scheduling, data source management, custody and control of the physical report files and the folders that contain them, and the maintenance of a diverse set of properties and resources.
One of the principal advantages that Reporting Services offers is the capability to manage reports and related items from a central location. Items that we can manage include reports, folders, data source connections, and resources. For these items, we define security, properties, and in some cases, scheduled operations. We can also create shared schedules and shared data sources, and make them available for general use.
The Report Manager centralizes these and other Reporting Services functions. Reports can be uploaded to the report server easily from either the Report Designer or the Report Manager, and can be viewed thereafter via a web browser. Multiple types of security can be assigned during the upload process, as well as elsewhere, as we see in Illustration 3, where I have chosen the sample reports folder, along with Windows NT Integrated Security, in the selection of the reports location (called the "data source" in this step).
Illustration 3: Tell Report Manager Which Reports to Upload via the New Data Source Window
NOTE: We will explore the many options available in Report Manager in our article, The Managing Phase, later in the series.
Next, we can simply select the reports we wish to upload by typing them in, or by navigating to their locations, with the Upload capability, after which the Report Manager window is populated with the reports in variable presentations, as shown in Illustration 4.
Illustration 4: Report Manager Displays Uploaded Reports for Selection by Information Consumers
Management capabilities can be controlled for end users by an Administrator, who can assign permissions, so in addition to the access security provided within Reporting Services, we are also given functional security options, for a refined set of controls over user activities. We will explore the degrees of management and various capabilities that are possible in our article, The Managing Phase, later, but suffice it to say that the overhead involved is widely customizable to an organization's needs. I will also present a subsequent article on report server administration to explore the options and processes that can be involved from that perspective.
Report Access and Delivery
Like most web-enabled enterprise reporting packages that exist today, Reporting Services allows "self-serve" access, where information consumers can view reports on demand. Consumers can also access reports via subscription processes, whereby reports are generated and delivered to the consumer at a specific destination on a recurring basis. Consumers that are granted access privileges can select a report from a folder hierarchy on the web server, in a process as simple as clicking one of the reports as depicted in Illustration 4 of our last section. The result is a report generated on demand, an example of which is depicted in Illustration 5.
Illustration 5: Sample Report Selected and Generated from the Browser of an Authorized Consumer
Subscription options include notification when a report is run, after which intended consumers can browse it at a standard location. E-mailing of a copy of the report to the intended audience is also accommodated. Notification and other processes can be based upon data-driven criteria: the recipient list can rely upon stored data that exists when the subscription is triggered, such as the data found in a customer database, for example.
Many interchangeable options exist with regard to viewing / exporting formats, as depicted in Illustration 6. Selections within the browser interface include XML file with data, CSV, TIFF, PDF (Adobe Acrobat), Web Archive, Excel, and HTML with Office Web Components. As we have already noted, integration with Microsoft Office is another obvious advantage offered by Reporting Services.
Illustration 6: Interchangeable Viewing Options Abound in Reporting Services
Summary and Conclusion ...
When Microsoft decided not to wait until the arrival of Yukon, the next release of MSSQL Server, to deliver Reporting Services, it offered organizations an early opportunity to shed the high-priced baggage of proprietary reporting options that have dominated enterprise reporting for many years. Reporting Services offers true integrated report authoring, management, and access and delivery in a single package, providing organizations a new paradigm in enterprise reporting. As we continue in our new series, we will examine in detail many of the capabilities of Reporting Services, comparing and contrasting them to the approaches of the past, and looking forward to a more cost effective, integrated tomorrow.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.