CTS – Your Technology Partner

PowerPivot for SharePoint 2010

Written by Craig Butler on December 6, 2012

By Tim Rooks

PowerPivot is the self-checkout aisle of business intelligence.

Install the free PowerPivot add-in for Excel 2010 and you can import, tabulate, summarize, and otherwise slice and dice data from multiple external sources, and you don’t need a separate BI engine like SQL Server Analysis Services. Develop data models, complex calculations, and create data visualization objects (like slicers and pivot tables) without needing specialized analytics training.

Something else you won’t need: external connections to manage. PowerPivot compresses the data and stores it with the spreadsheet. When the spreadsheet is open, Excel keeps the data in memory, drastically reducing processing time compared to traditional disk-bound analysis engines. When the spreadsheet is saved to disk, typical compression ratios are around 10:1; you can work with a lot of data without needing a lot of space. Since the data is local, you can work while disconnected and even distribute to off-site users with no access to the source systems.

PowerPivot extends Excel’s familiar formula language with Data Analysis Expressions (DAX). With DAX you can go beyond simple calculations and create advanced analytical applications.

PowerPivot also plays nice with SharePoint. If your SharePoint 2010 portal is configured to support PowerPivot, you can share your data models and analysis easily, and even configure automatic data refresh cycles.

The PowerPivot Management Dashboard provides reports on the performance, security, and availability of your shared applications. Your IT administrators can easily identify infrastructure bottlenecks and adapt to meet changing usage needs.

Get ahead of the BI curve by downloading the free Excel add-in and figuring out how you can best use this powerful and fast self-service data analysis tool.