I have been fiddling about with Data Explorer some more and have built an interesting little mashup that enables one to discover the number of Google Reader subscribers to each RSS feed on SQLBlog.com. Before I show you how its built I'll whet your appetite with a screenshot of the output:
For brevity I have deliberately hidden some of the results however you can see the full dataset for yourself (and see if any of the numbers have changed since I took this screenshot) by visiting https://ws41451459.dataexplorer.sqlazurelabs.com/Published/SQLBlog%20subscriber%20counts%20on%20Google%20Reader
and opening the data in the format of your choosing (i.e. Excel, OData or CSV). All the data is publicly available as is the mashup so you won't have any difficulty in accessing it. Opening the data yourself will also illustrate how long it takes Data Explorer to execute the mashup which, when you consider that the mashup queries the Google Reader API for data on each RSS feed in turn, could be quite interesting in itself.
So, how is it done? Its very simple once you know how, my mashup has four resources:
Which we can take a look at in turn.
This is simply a function that will remove commas from a string, this is important because the API that we are using returns numbers as text and any numbers greater than 999 get commas inserted. It is defined simply as:
(value) => Text.Replace(value,",","")
Resource: List Of Feeds
Simply a list of RSS feeds for which we are going to get the subscriber count. The first task, "Typed list of feeds" , is our typed-in list of RSS feeds:
The second task, "Convert to table", does exactly what it says on the tin:
N.B. I have, by the way, complained loudly about the inability to resize columns in the Data Explorer UI.
This is where the real work occurs. We are using an API provided by Carter Cole at http://cartercole.com/dev/api/greaderapi.asp that wraps the Google Reader API thus making it easy to query for number of subscribers.
(feed) => StripOutCommas(Json.Document(Web.Contents("http://cartercole.com/dev/api/greaderapi.asp", [Query = [feed = feed]]))[subscribers])
Here I have essentially defined a function that (1) calls Carter's API, (2) passes it a parameter called feed, (3) converts the result into a JSON document, (4) extracts the "subscribers" value and (5) passes the result to StripOutCommas().
Resource: Number Of Subscribers Per Feed
This resource is what pulls everything together. Firstly the "Call func on each RSS feed" task calls our "GetNumberOfSubscribersForFeed" resource on each RSS feed in the "List Of Feeds" resource:
Table.AddColumn(#"List Of Feeds", "NumberOfSubscribers", each GetNumberOfSubscribersForFeed([feed]))
The "Rename column", "Convert Text to Integer" & "Sort Subscribers DESC" tasks are, hopefully, self-explanatory.
And that's it! As already stated the mashup has been published at https://ws41451459.dataexplorer.sqlazurelabs.com/Published/SQLBlog%20subscriber%20counts%20on%20Google%20Reader from where you can:
- Download the mashup output as a CSV file
- Download the mashup output as an Excel document
- Download the mashup itself so you can play with it at your leisure
- View the output in an OData feed
If you want to use the mashup to get similar counts for your own set of RSS feeds simply change the "List of Feeds" resource appropriately. Happy data exploring!