In yesterday’s post I showed how you could create surveys in the Excel 2013 Web App, and mentioned that I would have liked to consume the data generated by a survey via the new Excel Services OData API but couldn’t get it working. Well, after a good night’s sleep and a bit more tinkering I’ve been successful so here’s the blog post I promised!
First of all, what did I need to do to get this working? Well, enable Excel Services for a start, duh. This can be done by going to Settings, then Site Collections features, and activating Sharepoint Server Enterprise Site Collection features:
With that done, and making sure that my permissions are all in order, I can go into Excel, start the OData feed import wizard (weirdly, the PowerPivot equivalent didn’t work) and enter the URL for the table in my worksheet (called Table1, helpfully):
Here’s what the URL for the Survey worksheet I created in yesterday’s post looks like:
https://mydomain.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/SurveyTest.xlsx/OData/Table1
(there’s much more detail on how OData requests for Excel Services can be constructed here).
And bingo, the data from my survey is loaded into Excel/PowerPivot and I can query it quite happily. Nothing to it.
In a way it’s a good thing I’m writing about this as a separate post because I’m a big fan of OData and I believe that the Excel Services OData API is a big deal. It’s going to be useful for a lot more than consuming data from surveys: I can imagine it could be used for simple budgeting solutions where managers input values on a number of spreadsheets, which are then pulled together into a PowerPivot model for reporting and analysis; I can also imagine it being used for simple MDM scenarios where dimension tables are held in Excel so users can edit them easily. There are some obvious dangers with using Excel as a kind of database in this way, but there are also many advantages too, most of which I outlined in my earlier discussions of data stores that are simultaneously human readable and machine readable (see here and here). I can see it as being the glue for elaborate multi-spreadsheet-based solutions, although it’s still fairly clunky and some of the ideas I saw in Project Dirigible last year are far in advance of what Excel 2013 offers now. It’s good to see Microsoft giving us an API like this though and I’m sure we’ll see some very imaginative uses for it in the future.
My inclination still will be to ETL the spreadsheets into the data warehouse and have it go through data quality checks and strongly type the data and then load into PowerPivot but I think maybe with OData I can find some use cases that would greatly help cut down development time yet still have a robust enough solution where data quality issues will be manageable. I think this will be a mind shift for most data professionals like myself. It will be interesting to hear from you Chris actual real-world uses of OData to source data directly from into PowerPivot from spreadsheets.
I agree that it makes a lot of sense to ETL the spreadsheets into a data warehouse where one exists, but the point I should have made is that for self-service BI scenarios (where the likes of you and me are not involved) it will provide a ‘robust enough’ solution and one that will be very quick and easy to build.
We have seen that the XML Verbose-ness of OData feeds means it does not work very well for very large datasets. Would you have any suggestions on how to leverage OData on very large datasets (i,e 5 million rows)
No, but why would you want to use OData for large datasets? I’ve used it for downloading quite large amounts of data (millions of rows) from the Azure Datamarket, but I don’t think it makes sense to use it if there are other options.
Has anyone an idea on how make it work to import sharepoint online odata directly to power pivot. I am always getting errors with authentication.
thxs
It works fine for me – I’ve done it successfully in Excel 2010 and 2013 many times. So it is possible…
I haven’t had much luck with power pivot and SharePoint Online lists. So far only got power query connections working. However I’m gonna give this a go tonight!