{"id":1780,"date":"2010-04-21T18:02:36","date_gmt":"2010-04-21T18:02:36","guid":{"rendered":"http:\/\/www.markwilson.co.uk\/blog\/?p=1780"},"modified":"2010-04-22T15:47:36","modified_gmt":"2010-04-22T15:47:36","slug":"introducing-microsoft-powerpivot","status":"publish","type":"post","link":"https:\/\/www.markwilson.co.uk\/blog\/2010\/04\/introducing-microsoft-powerpivot.htm","title":{"rendered":"Introducing Microsoft PowerPivot"},"content":{"rendered":"

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

Formerly codenamed Gemini, PowerPivot is part of the SQL Server 2008 R2 release but does not really need SQL Server.\u00c2\u00a0 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.<\/p>\n

PowerPivot allows users to pull in large quantities of data from disparate sources for fast analytics.\u00c2\u00a0 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.<\/p>\n

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.\u00c2\u00a0\u00c2\u00a0Business 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.<\/p>\n

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.\u00c2\u00a0\u00c2\u00a0 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.\u00c2\u00a0<\/p>\n

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.\u00c2\u00a0 It’s worth noting though that, if SharePoint is used,\u00c2\u00a0an Enterprise SharePoint platform is required in order to provide the Excel Services capability.<\/p>\n

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.\u00c2\u00a0<\/p>\n

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.<\/p>\n

In summary, PowerPivot allows:<\/p>\n