THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Traversing the Facebook Graph using Data Explorer

As I mentioned yesterday Microsoft Data Explorer Preview for Excel has hit the streets and one new feature is a built-in adapter for talking to the Facebook Graph API:

image

I’ve been taking it for a spin for today and was quite interested in its capabilities. Even without landing any data into Excel this is still a handy interactive Facebook graph explorer as I’ll demonstrate herein.


A good place to start is with a list of statuses. I have chosen “me” as the “Username or object id” (because frankly I didn’t know what else to put in there) and “statuses” as the dataset to return:

image

After the standard merry authentication dance with Facebook Connect one’s history of Facebook statuses is returned which on its own might make interesting reading:

SNAGHTMLc6a91d4

however let’s do something a bit more interesting. I remove the columns I’m not interested in:

SNAGHTMLc6db2eb

to leave just my statuses, likes and comments:

SNAGHTMLc6e96f2

Notice that the likes and comments fields contain either a hyperlinked “Table” or null, “Table” means we have a set of likes or comments respectively – in other words Data Explorer is letting me browse through the one-to-many relationships inherent in the data and I happen to think that is very cool indeed. For example, selecting a link shows me the likes or comments respectively for the status in question (“Want to hear my Elton John joke? Its a little bit funny…”):

SNAGHTMLcb762f9

What would be more interesting would be if I could expand to show all comments against a status and also who made that comment. Turns out we can do just that, first click on this button called “Expand”:

image

which displays a list of columns that I want to show in the resultset; I choose “from” & “message”:

image

and now I can see all the comments against the relevant status. For all you out there whose heads are in SQL-land, I’ve effectively LEFT JOINed statuses to comments:

image

Last step, the comments.from field tells me who left the comment:

image

So let’s do a similar trick to show the name of the commenter in my resultset and voila:

SNAGHTMLc870b67

we have a list of my statuses along with all the comments and the name of the person that made the comment! We have effectively joined three linked datasets (statuses, comments, person), all inside Excel, and all without writing a scrap of code. Personally I think that’s very cool.


Now let’s go back to just our list of statuses:

SNAGHTMLc6e96f2

Let’s say we now want to count the number of likes and comments on each status and sort the data accordingly. I use the expand feature as before except this time I choose to aggregate rather than expand:

image

That gives me the number of likes and comments per status which I can then sort however I choose:

image

and with a couple of quick aesthetic column renames I have all my statuses with number of likes and comments in descending order of popularity (as measured by the count of likes):

SNAGHTMLca00a5e

I can then pull that resultset into Excel and chart it to see if there is any correlation between number of likes and number of comments:

image

Imagine aggregating over something more interesting than Facebook statuses and I think you can realise the potential here.


Hopefully this has served as a useful introduction to Data Explorer’s ability to query Facebook data. What interests me most about this is not the ability to query Facebook per se, its the ability to query and traverse over graph data using a tool that isn’t aimed at developers. Now imagine being being able to do the same with the growing corpus of linked data graphs that exist on the web in RDF format and you start to realise the potential here; I truly hope that the ability to query RDF data is high on the agenda of the Data Explorer team*.

If you want to have a go with Data Explorer yourself then go ahead and download it from http://www.microsoft.com/en-us/download/details.aspx?id=36803; its free, although you will need Excel 2010 or Excel 2013 installed.

@Jamiet 

* For what its worth I’ve had a watching brief on RDF, the Semantic Web and Linked Data since way back in November 2004 and I’ve been waiting since then for it to hit the big time. I don’t think that’s going to happen any time soon but having support from tools like Data Explorer would be a step in the right direction.

Published Thursday, February 28, 2013 11:16 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Eric Lawson said:

Thanks for this excellent run through of the data explorer Jamie. The potential of this when linked to feeds like Facebook (any sign of Twitter?) would appear limitless.

Perhaps this puts a different light on the recent debate on Excel 2013 and PowerPivot licensing.

It's downloaded and I am quite looking forward to trying it out in the morning.

February 28, 2013 5:41 PM
 

jamiet said:

Good stuff Eric, I'd definitely be interested in hearing/reading your thoughts once you've had a go with it.

March 1, 2013 4:20 AM
 

Kerry white said:

Great post thank you. Can't wait to give it a try

March 2, 2013 4:40 AM
 

Devin Knight said:

Got to play with this a bit today.  Looks like the section that defaults to "me" for username or object ID could be a corporate page.  For example, I'm and administrator of our corporate page so I type in the pragmaticworks and i can now explore the company sentiment.  Writing a post on it now. :)

March 5, 2013 11:12 AM
 

SSIS Junkie said:

In 2007 Microsoft announced a program of work under the codename Oslo which was intended to be a modelling

March 12, 2013 5:07 PM
 

Jannie said:

Please show me how to get replies to those comments made on the statuses aka messages. I want everything said on my page. Messages, Comments, and Replies. Thank you!

October 9, 2013 4:54 AM
 

Excel Data Explorer – yksi syy v??hemm??n sulkea Excel (nyk. Power Query) | Onsight Helsinki said:

August 8, 2014 8:16 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement