Use Data Feeds (Power Pivot for SharePoint)
Data feeds are one or more data streams that are generated from an online data source and streamed to a destination document or application. If you are using Power Pivot for Excel, data feeds can help you get existing corporate or business data from arbitrary data sources into the Power Pivot window in your Excel 2010 workbook. After you import a data feed to a workbook, you can reference it later in any data refresh operations that you schedule on a SharePoint server.
How you use a data feed depends on whether you are using built-in export features in applications that support Atom data feeds, or creating and using custom data services. Applications that are able to publish and read Atom XML data provide seamless data transfer that hides the mechanics of data feeds and data services from users. To a user, he or she is simply moving data from one application to another.
SQL Server 2008 R2 Reporting Services and Microsoft SharePoint 2010 provide data feeds that can be used in Power Pivot workbooks. You can use the information in this topic to learn how to access data feeds from reports and lists that you already have.
This topic contains the following sections:
You must have the Power Pivot for Excel to import a data feed into Excel 2010.
You must have a Web service or a data service that provides data in the Atom 1.0 format. Both SQL Server 2008 R2 Reporting Services and SharePoint 2010 can provide data in this format.
Before you can export a SharePoint list as a data feed, you must install ADO.NET Data Services on the SharePoint server. For more information, see Install ADO.NET Data Services to support data feed exports of SharePoint lists.
In a SharePoint 2010 farm, a SharePoint list has an Export as Data Feed button on the List ribbon. You can click this button to export the list as a feed. For best results, you should have Excel 2010 with the Power Pivot client application on your workstation. The Power Pivot client application will launch in response to the data feed export, creating a new Power Pivot table that contains the list.
Open the list on your SharePoint site.
In List Tools, click List.
In Connect and Export, click Export as Data Feed.
The Export as Data Feed button is added to SharePoint by way of Power Pivot. If you do not have Power Pivot for SharePoint installed or you did not activate the Power Pivot feature, this button will not be available.
Click Open if Power Pivot for Excel is installed locally, or click Save to save the .atomsvc document to your hard drive for import operations at a later time.
If you chose Open, use the Table Import Wizard to import the data feed to a worksheet. The data feed will be added as a new table in the Power Pivot window.
An error will occur if ADO.NET Data Services 3.5.1 is not installed on the SharePoint server. For more information about the error and how to resolve it, see Install ADO.NET Data Services to support data feed exports of SharePoint lists.
If you have a deployment of SQL Server 2008 R2 Reporting Services, you can use the new Atom rendering extension to generate a data feed from an existing report. For best results, you should have Excel 2010 with the Power Pivot for Excel on your workstation. The Power Pivot client application will launch in response to the data feed export, automatically adding and relating the tables and columns as they are streamed in.
For instructions on how to export a data feed from a report, see Generate Data Feeds from a Report (Report Builder and SSRS) in the Report Builder help file.
To set up a recurring data refresh schedule that re-imports report data into a Power Pivot workbook that is published to a SharePoint library, the report server must be configured for SharePoint integration. For more information about using Power Pivot for SharePoint and Reporting Services together, see Configuration and Administration of a Report Server (Reporting Services SharePoint Mode).
If you have a custom data service that generates Atom feeds, you can set up a data service document as a way to make the data available to users and applications. A data service document (.atomsvc) file specifies one or more connections to online sources that publish data in the Atom wire format. Data service documents can be created in a data feed library, which is a special-purpose library that provides a common access point for browsing data service documents that have been published to a SharePoint server. Information workers who have permission to access data service documents in the data feed library can reference the document's SharePoint URL to import the data feeds to their workbooks and applications.
Open a data feed library that was created by your site administrator. For more information, see Create or Customize a Data Feed Library (Power Pivot for SharePoint).
In Library Tools, click Documents.
Click New Document.
Provide a file name and description.
Specify one or more URLs that provide the feed:
Base URL is optional. You should specify it if a data service document provides multiple feeds. Base URL should specify the portion of the URL that is common to all the feeds (for example, the server name and site). If you are creating a data service document to a Reporting Services report, the base URL would be the report server URL and report.
Web Service URL is required. Without the Base URL, this value must include http:// or https:// in the address. If you specified a Base URL, the Web service URL is the portion that follows the Base URL. For example, if the full URL is http://adventure-works/inventory/today.aspx, the Base URL would be http://adventure-works/inventory, and the Web service URL would be /today.aspx.
The Web service URL can include parameters that filter or select a subset of data. The application or service that provides the feed must support the parameters that you specify in the URL.
Enter a Table Name, one table for each feed. This value is required. The table name is used by a client application that consumes the data feed. In Power Pivot for Excel, the table name is used to name tables in the Power Pivot window that will contain the imported data.