SSIS Junkie : Data Explorer

M lives!

jamiet — Tue, 12 Mar 2013 21:07:01 GMT

In 2007 Microsoft announced a program of work under the codename Oslo which was intended to be a modelling technology and if you believed the hype this was going to be a very big initiative within Microsoft. By September 2010 Oslo was effectively dead (read blog post Update on SQL Server Modeling CTP (Repository/Modeling Services, "Quadrant" and "M") and if my memory serves me correctly that was quite a lot of anger from customers that had invested time and effort into Oslo. Specifically the three parts of Oslo that were killed were:

The Oslo Repository
Quadrant – Effectively a query tool for SQL Server that featured an infinite canvas (which I actually really liked)
M – a modelling language

M in particular had a lot of hype behind it and many people were mystified when it was seemingly canned. In the aforementioned blog post the lead architect for Oslo, Don Box, had this to say:

we created a language codenamed “M” for defining schema, constraints, queries, and transformations. While we used “M” to build the “Oslo” repository and “Quadrant,” there has been significant interest both inside and outside of Microsoft in using “M” for other applications. We are continuing our investment in this technology and will share our plans for productization once they are concrete.

So there was a promise to keep M alive but since then nothing has been heard of it and I suspect many people (including myself) assumed it had gone the way of the dodo. As it turns out that isn’t the case, M does indeed live on as the query language that underpins the recently released Microsoft Data Explorer for Excel Preview (see my blog post Data Explorer hits full preview for more info). An updated version of Data Explorer was released today (read: A new build of “Data Explorer”, and an Auto Update feature) and one of the new features is something they call Advanced Query Editing, that is the ability to see the underlying query that gets built by the UI. Here’s an example I lifted from my previous post Traversing the Facebook Graph using Data Explorer:

That query language…is M. Here it is in raw copyable form:

let
    Source = Facebook.Graph("https://graph.facebook.com/me/statuses"),
    #"Expand likes" = Table.ExpandTableColumn(Source, "likes", {"name"}, {"likes.name"}),
    #"Expand comments" = Table.ExpandTableColumn(#"Expand likes", "comments", {"message"}, {"comments.message"}),
    HiddenOtherColumns = Table.SelectColumns(#"Expand comments",{"message", "likes.name", "comments.message"})
in
    HiddenOtherColumns

At this stage its looking very likely that Data Explorer will come to market which means that Don Box wasn’t lying and one of the most lamented parts of Oslo will do too. Data Explorer is effectively a free query builder for M queries – go and download and have a play.

@Jamiet

Weekend reading – Data Explorer, Quandl, Flatmerge and a SQL Saturday app

jamiet — Sat, 09 Mar 2013 18:30:04 GMT

Here are some things that have piqued my interest on the interwebs over the last few days.

Parameterized queries and Security in Data Explorer

Chris Webb put a post up on the Data Explorer forum asking about parameterizing queries in Data Explorer and Miguel Llopis from the Data Explorer product team replied with some useful information:

…there is some risk for users to leak information to external sources when doing this, and so we try to prevent this from being done "by default". You can disable this level of protection by clicking the "Fast Combine" button in the Data Explorer ribbon tab. More information about Fast Combine and Privacy Levels can be found in our Help contents: http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

Following Miguel’s link shows this information:

So, default behaviour in Data Explorer is that the user is protected from inadvertently leaking information to 3rd parties. Its good to know that security has been prevalent thinking within the Data Explorer team however users do need to be aware that this behaviour exists, hence my mentioning it here.

Quandl – a search engine for datasets

I have stumbled across a site called Quandl that looks interesting, it bills itself as “Intelligent search for numerical data”. Essentially this is a search engine for finding datasets on the web which should be a useful resource in the emerging world of self service BI.

I’m writing this on a train so as an example I used Quandl to search for data on UK train journeys and first result was Train movements : 1000 Train-kilometre : Passenger trains : United Kingdom:

Here we have some raw data pertaining to train movements in the UK from 2004 to 2011. Quandl provides a chart of the data, a link to the source and an indication of the age of the data. It also enables us to download the data and provides Excel, CSV, JSON & XML as choices of data format.

An interesting idea indeed, Quandl is in its infancy though I shall be keeping a watching brief to see if it turns out to be a success or not.

Publish your own datasets with Flatmerge

The aforementioned Chris Webb put me onto this one. Flatmerge is a startup from Michigan, US that enables one to publish their data for public consumption:

With the FlatMerge data storage platform it's easy to share data in the cloud and use it in other applications. Just upload data and let FlatMerge discover it's actual data types and make the data and metadata available in JSON or XML format through (OData) URL queries.

Flatmerge are using OData-compliant URI query formats and they tell me that OData output is coming soon:

We currently support some OData queries. Data/Meta is returned in plain JSON or XML. OData output is coming soon!

That Flatmerge chose to use OData to publish their data is interesting – I’ve long suspected that greater OData adoption wouldn’t be far away once Excel natively supported it as an external data source and Flatmerge have realised the value in doing this. Flatmerge enables one to publish data to the web, Quandl helps people find data on the web – perhaps these two should go out for coffee sometime

SQL Saturday app for Windows Phone

Michael Wells has built a Windows Phone app for SQL Saturday (particularly pertinent for me at the moment as I am on my way home form SQL Saturday 194).

It provides data about each event, including the all important schedule information.

I have slightly mixed feelings about this. On the one hand its fantastic to see a community member voluntarily build a great FREE resource for the SQL community – massive credit to Michael for doing this. On the other hand it highlights one of my pet peeves about the current app culture that is prevalent on smartphones – this is an app that you can only use if you have a certain type of phone. The information presented here is valuable and given away for free, why is it hidden behind a gated app store? Should there not be a SQL Saturday website that is optimised for and viewable on any mobile web browser? Better still, its the schedule data here that is most valuable so why not publish that data in a format that allows one to view that schedule in one’s phone/PC/tablet calendar regardless of the type of device they are using? That data format, by the way, is iCalendar which is something that regular readers are probably fed up of me banging on about.

I hope that doesn’t detract from Michael’s great efforts here; his app is fulfilling an important need, I just happen to think its a shame that that need even exists when there are mechanisms already in place for delivering this data to us in a more efficient matter. On the other hand its hard to argue with the ease at which apps deliver information to us so perhaps I should just quietly climb down off of my soapbox! Comments are welcome!

@Jamiet

Traversing the Facebook Graph using Data Explorer

jamiet — Thu, 28 Feb 2013 22:16:44 GMT

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:

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:

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:

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

to leave just my statuses, likes and comments:

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…”):

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”:

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

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:

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

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

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:

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:

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

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):

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:

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.

Data Explorer hits full preview

jamiet — Wed, 27 Feb 2013 15:49:00 GMT

A short recap

At the PASS Summit 2011 a project that existed as part of the now-defunct SQL Azure Labs was announced – Data Explorer. I posted some initial thoughts at Thoughts on Data Explorer and continued to post some walkthroughs and feedback. Data Explorer was an interesting technology to me which in October 2011 I described as:

Data Explorer is an ETL tool and given my obvious SSIS affiliations that makes it very interesting to me. That it runs as a cloud service and will be available to non-developers only makes it more intriguing and I can't wait until Data Explorer becomes available for us to tinker with later this year.

That being said Data Explorer always felt uncomfortably as if it was an interesting piece of tech trying to find both a problem to solve and a place to live.

Now in preview

Today the second of those conundrums has been answered by the release of a new public preview; Data Explorer is going to live in Excel and its official title is now “Microsoft Data Explorer Preview for Excel” (I still dislike the name by the way):

Here’s a video that describes Data Explorer’s pitch today:

Microsoft Data Explorer Preview for Excel

If the video doesn’t render properly check it out on YouTube instead.

From the video:

Data Explorer enhances the self-service experience in Excel by simplifying data discovery and access to a broad range of public and enterprise data sources enabling richer insights from data that has traditionally been difficult for users to get at.
(emphasis is mine)

In short, Data Explorer is targeted at Excel users (Microsoft calls them “Information Workers”) and now looks like this:

The data in this screenshot was imported from http://en.wikipedia.org/wiki/Super_league.

which (I think) is neat and tidy and pretty intuitive whereas a year ago it looked like this:

and didn’t seemingly know what it wanted to be other than yet another tool for developers. It didn’t have an identity and it didn’t have a raison d’etre. Today’s preview release goes some way to solving those things.

Online Search

One very interesting new feature is Online Search which enables you to search the web for datasets. In this example I use the feature to search for data on Apollo space missions:

I find a list of all the missions and with one click its in Excel:

where I can do all the normal stuff that one would normally do in Excel. Here I have used Excel 2013’s Flash Fill feature to extract the year and pivot the data to discover which rocket was most popular and in which year the most missions were launched:

You get the idea. This is powerful stuff! The workbook that I built for this simple demo is available for viewing online or download at 20130227 Apollo Missions - Simple Data Explorer Demo.xlsx.

Today’s release is also the first from a product team that has a number of other interesting projects in the pipeline that you will see more of in 2013 – watch this space.

Data Explorer. Is it A2P?

One last point I’ll make is that Data Explorer as it exists today ably fulfils the “Acquire” and “Processing” aspects of Mike Reich’s A2P approach which I talked about only twelve days ago at ETL is dead, long live AP2 ?. Interestingly Data Explorer in its October 2011 guise also provided the last part of A2P – “Publishing” (I demo’d the Publishing ability at Data Explorer walkthrough – Parsing a Twitter list) however that ability has been removed in this release in favour of landing the data into Excel. I remain hopeful that the ability to publish a Data Explorer feed returns in the future.

Get the bits

Download “Microsoft Data Explorer Preview for Excel” from http://www.microsoft.com/en-us/download/details.aspx?id=36803

I’m not the only one blogging about this. Check out:

Public Preview of Data Explorer by Chris Webb
Announcing Microsoft “Data Explorer” Preview for Excel by the Data Explorer product team

@Jamiet

Querying RSS feed subscriber count on Google Reader using Data Explorer‏

jamiet — Wed, 21 Dec 2011 20:29:00 GMT

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.

Resource: StripOutCommas

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.

Resource: GetNumberOfSubscribersForFeed

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.

Consume

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!

@Jamiet

Data Explorer Feedback part 1

jamiet — Wed, 07 Dec 2011 20:56:56 GMT

Earlier today I posted Data Explorer walkthrough – Parsing a Twitter list in which I explained that I have been using Data Explorer for a few weeks now. In that time I have been compiling some feedback for the Data Explorer product team which I was going to email privately but I see no harm in putting these thoughts into the public domain, hence why I am writing this blog post – a hodge podge of thoughts/suggestions/gripes from the past few weeks of using Data Explorer.

I’m very impressed that if I change the name of a resources any references to that resource are updated accordingly. After years of working with the SSIS dev tools I’m automatically wired to expect that not to work.
I love the composability of tasks and resources (e.g. Define a custom function as a resource and use it in another resource). That idea of composability and reuse is something that is sadly lacking in SSIS (and the SSIS team know that because I’ve moaned about it often enough ).
If the name of the resource is too large for the box then there is no way to see the full name without editing it:

It would be nice to be able to reorder the list of resources
We *really* need the ability to widen the columns when viewing the results of a task.
Data Explorer seems a bit lacking in its abilities to parse HTML & XML right now. For example, I'm surprised that I can't use XPath to extract the contents of an HTML/XML document. In fact, the lack of such features is the most disappointing aspect of Data Explorer thus far and means that the product is still a long way off being a Kapow competitor, remembering that this is still an early beta of course.
We need better ways of visualising the output from a mashup (i.e. something that doesn’t require the user to have Excel installed). How about giving SSRS the ability to consume an OData feed – we could then host such a report on SQL Azure Reporting, perhaps even sell that report via the Azure Marketplace.
If I make a mashup publicly available I would like to be able to know how many times it is called. I believe that the ability to INSERT data (e.g. into SQL Azure) using Data Explorer will be coming soon (its kind of already there with the Snapshot feature) so I'm wondering if there is a way to "trigger" an insertion when someone consumes one of my exposed resources? I suspect the answer is "not right now" so consider that a feature request. I guess you could crystalise that request as "Provide an eventing model within a mashup so that resources can be triggered when some event (e.g. Someone consumes a resource) occurs."
We need a scheduler that enables us to run Snapshots at some pre-defined time.
I have a mashup published at https://ws41451459.dataexplorer.sqlazurelabs.com/Published/TwitterListDemo. The link for the feed is https://ws41451459.dataexplorer.sqlazurelabs.com/Published/TwitterListDemo/Feed but that doesn’t actually show me any data, it shows me a list of resources that produce the data however in this case there is only one resource (https://ws41451459.dataexplorer.sqlazurelabs.com/Published/TwitterListDemo/Feed/jamiet-dataexplorer-text_user_screen_name). Would it not make sense to link directly to the feed with data in it if there is only actually one external resource in my mashup? It would save a mouse-click at least.
I haven’t yet figured out if its possible to do recursion. Still working on that one.
In my Twitter List example the values “jamiet” & “data-explorer” for the list owner and list slug respectively were hardcoded in the mashup; yes, they were parameters to a function but they were still hardcoded. It would have been much better if I were able to give the consumer of the mashup the ability to define/override those values when they consume it. In other words, a mashup needs to be parameterizable.
Overall I am very impressed and the excitement that I displayed in my initial post Thoughts on Data Explorer is justified. Data Explorer coalesces nicely with existing interests of mine such as SQL Server, ETL & web-enabled data and I have high hopes that I will be using this extensively for clients in the years to come.

