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:
SharePoint 2010 | Any data you've got on SharePoint as of version 2010 can be manipulated via the OData protocol, which makes the SharePoint developer API considerably simpler. |
IBM WebSphere | The IBM the WebSphere eXtreme Scale REST data service supports OData. |
Microsoft SQL Azure | If you have a SQL Azure database account you can easily expose an OData service through a simple configuration portal. You can select authenticated or anonymous access and expose different OData views according to permissions granted to the specified SQL Azure database user. |
Windows Azure Table Storage | Windows Azure Table provides scalable, available, and durable structured storage in the form of tables exposed as OData services. |
SQL Server Reporting Services | Microsoft SQL Server 2008 R2 Reporting Services can expose data from reports as OData. [See TechNet’s Generating Data Feeds from Reports (Report Builder 3.0 and SSRS) article, which mentions AtomPub but not OData.] |
Microsoft Dynamics CRM 2011 | The latest version allows you to query using OData |
GeoREST | GeoREST is a web-centric framework for distributing geospatial data. It allows RESTful feature-based access to spatial data sources, including full editing capabilities, through a MapGuide server or directly via FDO. |
SDL Tridion 2011 | SDL Tridion is a Web Content Management solution, the Content Services component now supports OData |
Webnodes CMS | Webnodes CMS is an enterprise quality ASP.NET CMS with a unique semantic content technology. Webnodes recently added OData support. Read more about it here. |
Telerik OpenAccess ORM | In mid-2010 Telerik released a LINQ implementation that is simple to use and produces domain models very fast. Built on top of the enterprise-grade Telerik OpenAccess ORM the LINQ implementation allows you to easily build an OData feed via a few easy steps by using the OpenAccess Visual Designer and the Data Services Wizard. For more info, visit www.telerik.com/odata |
Sitefinity CMS by Telerik | The Sitefinity CMS by Telerik is ready to host OData services. With the powerful API, any developer can expose any information from the CMS through a custom OData service. For more info, visit |
Telerik TeamPulse | The Telerik TeamPulse Silverlight client interacts with the database using a WCF data service, and more specifically by using the Open Data Protocol which is a popular way to expose information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites. The OData protocol comes in extremely handy for TeamPulse, because it exposes the TeamPulse data for digesting and distribution among teams and people, making sure that everyone will find what they need very quickly within the large repository of valuable information in the TeamPulse data store. For more info, visit www.telerik.com/odata |
Build your own | Using the OData-SDK you can add support for OData to your application. |
Facebook Insights | An OData Service for consuming Facebook Insights data. | browse... |
ebay | ebay now exposes its catalog via OData | browse... |
Netflix | The complete netflix catalog title via OData. See the Netflix developer OData documentation for more information | browse... |
twitpic | twitpic now exposes its Images, Users, Comments etc via OData | browse... |
Windows Live | You can now use an OData client to talk to your Windows Live resources (Photos, Contacts, Status, etc) whose REST endpoints are now OData endpoints. | |
Microsoft PDC 2010 | Information about all the sessions / speakers etc for Microsoft PDC 2010 exposed via OData | browse... |
Pluralsight | Pluralsight courses are now available via an OData feed | browse... |
DevExpress Channel | DevExpress has lots of training videos, now available via an OData feed. | browse... |
vanGuide | A social map of Vancouver Open Data. A collection of data services showing everything from parking lots to drinking fountains. | browse... |
Vancouver Street Parking | This feed exposes Vancouver street parking information. | browse... |
Open Government Data Initiative | Open Government Data Initiative (OGDI) is an open source data publishing solution for government agencies. | browse... |
Open Science Data Initiative | OSDI is based on OGDI which in turn uses the Azure Services Platform to make it easier to publish and use a wide variety of scientific data from government agencies. | Lots of feeds but no service document. You can however use their custom browser. |
The City of Edmonton Open Data Catalogue | Public data from the city of Edmonton. | browse... |
Windows Azure Marketplace DataMarket | Windows Azure Marketplace DataMarket allows producers to sell premier data to consumers, using OData. | |
TechEd 2010 | Microsoft TechEd 2010 conference session data. | browse... |
Nerd Dinner | Nerd Dinner is a website that helps nerds to meet and talk, not surprisingly it has adopted OData | browse... |
DBpedia | A community effort to extract structured information from Wikipedia and to make this information available on the Web, with full support for OData interactions on the live query services. (Powered by OpenLink Virtuoso.) | browse or query |
Linked Open Data Cloud Cache | Mirrors and interlinks dozens of data sets including all of data.gov, with full support for OData interactions. (Powered by OpenLink Virtuoso.) | browse or query |
OData Test Service (Read-Only) | This service is specially designed to introduce OData, it has a simple model and only a small number of resources. | browse... |
OData Test Service (Read-Write) | As above, but this time read-write (with some restrictions). | browse... |
Northwind | The famous Northwind Database exposed as an OData Service. | browse... |
OData Website Data | Data, like producers and consumers, from the OData Website exposed as OData. | browse... |
Stack Overflow | Q&A for programmers | browse... |
Super User | Q&A for computer enthusiasts and power users | browse... |
Server Fault | Q&A for system administrators and IT professionals | browse... |
Meta Stack Overflow | Q&A about Stack Overflow, Server Fault and Super User | browse... |
Telerik TV | Telerik's catalog of libraries, videos, Tags and Series | browse ... |
Public Transit Data Community | Collection of mass transit data from a variety of transportation agencies across the United States. See developer documentation for more details. | browse ... |
LogMyTime | Project time tracking software for freelancers and small to medium teams. | browse... |
INETA Live | INETA Live has an OData feed providing access to their vast library of User Group Presentations. | browse... |
Microsoft Pinpoint | Microsoft Pinpoint marketplace now exposes its data using OData - more details coming soon | |
Proagora | Proagora is a site that allows you to search for jobs, companies, and experts. | browse in English or French |
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:
| OData Consumers |
| 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: 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
After downloading and installing PowerPivot for Excel 2010 from http://powerpivot.com/, click the PowerPivot tab to open the PowerPivot ribbon and click the PowerPivot Window Launch button to open its ribbon.
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.
Leave the metadata window open, click the From Data Feeds button to open the Table Import Wizard’s Connect to Data Feed dialog, type or paste the same Data Feed URL, http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc for the demonstration Web Database, and add a Friendly Connection Name, as shown here:
Figure 3.
Click Next to open the Select Tables and Views dialog, after providing your credentials again, if requested. Mark the check boxes for the Source Tables (lists) you want to include:
Figure 4.
Click Finish to import the data:
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.
Technorati Tags:
Access,
Microsoft Access,
Microsoft Access 2010,
Access Web Databases,
SharePoint,
SharePoint Server 2010,
SharePoint Lists,
Access Services,
Open Data Protocol,
OData,
OData Producers,
OData Consumers,
PowerPivot,
PowerPivot for Excel,
PowerPivot for Excel 2010
Live OData Services
After downloading and installing PowerPivot for Excel 2010 from http://powerpivot.com/, click the PowerPivot tab to open the PowerPivot ribbon and click the PowerPivot Window Launch button to open its ribbon.
Leave the metadata window open, click the From Data Feeds button to open the Table Import Wizard’s Connect to Data Feed dialog, type or paste the same Data Feed URL, http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc for the demonstration Web Database, and add a Friendly Connection Name, as shown here:
Figure 3.
Click Next to open the Select Tables and Views dialog, after providing your credentials again, if requested. Mark the check boxes for the Source Tables (lists) you want to include:
Figure 4.
No comments:
Post a Comment