SSIS Junkie : data interoperability

Enterprise Mashups

jamie.thomson — Tue, 23 Jun 2009 22:16:00 GMT

Of late one may have noticed that I have become very interested in high-brow, generally vaporous, disciplines such as RESTful data services, data interoperability and cloud computing whilst coincident with that has been the inexorable rise of the term “mashup” in the information technology lexicon.

“Mashup” means different things to different people but to me its simply the practice of combining data from multiple places with the aim of discovering or passing on knowledge that wasn’t known before. Well hey, that sounds a lot like what I do in my day job; the main difference being that I don’t generally hear the term “mashup” being bandied about the London meeting rooms that I frequent to the same extent that it does in the funky web 2.0 and swanky startup world; the term I hear (and use) is the considerably less cool “data integration”. Fundamentally though I don’t think there’s that much difference between the two so maybe enterprise data integration people like myself have something to learn from these so-called mashup players.

One of my favourite mashup tools out there is one I’ve spoken about before – Yahoo Pipes. If you haven’t had a look at this it really is worth taking a glance. Yahoo Pipes enables you to extract data from multiple web-based data sources, transform it using a series of operations like sorting, joining, unioning and filtering before finally outputting that transformed data in one of a number of different formats; its a data pipeline for web-based data (A pipeline? Oh, there’s something else I’ve talked about before – noticing a pattern here?). Here is an example of a Yahoo Pipe: Yahoo Finance Stock Quote Watch List Feed w/Chart

Extracting. Transforming. Sorting. Unioning. Filtering. Outputting. This Yahoo Pipes thing is starting to sound awfully like ETL tools such as SQL Server Integration Services (SSIS) wouldn’t you say? They even look a little bit like each other with their boxes joined up with lines between them:

I’m now reminded of what my good friend Andy Britcliffe of Sharpcloud once said to me upon reading my blog post (and viewing the embedded video) Consuming web services in SSIS 2008 a full two years ago. I distinctly remember Andy’s words on that occasion: “SSIS is the ultimate mashup tool”! I didn’t disagree!

Most mashup tools share one common characteristic in that they invariably require someone with some technical nous to set them up in advance so that they can be used by the less tech-savvy amongst us and the same applies in enterprises as well; data is distributed by the IT guys to the information workers and this distribution of data typically takes months whereas the consumers of that data want it available in hours. In both arenas I sense a shift occurring; now the consumers of the data are being empowered to find and interrogate data for themselves and in the enterprise this is happening through the adoption of tools such as Qlikview, Omniscope and (in the near future) Microsoft’s Gemini. I find this to be a fascinating development not because it means there may be less work for me to do (admittedly that would be nice) but because information workers now have the opportunity to be much more productive in their daily jobs and I expect those who invest in learning these new technologies to be the cream that rises to the top of enterprises in the near future.

Up until recently I hadn’t been all that interested in Microsoft’s Gemini project, indeed I was very sceptical of it, but as I started to formulate some of the thoughts that I’m writing about here I began to realise how important it will be when it gets released sometime (hopefully) in early 2010. I earlier described mashups as being “the practice of combining data from multiple places with the aim of discovering or passing on knowledge that wasn’t known before” and that description fits very well with Gemini. If you don’t know what Gemini is take a look at this video:

That demo glosses over the main point I’m making which is that here we see data that is originally pulled from multiple sources and combined in a familiar place (Excel) where the end user can consume it. The person speaking in the video is Donald Farmer and he has a blog entry with many other links to Gemini resources at Microsoft Project Gemini links.

At the top of this email I also talked about how I’m interested in data services, that is data available over the web that we can consume via an API and use for our own knowledge discovery and I was introduced to such a data service just yesterday when listening to Jon Udell’s “Interviews with Innovators” podcast. In the most recent episode Jon interviewed Stephen Willmott whose company 3scale Networks has taken it upon themselves to make data held by the United Nations freely available via a data service to anyone that would like to consume it [UPDATE: Read Jon's own writeup of the interview at Influencing the production of public data]. For example, if you want to know the United Kingdom population’s annual growth rate since 1991, that data is available, for free, at http://undata-api.appspot.com/data/query/Population%20annual%20growth%20rate%20(percent)/United%20Kingdom?user_key=XXXX (you need to sign-up for a free user-key and substitute it for XXXX in order for this query to work) and is returned like so:

“Wouldn’t it be cool” I thought, “if I could consume that data inside of Excel using Gemini”, perhaps in this example to combine it with birth rates over the same period to discover if there is a correlation between the two. At the time though I didn’t know if Gemini made it possible to consume data directly from data sources so I went straight to ask the man who would know, the aforementioned Donald Farmer. I contacted Donald over Twitter and here is the conversation that ensued:

Me: @donalddotfarmer Is there a list of data sources types from which #Gemini can get data? Interested in data from web APIs e.g. undata-api.org (link)
Donald: @jamiet I'll need to check out that site in particular, but we do support Atom feeds. (link)
Me: @donalddotfarmer Ahh that's good news. How about POX/RSS? Does Gemini allow us to parse it or use XQuery? (link)
Donald: @jamiet No we don't support XQuery - we just consume Atom feeds as they come - the users can then filter and sort in Gemini (link)
Me: @donalddotfarmer OK, so Atom only right now. Looking forward to getting hands dirty, think I know what 1st feature request will be :) (link)