Data Explorer walkthrough – Parsing a Twitter list

jamiet — Wed, 07 Dec 2011 13:40:21 GMT

Yesterday the public availability of Data Explorer, a new data mashup tool from the SQL Server team, was announced at Announcing the Labs release of Microsoft Codename “Data Explorer”. Upon seeing the first public demos of Data Explorer at SQL Pass 2011 I published a blog post Thoughts on Data Explorer which must have caught someone’s attention because soon after I was lucky enough to be invited onto an early preview of Data Explorer, hence I have spent the past few weeks familiarising myself with it. In this blog post I am going to demonstrate how one can use Data Explorer to consume and parse a Twitter list.

I have set up a list specifically for this demo and suitably it is a list of tweeters that tweet about Data Explorer – you can view the list at http://twitter.com/#!/list/jamiet/data-explorer. Note that some of the screenshots in this blog post were taken prior to the public release and many of them have been altered slightly since then; with that in mind, here we go.

First, browse to https://dataexplorer.sqlazurelabs.com/ and log in

When logged in select New to create a new mashup

Give your mashup a suitable name

You will be shown some options for consuming a source of data. Click on Formula

We’re going to be good web citizens and use JSON rather than XML to return data from our list. The URI for our Twitter API call is https://api.twitter.com/1/lists/statuses.json?slug=data-explorer&owner_screen_name=jamiet, note how I have specified the list owner (me) and the name of the list (what they call the slug) “data-explorer” as query parameters. If you go to that URL in your browser then you will be prompted to save a file containing the returned JSON document which, if all you want to do is see the document, isn’t very useful. In debugging my mashups I have found a service called JSON Formatter to be invaluable because it allows us to see the contents of a JSON document by supplying the URI of that document as a parameter like so: http://jsonformatter.curiousconcept.com/#https://api.twitter.com/1/lists/statuses.json?slug=data-explorer&owner_screen_name=jamiet. It might be useful to keep that site open in a separate window as you attempt to build the mashup below.

I’ve digressed a little, let’s get back to our mashup. We’re going to use a function called Web.Contents() to consume the contents of the Twitter API call and pass the results into another function, Json.Document(), which parses the JSON document for us. The full formula is:

