Data Feeds Overview (PowerPivot for SharePoint)
A data feed provides a data stream on demand from an online data source to a client application. In SQL Server 2008 R2 Analysis Services, you can use data feeds to push Atom XML data from any source into a PowerPivot data source.
Because data feeds are based on published standards, you can use them in ways that go beyond the specific role that feeds perform relative to PowerPivot data sources. Broad application of data feed technology is beyond the scope of this topic, but you can follow the links at the end to read about the standards and data service technologies that describe Atom feed usage.
Click a link in the following list to learn more about the data feed feature that supports PowerPivot data sources:
Definition of a Data Feed
Definition of an Atom Data Service
About Data Service Documents (.atomsvc file)
Processing a Data Service Document
Authenticating a Data Feed Import Operation
Reading a Data Feed in the PowerPivot Client Application
About the Atom Standard
Learn More About Data Services and Data Feeds
Definition of a Data Feed
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. A service or application provides the feed upon request, retrieving data from arbitrary data sources and then publishing it in real time to client applications or users who request it.
.gif "Components and process in a live data feed")
As an Analysis Services feature, data feeds give data specialists a useful tool for making organizational data readily available to PowerPivot data sources. If you have corporate data sources that are not easily accessed through the client data provider, or if data is accessed through complex queries that you prefer to hide, you can build an Atom data service around your data source that exposes data in a feed format. Numerous products and technologies are available to help you expose corporate data as a REST application or Atom data service, including the Microsoft ADO.NET Framework and Windows Communication Foundation (WCF) technology.
In addition to custom solutions that you create in-house, users can access data feeds from applications that support the feed format as a built-in feature. Data feeds are supported in SQL Server 2008 R2 components and in several Microsoft Office 2010 products.
Definition of an Atom Data Service
Before you can use a data feed, you must have an online data source, sometimes called a data service, which generates the feed. The data service is any URL addressable HTTP endpoint that provides the data and wraps it in an XML data structure that conforms to the Atom wire format.
For the purposes of importing data feeds into a PowerPivot data source, the data service might be an ADO.NET data service, a Web service, a REST application, or even a URL to a static document that contains XML data in an Atom format.
Data service capability can also be built into applications. These applications provide ready-to-use data feeds by publishing or exporting data structures as data feeds. Examples of Microsoft products that can generate data feeds include:
Microsoft SharePoint 2010. A SharePoint list that can export its structure and its contents as a data feed.
A SQL Server 2008 R2 Reporting Services. Reporting Services includes built-in capability for streaming data regions in a report as Atom 1.0 data feeds. For more information, see How to: Use Data Feeds (PowerPivot for SharePoint).
About Data Service Documents (.atomsvc file)
A data service document specifies a connection to one or more data feeds. At a minimum, this is a simple URL to the data service that produces a feed, but it can also be a complex URL that includes selection and filtering parameters.
In SQL Server 2008 R2 Analysis Services components, a data service document also specifies the name of a table that will be created in a PowerPivot data source when a data feed is imported. Specifying a destination table is required in data service documents that are processed by the PowerPivot client application. The table name defines the location of the data in a PowerPivot data source after the feed is imported.
Data service documents can be created manually if you know the XML syntax, but more often they are created on-the-fly by applications that generate feeds. Reporting Services and SharePoint server components will create data service documents internally as part of feed generation. If the PowerPivot client application is detected on your computer when a data service document is auto-generated in memory, the client application will start up automatically and immediately import the feed into a new or open PowerPivot data source. If a local client application is not available to import the data, the information worker is prompted to save the data service document (.atomsvc) to the file system to process the feed later.
The following illustration shows how requests for a data feed are resolved. In this case, the application that provides the data feed is a Reporting Services report. When a user exports a report as a data feed, the feed will either show up in a table control in a PowerPivot data source, or the user will be prompted to save a data service document (.atomsvc) file to the file system. If the PowerPivot client application is not installed on your computer, the only option available is to save the file.
.gif "Discarding or saving a data service document")
Note
An internet media type (sometimes called a MIME type) is defined for .atomsvc files. On the server, IIS 7.0 is configured to allow .atomsvc and application\atomsvc+xml. On a client workstation, the MIME type is associated with the .atomsvc file extension and PowerPivot for Excel. PowerPivot for Excel will launch automatically when an .atomsvc file is opened on the local computer.
Creating a Data Service Document (.atomsvc) file on a SharePoint site
SharePoint site administrators can create .atomsvc files in a SharePoint library to help make data feeds available to PowerPivot client application users. As developers and data specialists create Web applications and data services that produce feeds, they need a way to make those feeds available to information workers. Creating a data service document in a SharePoint library is one way to make feeds available across your organization.
On a SharePoint site, data service documents can be created in a data feed library by any user who has Contribute permissions. A data feed library adds support for a data service document content type. This content type provides the basis for creating and editing a data service document. Because the document is stored in a library, you can also delete and secure data service documents to control the availability of a feed to users in your organization. To access a data service document in a library, an information worker specifies the SharePoint URL to the item in a data feed library.
Processing a Data Service Document
Client applications, such as the PowerPivot for Excel, can process a data service document on demand to consume a data feed for immediate use in the application. When the service document is processed, a request for data is sent to the service URL in the document. For the PowerPivot for Excel, loading a data service document by its SharePoint path starts a sequence of events that adds new data in an PowerPivot data source.
The following diagram illustrates how data feeds are requested, published, and imported into an PowerPivot workbook.
.gif "Data feed flow from service to worksheet")
Step one. The PowerPivot client application issues an HTTP request to load a data service document from a SharePoint library. The request originates from the Import Data Wizard in the client application. The user specifies a SharePoint path to a data service document. Inside the document is a pointer to one or more data service URLs that provide one or more data feeds.
Step two. The PowerPivot client application reads the data service document. Within a single data service document, there can be any combination of data services or Web applications that publish syndicated Atom data on an external web site. Each entry in the document represents a single feed, which is specified by connection information to a data service or application that provides it. As shown in the illustration, a single data service document might contain multiple feeds that are generated from different sources. Each service or application is accessed through its HTTP endpoint anonymously or using the security context of the user initiating the import operation.
Step three. The PowerPivot client application sends an HTTP request to each service or application that is referenced in the data service document. The client application issues an HTTP GET request based on the URL of the feed.
Step four. The data service or application generates a feed. The structure of the feed is determined by the service or application that provides it. A feed might be an XML representation of a database table or view, or it might aggregate data from different tables or views based on a complex URL in the data service document.
Step five. The PowerPivot client application completes the import by creating a separate table for each feed. The name of the table that is created is based on a table name found in the data service document. Each data feed entry in a data service document is associated with the name of the table that holds the data after the import is complete. Although data feeds are generated asynchronously, the feeds are added to tables in a single operation. The Import Wizard waits until each feed is returned before it adds the data to the PowerPivot data source.
Authenticating a Data Feed Import Operation
When the PowerPivot client application makes HTTP requests to the data feed URLs in the data service document, the request is authenticated on the receiving end by the data service or application that produces the feed.
Authenticating Data Feeds from a Reporting Services Report or SharePoint List
Data feeds that you get from export operations from Reporting Services or a SharePoint list are always made under the Windows security identity of the current user. Only Windows integrated security is supported for data feeds that transport data from these applications to the PowerPivot client application.
You cannot specify Basic authentication, nor can you specify anonymous access. The properties for setting the authentication type are not available in application-to-application data transfers.
If the current user has a security context that is not Windows security principal (that is, if the user connected to Reporting Services using forms authentication, or the user connected to a SharePoint web application using forms authentication or a non-Windows security principle), that user will not be able to export a report or list as a data feed.
Authenticating Data Feeds from the PowerPivot Client Application
For data feed import operations that are specified through the Import Data wizard in the PowerPivot client application, you can specify the authentication type in the Advanced Properties dialog box.
Three authentication schemes are supported on the HTTP request:
Windows integrated security (Negotiate)
Basic authentication in clear text
Anonymous access
These schemes are supported by the .NET Managed Provider for Atom feeds, which the PowerPivot client application uses to import the feeds.
By default, the provider issues the connection under the Windows security identity of the user who is importing the data. However, a user can set advanced properties on the provider to specify a User ID and password. In this case, the connection request is sent using Basic authentication, and the user name and password are sent in clear text to the remote server. For security reasons, you should only do this if the connection is over an encrypted channel.
Anonymous access is not explicitly supported, but you can connect as an anonymous user if you leave the User ID and Password properties set to null, and you set the Integrated Security property to false.
Reading a Data Feed in the PowerPivot Client Application
A data reader takes each data feed that you import and converts it to an object or structure that the application understands. The PowerPivot for Excel used for creating and viewing PowerPivot data sources includes a built-in data reader. The data reader creates a table control for each tabular structure in the data feed.
Relating Feeds in a PowerPivot Data source
When you import multiple feeds in a single operation, PowerPivot client application will look for related data among the feeds. For example, if a data service document specifies a series of feeds that return tabular data from the same relational database, the PowerPivot client application can usually reconstruct the table relationships in the imported data. Similarly, if multiple feeds are generated from a single Reporting Services report, relationships between data regions in the report are reflected in the imported data.
To recreate relationships that exist in the original data, the feeds must originate from a common source that provides a framework for relating the data it contains. The PowerPivot client application will not automatically establish relationships for tables that are unrelated in the original source, even if they share the same data and column attributes. However, when data and column attributes are held in common, you can easily create relationships manually.
About the Atom Standard
The Atom 1.0 standard describes a pair of specifications for both publishing and structuring an XML data payload:
RFC5023 specifies the Atom Publishing Protocol that describes service discovery and how paths to a data object are constructed. A data service document (.atomsvc) file that you create in a SharePoint site conforms to the service document definition that this protocol describes.
RFC4287 specifies the Atom Syndication Format that specifies how a client-side data reader interprets and presents the data in an application. The PowerPivot for Excel includes an embedded data reader that supports this format. Data services that provide the actual data must implement this protocol.
The XML format includes required and optional elements that define overall structure, with provisions for custom content that makes up the bulk of the payload.
If you are familiar with RSS feeds, you might wonder how Atom feeds compare. A data feed that conforms to the Atom standard is similar to an RSS feed, in that they both publish syndicated data on demand. Atom differs from RSS in that it provides an additional specification for well-formed XML data. This is necessary for importing the kind of well-structured business data that you might want to use in business intelligence solutions. For this reason, Atom (and not RSS) is the supported standard for feeds used in PowerPivot data sources.
Learn More About Data Services and Data Feeds
The following links provide useful information and hands-on instruction for using ADO.NET data services:
Overview: ADO.NET Data Services
Using ADO.NET Data Services
Data Service Quick Start (ADO.NET Data Services Framework)
The next two links connect you to the Atom protocol documents on the IETF web site: