Forum Discussion
Excel Services and On-Premises SQL Data
Hey Eric
THe part that you're missing is the On-Prem data gateway from Power BI. With that (once configured) Excel in Office 365 will refresh from on prem data.
Hi John,
I finally have the Data Gateway set up and am able to schedule Power BI refreshes. Yay!
I am at loss, however, on how to connnect an Excel file to a data source in the Power BI Data Gateway.
I have used the "Analyze in Excel" feature to get the ODC file which makes a connection to the Power BI data source through a URL.
I thought this was it but it fails to refresh in Excel Services. I believe because it links to a model, but I am not certain.
Any advice on connecting Excel to the data source in Power BI Data Gateway?
Thanks!
eRic
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"
I think the part that a bunch of us (myself included) were missing was adding the workbook to the PowerBI Workspace and then going under Settings and Workbooks and assigning the gateway to that workbook. That is what tells the excel file where to actually look for it's refreshes!
Now the question is, how can other users also refresh or are we at the mercy of the scheduled refreshes under my PowerBI User??? Currently we get an error when another user tries to refresh the pivot table...
Thanks
- "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 😞
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
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
