Wednesday, March 16, 2011

Access Web Databases on AccessHosting.com: What is OData and Why Should I Care?

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:

imageThe 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:

imageApplications that can expose 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.

image Live OData Services

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:

imageOData 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

imageAfter 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:

image

Figure 1.

Click OK to display the OData metadata:

image

Figure 2.

imageLeave 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:

imageFigure 3.

imageClick 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:

image Figure 4.

Click Finish to import the data:

image

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):

image

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:

image

Figure 7.

At this point, you can perform all common Excel PivotTable operations on the PowerPivot data.

No comments:

Post a Comment