OData is a new web service protocol, developed by Microsoft, which sits somewhere between ODBC, Rest, and a business intelligence oriented SQL.
The protocol seems to be fairly complex, and as such, does not seem well-suited to “big data” workflows. It does, however have built in support for things like differential data loads, so there is some hope there, however minor. While Microsoft claims the big benefits of OData is API standardization, the big win currently is that a lot of Microsoft products speak the protocol: Excel 2013 can natively import data from OData services (and there is a plugin for 2010), as can Sharepoint. Notably the demo applications of Excel use access files with ~5-10 million row tables in them, which is well beyond what I would naively expect Excel to handle.
When you enable “PowerPivot”, you get a new ribbon:
When you click ‘Manage’ you get to another screen, where you can import data from other Excel files, Access databases, real databases, or a web service. From here you can also define foreign key relationships, so once you get a bunch of data downloaded, you can join across multiple sources.
While this sounds like an abomination, there are people who already run entire businesses this way. For instance, I ran across a grocery store that would have a regular database for member information and inventory, and separate excel spreadsheets emailed to them by manufacturers – this all joined together in Excel to produce mailings, etc.
One other thing of note is that you can do imports from the ‘Azure Data Market’, which is a bunch of free and for-sale data sets, potentially a good distribution channel for someone with valuable data.
If you’re strictly interested in developing OData API endpoints, Excel is still a nice tool, because you can trap the network traffic in Fiddler to see what the protocol looks like.
There are a few Java libraries to help support this protocol, although so far all seem a lot larger than one would really want (e.g. you end up pulling in a lot of dependencies, many of which will be incompatible what what you already use, e.g. CXF).
You can, however, get a really simple project up and running fairly quickly by following the maven instructions [[http://olingo.apache.org/doc/sample-setup.html]] (maven archetypes are just templates for projects, so these instructions aren’t really that clever)
mvn archetype:generate \
-DinteractiveMode=false \
-Dversion=1.0.0-SNAPSHOT \
-DgroupId=com.sample \
-DartifactId=my-car-service \
-DarchetypeGroupId=org.apache.olingo \
-DarchetypeArtifactId=olingo-odata2-sample-cars-service-archetype \
-DarchetypeVersion=RELEASE \
cd my-car-service
mvn clean install
cd target
You then need to copy “my-car-service.war” into Tomcat, and when you load http://localhost:8080/my-car-service/, you’ll get this page:
To get Excel to read this, you need to use a slightly different URL:
http://localhost:8080/my-car-service/MyFormula.svc/
One of the neat things about this protocol is that it allows Excel to auto-discover the table names:
So far, it just seems to import all the data, so be careful:
Now you have the data, and can pull a refresh whenever you want it, and that’s it!
