Forum Discussion

Eric Adler's avatar
Eric Adler
Iron Contributor
Jul 25, 2016

Excel Services and On-Premises SQL Data

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 ...
sharepoint
John White's avatar
John White
MVP
Aug 11, 2016

l that you should need to do is to first, register that data source with the gateway, and then connect to it with Excel directly, as you would do normally). If the data source is registered correctly, it should "just work"

Jochen Juelke's avatar
Jochen Juelke
Copper Contributor
Oct 07, 2017

Hm , to clarify, the scenario you think should work is?:

SharePoint Online>Excel File>Excel Power Pivot>Data Gateway>onPrem Datasource (ie.SQL or SSAS)

What seems not working currently is:

SharePoint Online>Excel File>Data Gateway>onPrem SSAS (Tabular/OLAP "Live/Direct Connection")

-->Tried by Connecting in Excel to onPrem SSAS "direct" (local Servername) and also via the ODC File (provided with "analyse in excel"), in both cases clicking in the excel online pivot table throws error, the gateway seems not to be involved

see also:

https://community.powerbi.com/t5/Integrations-with-Files-and/Use-Power-BI-as-Data-Source-for-Excel-and-Utilize-Data-Gateway/td-p/57934

 

Resources