Reporting might not be the sexiest DBA task, but it's essential for every company and every department that stores information in a database. Many people use front-end tools such as Microsoft Excel and Access to get reports from SQL Server and other databases. Microsoft also offers development tools that you can use to build your own reports. But strangely, Redmond has stayed out of the reporting space, ceding this lucrative market to a variety of third-party vendors—until now.
By the end of the year, Microsoft will roll out a new SQL Server product called Reporting Services that will let you manage the entire reporting life cycle, from authoring, to management, to delivery. The company says it developed Reporting Services in response to customers who've been asking for a reporting platform that is built on the Microsoft .NET Framework, integrates well with other Microsoft applications, works great with SQL Server and other database systems, and can be extended by third-party developers. Like many Microsoft offerings, Reporting Services—formerly code-named Rosetta and originally expected as part of the Yukon release of SQL Server—is designed as a platform play. Microsoft wants to own the core reporting platform while leaving enough gaps in the offering to encourage a rich vendor add-on and partner market, as we've seen develop in the larger SQL Server space and in the Analysis Services OLAP arena. Let's look at the three core areas of the reporting life cycle and see how Reporting Services fits in.
Report authoring. Reporting Services will provide a WYSIWYG designer for defining a report's layout. You'll also be able to lay out data in a variety of formats and create powerful banded reports that incorporate tabular and visual representations of data on the same printed page or online display.
However, Reporting Services' authoring capabilities run much deeper because Microsoft is publishing the XML-based Report Definition Language (RDL) that the product uses under the covers. With RDL exposed, Reporting Services becomes a genuine server platform that corporate IT shops and commercial software vendors can develop against. For example, vendors could create visual authoring environments for end users, who would then be able to create custom reports and integrate their existing reporting products with Reporting Services. Such add-ons, in fact, will be welcome because Microsoft's initial authoring support seems tailored more for developers and IT professionals than for end users.
Reporting Services will also let you easily create heterogeneous reports based on multiple data sources, including relational, flat file, and OLAP. Today, most people use manual processes of some sort to build their multisource reports. For example, you might paste data from multiple sources into an Excel spreadsheet before doing further pivoting or analysis on the integrated data sets. Of course, reporting products from such companies as Business Objects, Cognos, and Crystal Decisions make reporting from heterogeneous data sources easier, but these products are invariably expensive and lock users into a particular reporting platform paradigm. This is where RDL's openness becomes so important: You'll be able to easily extend the core Reporting Services' platform by using third-party tools or homegrown development efforts.
Report management. Report management encompasses scalability, security, and scheduling of reports. Microsoft implemented Reporting Services as a Win32 service that will allow multithreaded processing of complex reports that pull data from multiple data sources in parallel. This approach provides much better response time and throughput for complex reports than querying heterogeneous sources serially.
In addition, Microsoft built Reporting Services to be stateless with respect to user connections asking for reports. This statelessness will let you create a scalable Web farm of report servers that can service thousands or perhaps tens of thousands of connected users. IT architects will be able to deploy one logical image of a Reporting Services solution that could comprise multiple physical machines. Consider large telecommunications or utility companies that want to provide sophisticated reporting solutions to customers and deliver those reports through email or Web phones to home PCs. These companies might have more than tens of thousands of customers who all want to look at their monthly bill at the same time. Scalable reporting farms suddenly become an important architectural feature.
You'll be able to achieve additional scalability through Reporting Services' ability to provide on-demand reports that are either live or cached. A live report queries the underlying data source in realtime to retrieve data, whereas a cached report maintains a cached version of the report's data set for a defined period of time. Suppose that five users all want to run an expensive report. With cached versions of the report, your system could generate the data set once, then reuse that data set for the other four users requesting the same data. You could also use Reporting Services' scheduling capabilities to run the report every Monday at 8 a.m., then all users could simply grab the cached copy.
Reporting Services' reliance on XML and .NET-based data technologies provides a flexible architecture, creating the foundation for another interesting Reporting Services feature: letting one report "piggyback" on another. For example, the Show Yearly Sales report contains annual sales data, but you might want to have another report that lists quarterly sales. With Reporting Services, you could use the data set output of the yearly report as the data set input for the quarterly report. And role- and user-based permissions let you horizontally and vertically partition the data in a report so that multiple people can use the same report and see only the data they're supposed to.
Report delivery. With Reporting Services, you'll be able to define a report independent of how you want to render it. Reporting Services lets you easily create a single report that you can deploy in almost any format (including PDF, TIFF, HTML, and data formats such as XML or CSV) for almost any device.
Existing reporting solutions support a pull model, in which the report consumer initiates the process of running the report. However, you can also deliver Reporting Services reports through a push model, in which an administrator initiates a report or a specified event triggers the report.
You can build static reports, or you can build interactive reports that let users drill down into the data or re-sort it, even if the report is disconnected from the original data source. Reporting Services' interactive features use standard Web-based technologies such as HTML, HTTP, and SOAP protocols that you'd typically use to develop Web applications. In fact, the Reporting Services demos I've seen blur the line between traditional reporting functions and application development. For example, how many applications have you seen in which the Web page is simply a container for showing tabular or graphical data of some type? Reporting Services can easily become a development environment for applications that need to display data in tabular and visual formats.
Filling a need. More than one excited reader contacted me about how to get into the Reporting Services beta program after I wrote about the new product in SQL Server Magazine UPDATE ("Reporting Services Quietly Develops," May 8, 2003, InstantDoc ID 38983). And 89 percent of the 333 people who responded to an Instant Poll in that issue of UPDATE said they were interested in the new product, as Figure 1 shows. (You can apply for the public beta, expected in August, at http://www.microsoft.com/sql/evaluation/betanominations.asp.)
Daniel Reber of Datamasters, Inc., a Pennsylvania company that designs, develops, implements, and supports healthcare software, said that Datamasters is looking at Reporting Services to enhance its current report-delivery system. "We believe that a reporting solution tightly integrated with SQL Server could lead to major performance gains for our report-delivery system," he explained. "Depending on the sophistication of the event-based \[push\] delivery, we may also replace our current alert functionality. Because we're planning to integrate with SharePoint in the near future, Reporting Services could give us a big head start."
Every SQL Server shop will need to reevaluate its current reporting strategies to see whether the Reporting Services tool suite will provide more functionality for less money than existing third-party tools.
Many organizations that are looking for a managed reporting solution might choose Reporting Services even if their underlying data isn't stored in SQL Server (although you'll need a SQL Server 2000 license to use Reporting Services). This scenario has been common in the extraction, transformation, and loading (ETL) and OLAP spaces, where SQL Server's Data Transformation Services (DTS) and Analysis Services have become important offerings in their own right, apart from SQL Server.
Reporting Services' rich features across the entire reporting life cycle, coupled with the aggressive pricing that Microsoft is sure to set, will create an offering that could profoundly affect the way corporate and noncorporate individuals receive the reports they use daily. (As of press time, Microsoft hadn't released pricing or packaging details; for the latest Reporting Services information, see http://www.microsoft.com/sql/evaluation/bi/reportingservices.asp.) With the launch of Reporting Services, SQL Server will become the only major relational database management system (RDBMS) to include a rich, managed reporting environment as a core part of the platform. Even if you don't like or use Microsoft database technology, you'll eventually benefit from the release of Reporting Services as Oracle and IBM are forced to follow suit and give their customers the value of integrated reporting capabilities.