SQL Server 2008 R2: Business Intelligence for Excel and SharePoint

By Barb Mosher (@barbmosher) Apr 22, 2010

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.