Put to the Test: Microsoft Report Builder

Microsoft first released Reporting Services in January 2004. It was slated to debut as part of SQL Server 2005, but given the repeated delays of that product and the solid capabilities in Reporting Services, Microsoft accelerated the offering as a free download to SQL Server 2000 customers. Reporting Services was positioned as a tool for IT developers, not business users. Authoring is done on the desktop using Business Intelligence Development Studio, a shell within Visual Studio 2005. Developers create queries using a graphical query builder that lets them join tables (similar to Microsoft Access) or, for more advanced queries, write custom SQL statements.

To address the needs of business authors, Microsoft released Report Builder in November 2005 as part of SQL Server 2005. It relies on the same Reporting Services infrastructure as reports authored in Visual Studio. Report Builder has let Microsoft compete more directly with BI pure-play vendors, but this first attempt at addressing business-user reporting needs doesn't measure up to the competition in terms of flexibility, data access or, surprisingly, Office integration.

Reporting Services is just one module within Microsoft's BI portfolio (see "The Microsoft BI Platform" left). Analysis Services, also improved in 2005, provides OLAP capabilities. Microsoft's ProClarity, acquired last May, addresses visualization and dashboarding. ProClarity is still sold as a separate product, but Microsoft plans to integrate its capabilities into PerformancePoint (due mid-2007), a product that will include budgeting, planning and scorecard capabilities.

Delivering A Business View

A key requirement for any business query tool is a metadata layer that shields users from the underlying complexities of physical table structures. Report models are a key feature of Report Builder, providing this business view of the data. Reporting Services developers also can access report models as a data source, thus providing a library of reusable metrics and improving developer productivity.

IT administrators build report models using the Business Intelligence Development Studio. One report model can access only one data source--either a SQL Server relational database or Analysis Services. The current lack of more open data access is one of Report Builder's greatest limitations. For the short term, this drawback means that Report Builder is only suitable for customers who access SQL Server data warehouses. Support for Oracle databases is to be added later this quarter.

Building a report model is straightforward, and I liked that you could easily insert all related tables with a single click (assuming the key names use conformed dimensions). Report models can access complex data models--dimensional or not, denormalized or not--including those with multiple fact tables. The model builder will resolve many-to-many join relationships automatically by creating a junction table. Fields can be based on physical columns in a table or expressions, such as revenue=qty* (unit price-discount amount).

Unfortunately, cascading lists of values are not available. You can create help text on field descriptions, but these descriptions never appear to the end user in Report Builder.

Web-based Authoring

In contrast to the leading BI vendors that now use DHTML or Java applets to provide Web-based authoring, Microsoft provides a "Click Once" application, a kind of plug-in that communicates with Reporting Services via Web services. Users launch the Report Builder smart client from within the Web-based Report Manager, which serves as a BI portal. This downloads a small application (5.9 MB) that provides a rich Windows-interface with drag-and-drop capabilities (functionality some BI vendors have struggled to deliver in redesigning products for the Web).

To build a query, users first decide on a layout and then design in an interface that provides a structural, rather than WYSIWYG, view (see screen, left). Users drag and drop fields from the report model onto the layout. If you build a tabular report, there's no way to change it to a chart or crosstab; instead, you must start from scratch. It's also not possible to design a report that contains both a table and a chart on one page. These design limitations are competitive weakness compared to more flexible reporting products from leading BI pure-plays. In addition, changes to a report, such as adding a calculation or filtering the data, forces a query refresh. This lack of caching places additional demands on the data-source server. BusinessObjects XI R2 and MicroStrategy 8 do a better job of minimizing the impact on the data source.

Report layout options are basic, consisting of pivot, crosstab or chart. In a major weakness, there's no master/detail report, a common report style that lets users group tables into sections. However, there are many chart types and plenty of options to adjust the scale, individual bar colors, label placement and so on.

Interacting With Reports

