PowerPivot Components and Tools
PowerPivot refers to a collection of applications and services that provide an end-to-end solution for creating and sharing business intelligence using Excel and SharePoint. In contrast with other SQL Server components, PowerPivot data is not created, viewed, or managed using the same tools and applications that you might use to build other SQL Server or Analysis Services data sources. This topic describes the tools and applications that you use with PowerPivot data.
To create PowerPivot data, you must use PowerPivot for Excel. PowerPivot for Excel is an add-in to Excel 2010 that is used to assemble PowerPivot data inside an Excel workbook. It is the only client application for creating or modifying PowerPivot data. Other authoring tools, such as Business Intelligence Development Studio, cannot be used to create or modify PowerPivot data.
Administration is always through SharePoint 2010 Central Administration. Although you can use SQL Server Management Studio to view or verify whether a read-only PowerPivot data source is running on an Analysis Services server instance, you cannot modify or manage PowerPivot data from Management Studio. The same restriction applies to SQL Server Configuration Manager. You can use it to view service properties, but you should never use it to modify server state or change account information. All configuration and management must be done through Central Administration so that services and applications remain in synch.
Viewer applications for PowerPivot data include Excel 2010, SQL Server 2008 R2 Reporting Services reports, or a browser that points to a PowerPivot workbook published on a SharePoint 2010 server (the server must have PowerPivot for SharePoint). When Excel Services is enabled on SharePoint, users can view and interact with workbooks in a browser window, with no additional client software required.
If you publish Excel workbooks that contain PowerPivot data to a SharePoint library, you must have PowerPivot for SharePoint if you want to view the workbooks from a SharePoint site. Although PowerPivot data is presented visually in Excel, internally the data is Analysis Services multidimensional data that is extracted from the workbook by the PowerPivot System Service. The data is loaded in server memory by Analysis Services server instances for fast data analysis in the farm.