PowerPivot Overview

Microsoft’s Analysis Services product team (in the SQL Server division) has developed a new product that provides self-service BI (Business Intelligence) functionality for users of Microsoft Office. This product is generally referred to as PowerPivot, and it consists of both a client-side component (PowerPivot for Excel) and also a server side component (PowerPivot for SharePoint).

Looking for PowerPivot Help? Click below to find answers about PowerPivot:

PowerPivot for Excel Help on TechNet Library:

SQL Server 2012
SQL Server 2008 R2
PowerPivot for SharePoint Help on the TechNet Library
PowerPivot for Excel Resource Center on TechNet

In this article:

About PowerPivot
PowerPivot Wiki Pages
TechNet Library
Community Resources
See Also

About PowerPivot

Watch this introduction video, part 3 in the series of PowerPivot Introduction Videos:

PowerPivot has four basic steps to what it offers:

Within the PowerPivot for Excel environment, you can bring data from virtually anywhere.
Easily organize, connect, and manipulate tables of large data sets.
Perform an in-depth analysis of your data, any way you want to slice it.
Use PowerPivot for SharePoint to share your workbooks across your team or publish them to the Web.

PowerPivot for Excel is an add-in for Microsoft Office Excel 2010 that is available as a free download from the Web at http://powerpivot.com/. The idea is that Excel users can install this add-in and start using PowerPivot on a stand-alone basis. When users want to share the results of their work, they will publish their workbooks to SharePoint servers that have the server side component installed.

PowerPivot for Excel supports self-service business intelligence in the following ways.

Current row-and-column limitations in Excel are removed so that you can import much more data.
A data relationship layer lets you integrate data from different sources and work with all of the data holistically. You can enter data, copy data from other worksheets, or import data from corporate databases. You can build relationships among the data to analyze it as if it all originated from a single source.
Create portable, reusable data. Data stays inside the workbook. You do not need manage external data connections. If you publish, move, copy, or share a workbook, all the data goes with it.
PowerPivot data is fully and immediately available to the rest of the workbook. You can switch between Excel and PowerPivot windows to work on the data and its presentation in PivotTables or charts in an interactive fashion. Working on data or on its presentation are not separate tasks. You work on both together in the same Excel environment.

PowerPivot for SharePoint is an add-in for Microsoft SharePoint that is available as part of Microsoft SQL Server 2008 R2. When it is installed on top of SharePoint and used in conjunction with Excel Services, it provides a platform for publishing and sharing PowerPivot workbooks.

In contrast with other large, corporate BI data sources that are limited in number and closely managed by database specialists, PowerPivot data is managed by services and infrastructure. At any given time, there might be tens or hundreds of PowerPivot workbooks open in memory on application servers in the farm. The PowerPivot System Service tracks this activity, setting up new connections to data that is already loaded in memory, and caching or unloading data if it is no longer used or when there is contention for system resources. Server health data and other usage data is gathered and presented in reports to give you insight into how well the system is performing.

For assistance and an explanation of setting up the architecture, view the PowerPivot for SharePoint Architecture videos.

Who is PowerPivot for?

Excel users who know how to structure, analyze, and calculate multidimensional data in workbooks and PivotTables.
SharePoint users who use team sites and document management features to store information and collaborate with colleagues.
Database professionals and IT pros who want to delegate business data development tasks to those who need the data most, but want to retain sufficient controls so that data can be secured, monitored, reproduced, and archived.

↑ Return to Top