In an effort to make data available to a myriad of stakeholders from analysts to executives I began implementing OData services via C# and IIS as an experiment. With the tools available in Visual Studio, it is quite easy to spin up a quick service to vend JSON data.  In the following example I am using Visual Studio Community 2013 and SQL Server Express on my Windows 8.1 laptop.  As a prerequisite, I downloaded the AdventureWorks Data Warehouse 2012 from:

 http://msftdbprodsamples.codeplex.com/releases/view/105902 and restored it to my Express instance.

To begin we open Visual Studio and start a new ASP.NET Web API project, give it a name, AdventureWorksOData seemed appropriate.

Next we work through the wizard with the default settings. Open a new project and select the Web API as shown below.

Once the project and solution are created we need to get the relevant libraries.  From the References node in the Solution Explorer, right click and open up NuGet.  In the NuGet interface search in the upper right search box for 'OData'. We want the v1-3 package.

After installing the OData NuGet package, we can start building out our Entity Framework data model.  This model is generated from the AdventureWorks database we have restored to our instance.

Right click the Model entry in the Solution Explorer, and select Add -> New Item from the menu.

Select the ADO.Net Entity Data Model from the options and give it a name. Next a wizard pops up to walk you through building the model.

Select the option shown to build from a database. Next you have to point the wizard to the database you would like to model.

Create the needed connections.

Below I point it to my instance on my laptop where I have alread restored the AdventureWorksDW2012 database, and select that database.

Save the connection.

I was prompted to select the version of Entity Framework I wanted to use. I took the default.

Finally we get to pick some tables. To keep things light, I stuck with FactInternetSales and some of its relations, as shown below.

When we finish we get an model like so:

Pretty cool, so now what?  Well, before we do anything, we need to build the solution. This will refresh the solution with details about the model that will have to be in the solution before we can build our controllers. 

First, build the solution with F5. Then right click on the controller folder in the Solution Explorer, then select Add -> Controller. Something like this should pop up.

Select the Web API 2 OData Controller, as shown, and click Add. There are more decisions to make. Select the drop down for the model class and pick from the tables we added to our Entity Framework model.  Don’t see any? Try building the solution again. If you forgot to build this before, the dropdowns won’t be populated.

After you add your controller, you will should get a new code page named for the newly created controller class, and a readme.txt pops up with some instructions.

What does that mean exactly? Well, if we open up the class we get more details along with the code.

In the comments here we can see instructions to add the using statements and builder calls to our WebApiConfig file.  So let’s do that. Open up the file and we see.

Make the modifications as instructed and we end up with:

Wow, that seems like a lot of effort and wizardry.  What do we get for all that? Well, mashing the run button should kick up the service in our browser of choice.  I prefer Chrome because it doesn’t try to download the JSON results we get back from the service in a file; it just displays them.  Like this:

You may have to tack on ‘/Odata’ to the URL, but here we can see our entities from our model.  With some spiffy querying via the OData standards (http://www.odata.org/) we can take the top 10 from our Sale table. 

Cool! So we have some data via a service.  To get the other tables folded in, you will need to go back and add controllers for each and rebuild the solution.

How does this help us? Well, with this service deployed anyone, can connect up and pull data down.  The structure also gives us a layer of abstraction between the Warehouse implementation and the consumer, thus allowing us to insulate folks from change.

Now for ingestion, Excel’s Power Query features now consume OData with a few clicks. This allows eager analysts or Excel savvy executives to pull data they want to prototype reports or do some lightweight analytics. 

If you open Excel and go to the Data tab you will see an icon for additional sources.  If you select the OData option and point it to our service, you can pull down our data.

Additionally now that we have a service we can vend to other tools as well.  For instance if you had R (http://cran.r-project.org/bin/windows/base/) and R Studio (http://www.rstudio.com/products/rstudio/download/) installed (like any "real" "data scientist") you could consume the same service with R and explore the data with R code.  

It is not pretty. I’m not an expert R coder, and this is a very simple example to show how I can get the JSON and build a graph.  Albeit an UGLY one.

There are some additional hurtles with data volume, security concerns and deployment, however, we built an OData web service by writting absolutely no C# code - just some cutting and pasting.  Additionally were able to see our data flowing through the service to a browser and a couple other tools.  I hope this brief example shows how easy it is to get started.