= Json.Document(Web.Contents(“https://api.twitter.com/1/lists/statuses.json?slug=data-explorer&owner_screen_name=jamiet”))

When you type in that formula and simply hit enter you’re probably going to be faced with this screen:

Its asking you how you want to authenticate with the Twitter API. Calls to the https://api.twitter.com/1/lists/statuses.json resource don’t require authentication so anonymous access is fine, just hit continue. When you do you will see something like this:

The icon

essentially indicates a dataset, so each record of these results is in itself another dataset. We’ll come onto how we further parse all of this later on but before we do we should clean up our existing formula so that we’re not hardcoding the values “data-explorer” and “jamiet”.

The Web.Contents() function possesses the ability to specify named parameters rather than including them in the full URL. Change the formula to:

= Json.Document(Web.Contents("https://api.twitter.com/1/lists/statuses.json", [Query = [slug="data-explorer", owner_screen_name="jamiet"] ])) :

That will return the same result as before but now we’ve broken out the query parameters {slug, owner_screen_name} into parameters of Web.Contents(). That’s kinda nice but they’re still hardcoded; instead what we want to do is turn the whole formula into a callable function, we do that by specifying a function signature and including the parameters of the signature in the formula like so:

= (slug,owner_screen_name) => Json.Document(Web.Contents("https://api.twitter.com/1/lists/statuses.json", [Query = [slug=slug, owner_screen_name=owner_screen_name] ]))

Let’s give our new function a more meaningful name by right-clicking on the resource name which is currently set as “Custom1” and renaming it as “GetTwitterList”:

We have now defined a new function within our mashup called GetTwitterList(slug, owner_screen_name) that we can call as if it were a built-in function.

Let’s create a new resource as a formula that uses our new custom function and pass it some parameter values:

= GetTwitterList("data-explorer", "jamiet")

We still have the same results but now via a nice neat function that abstracts away the complexity of Json.Document( Web.Contents() ).

As stated earlier each of the records is in itself a dataset each of which, in this case, represents lots of information about a single tweet. We can go a long way to parsing out the information using a function called IntoTable() that takes a dataset and converts it into a table of values:

Here is the result of applying IntoTable() to the results of GetTwitterlist():

This is much more useful, we can now see lots of information about each tweet however notice that information about the user who wrote the tweet is wrapped up in yet another nested dataset called “user”.

All the time note how whatever data we are seeing and whatever we do to that data via the graphical UIs is always reflected in the formula bar; in the screenshot immediately above notice that we are selecting the “user” and “text” columns (the checkbox for “user” is off the screen but is checked).

We can now parse out the user’s screen_name using a different function – AddColumn(). AddColumn() taken an input and allows us to define a new column (in this case called “user-screen_name”) and specify an expression for that column based on the input. A picture speaks a thousand words so:

= Table.AddColumn(intoTable, "user_screen_name", each [user][screen_name])

There we have our new column, user_screen_name, containing the name of the tweeter that tweeted the tweet. At this point let’s take a look at the raw JSON to see where this got parsed out from:

Notice that the screen_name, UserEd_, is embedded 3 levels deep within the hierarchical JSON document.

We’re almost there now. The final step is to use the function SelectColumns() to select the subset of columns that we are interested in::

= Table.SelectColumns(InsertedCustom,{"text", "user_screen_name"})

Which gives us our final result:

At this point hit the Save button:

OK, so we have a mashup that pulls some data out of twitter, parses it and then….well…nothing! It doesn’t actually do anything with that data. We have to publish the mashup so that it can be consumed and we do that by heading back to the home page (which is referred to as “My Workspace”) by clicking the My Workspace button near the top of the page:

Back in My Workspace you can select your newly created mashup (by clicking on it) and options Preview, Snapshot & Publish appear:

We’ll ignore Preview and Snapshot for now, hit the Publish button instead at which point we are prompted for a name that we will publish the mashup as:

Hitting Publish will do the necessary and make our data feed available at a public URI:

Head to that URL (https://ws41451459.dataexplorer.sqlazurelabs.com/Published/TwitterListDemo) and here’s what you see:

You can download the mashup output as a CSV file or an Excel workbook. You can also download the whole mashup so you can edit it as you see fit and, most importantly, you can access the output of the mashup via an OData feed at https://ws41451459.dataexplorer.sqlazurelabs.com/Published/TwitterListDemo/Feed/jamiet-dataexplorer-text_user_screen_name

We have used Data Explorer’s JSON parsing and dataset navigation abilities to pull out the data that we are interested in and present it in a neat rectangular data structure that we are familiar with. Moreover we have done it without installing any software and we have made that data accessible via an open protocol; that’s pretty powerful and, in my wholly worthless opinion, very cool indeed.

Have fun playing with Data Explorer. Feel free to download my Twitter List Demo mashup and mess about with it to your heart’s content.

@jamiet

Thoughts on Data Explorer

jamiet — Mon, 24 Oct 2011 09:55:00 GMT

To my mind the most interesting piece of news to come out of the recent PASS conference was the unveiling of a new SQL Azure Labs project coming from the SQL Server organisation that has the codename "Data Explorer" (not a very imaginitive codename I'm sure you'll agree) and for which there is information available at http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx (in case you've surfed on here a few months on from when I originally wrote this blog post you should expect that that that URI will have become a dead link).

My good buddy Chris Webb (blog | twitter) has already blogged about Data Explorer at Pass Summit 2011 - Day 1 Keynote and Self-Service ETL with Data Explorer in which he made a very telling observation:

It allows you to mash up data from various different sources then publish the result as an OData feed – very similar to Yahoo Pipes

I couldn't agree more with that assertion. I blogged about Yahoo Pipes over four years ago at Taking Yahoo Pipes for a test drive and I referred to it then as "ETL for RSS feeds"; it interested me greatly because here was a tool that enabled non-developers to pull data from multiple sources and make it available as a single data source that could be easily consumed; moreover it ran as a cloud service which has also long been an interest of mine. Granted, it only did this for RSS feeds but the premise was still really interesting to me; I believe that making data easily consumable is far more important than the tool chosen to consume it hence why I'm such a massive advocate of iCalendar for BI and why you'll rarely find me talking about the likes of Business Objects, Cognos, Qlikview, Tableau and Power View on this blog (no disrespect intended to those tools or the people that use them - they're just not what floats my boat).

Where Yahoo Pipes consumes RSS feeds and provides RSS feeds, Data Explorer consumes from loads of different places and provides OData feeds (something I've been banging on about for a while now) and if you're in the Microsoft ecosystem OData is increasingly looking like the lingua franca for platform and device independent data integration. Moreover, according to recent blog post Creating a custom RSS reader in Montego (cloud) by project lead Tim Mallalieu Data Explorer will also be able to pull data directly out of web pages and that is stepping firmly into the territory of Kapow which, again, is a tool that Chris and I have blogged about before at Kapow – ETL for HTML and Kapow Technologies. Chris referred to Kapow as:

a cross between a screenscraper and an ETL tool

and again I wouldn't disagree. Data Explorer looks like filling the missing link that I was alluding to in the final paragraphs of my June 2009 blog post Enterprise Mashups.

Are you spotting a common theme here? Data Explorer is an ETL tool and given my obvious SSIS affiliations that makes it very interesting to me. That it runs as a cloud service and will be available to non-developers only makes it more intriguing and I can't wait until Data Explorer becomes available for us to tinker with later this year. No doubt Chris will be keeping a watching brief too.

@Jamiet

UPDATE:Some further thoughts...

It would be interesting to see what else could be done with this data once its exposed as a feed. I'll wager that in the not too distant future you'll be able to (for example) sell the output from your Data Explorer mashup on Azure Datamarket or view geocoded feeds on Bing Maps (note that Geospatial support is coming to OData in the very near future). There are lots of possibilities I'm sure and I'm looking forward to seeing what ideas others have for using and sharing this data.

I'm also wondering whether there will be an option to host Data Explorer (and hence Data Explorer mashups) inside the enterprise. Today most enterprise data is contained within the corporate firewall thus will not be accessible from a Data Explorer service provided via SQL Azure; it would be a shame if such data could not be accessed by Data Explorer and hence why I hope there will be an on-premise version available. I can think of many scenarios at my past clients where the ability to easily make data consumable over HTTP and behind the firewall would have been invaluable.

SSIS Junkie : Data Explorer

M lives!

Weekend reading – Data Explorer, Quandl, Flatmerge and a SQL Saturday app

Parameterized queries and Security in Data Explorer

Quandl – a search engine for datasets

Publish your own datasets with Flatmerge

SQL Saturday app for Windows Phone

Traversing the Facebook Graph using Data Explorer

Data Explorer hits full preview

A short recap

Now in preview

Online Search

Data Explorer. Is it A2P?

Get the bits

Read more

Querying RSS feed subscriber count on Google Reader using Data Explorer‏

Resource: StripOutCommas

(value) => Text.Replace(value,",","")

Resource: List Of Feeds

Resource: GetNumberOfSubscribersForFeed

(feed) => StripOutCommas(Json.Document(Web.Contents("http://cartercole.com/dev/api/greaderapi.asp", [Query = [feed = feed]]))[subscribers])

Resource: Number Of Subscribers Per Feed

Consume

Data Explorer Feedback part 1

Data Explorer walkthrough – Parsing a Twitter list

Thoughts on Data Explorer