Once you're satisfied with the query design and report layout, reports are saved to the repository and accessed via the Web-based Report Manager (or via SharePoint WebParts). Report Manager offers a common report viewer for reports authored in Report Builder or developed in Visual Studio, providing a seamless experience for information consumers. Leading BI pure-play vendors also offer portals for BI content, but interactivity options and menu selections sometimes differ depending on the type of content accessed.

Report Manager offers a number of caching options to improve performance. In fact, report viewing interactivity is one of the strongest features of the product. Report consumers can resort columns, find particular values and perform multiple drill-downs -- a capability referred to as "infinite drill" (see the screen, right). Infinite drill is a handy feature, but it must be supported by the object properties in the data model. Also, it's not possible to define a path from country-to-state-to-city on a "Total Sales by Country" report, for example; instead, the drill immediately goes to the individual order numbers that make up the total sales.

Although drilling down was straightforward, drilling up was not (though the browser's back button works). In this regard, genuine OLAP capabilities, such as drill by and modeling of hierarchies, requires Analysis Services and Excel or ProClarity as the front-end. Still, infinite drill will prove valuable to report consumers who want an additional level of detail without having to build a new report or without developers having to explicitly link sub reports.

If you need to focus on a subset of the report and filter certain values, you must rerun the query, whereas competing products (Business Objects Web Intelligence and Hyperion System 9 Interactive Reporting among them) offer filtering on a cached data set. If you add a prompt to a report, an intuitive filter drop-down menu is added to the report viewer, and once again the query re-executes. The workflow for viewing and then modifying the same report needs improvement as users must separately launch Report Builder (it can't be can't be launched from the report viewer) and then rerun the query.

Given that Microsoft owns Office, it's surprising to me that the company's BI and Office integration lags that of BI competitors. Users can't access report models or reports from Office, and this hasn't changed in Office 2007. Many pure-play BI vendors now offer access over PowerPoint, but, here again, this feature is lacking in Report Builder as well as other modules of Microsoft BI. Users can, however, export data to Excel or schedule data to be exported to Excel. Microsoft has greatly improved the Analysis Services integration in Excel 2007 but the same improvements have clearly not carried through to relational reporting via Report Builder.

Evolving The Platform

Microsoft has long had a strategy of incremental improvement, and with Report Builder, it's a clear start to addressing the needs of both power users and business query authors. Given that Report Builder is bundled with SQL Server 2005 at no additional charge, it's a module that Microsoft customers should evaluate and continue to monitor for future improvements. However, it cannot be called a best-of-breed product at this point given the inflexibility in report layouts, limited data access and limited Office integration.

These drawbacks will drive sophisticated customers who wish to use more mature parts of the Microsoft BI solution (such as Analysis Services) to supplement with more flexible and capable ad hoc query tools from BI pure-play vendors. With many enterprises attempting to reduce the number of different BI tools they use, such a jig-saw approach is fraught with trade-offs, risks and cost implications.

For SQL Server customers who are on a tight budget and who lack a self-service reporting environment, Report Builder offers a low-cost introduction to ad hoc query and reporting. The question is, will you outgrow the capabilities more quickly than Microsoft can enhance the product?

Microsoft Report Builder is an ad hoc query component for Reporting Services and is bundled with SQL Server 2005 at no charge.

The Microsoft BI Platform consists of the following products: SQL Server 2005: Server platform Reporting Services: Reporting component of SQL Server Analysis Services: OLAP component of SQL Server Integration Services: ETL component of SQL Server

BI tools include: Report Builder: Ad hoc query component of Reporting Services Report Designer: Design module within Visual Studio for Reporting Services Excel 2007: Front end to Analysis Services Business Scorecard Manager: Scorecards and strategy maps ProClarity 6.2: Dashboard and visualization for MS Analysis Services/P>

Cindi Howson is the president of ASK, a BI consultancy. She teaches The Data Warehousing Institute's "Evaluating BI Toolsets" and publishes in-depth product reviews at BIScorecard.com. Write to her at [email protected].