SQL Server 2008 Business Intelligence Enhancements

Report authoring. While improved report rendering is all well and good, better report authoring capabilities bring SQL Server 2008 SSRS to a new level of usability for developers, power users, and end users looking for easier report creation. Microsoft has been touting the Tablix data region type, which features fixed and dynamic columns and rows, arbitrary nesting on rows and columns, optional omission of row or column headers, and the ability to apply multiple parallel rows and column members within the same report.

Report authoring data visualizations now have better visual fidelity between formats and support for rich report formats, such as tables and matrices. Enhanced features include:

• Expression placeholder text. Expressions use placeholder display text in text boxes on the report design surface or in data regions.
• Expression-based parameter prompts. The Prompt property for a report parameter can be an expression.
• Processing-time variables. Variables that are global throughout the report or local to a particular group can be declared and referred to in expressions.

The Report Designer has also been upgraded with features such as new query constructs to return all instances in a recursive hierarchy. New query constructs support functions such as Rank and Top N. The tool has a new UI for obtaining grand totals, and it supports cross-joins, which are required for common analytic queries. SSRS business users have wanted a more user-friendly version of the BI Development Studio report designer tool. The SQL Server team responded by creating a separate standalone report designer outside of this tool.

Other enhanced features to help you design reports include:

• Entity hierarchies that provide a flattened analyticstyle metadata browser that presents all entities as a flattened list.
• Live data in design view that allows display of live data by using simple iteration of design-time elements.
• Instances in metadata browser that extend the metadata browser to include instance data.
• Filtering on the design surface that adds UI elements for defining basic filter conditions directly on the design surface.
• An interface that mirrors the Office 2007 products.
• Conditional formatting in response to customer recommendations.
• Standalone deployment that helps address issues that occur during Click-Once deployment.
• Built-in forms authentication that enables users to easily switch between Windows and Forms.
• Report Server application embedding that enables the URLs in reports and subscriptions to point back to front-end applications.

SQL Server 2008 BI—Is It for Your Organization?
As I’ve mentioned earlier, I see scalability and performance as the most significant areas of improvement in SQL Server 2008. Reports run faster, various queries can execute faster, and writebacks in SSAS are faster. A handful of brand-new capabilities, such as the Data Profiler in SSIS, may also make you think seriously about migration. Overall, SQL Server 2008 is an evolutionary upgrade which provides a better performing BI platform.