OData with DataJS

If you've been rolling your own OData requests using jQuery, you might want to look at DataJS. It simplifies your code quite a bit and is really easy to use.

Here are examples of jQuery getJson and ajax methods:

// - Roll your own ajax (see OData version below)
$.ajax({
    type: "GET",
    url: "/OData.svc/PortfolioManagerGroups",
    headers: { "Accept" : "application/json",
               "MaxDataServiceVersion" : "2.0" },
    dataType: "json",
    success: listGroups,
    error: ajaxError
});

This is the DataJS vesion of the above jQuery.ajax() example

OData.read("/OData.svc/PortfolioManagerGroups", 
            listGroups, odataError);

This example shows how to do a JsonP formatted query in DataJS.

// I like to wrap this in a function
function readJsonp(qry, callback) {
    OData.defaultHttpClient.enableJsonpCallback = true;
    OData.read(qry, callback, odataError);
    OData.defaultHttpClient.enableJsonpCallback = false;
}

// A query would look like this. Callback should loop through data.results
var qry = "http://someplace.com/Accounts?$select=ACCT_CD,ACCT_NAME&$orderby=ACCT_CD";
readJsonp(qry, listAccounts);

Personal BI Tool - PowerPivot for Excel

On the continuing odyssey to discover new ways to leverage OData services, I finally loaded up PowerPivot on advice from one of my colleagues (ok - my boss) and can only say wow.

There are some rough edges, mostly due I think to the fact that I'm overtaxing my "workstation" (a VM running with 2Gig of ram and 1 CPU). However, even in this constrained environment I was able to run through the tutorial - which includes crunching numbers in 2+ million row sales fact table.

The tools here are based on running SQL 2012 in an embedded mode within Excel (no need to install SQL tools - just Excel 2010 and this free add-in). So you get a lot of tooling to import data and manipulate it, as well as do calculations on it.  This won't replace fullblown BI solutions, but it is pretty darn capable.

And, when you combine this with the ability to source data from databases, flat files, excel tables, AND OData (WCF Data Service) sources, this tool puts a lot of power in the hands of analysts.

PowerPivot download

PowerPivot Tutorial (highly recommended - takes about 2 hours to run through it)

Sourcing OData feeds into PowerPivot

WCF Data Service version 5 OData V3 Support

Finally, Entity Framework Code First DbContext is supported by the WCF Data Service, as well as additional features in the OData standard.  Version 5.0 download here

There a few steps you need to take to upgrade your WCF Data Service though. (details here)

Strangely, support for JSONP was left out of this new release. If you are using this workaround, as I am, it's a one line fix - change the Accept header fix-up to: application/json;odata=verbose

When you add a new WCF Data Service into a Visual Studio web project, you'll have to repeat the steps given above, unless you are willing to dig under the covers (I am not ;) and create an item template for the new version in Visual Studio.

A couple of other interesting links:
WCF Data Services Toolkit  - An OData framework for incorporating non-SQL (or more accurately non-Entity Framework) data sources into an OData service.

JayData - a linq-like javascript library that can use Odata. (uses DataJS under the covers)