Updated 3/16/2011 with addition of LightSwitch Beta 2 as an OData consumer.
Updated 3/14/2011 with instructions for logging in to the Northwind Traders demonstration Web Database with public, View Only permission and viewing list/table data with PowerPivot for Excel. See the Browsing OData-formatted Web Database Content in PowerPivot for Excel section below.
One of the data interchange formats for SharePoint lists is the Open Data Protocol. According to Microsoft’s Open Data Protocol (OData) Web site:
The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications,services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.
OData is consistent with the way the Web works - it makes a deep commitment to URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with those resources (just like the Web). This commitment to core Web principles allows OData to enable a new level of data integration and interoperability across a broad range of clients, servers,services, and tools.
OData is released under [Microsoft’s] Open Specification Promise to allow anyone to freely interoperate with OData implementations.
OData was known during its beta period as Project “Astoria” and later as ADO.NET Data Services. It’s current name is Windows Communication Framework (WCF) Data Services. OData’s generally accepted as adhering to the Web’s Representational State Transfer (REST) architectural style, which qualifies the protocol as RESTful. You can keep up to date with OData developments by subscribing to MSDN’s WCF Data Services Team blog.
The OData Web site provides the following lists of application that expose OData services and current live OData services:
| |
|---|---|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
 Live OData Services | ||
|---|---|---|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
| |
|
| |
|
| |
|
| |
|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
One of the primary applications for OData-formatted information is delivering data to smartphones. Following is a list of OData consumers from the OData Web site with smartphone consumer SDKs emphasized:
Browsers Most modern browsers allow you to browse Atom based feeds. Simply point your browser at one of the OData producers.
Visual Studio LightSwitch VS LightSwitch Beta 2 supports OData from SharePoint 2010 lists, as well as SQL Server and SQL Azure as data sources. Initial tests with Access Web Databases throw an error. I’ll report the status of a fix for the error with an update.
OData Explorer A Silverlight application that can browse OData Services. It is available as part of the OData SDK Code Samples, and is available online at Silverlight.net/ODataExplorer.
Excel 2010 PowerPivot for Excel 2010 is a plugin to Excel 2010 that has OData support built-in.
LinQPad LINQPad is a tool for building OData queries interactively.
Sesame - OData Browser A preview version of Fabrice Marguerie's OData Browser.
Client Libraries Client libraries are programming libraries that make it easy to consume OData services. We already have libraries that target:
- Javascript
- PHP
- Java
- Windows Phone 7 Series
- iPhone (Objective C)
- [Android (via Restlet edition for Android; see Jerome Louvel’s Restlet supports OData, the Open Data Protocol of 3/15/2010)]
- .NET
For a complete list visit the OData SDK.
OData Helper for WebMatrix The OData Helper for WebMatrix and ASP.NET Web Pages allows you to easily retrieve and update data from any service that exposes its data using the OData Protocol.
Tableau Tableau - an excellent client-side analytics tool - can now consume OData feeds
Telerik RadGrid for ASP.NET Ajax RadGrid for ASP.NET Ajax supports automatic client-side databinding for OData services, even at remote URLs (through JSONP), where you get automatic binding, paging, filtering and sorting of the data with Telerik Ajax Grid.
Telerik RadControls for Silverlight and WPF Being built on a naturally rich UI technology, the Telerik Silverlight and WPF controls will display the data in nifty styles and custom-tailored filters. Hierarchy, sorting, filtering, grouping, etc. are performed directly on the service with no extra development effort.
Telerik Reporting Telerik Reporting can connect and consume an existing OData feed with the help of WCF Data Services.
Database .NET v3 Database .NET v3 - A free, easy-to-use and intuitive database management tool, supports OData
Browsing OData-formatted Web Database Content in PowerPivot for Excel
Update 3/14/2011: If you want to run a live test with the NorthwindTraders Web Database site hosted in an AccessHosting.com Trial account, connect to http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc in a browser. When the Windows Security dialog appears, type AH\devtest1 as the username and access as the password, and mark the Remember My Credentials check box:
Figure 1.
Click OK to display the OData metadata:
Figure 2.
Figure 5.
Click Close to display the contents of the first list in alphabetical order (Categories). Click the tab at the bottom of the page to display the list you want (Products for this example):
Figure 6. Select the columns that don’t contain interesting information, right click a selected column and choose Hide or Delete to remove it fom the Pivot table, and drag foreign key (lookup) values, such as CategoryID and SupplierID to the left:
Figure 7.
At this point, you can perform all common Excel PivotTable operations on the PowerPivot data.
