Overview: Recently I was asked if PowerPivot could get data from a Web service. This post discusses how you could make happen if an Atom-aware Data Feed is available.

Intro to Data Feeds in PowerPivot

There are several options for importing external data into PowerPivot. One of these is Data Feeds.

MSDN defines data feeds which are supported by PowerPivot:

“A data feed is an XML Atom 1.0 data payload generated by a Web service, data service, or Web application that supports the Atom standard. A data feed has a consistent XML tabular structure that does not change over time, and variable data that can be different each time the feed is generated.”

Data feeds can be used to gain access to all types of data, inside or outside of a private organization. Inside an organization, feeds can be one option for providing data to self-service BI users who are not permitted to have direct access to databases.

Working with Data Feeds in PowerPivot

The Web service or application needs to provide a Data Feed structured in the Atom 1.0 format, for PowerPivot to be able to consume it.
A URL is made available as the Data Feed. This can be a simple URL, or a more complex URL which passes parameters (up to the 2,048 character limit).
PowerPivot does not support RSS feeds. This is because the Atom format follows a standard which is more structured & rigid, making it more suitable to working with data.
By default, Windows authentication is used for importing data into PowerPivot. Very few exceptions are allowed (or explicitly supported).

Steps to use a Data Feed in PowerPivot

1. First, find the URL of the data feed you need. For this example, let’s use the PluralSight Training Catalog found here: http://www.pluralsight-training.net/odata/

Note: This is a consumer sample provided by OData.org.

2. Copy the URL onto your clipboard.

3. In the PowerPivot window, click the “From Data Feeds” button. In the Data Feed URL text box, paste the URL from your clipboard.

Test the Connection to ensure it works, then hit Next.

4. Select the Tables you wish to import into PowerPivot and then Finish. (I chose them all the first time since I hadn’t seen this set of data previously. If you can, deselect tables you don’t need – reducing the size simplifies your PowerPivot model & improves its performance.)

Hint! The Filter Details isn’t functional when you’re working with a Data Feed. If you need to filter the results, it would need to be done by passing parameters within the URL.

5. You can observe the progress as it imports the data into PowerPivot.

Hint! Extremely large datasets will take some time – that’s our trade-off for ease of use with PowerPivot.

6. And now the data is imported.

Hint! Depending on how the underlying data for the feed was set up, you may or may not get relationships set up for you in PowerPivot. In this sample dataset, there were no relationships auto-detected by PowerPivot.

Data Feeds Supported within the Microsoft Stack

Just to take it a step further … In addition to custom Web services or applications, there are two areas within the Microsoft stack where you can currently use data feeds to import data into PowerPivot:

SQL Server Reporting Services reports (starting with SQL Server 2008 R2). Let’s say there’s a Weekly Sales Report by Region, generated by the data warehouse which handles some transformations and data quality issues. Since this Weekly Sales Report by Region is considered the authority, there’s no need to reinvent the wheel to re-accumulate those numbers. Subscribing to the authoritative report improves accuracy & consistency, not to mention saves you time when developing the PowerPivot model. (Remember … it’s one feed per “data region” in the report.)
Lists in SharePoint 2010. Let’s say there’s a list which maintains the Salesperson assigned to each geographic area (think of this as some master data maintained in a SharePoint list). By subscribing to this list, which is considered the authority, the most recent geographic assignments will be retrieved which increases the accuracy & consistency of reports created from the PowerPivot model.

To find the URL to use in a Data Feed, click the “Export to Data Feed” icon in the report toolbar (after the report has been executed) as shown below, or in SharePoint.

Finding More Information

MSDN – Data Feeds Overview (PowerPivot for SharePoint)

IT Conversations – PowerPivot, OData, and the Democratization of Business Intelligence

TechNet – Generating Data Feeds from Reports (Report Builder and SSRS)