PowerPivot Features
SQL Server 2008 R2 Analysis Services brings large scale data analytics to the Office 2010 desktop, with full SharePoint integration so that you can share and manage data from SharePoint sites.
Feature Categories
Click the following categories to learn about new features in Analysis Services:
SQL Server PowerPivot for Excel. Assemble and analyze large scale, heterogeneous PowerPivot data in Excel 2010 workbooks. Create relationships between tables to join data from a variety of sources into a new composite data source. Use a rich expression language to create relational queries for custom aggregations, calculations, and filters. Add data visualization and interaction through PivotTables, PivotCharts, Slicers, and filters in Excel reports.
Data Analysis Expression (DAX) Language. A new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis.
SQL Server PowerPivot for SharePoint. In a SharePoint environment, PowerPivot query processing and data refresh for published workbooks is enabled through PowerPivot server components that are available in the SQL Server 2008 R2 release. The PowerPivot for SharePoint feature provides services, a management dashboard, library templates, application pages, and reports for using and managing server software. PowerPivot server components are fully integrated in SharePoint. SharePoint administrators use Central Administration to configure and manage PowerPivot services in the farm.
VertiPaq Data Compression and Fast Processing. VertiPaq is fast, column oriented processing for published PowerPivot datasets in a SharePoint farm and for offline access while building or modifying large scale PowerPivot data in an Excel workbook.
Reporting Services integration. Create reports based on PowerPivot data in Excel workbooks, or populate a workbook with imported data from a Reporting Services report.
Programmatic Interfaces. Use programmatic features to customize workbooks and automate administrative tasks.
Unsupported Features. Describes the feature differences between native mode Analysis Services and SharePoint integrated mode.
SQL Server PowerPivot for Excel
SQL Server PowerPivot for Excel is a new add-in that extends Excel 2010 to support extremely large datasets that you create based on heterogeneous data from corporate databases on a network, public data on the web, or local data on your computer. Additional capacity is available through a separate PowerPivot window that you open from within Excel. Within this window, you use column mapping to establish relationships between similar data from different sources. The end result is a new data source embedded within the workbook that becomes the basis for interactive PivotTables and PivotChart reports you create in the same (.xlsx) file.
Features at a glance
SQL Server PowerPivot for Excel includes the following features:
A window for adding and creating relationships in the data. The PowerPivot window is separate from the Excel window so that you can switch between the data definition and visualization environments. The window has its own ribbon menu with commands and tools that you use to enrich your data.
A local VertiPaq data processor that loads large scale data in memory for offline processing on a workstation. The local processor is a client-side version of the Analysis Services service that runs on a SharePoint server. It is included in PowerPivot for Excel.
Same-file storage for data and presentation objects. PowerPivot data, PivotTables, and PivotCharts are part of the same .xlsx file. There is only one file to create and manage. The file can be used as it was published, or it can become a data source for a Reporting Services report or another workbook.
Updated Analysis Services client libraries and OLE DB provider. AMO and ADOMD.NET libraries are updated to work with a local Vertipaq processor. The add-in also installs an updated OLE DB Provider for Analysis Services used to connect to PowerPivot data when operating in Vertipaq server mode.
Detailed Feature Description
Use the information in the following table to learn more about PowerPivot features.
Feature |
Description |
|---|---|
PowerPivot window |
The PowerPivot window provides a familiar grid layout that contains the tabular data you import, download, or link to. You can also copy-paste additional data that you want to include in your analysis. Each set of data is placed in a separate table in the window. You can create relationships between columns in the tables to create a holistic view of the data that becomes the basis for PivotTables, PivotCharts, and other aggregation and summation features in Excel. You can use Data Analysis Expressions (DAX) expressions or the relationship-building tools to create relationships between tables. |
Table Import Wizard |
You can use the Table Import Wizard to add business data from your corporate network, you local computer, or ad hoc data sources on the web. You can connect to external data sources directly, or use predefined connection objects such as ODC to get the data. You can also enter or copy in data from other worksheets, documents, or text files. To import data, you can use point-and-click actions to auto generate a query that retrieves data from a single table or multiple tables. You can run the wizard repeatedly to get data from additional data sources. Data is placed in separate tables on tabbed pages. Tabbed pages provide maximum room for viewing the data in each table. A new tabbed page and table is created each time you import a new dataset. |
Relationship definition tools |
You can create relationships between data sources by matching columns that contain similar or identical data. Column-to-column mapping across the full set of tables allows you to build a multidimensional data source between tables that are from different data sources. You can edit relationships at the table level, by selecting which tables you want to join, or by remapping individual columns. |
Data reuse from other applications (data feeds) |
PowerPivot for Excel includes built-in support for consuming data feeds as a source of data in the PowerPivot window. Data feeds can be one-time or repeatable data imports from URL addressable data sources that return XML tabular data. More importantly, data feeds provide the basis for data reuse with Reporting Services and SharePoint lists. The built-in data export features in Reporting Services exports report datasets as feeds that become tabular datasets in your workbook. |
Data refresh |
For connected data sources that you import into the PowerPivot window, you can configure autonomous data refresh to update data on a scheduled basis after you publish the workbook to a SharePoint library. PowerPivot data refresh is supported for data sources that can be accessed after the workbook is published to SharePoint. You can work with all your data, regardless of its origin. However, data that originates from online sources retains connection information that can be used later to refresh data values automatically. |
Excel worksheet integration |
Excel worksheets provide all data visualization and interaction. PivotTables, PivotCharts, filters, and Slicers provide the presentation of PowerPivot data you create in the PowerPivot window. |
For more information about how to install and open PowerPivot for Excel, see Install PowerPivot for Excel (SQL Server Books Online) and PowerPivot for Excel.
Data Analysis Expression (DAX) Language
PowerPivot for Excel includes an expression language that gives you programmatic access to PowerPivot data structures in the workbook. You can use the expression language to create formulas, filters, and calculated columns. You can use it create relational queries on the tables in the workspace. You build DAX expressions in PowerPivot for Excel.
For more information, see Data Analysis Expressions and MDX. DAX is also documented in the online help of the PowerPivot for Excel add-in. For more information, see the help file that is installed with the add-in.
SQL Server PowerPivot for SharePoint
Microsoft SQL Server PowerPivot for SharePoint adds services, a management dashboard, library templates, reports, and content types to support PowerPivot data access and server administration in a SharePoint farm.
Features at a glance
PowerPivot for SharePoint includes the following features:
Supports requests for PowerPivot data in the farm. A PowerPivot System Service intercepts requests for PowerPivot data in an Excel workbook, and allocates the request to an available Analysis Services server instance in the farm.
Data refresh configuration and administration by workbook authors and owners. Users who create and publish a workbook can set up data refresh schedules to import newer data automatically.
Rich preview and document presentation through PowerPivot Gallery. You can also preview and create Reporting Services reports from this central location.
Server configuration and management through SharePoint Central Administration and PowerPivot Management Dashboard.
Scalable solution. You can add or remove entire installations of PowerPivot for SharePoint to increase or decrease processing capacity across the farm. Autodiscovery features in SharePoint and load balancing by the PowerPivot System Service ensure that new server instances are readily found and automatically used.
Detailed Feature Description
SharePoint integration enables self-service data ownership because it provides the tools and services for storing and managing PowerPivot workbooks, and for setting permissions that control access to content and operations.
Feature |
Description |
|---|---|
SharePoint integration. Content storage and document management in the farm. |
Centralized data access and document management is enabled through SharePoint sites and libraries. Document management is simplified because both data and presentation objects are in one file. You can use content types and workflows to enforce retention policies or processes. Versioning support enables you to track the changes to your solution. You can enable major and minor versioning to enforce a checkout policy when making changes to data or the presentation layer. For more information, see SharePoint Administration (PowerPivot for SharePoint). |
Excel Services integration |
Excel Services performs data rendering and visualization for Excel workbooks that contain PowerPivot data. Because PowerPivot software does not provide its own data visualization, enabling Excel Services on SharePoint is an essential part of any PowerPivot server deployment. For more information, see Plan for PowerPivot Integration with Excel Services. |
PowerPivot Gallery |
Rich preview and document access is provided through a new PowerPivot Gallery library that includes accurate thumbnail images of the worksheets or named ranges in a published PowerPivot workbook. Using PowerPivot Gallery, people can preview a workbook before opening it or downloading it to a desktop application. For more information, see Manage PowerPivot Gallery. |
PowerPivot Management Dashboard |
In Central Administration, farm and service administrators can view consolidated reporting data about PowerPivot processing in the farm. Usage reports can reveal data consumption patterns that show how PowerPivot data is being used. Administrators can supplement or replace the built-in reports through custom reports. For more information, see PowerPivot Management Dashboard. |
PowerPivot data refresh |
Autonomous data refresh is supported for published workbooks that contain PowerPivot data. Workbook owners can schedule data refresh to get updated data from any connected data sources that provided original data to the workbook. Users can view PowerPivot data refresh status and history for each PowerPivot workbook. For more information, see Manage PowerPivot Data Refresh. |
Data feeds for consuming and moving data between applications |
Data feeds are used to perform one-time or repeatable data imports from URL addressable data sources that return XML tabular data. You can create a data feed library to provide general access to data services that generate the feeds. For more information, see Manage PowerPivot Data Feeds. |
Authentication and access control |
The authentication methods and authorization model in SharePoint extends to PowerPivot workbooks. There is no separate authentication or authorization model to implement. Permissions in your SharePoint deployment determine access to the document. For more information, see Plan PowerPivot Authentication and Authorization. |
VertiPaq Data Compression and Fast Processing
In-memory data compression and processing describes VertiPaq server mode, a new class of Analysis Services processing capability for PowerPivot data that is embedded in Excel workbooks. Processing metrics for VertiPaq can vary considerably depending on the degree of redundancy in the original data sources: the higher the redundancy in the data, the faster the processing.
Data can be processed in three ways: on demand from the client computer, on demand in a SharePoint farm, or on a schedule via data refresh.
Feature |
Description |
|---|---|
On-demand query processing in a farm |
Embedded or linked PowerPivot data is loaded on demand on Analysis Services service instances in the farm, and unloaded when the data becomes inactive or if there is resource contention on the server. PowerPivot data is processed separately from the rest of the Excel workbook. Excel detects the PowerPivot data and automatically forwards processing requests to an Analysis Services server. To the user, request redirection and Analysis Services data processing is transparent. |
Local processing in the client application |
Local processing is performed on a computer that has an installation of PowerPivot for Excel. The local processor is based on the server implementation of Analysis Services service that runs in a SharePoint farm. It runs in-process on the client application. There is no separate service to provision or manage. |
Unattended data refresh |
For PowerPivot data in workbooks, you can schedule data refresh to update PowerPivot data on a frequency that you define. PowerPivot data refresh is scheduled and managed in application pages on a SharePoint site. For more information, see Enable and Configure PowerPivot Data Refresh. |
For more information about query processing in the farm, see Analysis Services in SharePoint integrated mode (PowerPivot for SharePoint).
Reporting Services integration
Deploying the SQL Server 2008 R2 releases of both Reporting Services and SQL Server PowerPivot for SharePoint in the same SharePoint Server 2010 farm offers the following integration points.
Integrated feature |
Description |
|---|---|
Use a report as a connected data source in a PowerPivot workbook. |
From Report Manager or report server pages in a SharePoint site, you can export report data to the PowerPivot window. Behind the scenes, the report server streams the report as multiple data feeds to PowerPivot tables in the window. By using the data feed export option, you can push report data into a PowerPivot workbook, making a Reporting Services report an instant data source. |
Create a new report from PowerPivot workbooks in PowerPivot Gallery |
When you publish a PowerPivot workbook to the PowerPivot Gallery library, you can use the New Report option to launch Report Builder and start a new report that uses the PowerPivot workbook as a data source. |
For more information, see Reporting Services with SharePoint Integration and Plan for PowerPivot Integration with Reporting Services.
Programmatic Interfaces
Developers and administrators who automate deployment or configuration tasks through script can use the following programmatic interfaces with a deployment of SQL Server PowerPivot for SharePoint.
Interface or technology |
Description |
|---|---|
AMO |
Analysis Management Objects (AMO) is an administrative programming interface for managing a running instance of Analysis Services. You can use AMO query commands to view properties or structures of a PowerPivot workbook that is loaded on an Analysis Services instance in the farm. You cannot use AMO to create or alter any Analysis Services objects. In SharePoint integrated mode, PowerPivot workbooks can only be created or modified in the Excel client application. In a SharePoint run time environment, Analysis Services data structures in a PowerPivot workbook are read-only. For more information, see Microsoft.AnalysisServices. |
*Windows PowerShell |
PowerShell is used for administrative programming in a SharePoint environment. For deployments of SQL Server PowerPivot for SharePoint, you can make limited use of PowerShell script to support some installation and configuration tasks. For more information, see PowerShell Configuration Script (PowerPivot for SharePoint). |
Data services and data feeds |
You can write custom data services to pipe XML data to PowerPivot workbooks via data feeds. The data service streams one or more data feeds to a recipient workbook that contains the embedded data. A data analyst or information worker can access the feeds that the service provides by exporting data from applications that produce the data feed format, or by setting up a data service document in a SharePoint library. He or she can then reference the data service document to generate data feeds on demand. For more information, see Data Feed XML Syntax. |
Unsupported Features
If you are familiar with Analysis Services functionality based on previous releases of SQL Server, you should know that PowerPivot for SharePoint has a different purpose and capabilities from a native mode instance of Analysis Services. Major differences include the following:
MDX is supported in limited ways (for example, you can view properties of an in-memory PowerPivot data file). Session cubes, named sets, and measures are not supported. Writeback is not supported. Parent-child dimensions are not supported. MDX script is not supported.
User-defined hierarchies are not supported. PowerPivot data is not hierarchical. For this reason, PerformancePoint integration is only partially supported. You can connect to PowerPivot data in PerformancePoint dashboards and web parts, but you only get a flat version of the data. KPIs or hierarchical data that you would otherwise get if you connected to native mode Analysis Services cube databases is not exposed through PowerPivot data connections in PerformancePoint.
MOLAP, HOLAP, and ROLAP modes are not supported on Analysis Services server instances in a SharePoint farm. PowerPivot data in a workbook cannot be migrated to a MOLAP, HOLAP, or ROLAP cube database. PowerPivot data must run on an Analysis Services server in SharePoint integrated mode, which is not available outside of a SharePoint farm.
Data Mining is not supported. DMX is not supported.
Business Intelligence Development Studio (BIDS) and Management Studio are not supported for creating PowerPivot data. PowerPivot for Excel is the sole authoring tool for creating PowerPivot data.
Excel PivotTables that go against PowerPivot data do not support the following: writeback, offline cubes, drill through.