Search results matching tag 'Open Data'

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

Exploring earnings data for the UK [Open Data]

jamiet — Mon, 11 Feb 2013 13:34:02 GMT

I have a burgeoning interest in the world of Open Data which wikipedia describes as:

Open data is the idea that certain data should be freely available to everyone to use and republish as they wish, without restrictions from copyright, patents or other mechanisms of control. The goals of the open data movement are similar to those of other "Open" movements such as open source, open hardware, open content, and open access. The philosophy behind open data has been long established, but the term "open data" itself is recent, gaining popularity with the rise of the Internet and World Wide Web and, especially, with the launch of open-data government initiatives such as Data.gov.
http://en.wikipedia.org/wiki/Open_data

To that end I follow @LondonDatastore on Twitter as they are actively publishing Open Data pertaining to the city in which I live, London. Four days ago they announced that a new dataset had been released that provided earnings information, not just for London but for the whole country and going back many years too. The provided link, http://data.london.gov.uk/datastore/package/earnings-workplace-borough, brings up a page from where one can download an Excel workbook containing some data. Unfortunately the data in that workbook is not, in my opinion, provided in such a manner that makes it easily explorable (which I have complained about before on this blog); the data is spread over multiple worksheets:

Worse, the data is already aggregated and pivoted:

In other words the workbook does not contain the raw row-level data from which this pivoted data is produced. Thankfully a link (https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=99) is provided from where the raw data can be downloaded. I found the interface there to be slightly clunky but that’s a minor quibble – that raw data is available should be considered a major boon. I downloaded earnings data for:

Local Authority
Gender
Full Time or Part Time
Year (1999-2012)

and have made that raw data available in a publicly accessible Excel workbook. You can view that workbook online (only a browser required) at AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx (from there you can also download for your own analysis).

The workbook also provides summaries over the raw data by way of pivot tables and charts. Arguably its clear to see, for example, that a gender imbalance exists although perhaps the gap may be lessening somewhat

Its interesting to note that the average salary for Males dropped off in 2009/2010. Perhaps the economic events of 2008 are the cause for that, checking out data for only City of London (the traditional financial hub of the UK) suggests that may well be the case:

There will be many more nuggets of information available in this data, all we need to do is set the data free so that people can find it for themselves. That is the aim of this blog post so hit the link: AnnualPay_by_YearGenderLocalAuthorityPartorfulltime.xlsx and see what other nuggets you can find! If dataviz is an area that interests you then this is a cracking dataset to explore!

One other point I want to make is that the raw data is provided as a mean average which means that each aggregated figure is a mean average of some mean averages. This isn’t good as it distorts the data as I demonstrate with this simple example:

We have an average salary for both male and female (5000 & 9000 respectively) and the average of those two averages is 7000. However if we take the total “total salary” / total “tally of people asked” (127000 / 23) then the average is quite different – 5521.73.

That’s a large discrepancy even for only two rows of data and it highlights the problem of providing averages rather than the figures that created those averages. To me this is a data quality issue – the raw source data does not provide the requisite level of detail to enable accurate analysis. Quality of data is paramount.

@Jamiet