Intelligent Enterprise

PROS

• Consistent user experience for report consumers.

• Good report-based interactivity, including infinite drill.

• Report models can access complex data models and MSAS data sources .

• Price.

CONS

• Limited report layout options.

• Lacks broad data access.

• Limited Office integration.

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.

Click to enlarge in another window

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.