Lots of techy abbreviations in there so let me summarise. Gemini will be able to consume data from web services that deliver it in the popular Atom XML dialect (more on Wikipedia) which is great news and no great surprise given that Microsoft announced in February 2008 that Atom would be their XML syndication format of choice going forward (see my blog post Windows Live Dev announcements for a more complete commentary). I happen to know that the United Nations data provided by Stephen Willmott is not currently delivered in Atom format but no matter, at least things are moving in the right direction and as I alluded during my last tweet to Donald I’ll be asking for support for other syndication formats in the future.

This has turned into a rather rambling blog post so I’ll call a halt here. As always though I’d be interested to know other people’s thoughts on data services, usage of that data in enterprises or anything else I’ve mentioned herein so if you have any thoughts please leave comments in the space below!

-Jamie

Google Squared launches

jamie.thomson — Thu, 04 Jun 2009 14:22:09 GMT

Google have, today, released what looks like a very interesting product in Google Squared which is an extension to the search engine that tabulates your search results. Interesting because it presents results in a structured format that I assume one may, one day, be able to consume in any manner of ways (more on that in a minute).

Here’s an example, the results of a Google Squared search for “prime ministers”:

or how about “SSIS tasks”:

Google Squared has pulled back relevant information and put it into a table format which does seem very useful but in many ways it highlights a problem with search engines in this day and age; the information simply isn’t complete and therefore can’t be trusted. We don’t have a definitive list of all prime minsters here and neither do we have a definitive list of all tasks provided with SQL Server Integration Services (SSIS) either. Based on this would you trust the results coming out of Google Squared without additional research? I certainly wouldn’t.

On the subject of consuming this data, wouldn’t it be great if there were an easy mechanism of consuming structured data like this? Something none-proprietary, open and based on a standard? Well, if you had read and taken any notice of my recent blog post Whatever happened to Live Clipboard? you’d be aware that a mechanism that purports to solving this problem (i.e. Live Clipboard) has already been mooted but unfortunately has failed to gain any traction in the industry. That’s a real shame because Live Clipboard could provide a way to (for example) copy data from a Google Squared result into an Excel spreadsheet and vice versa. As I said in that previous blog post:

Here’s hoping [the lack of adoption of Live Clipboard] changes soon because it sounds like a very useful technology and to a fella like me whose primary interest is data integration anything that uses well-known standards as a method of doing that is worthy of attention.

All that being said Google Squared is a very interesting advancement in the increasingly intermingling worlds of data integration and search, even if it does think that George Clinton is an ex-US Vice President :)

Any thoughts? Let me know in the comments!

-Jamie

Whatever happened to Live Clipboard?

jamie.thomson — Wed, 27 May 2009 09:46:00 GMT

Anyone out there remember Live Clipboard? It was a very interesting incubation technology that came out of Microsoft’s Live Labs group way back in 2006 (I think) and how now been open sourced under the Creative Commons Attribution-ShareAlike License. It was also backed from way up on high – Ray Ozzie (now Chief Software Architect at Microsoft and also the guy who took over from Bill Gates) was its chief backer. Live Clipboard was in fact one of the few technologies that Ray blogged about on his, now defunct, MSN Spaces blog but sadly that blog post is no longer available.

So, what was Live Clipboard? From the site itself:

“Live Clipboard uses JavaScript and standard XML formats to easily move data from one web site to another, or between the web and standard applications. It extends the clipboard concept (familiar to most computer users) to the web.”

(http://www.liveclipboard.org/)

In other words, it is copy and paste for the web. You may think we already have that; after all, I can copy and paste some text from a text box on one page into a text box on another web page; but that isn’t really what this is about. Live Clipboard used XML markup to describe the data that was being copied thus if that XML was a well-known representation of the data (e.g. a microformat) then the receiving website could act upon that data accordingly. The canonical example is the one given at http://www.liveclipboard.org/:

“Let's say you have two sites both of which understand calendar data. I want to move an appointment from one site to another. With Live Clipboard, there is now an icon on each site, next to each piece of data that can be transported. Bring site A to the front, click on the icon and choose Copy, then bring site B to the front, click on the icon and choose Paste.“

Its not hard to envisage many other uses for such a technology, http://microformats.org has a number of fledgling microformat specifications that could all benefit from Live Clipboard:

Imagine finding someone’s contact details on their website and easily being able to transport those details into your address book with a couple of clicks – that’s the promise of Live Clipboard and microformats. Copy and paste is nearing ubiquity for smart devices (iPhone is expected to announce support in the next couple of weeks) and I doubt anyone reading this would contemplate using a PC that didn’t support it so I’m surprised that this similar concept for the web hasn’t taken off.

A number of large organisations have started to support Microformats most notably Google who recently announced that Googlebot would start seeking out Microformats and Microsoft themselves who have released Oomph, a microformats toolkit. Given that the use of microformats is now taking off I’m surprised that Live Clipboard hasn’t been heard of in such a long time. Here’s hoping that changes soon because it sounds like a very useful technology and to a fella like me whose primary interest is data integration anything that uses well-known standards as a method of doing that is worthy of attention.

Does anyone out there have any information to share about Live Clipboard?

-Jamie

Links:

Live Clipboard main site - http://www.liveclipboard.org/
Live Clipboard on Wikipedia - http://en.wikipedia.org/wiki/Live_Clipboard

UPDATE: Ray is well known for inviting people to contact him so I did that and asked him what had happened to Live Clipboard. Paraphrasing his reply: even though there was initial interest, once it was open sourced there was a lack of take-up within the wider web developer community.