Spreadsheets, which have long been a disruptive force to enterprise IT, to some extent are the "killer" applications that helped drive the adoption of personal computers (PCs) in the enterprise. Spreadsheet products such as Lotus 1,2,3 - and early versions of Excel on the Mac - saw rapid adoption by business users. Inevitably, these users pushed the boundaries of the spreadsheet model, using spreadsheets as databases, and even to develop simple business applications. In the late ‘80s, it was typical to see corporate IT rolling out massively expensive mainframe-based solutions, while departmental users got their real work done on spreadsheets running on cheap PCs.
The more things change, the more they stay the same. Spreadsheet capabilities have changed dramatically since the early days of PC computing, but spreadsheet users continue to employ the spreadsheet as a mini-database, data-entry platform or for a myriad of other uses that corporate IT would rather they did not. Nowhere is this more clear than in the arena of business intelligence (BI). Almost all enterprises have a corporate IT solution such as Cognos or Business Objects, but end users continue to create spreadsheet-based solutions - often called "spreadmarts" - to bridge the gap between corporate and departmental business intelligence demands.
With Office 2010 and the Microsoft PowerPivot, Microsoft hopes to capitalize on the widespread use of Excel as a self-serve BI platform by providing specific features that help the Excel user capture, analyse and publish Business Intelligence data.
PowerPivot is an add-on to Excel 2010 that leverages - but does not require - new features in Sharepoint 2010 and SQL Server 2008 R2. It's not expected that the Excel PowerPivot component will require additional licensing, though to experience the full power of the solution you'd need to use the server side components delivered in SQL Server 2008R2 and SharePoint 2010. Microsoft presumably hopes to monetize Excel PowerPivot through SQL Server and PowerPoint licenses.
PowerPivot in Excel 2010 is an evolutionary improvement to the familiar Pivot Table model. Excel users can import or link to data in the usual way - via ODBC (Open Database Connectivity), or the like - and construct Pivot tables for aggregation, filtering and exploration. Excel users will find the data connectivity and basic pivot table functionality very familiar.
PowerPivot has significant performance advantages over older pivot tables, however. PowerPivot tables can take advantage of far more memory and handle much greater worksheet sizes than previous versions of Excel. On 64-bit workstations, massive amounts of memory can be utilized and enormous data sets manipulated.
The PowerPivot interface also includes some useful new capabilities, such as the "slice" capability that allows real time drilldown into subsets of the data. Furthermore, PowerPivot allows the combination of multiple views of the data on a single dashboard-like page. Data Analysis eXpressions (DAX) includes more powerful functions for BI type data manipulations, including iterative expressions, grouping, linking, and filtering.
The full promise of PowerPivot is realized when the Excel plug-in is combined with SQL Server PowerPivot for SharePoint. When these server components are present, analyses developed in Excel can be published to SharePoint, refreshed at regular intervals and accessed in a browser without the need for Excel. In this way, business users can construct fairly attractive BI dashboards without the need for sophisticated programming.
PowerPivot doesn't claim to solve the "corporate" BI goals of Cognos or Business Objects. These tools are designed to solve corporate requirements where consistency and compliance are more important than flexibility. But, for people who use Excel to perform department-level BI, PowerPivot should be a very welcome innovation.