PowerPivot in SQL Server 2008 R2

Microsoft SQL Server 2008 R2 delivers many powerful new features. PowerPivot is one of the most significant, arguably Microsoft’s most important Business Intelligence (BI) advance since the advent of Microsoft SQL Server Analysis Services 2005. It seriously delivers on the long-awaited promise of “self-service BI” and “BI for the masses”, by leveraging Microsoft Excel, the world’s most-used analysis platform, Microsoft SharePoint, the rapidly growing collaboration platform, and a new, in-memory version of Analysis Services. PowerPivot enables end users to build high performance analytic applications without having to wait for IT to build cubes and develop the extract transform and load (ETL) process to populate them, or to learn MDX to achieve multidimensional calculations. At the same time it allows users to collaborate by sharing a single copy of the application and allows IT to monitor such usage, ultimately taking over management and control of the application where that makes sense. This lab will expose you to the full range of new features in Microsoft Office 2010, Microsoft SharePoint 2010 and Microsoft SQL Server 2008 R2 which, working together, achieve the self-service BI vision.

The point about users not having to wait for IT is more significant than it might seem at first. It is not just about users being able to do timely analysis because the typical IT backlog of work can be avoided. At least as important is the enablement of true self-service BI, understood as the user being able to build their own analytic models from whatever data sources are available to them, unconstrained by the predefined structures, data relationships and data sources that would be inevitable in anything pre-built for them by IT. This vision turns the typical analysis/build cycle on its head, enabling users to show IT what they need by building it themselves. Later, if it makes sense, IT can use the analytic model as a starting point for re-architecting to achieve any number of optimizations, and in the meantime can monitor its usage and manage it as a full-fledged information asset.

The new version of Analysis Services implements an in-memory, columnar database. The data compression associated with (and necessitated by) a columnar database architecture allows vast amounts of source data to be stored in-memory, and because in memory, to be sliced and diced orders of magnitude faster than would otherwise be possible. In PowerPivot this new analysis engine is packaged in two forms: a client add-in for Excel 2010 called SQL Server PowerPivot for Excel (PowerPivot for Excel) and a server component delivered thru SQL Server 2008 R2 to SharePoint 2010 called SQL Server PowerPivot for SharePoint (PowerPivot for SharePoint). The server version, though managed by SharePoint, appears to client tools like a normal Analysis Services server, the only difference being it is accessed using a URL.

The SQL Server PowerPivot for Excel component is initially used to load and organize BI data and build an analysis on it using pivot tables. The result is called a “model”. A wide range of data sources can be loaded. The overall experience is similar to how Excel pivot tables have traditionally been used for years, except that with PowerPivot, far more data can be manipulated (100 million rows has been demonstrated in 64 bit mode with sufficient memory). The data also can be analyzed with much faster performance than before. PowerPivot for Excel is an overlay on Excel but is designed to look and act like Excel as much as possible so that this greatly enhanced capability still feels familiar to both casual and power Excel users. Power users can also use a new expression language called Data Analysis Expressions (DAX) to exploit a host of multidimensional capabilities from within Excel that are difficult or impossible otherwise.

Workbooks containing PowerPivot data and pivot tables are XLSX files and can be shared, copied, emailed, uploaded to a SharePoint document library and otherwise treated just like non-PowerPivot workbooks. They can be opened in a browse-only mode by versions of Excel lacking PowerPivot for Excel (such as pre-Office 2010 versions). There may be limitations to treating PowerPivot workbooks in these traditional ways, however:

  • The file size may preclude emailing if a large amount of data was loaded, in spite of the compression.
  • A client must have PowerPivot for Excel to fully leverage such workbooks.
  • Such use does not avoid the proliferation of “spreadmarts” which are the bane of many IT departments.

Note: PowerPivot for SharePoint addresses all of these issues.

When users are satisfied with their client-based PowerPivot models and wish to share them with others, the “new” next step is to share them to Excel Services on a PowerPivot-enabled Sharepoint server. When this is done the models are automatically transformed into a new type of Analysis Services cube – the in-memory cube. SharePoint manages instances of Analysis Services as necessary to host the cubes and provide appropriate performance as users collaborate and share them. Shared PowerPivot models behave like any workbook in Excel Services, except that the data scale and analytic performance of PowerPivot continues to be available. In addition, once on the server, Reporting Services can use PowerPivot models as data sources. The data within them can be refreshed from the original sources. Finally, because a PowerPivot model becomes an actual Analysis Services cube on the server, IT can take over management of the model from the original creator, enhancing or even recreating the model using standard cube design best practices and leveraging all of the capabilities of “regular” Analysis Services.