I spent last week hanging out in West London at the Microsoft UK TechDays events in order to learn something more about the technologies I work with and something new about a few others.  Thursday’s SQL Server/Business Intelligence sessions definitely fell into the latter category and I saw some pretty cool stuff including PowerPivot.

Formerly codenamed Gemini, PowerPivot is part of the SQL Server 2008 R2 release but does not really need SQL Server.  It is intended to provide business end users with access to distributed data. Available for both Excel (2010 only) and for SharePoint, it lets Excel power users know where data is, how to get to it and need to share it.

PowerPivot allows users to pull in large quantities of data from disparate sources for fast analytics.  It can store hundreds of millions of rows of data in Excel and runs analysis services on the client, with data stored in memory – not SQL. Column-based compression is used to reduce the data size on disk (the actual ratio depends on whether that data is textual or numeric – numeric compresses well and that’s generally the sort of data that is used for this type of reporting). There is a limit of 4GB of address space and a 2GB file limit on disk but Microsoft state that’s not a limitation of PowerPivot – these restrictions mean that reports can be deployed to SharePoint later.

In order to make PowerPivot easy for business users to use, Microsoft has come up with a set of Data Analysis eXpressions (DAX) that provide a simplified set of analysis functions using Microsoft Excel-like syntax.  Business users can perform DAX functions and standard Excel calculations on the data, including the use of Excel slicers (a new feature so that end user do need to understand pivot tables in order to filter the data). Pivot tables, slicers and graphs can be built up to create a dashboard, which is just a few clicks away from being saved to SharePoint.

Once published, users can view a PowerPivot Gallery in SharePoint – including previews of reports, and so an existing report can be used as a data source for a new report using ATOM for the publishing/syndication.   Taking that a step further, PowerPivot for SharePoint allows pivots to be published as web applications for a team – and administrators can track usage of the dashboards that users create to discover those “apps” that are becoming business critical, in order to transition them to a state that is properly cleansed/governed. 

Whilst SharePoint is the only supported platform in order to publish PowerPivot reports (and obtain management data), the data consumed by the pivots may originate from a variety of sources.  It’s worth noting though that, if SharePoint is used, an Enterprise SharePoint platform is required in order to provide the Excel Services capability.

Whilst some are concerned about bringing together data from disparate sources, PowerPivot does not represent anarchy. The data may not have been cleansed (e.g. using fuzzy logic) but, if needs to be governed with proper stewardship, it can always be brought into the data warehouse. 

As for securing the data – if users can access the data, they can access it regardless of PowerPivot. Organisations should look to use rights management and other security mechanisms to protect data from information leakage.

In summary, PowerPivot allows:

• Analysis of external data within the context of corporate data.
• Analysis of large data sets beyond the limits of Excel.
• Sharing of insights.
• Consumption of reports as data sources.
• Easy access to data, without a major IT project.
• The ability to gather business requirements (e.g. identify commonly used reports) prior to implementing a fully managed reporting solution.

More details of PowerPivot can be found in the PowerPivotPro FAQ or at Microsoft’s PowerPivot site, where there are videos, demos, and the software can be downloaded for free.