Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Sharing Data From Excel as OData with FlatMerge

with 9 comments

At last, the Excel 2013 app I’ve really been waiting for! Those data visualization apps from last week have generated a lot of interest, but this is even cooler for a data geek like me.

A few months ago I came across FlatMerge, a startup that allows you to upload data and then make it available as an OData feed; I was going to blog about it but my fellow OData fan Jamie Thomson beat me to it. However at that point it was only a website where you could upload data… today, FlatMerge released its own, free (for the time being) Excel 2013 app which allows you to upload data direct from Excel. So you can take data from an Excel table:

image

Save it to FlatMerge:

image

And then import it into Excel 2013, PowerPivot, Data Explorer or any tool that supports OData feeds. Here’s the URL for the table I just uploaded (which, if I’ve read the docs correctly, should be publicly available):

https://fmsecure.azurewebsites.net/Data/oData/a5462e03-e0bc-44b4-a654-5dbbbd59cb59

It’s still a version 1.0 and there are a few features it’s missing that I’d like to see (like the ability to update a data source, and to control who has access to that data), but I think it’s very cool. I’ve seen tools that allow you to share data from Excel before but this is the first that uses OData, and this means you have a much greater degree of flexibility about how you consume your data. Arguably you could do the same thing by saving your Excel file to Sharepoint 2013 Excel Services and using the OData feed from an Excel Services table, but that’s a much more expensive and less user-friendly option.

I can imagine a whole bunch of uses for this, for example in a budgeting application where multiple Excel users need to submit their figures, which then need to be consolidated in a single Excel spreadsheet, maybe using Data Explorer.

Written by Chris Webb

May 30, 2013 at 6:42 am

Posted in Cloud, Excel

Tagged with

9 Responses

Subscribe to comments with RSS.

  1. Awesome. The only problem I see is that you’re not sharing the data master, you’re sharing a copy of it. Next step is to allow us to upload our Excel documents to FlatMerge and share the data therein as OData. We would be able to edit the Excel document directly thus any changes we make to the Excel document are automatically reflected in the OData feed.
    That’s basically what I’ve been asking Microsoft to do with SkyDrive for what seems like years now, but I don’t think they have any appetite to do it.

    Jamiet

    May 30, 2013 at 8:38 am

    • I agree this would be nice to have in Skydrive, but surely what you’re talking about is what’s possible today in Sharepoint 2013? When everyone has an Office 365 subscription (when) then this will be much easier…

      Chris Webb

      May 30, 2013 at 8:42 am

      • But I think there’s also a need for what the FlatMerge app is doing too, separate from what you describe: some people will not want to share an entire document, or even be able to.

        Chris Webb

        May 30, 2013 at 8:44 am

      • Its true that OData access to data in Excel workbooks is in SharePoint 2013/office365 but with the best will in the world there will always be laggards that are still running decrepit versions of SharePoint/Office/etc… I believe that that problem will diminish over time but its still going to be a problem for a long long time yet. Take my current client, they use Office365 for email but (a) its Office365 v1 (b) I’m still running an old version of Office (Office2010) and (c) they don’t even make any attempt to use SharePoint properly. I really do think we’ve got a long time until everyone is on the same platform all the time.
        SkyDrive on the other hand is just there, everyone has access to it, there are no organizational restrictions on who can and cannot see what. Its what I’d call frictionless. That’s why I’ve hosted my “London Borough of Hounslow Expenditure” workbook up there (see http://jamie-thomson.net/2013/03/24/charting-london-borough-of-hounslow-expenditure/ for more details). I stick it up there, I can edit at will from any device, and the charts in that blog post automatically update. As I say, frictionless.
        The only thing missing is OData access to the data in the workbook and as you rightly point out SharePoint 2013/Office365 could provide that but, for me, the positives of SkyDrive outweigh the positives of SharePoint.

        JT

        Jamiet

        May 30, 2013 at 8:53 am

      • I’ve just realized that none of the charts are showing up in that blog post cos I’ve exceeded the 5MB limit. Kinda negates my argument. Damnit! :)

        Jamiet

        May 30, 2013 at 8:59 am

      • “But I think there’s also a need for what the FlatMerge app is doing too”
        Yeah, fair point. But its not what *I* want :)

        Jamiet

        May 30, 2013 at 9:00 am

  2. I’ve just realized that none of the charts are showing up in that blog post cos I’ve exceeded the 5MB limit. Kinda negates my argument. Damnit! :)

    Jamiet

    May 30, 2013 at 8:58 am

    • I guess the real issue here is that MS needs to make some cash out of Office at some point, and OData access is most useful to Enterprise users (though I know there are plenty of non-Enterprise uses for it as well) so it gets excluded from the freebie version of the Excel Web App in Skydrive. Google, of course, gives full access to the Google Docs API in its free version… but I guess they want to win market share rather than make money. It’s the old why-can’t-I-have-this-Enterprise-Edition-feature-in-Standard-Edition problem we see in SQL Server.

      Chris Webb

      May 30, 2013 at 9:07 am

      • I do see Google Docs as the elephant in the room. I now see none-techie friends of mine that are choosing to use Google Docs rather than Office (one friend recently planned his wedding using Google Docs) and I don’t think it should be discounted. SkyDrive is the clear competitor to Google Docs and I do think Microsoft need to be wary of losing ground here.
        As ever getting the balance between different free/paid tiers is a perennial problem for Microsoft – its both a blessing and a curse.

        Jamiet

        May 30, 2013 at 9:28 am


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

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers

%d bloggers like this: