Intelligent Enterprise

In-memory analysis is clearly the big headline in the "R2" release of Microsoft SQL Server 2008, announced last week and expected in the first half of 2010. But the upgrade also promises master data management functionality, stream processing capabilities and improved datacenter administration. Current licensees will be able to sample it all in a Community Technical Preview (CTP) to be released this month. The three big themes behind the upgrades are pervasive insight, IT efficiency and dynamic development. But to dive into the details, here's a synopsis of what to expect in the next version of Microsoft SQL Server.

In-Memory Analysis

Microsoft SQL Server 2008 R2 will support in-memory analysis with two "PowerPivot" capabilities: PowerPivot for Excel and PowerPivot for SharePoint. The first is an add-in, in-memory business intelligence (BI) engine for Excel that will enable desktop users to bring together up to millions of rows of data for rapid, multidimensional analysis.

"This is a column-organized BI engine with high compression and high scan rates that will enable Excel users to work with very large amounts of information right on their desktop," says Tom Casey, General Manager, SQL Server Business Intelligence at Microsoft. "We often demonstrate the potential by using 100 million rows of data, but more typically users will be looking at many thousands of rows of information."

Speed and data capacity are limited only by the available processing power and memory of the hardware, according to Casey. The add-in will also let users mashup data from third-party providers, Web sources, external databases and so on, as if they are working with tables within Excel. "PowerPivot can infer many of the relationships and guide you in defining the relationships that need to be defined," Casey says. "Slicers" built into Excel are said to support the rapid, in-memory filtering, aggregation and navigation capabilities supported across large volumes of data.

PowerPivot for SharePoint facilitates collaboration by letting users upload PowerPivot-driven analyses to SharePoint. Microsoft has long had integrations between SQL Server Reporting Services and SharePoint; PowerPivot for SharePoint brings the same approach to Workbooks built in PowerPivot for Excel. Instead of sharing these analyses via e-mail or USB keys, however, they are shared securely on SharePoint. IT administrators will use the SharePoint security model to secure the workbooks, and schedule and manage the refresh rates of the data from the original sources.

The drawback of these PowerPivot offerings is that they will work only with Excel 2010 and SharePoint 2010, respectively. The one crack in this restriction is that PowerPivot analyses developed in Excel 2010 and posted to SharePoint 2010 will be shareable with Excel users who don't have the latest edition of Office/Excel. In that case, "the functionality of PowerPivot analyses will also work through Excel Services in SharePoint," Casey explains. So Excel 2007 users will be able to slice, dice and navigate PowerPivot analyses.