PowerPivot Concepts
Learn the terms and concepts behind self-service business intelligence.
PowerPivot workbook and PowerPivot data
A PowerPivot workbook is an Excel 2010 workbook (.xlsx) file that contains PowerPivot data. PowerPivot data is an embedded Analysis Services data source that you create in Excel 2010 using Microsoft SQL Server PowerPivot for Excel (an add-in that extends Excel to support large scale, multidimensional data). PowerPivot data is well-suited for business intelligence data analysis because it can hold a large amount of data, exceeding the one million row and column thresholds in a standard workbook. Additional capacity is possible because the data is assembled in a separate window that opens over Excel.A PowerPivot workbook can aggregate data from different sources, including web services, text files, relational and multidimensional databases, Reporting Services reports, and other worksheets.
PowerPivot data has no presentation layer apart from Excel and Excel Services. Out of the box, Excel 2010 provides data visualization and interaction. PivotTables, PivotCharts, slicers, and the familiar data grid are used to display and analyze PowerPivot data. When you publish a workbook to a SharePoint library, the embedded PowerPivot data remains an integral part of the document that you publish and view from a SharePoint site.
PowerPivot for Excel
PowerPivot for Excel is a SQL Server 2008 R2 add-in to Excel 2010 that provides a window for importing and working with PowerPivot data. It also includes a local processor for storing and processing large scale data in memory, and updated client libraries and data providers for accessing PowerPivot data.PowerPivot for SharePoint
PowerPivot for SharePoint is Analysis Services integration with Microsoft SharePoint Server 2010. It is a SQL Server 2008 R2 feature that adds PowerPivot data processing to a SharePoint farm, similar to how Excel Services adds calculations and rendering to workbooks published to a SharePoint site.PowerPivot for SharePoint is required if you want to view and collaborate on PowerPivot workbooks on SharePoint.
PowerPivot for SharePoint includes services, infrastructure, dashboards, web parts, content types, and library templates. To use PowerPivot for SharePoint, you run SQL Server setup on an application server or Web front end, and then use Central Administration to manage the feature across the farm.
Analysis Services service in SharePoint integrated mode
SQL Server 2008 R2 introduces the first release of SharePoint integrated mode for Analysis Services. In this release, SharePoint integrated mode is available only in PowerPivot for SharePoint, through an Analysis Services instance that is installed with a PowerPivot System Service on a SharePoint server.SharePoint integrated mode includes the VertiPaq engine for powerful in-memory data storage and processing that enables rapid processing of very large amounts of data. The high performance is accomplished through columnar storage and data compression.
SharePoint integrated mode does not support MOLAP, ROLAP, and HOLAP, or full MDX functionality. For more information, see the unsupported features section in PowerPivot Features.
PowerPivot System Service
PowerPivot System Service is the SharePoint infrastructure for Analysis Services in the farm. Server operations include listening for requests for PowerPivot data in the farm, establishing connections to the Analysis Services instances that load and unload PowerPivot data on SharePoint servers, collecting usage data, and monitoring server health and availability of Analysis Services server instances across the farm.A PowerPivot System Service is installed through PowerPivot for SharePoint. It is installed alongside an Analysis Services instance. It is available through one or more service applications that you define for it.
PowerPivot service application
PowerPivot service application defines a configurable, independent instance of a PowerPivot System Service.The purpose of a service application is to allow independent configurations of the same physical service, allowing you to isolate service application data and vary configuration for different SharePoint Web applications that consume the same resource.
PowerPivot application database
PowerPivot application database is an internal application database that stores configuration settings, connection information, server status, and schedule information for data refresh operations. Each PowerPivot service application uses a dedicated database for storing its internal data.PowerPivot Web service and PowerPivot managed extension
PowerPivot Web service is a thin middle-tier connection manager that runs on a Web front end. It coordinates and handles request-response exchanges between client applications and PowerPivot for SharePoint instances in the farm.PowerPivot managed extension is an assembly in the Analysis Services OLE DB provider client library that is installed on a client workstations via PowerPivot for Excel, and on application servers when you install PowerPivot for SharePoint. In terms of managed connections, both the Web service and the managed extension provide identical features; the specific implementation that is used varies depending on how the query processing request is formulated.
Self-service Business intelligence
Self-service business intelligence refers to the use of the analytic tools in Excel and the self-service site creation and document management capabilities of SharePoint to enable business analysts to create and share business intelligence (BI) solutions.