Scheduled Data Refresh of OData Sources in Power BI

With the recent public release of Power BI it is finally possible to refresh Power Pivot workbooks online.Once a workbook is uploaded to SharePoint online and “Enabled” for Power BI you can schedule an automatic data refresh for the Power Pivot model. Though, at the moment only a very limit number of data sources are supported:

  • Windows Azure SQL Database data
  • Open Data protocol (OData) feeds
  • On premises data sources that are enabled for access in Power BI for Office 365

Especially for public available data OData feeds are very popular, for example from Wikipedia. Those public data feeds usually do not require any authentication so one would expect these data sources to work flawless with a scheduled data refresh of Power BI. Well, you are wrong here!
When you create a simple Power Pivot model with one OData source to e.g. Wikipedia, publish it and setup scheduled data refresh you will receive the following error:

Refresh_Error

The error message itself does not reveal any insights on the actual error. If you are in the (more or less) lucky situation that you are also owner of that site you can go to the Power BI Admin Center to get some further details on the error:

AdminCenter_ErrorLog

By downloading the CSV or hovering over a single Message you can see the details:

  1. "Failed to find a match for the data source (connection string: data source=http://publicdata.clouddatahub.net/Web/Tables/fa9af6681cd64206b3aafe6d12408117/V1/Data;include atom elements=Auto;include expanded entities=False;integrated security=SSPI;persist security info=false;time out=600;schema sample size=25;retry count=5;retry sleep=100;keep alive=False;max received message size=4398046511104;base url=http://publicdata.clouddatahub.net/Web/Tables/fa9af6681cd64206b3aafe6d12408117/V1/Data) for the user 'Gerhard.Brueckl@XYZ.onmicrosoft.com'. The user is unauthorized or, the corresponding data source is not created. Check the user's permission to the data source or create a data source for the connection string. Tracing ID: 23f244ad-7921-48f7-b13a-ef68e8cf5503"

It says that for my user no corresponding data source exists or I do not have permissions to access it.

In our case the reason is that our user is unauthorized – the actually data source that was used must not necessarily exist in the user’s data sources (Those can be found via “My Power BI”) for scheduled data refresh to work.

So you will ask yourself what could go possibly wrong here as you are just accessing a public OData feed?!?
The reason is that in the connection string the “Integrated Security”-property is set to SSPI by default. In your local Excel/Power Pivot model this works just fine as the SSPI context can be resolved and sent to the OData feed. Sure the OData feed actually ignores this information as it is public but from an authentication point of view everything works correctly!

The problem with Scheduled Data Refresh is that SSPI simply does not work as it cannot be resolved and Power BI cannot use any service user for your request if SSPI is defined. The first thing that comes to your mind would be to simply set the authentication method to “Anonymous” which would be perfectly fine for a public data feed. However, Power Pivot does not support Anonymous authentication for OData sources:

OData_Authentication

As SSPI does not work we need to use “Basic” here and provide “User ID” and “Password”. Which UserID/Password you may ask? – and the simple answer is: “It does not matter!”
You can provide any values here, in my case I used “random” for both, User ID and Password! Another thing you need to ensure is that “Persist Securtiy Info” is set to True so your “Password” is stored in the final connection string making Power BI think that authentication is defined correctly within the connection string and Power BI does not have to do anything.

Once you changes those settings your data refresh will work like a charm:

Refresh_Success

On last thing you may realize is the “Running Time”. It takes significantly longer when doing the refresh in Power BI opposed to doing the refresh locally. Just keep that in mind, especially for bigger data feeds.

In the future I hope that Microsoft will introduce some kind of “Anonymous” authentication within the dropdown of Power Pivot or simply check at some point if the OData feed requires any authentication at all hence overwriting the authentication mode specified in the connection string when refreshing.

5 Replies to “Scheduled Data Refresh of OData Sources in Power BI”

  1. Hi Gerhard,

    Great article, I just had one question. How do I get into the Advanced screen in Excel 2013 in order to change the fields? I’ve looked all over but can’t seem to find it.

    Thanks,
    Andrew

    • Hi Andrew,

      you get to the Advanced Screen by navigating to your existing connections within Power Pivot. There you’ll find and Advanced Button which allows you to modify the settings as described above

      -gerhard

  2. Hi,
    I’m new to PowerBI. I was trying to import a power pivot to powerbi.com and schedule refresh for the same. I have connected to http://services.odata.org/AdventureWorksV3/AdventureWorks.svc
    in excel 2013 and so the power pivot has been created in my local system. Then I have uploaded it into onedrive personal. I have taken this power pivot from onedrive personal to PowerBi.com. But, when I tried to schedule refrfesh from powerBi.com, it shows an error message. “You cannot schedule refresh for this dataset because it gets data from sources that currently don’t support refresh.” But, the same data is refreshing from my local system if I manually refresh it. I have changed SSPI and all changes as you have mentioned. Also, I haven’t seen PowerBI admin center anywhere.
    Please suggest.

    Regards,
    Julie

    • Hi Julie,

      so this blog post was originally written for the “old” Power BI which was part of SharePoint Online. I have not tested this yet with the new Power BI (www.powerbi.com) but I would assume that there are similar issues.
      Please also check which data sources actually support online refresh at the moment. According to this site: https://support.office.com/en-us/article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e
      it should be possible to use Ananymous Authentication when connection to OData, but, as i said, i have not tested it yet

      I may post an updated article which targets the new Power BI in the future but thats just an idea at the moment

      -gerhard

    • Hi Julie,

      I just ran some tests and it looks as Anonymous Authentication is working just fine. Once you added your Excel (or .pbix) to the Power BI Service you can click the […] next to the Data Set and use [Schedule Refresh]
      You will be redirected to the Data Set Settings and should be able to set the credentials for the Data Source (being your OData feed). There you can simply select “Anonymous”.

      Just make sure to connect to your OData feed using Power Query – i am not sure if it works if you connect from Power Pivot directly

      kind regards,
      -gerhard

Leave a Reply to Julie Cancel reply

Your email address will not be published. Required fields are marked *

*