Search results matching tag 'OData'

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

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.

ETL Demo With Data from Data.Gov

KKline — Fri, 05 Aug 2011 21:45:00 GMT

A little over a month ago, I wrote an article (Is There Such a Thing as Easy ETL) about expressor software and their desktop ETL application, expressor Studio. I wrote about how it seemed much easier than the native ETL tools in SQL Server when I was reading up on the tool, but that the "proof would be in the pudding" so to speak when I actually tried it out loading some free (and incredibly useful) data from the US federal data clearinghouse, Data.Gov.

If you'd rather not read my entire previous article - quick recap, expressor Studio uses “semantic types” to manage and abstract mappings between sources and targets. In essence, these types are used for describing data in terms that humans can understand—instead of describing data in terms that computers can understand. The idea of semantic abstraction is quite intriguing and it gave me an excuse to use data from data.gov to build a quick demo. You can download the complete data set I used from the following location: International Statistics. (Note: I have this dream that I'm going to someday download all of this free statistical data sets, build a bunch of amazing and high-value analytics, and make a mint. If, instead, YOU do all of those things, then please pay to send at least one of my seven kids to college in repayment for the inspiration. I'm not kidding. I have SEVEN kids. God help me).

The federal government, to their credit, has made great progress in making data available. However, there is a big difference between accessing data and understanding data. When I first looked at one of the data files I downloaded, I figured it was going to take me years to decrypt the field names. Luckily, I did notice an Excel file with field names and descriptions. Seriously, there are single letter field names in these files where the field name “G” has a description of “Age group indicator” (Oh Wow). See the figure below.

It's stuff like this that reminds me why we have data quality and master data management tools. Ok, back to expressor Studio. I quickly mapped a couple of files into expressor Studio using their “Read File” operator. It was fairly simple and easy to use. My data included files with country area information, population, and gender information by year. Once I mapped these files I quickly wanted to shed the default cryptic, nay, nonsensical names. I could have just renamed the fields when I initially mapped them into the system but that would mean I would have to manage the names in three separate locations. Bah! It made more sense to create a common semantic type and reuse it across all three files.

There are two flavors of semantic types within expressor Studio to handle your mappings, atomic types or composite types. An atomic type is simply a single field name whereas a composite type is a combination of one more atomic types. Since the data files had many common fields, I decided to create a core set of atomic types that I could then roll up into composite types based on the files I was mapping. This kept the mappings simple and easy to understand and most importantly the whole exercise took about 5 minutes. Once the types were created I simply mapped the cryptic names from the files to the business friendly names in my semantic type. (I can't even begin to imagine how long this would've taken using native tools, but certainly not 5 minutes).

Now I was ready to move my data. I took the data from three files and combined them into one master dataset. From there, my international statistics from Data.Gov were pumped right into my waiting SQL Server database. Note that I could've used Excel or just about any other database as my target instead of SQL Server.

Now, you might be saying to yourself "That looks easy because you read all the help files first." Actually, no. In fact, some of my buddies like to lovingly tell me to "RTFM" from time to time. It's not that it offends my masculinity to read a manual. I just usually like to have a go first and then, if needed, go back to the manual. In fact, all I really used was this 5-minute demo video that in noticed when I was downloading the tool.

If you're tackling ETL and you want it fast and easy, then you might want to check out their website, www.expressor-software.com, to learn more about the expressor company and products.

Enjoy!

-Kev

P.S. Follow me on Twitter!

PDC schedule published as OData, but where's the iCalendar feed?

jamiet — Tue, 26 Oct 2010 13:51:00 GMT

Chris Sells announced on twitter earlier today that the schedule for the upcoming Professional Developers' Conference (PDC) has been published as an OData feed at: http://odata.microsoftpdc.com/ODataSchedule.svc

Whoop-de-doo! Now we can, get this, view the PDC schedule as raw XML rather than on a web page or in Outlook or on our phone, how cool is THAT? (conveying sarcasm in the written word is never easy but hopefully I've managed it here!)

Seriously, I admire Microsoft's commitment to OData, both in their Creative Commons licensing of it and support of it in a myriad of products but advocating its use for things that it patently should not be used for is verging on irresponsible and using OData to publish schedule information is a classic example.

A standard format for publishing schedule information over the web already exists, its called iCalendar (RFC5545). The beauty of iCalendar is that it is supported today in many tools (e.g. Outlook, Google Calendar, Hotmail Calendar, Apple iCal) so I can subscribe to an iCalendar feed and see that schedule information alongside, and intertwined with, my personal calendar and any other calendars that I happen to subscribe to. Moreover the beauty of subscribing versus importing is that any changes to the schedule will automatically get propogated to me. Can any of that be achieved with an OData feed? No!

On the off-chance that anyone in the PDC team is reading this I implore you, please, publish the schedule in a format that makes it useful. OData is not that format.

As an aside, I am an avid proponent of iCalendar and have a strong belief that adoption of it both in our work and home lives could have significantly positive repercussions for all of us. With that in mind I actively canvas people to publish their data in iCalendar format and also contribute to Jon Udell's Elmcity project which you can read more about at Elmcity Project FAQ. I encourage you to contribute.

@Jamiet

Explaining the difference between OData & RDF by way of analogy

jamiet — Wed, 14 Apr 2010 10:18:00 GMT

A couple of months back I wrote a blog post entitled Microsoft, OData and RDF where I gave a high level view of the OData protocol and how it compares to RDF. I talked about linked data, triples and such like which may have been somewhat useful however jargon-heavy. Earlier today Dr Michael Hausenblas (blog | twitter) offered an analogy which I think is probably more useful and with Michael's permission I'm re-posting it here:

Imagine a Web (a Web of Documents, if you wish), which is not based on HTML and hyperlinks, but on MS Word documents. The documents are all available on the Internet, so you can download them and consume the content. But after you’re done with a certain document that talks about a book, how do you learn more about it? For example, reviews about the book or where you can purchase it? Maybe the original document mentions that there is some more related information on another server. So you’d need to go there and look for the related bit of information yourself. You see? That’s what the Web is great at – you just click on a hyperlink and it takes you to the document (or section) you’re interested in. All the legwork is taken care of for you through HTML, URIs and HTTP.
Hm, right, but how is this related to OData?
Well, OData feels a bit like the above mentioned scenario, just concerning data. Of course you – well actually rather a software program I guess – can consume it (a single source), but that’s it.
from Oh – it is data on the Web by Michael Hausenblas

I believe that OData has loads of use cases but its important to understand its limitations as well and I think Michael has done a good job of explaining those limitations.

@Jamiet

OData.org updated, gives clues about future SQL Azure enhancements

jamiet — Tue, 16 Mar 2010 13:00:00 GMT

The OData website at http://www.odata.org/home has been updated today to provide a much more engaging page than the previous sterile attempt. Moreover its now chockful of information about the progress of OData including a blog, a list of products that produce OData feeds plus some live OData feeds that you can hit up today, a list of OData-compliant clients and an FAQ. Most interestingly SQL Azure is listed as a producer of OData feeds:

If you have a SQL Azure database account you can easily expose an OData feed through a simple configuration portal. You can select authenticated or anonymous access and expose different OData views according to permissions granted to the specified SQL Azure database user.

A preview of this upcoming SQL Azure service is available at https://www.sqlazurelabs.com/OData.aspx and it enables you to select one of your existing SQL Azure databases and, with a few clicks, turn it into an OData feed. It looks as though SQL Azure will soon be added to the stable of products that natively support OData, good news indeed.

@Jamiet

Microsoft, OData and RDF

jamiet — Wed, 03 Feb 2010 16:53:00 GMT

Earlier today Chris Webb posted a blog entry entitled OData and Microsoft BI where he remarked upon the increasing importance of Microsoft’s burgeoning OData protocol for accessing data over HTTP. Chris also linked to Douglas Purdy’s blog post OData: The Movie which listed the following Microsoft and non-Microsoft technologies that support (or soon will support) OData either as a data producer or a data consumer:

Excel
.Net client
AJAX Client
PHP client
Java client
Visual Studio
Sharepoint
SQL Server (via Reporting Services)
Codename "Dallas"
Websphere

To that list I would add Windows Azure which exposes data from its storage engine using OData and also the forthcoming Live Framework technology that is expected to surface user-centric data from Microsoft’s various Live Services properties (e.g. SkyDrive, Messenger, Calendar).

Chris suggested that a pervasive format for data on the web would be a big step forward because any consumer that understood that format could talk to any producer that chose to expose it; win-win-win indeed. Unfortunately life is of course never that simple and as is so often the case there are many competing technologies in this space including (but, I suspect, not limited to) Microsoft’s OData, Google’s GData and the World Wide Web consortium’s Resource Description Framework (RDF).

This is nothing new of course; we can draw parallels with Atom and RSS which were recent competing technologies used to expose blog syndication feeds. Interestingly both of these technologies generally sit side-by-side quite happily these days (read: Will the Online Identity War turn out like the XML Syndication War?) and it will be interesting to see whether the same will happen in the OData/GData/RDF space; will organisations opt to expose multiple service heads for their various databases? I await the answer to that one with interest.

RDF is of particular interest chiefly because one of its main proponents is Sir Tim Berners-Lee, the inventor of the world wide web. RDF has been knocking around for about 10 years now and Berners-Lee sees it as the technology that will underpin the growth of the fabled Semantic Web; indeed he has been heavily involved with the recently announced service http://data.gov.uk which exposes data held by the UK government and it is no surprise that that service exposes its data using RDF.

I don’t know too much about GData but I do have a working knowledge of both OData & RDF so perhaps an overview and comparison of the two might be useful. To this observer it appears as though OData is closely tied to the tables-rows-and-columns paradigm that anybody familiar with traditional relational databases will understand. An OData service exposes multiple entities (roughly analogous to tables) and the relationships between them.

RDF however is built around the concept of triples which I assert is more analogous to a conceptual Entity-Attribute-Value model (that assertion is something I hope to explore in a future blog post). The key difference between RDF and OData though is that an RDF document can contain links to RDF documents that are hosted on other services and attributes of entities are defined in terms of those other services. Put more simply, RDF data is one massively distributed, non-centralised, interlinked web of data and if that sounds an awful lot like the mass of HTML documents which form the world wide web then you shouldn’t be too surprised given that Berners-Lee is so heavily involved. The notion of linking data together in this way has also given rise to the very descriptive moniker linked data which you may well hear mentioned in the same sentence as “semantic web” from time to time.

This has been a rather rambling blog post and really its just me dumping some thoughts out onto your computer screen as you can tell from the rather unimaginative title. Nonetheless there is some interesting stuff going on here that I hope to explore in the future so if you're interested please return some time. If you have any thoughts I'd love to read them in the comments section below.

@Jamiet

OData gunning for ubiquity across Microsoft products

jamiet — Mon, 21 Dec 2009 00:17:06 GMT

There was lots of news that came out of Microsoft’s recent Professional Developer Conference (PDC) event however one item that may have slipped under the radar of many is that the data format specification for WCF Data Services (previously known as ADO.Net Data Services aka Astoria) has been proposed as an open standard for data exchange across the web. Its name? The Open Data Protocol, or OData for short. From the OData website:

The Open Data Protocol (OData) is a web protocol for querying and updating data. OData applies web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores.

You can read more at Open Data Protocol on MSDN.

[It should be noted that WCF Data Services and OData are not the same thing. My interpretation is that WCF Data Services is a technology that allows data to be surfaced as an OData-compliant service.]

Microsoft’s desire to get OData accepted as a universal data access protocol should not come as a surprise, I view OData as the next evolution in a journey that can count ODBC and OLE DB as significant milestones:

According to wikipedia Microsoft created ODBC back in 1992 as a standard for accessing relational database management systems.
OLE DB was the next evolution which provided a standard for accessing both relational and non-relational data sources in a uniform manner.
Now we have OData which is an attempt to provide a uniform representation for any data that can be surfaced over that which is becoming the lowest common denominator for data exchange - HTTP.

I’ve been following the evolution of Astoria/ADO.Net Data Services/WCF Data Services for quite some time now, even back as far as when an influential team within Microsoft were probably going to reject AtomPub (according to Dare Obasanjo) and instead use their own proprietary alternative (according to Yaron Goland). Back in March 2008 I noted with interest an announcement by George Moore on the Live Services development blog that ADO.Net Data Services (as it was then known) would form an integral part of Microsoft’s stack of data access protocols and technologies across all data storage technologies:

The complete storage and developer tools stack revealed at MIX08 looks like this (described top to bottom). You are free to utilize this stack at any level of abstraction – there are no requirements to use all layers, and you are free to substitute your own developer tools against any layer

Area Product, Library or Protocol

4: Synchronization infrastructure: "Astoria Offline"

Microsoft Sync Framework

Feedsync AtomPub extensions

3: Developer tools: ADO.NET Data Services

.NET WCF Syndication libraries

AtomPub URI namespace conventions

2: Protocols: AtomPub

Atom

1: Underlying Products and Services: On premises: SQL Server

Structured Cloud Storage: SQL Server Data Services

Live services: Spaces Photos and Application Data Storage

A Unified Standards-Based Protocols and Tooling Platform for Storage from Microsoft

21 months later and we are really starting to see the fruits of those labours come to bear. Sharepoint 2010 Lists, SQL Server Reporting Services 2008 R2, Excel 2010 (through Powerpivot), Project “Dallas” & Windows Azure are all either an OData producer or consumer and we are promised that there are more to come. Microsoft have received deserved criticism down the years for different teams building competing technologies yet here we witness a quiet unification around a common data access stack and that can only be a good thing. Do I sense the impact of Ray Ozzie here?

This has been a short commentary on the evolution of the OData protocol, any comments would be much appreciated.

@Jamiet

Further reading (some of which are linked to above):

OData is grease to cut data friction by Jon Udell
Now you know...it's Windows Azure by Pablo Castro
Open Data Protocol
OData.org
A Unified Standards-Based Protocols and Tooling Platform for Storage from Microsoft
Every SharePoint 2010 server is a Data Services server by Pablo Castro

*Area*	*Product, Library or Protocol*
4: Synchronization infrastructure:	"Astoria Offline"
	*Microsoft Sync Framework*
	*Feedsync AtomPub extensions*
3: Developer tools:	*ADO.NET Data Services*
	*.NET WCF Syndication libraries*
	AtomPub URI namespace conventions
2: Protocols:	*AtomPub*
2: Protocols:	*Atom*
1: Underlying Products and Services:	On premises: SQL Server
	Structured Cloud Storage: SQL Server Data Services
	Live services: Spaces Photos and Application Data Storage