Excel Services and On-Premises SQL Data

[Eric Adler]

I am looking to have an active data connection from an Excel file in Excel Services on SharePoint Online (O365) that has a data connection to an on-premises SQL database.

Need to be able to have data refresh work like it does with SharePoint on-premises.

The DMG used for PowerBI doesn't appear to have connections for Excel.

I am looking for "out-of-the-box" solution (or close to one). 

Is this still a supported scenario? It was widely used in on-premises SharePoint system as is currently blocking some key Office 365 adoption.

Thanks!

[#2]

"and then going under Settings and Workbooks and assigning the gateway" --> Hm, i can't find this dialog, maybe UI has changed again since your post date?! Can you publish a screenshoot? I'm also trying a XLS File with PowerBI.com as datasource (Analyse in Excel Feature). Neither publishing this file as workbook to BPI nor publishing this file to sharepoint online leads to an refreshable solution. In this scenario no gateway would be needed but this still not works :(

[#3]

Hey @Jochen Juelke, I was going to test for you today, but it appears there is an issue with getting workbooks from sharepoint sites currently, as per this link:

https://community.powerbi.com/t5/Service/Service-Get-Data-gt-Sharepoint-quot-The-URL-is-not-valid-or...

However, I added a new workbook from my OneDrive for Business which works fine right now (same thing otherwise...).  One thing to note, when you add it there are two options, import or connect, this is Connect. Here is a link (since I can't upload a screenie apparently...) to the screenshot of my Power BI settings screen:

Power BI workbook gateway settings.jpg 

[#4]

@MATT DRYFHOUT THANK YOU! I always searched for the workbook-properties-dialog while the excel file is opened/rendered in browser. But we must navigate as following:

- go to workspace overview (where Dasbords,Reports,Workbooks,Datasets tabs are displayed) 

- navigate NOT to Worksbooks tab and chosse "workbook>settings" there!

- but instead navigate to DATASETS tab and choose "settings" for anyone of your datasets

- Now you can switch to "Workbooks" tab where gateway can be configured

Follow Up Question:

Do you know any trick how to get the scenario working when the excel file is not based on a onPrem Datasource but uses a published PBI dataset as source (created via analyse in excel feature)?

We want to create a "dashboad" with content auf PBI Reports and XL Reports, both reports should be based on the same dataset (hosted in PBI).
Also this scenario seens simpler then the last (because no gateway is needed) i found no way to get this to work. After publishing a XLS, which has a Online PBI Dataset as source, PBI Service says "Data Refresh is not supported! But why? My explanation is that "asazure://" is used in the ConStr and it seems that is not jet supported. Do you have more insights on that?

 

[#5]

@John White can you share more information on how to configure Excel Online to access on-premise SQL Data. And does that also apply to Mac Office 365 Excel. I have attached an error I received when I tried refreshing excel online that is connected to an On-premise SQL Server

[#6]

@ambroseJoel  - All that you should need is an on-premises data gateway installed, and the data source in question registered. However, the error that you are showing indicated something else, which is odd, but I'm not sure about. 

[#7]

@ambroseJoel 

How Excel is connected, Power Query?

[#8]

@John White is the getaway that you are referring to the same one for Power BI? or do we have another one? and how do you connect it to Excel?

[#9]

@Sergei Baklan Yes, I am using Power Query

[#10]

@ambroseJoel Yes it is. You will install the On Premises Data Gateway, and then register the data source that your Power Query uses. Once registered, the Power Query should be refreshable. Here's tha main article on the gateway: 

On-premises data gateway - Power BI | Microsoft Docs

Here's a community post talking about it as well: Solved: Scheduled refresh for excel file with on-premise g... - Microsoft Power BI Community

[#11]

@ambroseJoel 

To my knowledge Excel Online doesn't support PQ refresh. That's use desktop app, or publish on Power BI (file shall have data model) and refresh from it, better use scheduled refresh. Another option is to use Flow to refresh such file. 

[#12]

@Sergei Baklan That's right - you need to connect the file to Power BI to facilitate and schedule the refresh - Excel Online doesn't do it on its own

[#13]

@John White Does it allow user to do the on demand refresh from on prem database if user is added in the ad group which gives appropriate permission to the database? I mean it works in excel desktop application but would it work the same way in Excel online too? The issue with current on prem data gateway is it runs from local machine rather then running from vm instance so the gateway can't be guaranteed to be running all the time from local machine. We want users to be able to refresh the power query dataset if they are connected to the network so excel desktop application works fine in that manner but having E5 license for that comes with its own cost I was wondering if the same is possible with F5 licensing where it only allows office online version.