SQLServer2008_logo_2009.jpg Some have said that the release of SQL Server 2008 R2 (news, site) isn't really a big deal. Others disagree. If you are interested in its business intelligence capabilities and SharePoint integration, then you will fall into the first category. Let's have a look at what's new in the R2 release.

Information Platform

Microsoft calls SQL Server 2008 R2 an Information Platform. This platform address three primary groups, or sets of needs:

  • Empower IT, including DBAs, to manage services easier
  • Provide tools to support the development of data rich apps
  • Support business users who focus on business intelligence using familiar toolsets

With this in mind, Microsoft identified three investment pillars R2 focused on as shown below:

SQL Server 2008 R2 Investment Pillars

An opportunity to speak with Sabrena McBride, Senior Product Manager for SQL Server, provided a lot of insight into what's new in R2. Some of the highlights:

  • Trusted, Scalable Platform: As you would expect, SQL Server 2008 R2 has been built to be very scalable and secure. It now offers Master Data Services -- a new service offering/technology enabler that provides organizations with the ability to ensure data consistency across heterogeneous systems. Organizations can determine what data they would want centrally managed, and allows applications to subscribe to that data. It includes versioning, business rules, hierarchy management, an audit trail and reporting. StreamInsight is high scale event processing. It provides near real-time streaming of events and enables developers to write applications using LINQ in .NET that use the information. One example provided was for hospital patient monitoring.
  • IT&Developer Efficiency: Of course there are new things for developers and DBAs. For DBAs there is multi-server management from a single window, similar to what you can do in System Center, but now within a familiar environment. One cool capability offered a bird's eye view across all instances and components, something many DBAs will appreciate. There's also the ability to manage capacity by setting policies -- affecting one or more instances and easier deployment using single unit packaging. For developers, SQL Azure is built on SQL Server 2008 and has all the same capabilities with the exception of BI and Reporting. And there is now direct connectivity to an Azure database because it is simply considered another data source.
  • Managed Self-Service Business Intelligence: This is the one we think offers the most interesting view, empowering business users to get the analyze information using tools they are already very familiar with Excel and SharePoint.

Managed Self-Service BI

Among other things, SQL Server 2008 R2 is a BI platform. Designed to support the needs of the BI analyst, or power user, it offers connections to some tools you are already using everyday.

SQL Server 2008 R2 BI Solution Stack

PowerPivot for Excel

An add-in for Excel, PowerPivot is built on the SQL Server Analysis Service Engine Vertipaq. Vertipaq enables faster processing for PowerPivot datasets (Excel can support up to 1 million rows of data) in both a SharePoint farm and for offline access. Excel worksheets, using PivotTables, PivotCharts, filters and Slicers are used to represent your PowerPivot data.

PowerPivot for Excel

Additional new features include:

  • Relationship Definition Tool: Relate columns in different data sources, offering the ability to create multi-dimensional data source.
  • Data Feeds: Data feeds come from URL addressable data sources and can be one-time feeds or on-going.
  • Table Wizard: Pull in data from other sources either by cutting and pasting, using external data sources or predefined connection objects.

Reporting Services 3.0 for Excel

Report Builder 2.0 offered you the Office 2010 look and feel, but 3.0 offers you more: Grab and Go reporting. To start there is a report part Gallery where you can access report parts that have already been built allowing you to create mashups of data for new reports.ReportBuilder30.jpg
Report Builder 3.0

You can also use a PowerPivot Excel Workbook as the data source for a new report -- it must be published in the PowerPivot Gallery Library. Another nice feature is the ability to take existing reports created in other versions of SQL Server and move them into Report Builder 3.0. You can break these reports into report parts and save them in the Gallery.

Get Microsoft Silverlight

PowerPivot for SharePoint

If SharePoint is a primary collaboration tool for your organization then you will like how it can support BI activities. A new PowerPivot Gallery provides an interface that allows you to view an image of a workbook and see what's in it without having to open the workbook itself.

You can also schedule regular automatic data refreshes for workbooks so you know the data is always up to date. There's also a status and history view to see when the data has been updated.

PowerPivot for SharePoint

And for those who don't want to deal with a separate authentication method, PowerPivot workbooks uses the same authentication and authorization methods as SharePoint.

Reporting Services 3.0 for SharePoint

A Report Viewer Web Part let's you easily view and move through reports directly within SharePoint. You can even use a SharePoint list as a data source for a report.

Also new is the ability to view report server items from more than one SharePoint zone (internet, extranet, intranet, default or custom).

It's important to note here that connection to SharePoint enables people who don't have or need access to Reports Services or PowerPivot directly to view reports and workbooks.

SharePoint also has an Operations Dashboard that let's Administrators see what workbooks and reports are used the most. If needed they can then take under their wing support for those more widely used workbooks and reports ensuring they are secure and available to those who do have access.

Get Microsoft Silverlight

Empowering the Business User

We've really only scratched the surface of the new BI capabilities with SQL Server 2008 R2. BI activities are becoming a primary activities of power users and business analyst and they need access to tools that make their job easier and faster to do. You can learn more about how SQL Server 2008 R2 meets those needs and more by checking out their digital tour website.