Power Pivot Overview

2024-01-17

Microsoft’s Analysis Services product team (in the SQL Server division) has developed a 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 (Power Pivot for SharePoint).

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

Power Pivot for Excel Help on TechNet Library:

About Power Pivot

Power Pivot 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.

Power Pivot 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.

Power Pivot 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 [[articles: PowerPivot for SharePoint Architecture]] videos.

Who is Power Pivot 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

Power Pivot Wiki Pages

[[articles: PowerPivot: Install the PowerPivot Add-In for Excel|Install PowerPivot]]
[[articles: PowerPivot Glossary]]
[[SSRS 2008 R2 Atom Data Feeds for PowerPivot]]
Data Analysis Expressions (DAX) Language
Grouping by Dates in PivotTables
Using SQL Server Data Mining Add-Ins with PowerPivot

Videos

[[PowerPivot Introduction Videos]]

SharePoint:

[[Install PowerPivot for SharePoint on a Domain Controller]] (4-video series)
[[articles: PowerPivot for SharePoint Architecture]] (4-video series)

Excel:

[[articles: PowerPivot: Install the PowerPivot Add-In for Excel|Install PowerPivot]]
Import Linked Data from Excel
Import Data from a SQL Server Database
Create a PivotChart from PowerPivot Data
[[articles: PowerPivot: Import Data from an Access Database (video)|Import Data from Access]]

↑ Return to Top