SharePoint Online & Excel Services -> unable to refresh data connections

Question

I'm trying to create a few very simple reports based on my SharePoint Online data using Excel. I've got as far as getting the data into Excel and creating my report, and now I'm trying to upload the Excel file back to SharePoint and make it available as a WebPart.

The report displays just fine, but I cannot refresh the data anymore, I get the following exception:

We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: owssvr

I find that strange, because refreshing works if I download the file to my desktop.

I've done some research online, but couldn't find any solutions. Also this post didn't help : Unable to refresh data for a data connection in the workbook.

This post seems to suggest that it's never going to work.

Anyone has an idea what to do? I'm also open to suggestions what else to use for simple reporting in Sharepoint Online.

I have E3 subscriptions and the workbook is published on a Business Intelligence Center sitecollection. My SharePoint online tenant is runnig SP2013.

Answer 1

1. Create a data user on your Active Directory
2. Map the user into SQLserver to the database your are using in your Excel file
3. Go to SharePoint Central Administration
   and select the following :
   -> Application Manager -> Manage service application -> Global Settings -> on excel services settings choose External Data -> Select create a new unattended service account -> finally type the user which you create in first step and put password