OData and Microsoft BI

I first came across OData last year when Jamie Thomson blogged about it; since then I hadn’t really thought about it much until I came across Douglas Purdy’s blog and specifically his post from yesterday which really brought home how important it is to the future of Microsoft BI. I would urge you to watch the video “OData: The Movie” that he mentions in his post because it gives a really good introduction to the way that OData can be used in BI scenarios; if you don’t have the time, all you really need to know is that it’s OData that makes it possible for PowerPivot to consume data from SSRS reports and Sharepoint lists.

Just watching this video made my mind boggle with the possibilities of OData, although since I’m a long way from being an expert in this area I won’t bother to detail all of these fantasies as they’re probably rubbish. However, just consider how much easier life would be for the PowerPivot user of the future if the internet was full of sites that supported OData; certainly, when I’ve looked at the new UK government websites such as the recently-launched http://data.gov.uk/ and http://data.london.gov.uk/, or even sites like the Guardian Data Store that I blogged about last year, I’ve felt that the lack of a standard format to consume this data (a badly-formatted Google spreadsheet is not ideal) has seriously limited these sites’ usefulness. And what if Bing came up with a service like the sadly-useless Google Squared, where you could search for data on the web and return it in a structured, OData format?

I’d also love to see Analysis Services support OData too, in some shape or form. Perhaps it could be used to solve some of the same problems with XMLA that Julian Hyde lists in his recent post on xmla4js. Wouldn’t it also be cool if there was a standard interface for publishing an MDX query up to SSAS from any client tool, and then be able to consume it via OData (similar to what I suggested here)? You’d then enable scenarios like this: power user creates query using an advanced SSAS client tool, then publishes it up to SSAS whereupon it becomes something similar to a SQL stored proc with the results available as an OData feed, and therefore can be consumed either in your own code (eg in a website), by PowerPivot users, or by something like SSRS (which would then know nothing about the query used but would just be used to format the resultset).

6 thoughts on “OData and Microsoft BI

  1. I\’ve read the blogs, I\’ve watched the (hugely humongous…) video, but I still am not clear on what value OData provides that the URI/HTTP/RDF/LinkedData "stack" doesn\’t…

  2. Jamie did another post on this subject the other day, which partly answers your question (and some of the comments add more): http://sqlblog.com/blogs/jamie_thomson/archive/2010/02/03/microsoft-odata-and-rdf.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+jamiet+(Jamie+Thomson)I suppose the other main point is that, from my point of view, Microsoft is making a big push to support OData and isn\’t making the same effort to support RDF… so the value is that I\’m actually going to be able to use it against data sources I work with every day.

  3. Fantastic! no more ETL for those simple reports. Just connect through Powerpivot with MOSS 2010. Of course the \’business end user\’ is still stuck with Excel 2010 in which they can not do adbvanced analytics, but hey; it is a start :)

  4. Ian Smith — you CAN do advanced analytics with Excel 2010 — the Data Mining 32-bit client will work with 32-bit, and there should be a new data mining client when Office 2010 is released — also there are many add-ins for Excel. See my data mining portal http://www.marktab.net for resources on Microsoft Data Mining. I consider data mining to be "advanced" because mathematically it requires graduate-level statistics and business (combined) to achieve maximum results.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s