Chris Webb's BI Blog

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

Consuming OData feeds from Excel Services 2013 in PowerPivot

with 9 comments

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:

image

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):

image

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.

image

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.

Written by Chris Webb

July 24, 2012 at 10:01 pm

Posted in Excel, OData, PowerPivot

9 Responses

Subscribe to comments with RSS.

  1. 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.

    suhail ali

    July 24, 2012 at 10:23 pm

    • 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.

      Chris Webb

      July 24, 2012 at 10:28 pm

  2. 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)

    dipakb

    July 25, 2012 at 10:57 pm

    • 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.

      Chris Webb

      July 25, 2012 at 10:59 pm

  3. […] 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 […]

  4. 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

    Stef

    July 25, 2013 at 12:52 pm

    • It works fine for me – I’ve done it successfully in Excel 2010 and 2013 many times. So it is possible…

      Chris Webb

      July 25, 2013 at 12:54 pm

  5. 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!

    brett

    September 17, 2014 at 11:00 pm


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 3,296 other followers

%d bloggers like this: