Visualise Azure Table Storage with Excel and Fiddler

Today I came across an interesting problem;

I’m a big fan of Table Storage but its potential is yet to be realised because the tool support just isn’t a match for databases. We’ve got a solution which lays down a lot of data into Azure Table storage but the options to view that data is limited. There are plenty of available viewers including Visual Studio, Azure Storage Explorer and others. The problem with all of these viewers is they are limited to plain old tablular data views.

What if we want to “visualise” the data? Is there a tool to provide a graphical view of my Table Storage data?

Sure, we could open Visual Studio grab a charting control deploy a WebRole to build images from the data but I’m no fan of writing code, and prefer to build solutions on the shoulder of others. This, naturally, got me to thinking about Excel!

Microsoft in their wisdom and a view to the future of an “Open Data” world used oData to interface with Table Storage. oData is a REST protocol built for interacting with data stores over HTTP. The entire Azure Data Market is presented through oData and there are some great examples of presenting data through oData (like the Netflix API).

With that said, shouldn’t it be easy to point Excel at the oData interface and chart the data?

Yes and No.

Yes Excel has the ability to interface with oData through both the PowerPivot plugin add on and natively in the latest version of Excel 2013

But if you point directly at your Table Storage URL endpoint then you’ll get an access denied message. The problem is there is no way to express your table storage credentials to connect to the data store. And it’s more complicated than just adding the storage key onto the request because the Azure implementation of Table Storage requires that authentication headers have just a hash of some request parameters and the storage key. (This is sensible since your secure storage key never needs to cross the wire.) The concept of the “SharedKey” and SharedKeyLite” are well described here.

So what we really need is a local proxy that will intercept the requests from Excel or other clients and insert the required SharedKeyLite Authorization header. One of the best and most powerful local proxy servers is … Fiddler. Fiddler provides an “Extension” plugin architecture that gives us just the hooks required to implement our authentication logic as requests are being intercepted.

If your interested in how to build such a Fiddler extension, build the source [office src=”https://skydrive.live.com/embed?cid=E7F2E3C8DA56735E&resid=E7F2E3C8DA56735E%213782&authkey=AIUvBnb0Kja_gaI” width=”98″ height=”120″], otherwise just grab the assembly [office src=”https://skydrive.live.com/embed?cid=E7F2E3C8DA56735E&resid=E7F2E3C8DA56735E%213783&authkey=AKJODwvk1i-ebQE” width=”98″ height=”120″] and drop it into \Program Files (x86)\Fiddler2\Scripts. Next time you start Fiddler there will be a new Tools menu item “Azure Auth…” to set up your account and storage keys.

After which, the extension will watch for any requests to youraccount.table.core.windows.net and will intercept and add the required Authorization headers for the Azure Table Storage. To test it out, launch a browser (for IE you’ll want to turn off the default feed reading view as per this article) and browse to the URL http://youraccount.table.core.windows.net/Tables(). This will return all the tables available in your account.

Now try with Excel using one of your tables using Data->From Other Sources-> From oData Data Feed

And Choose your table

Finish the Wizard and then choose one of the Excel data presentations

Now you can chart your Azure Table Storage data with no code!

Note: updated now to include oData paging.