Forum Discussion
Excel Services and On-Premises SQL 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 😞
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-you/td-p/775706
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:
https://linkatech365-my.sharepoint.com/:i:/g/personal/matt_scouttg_com/EYJUGCXj8AdPuQLPrXuckEIBpYhqHlNSm6hXjkDX-ItCeA?e=eF5rhr
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
I have gained a bit of headway on this model. I now have Exel files being updated from on-premises systems via the Power BI gateway. That is a great win!
The model is, however, a bit different than I was thinking and different then what I believe is represented in this article (last option in the list) https://support.office.com/en-us/article/Use-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc6-8384-4d7d-b00f-b283c1e192ef
The end goal is: Open a web page in SharePoint with a Excel Web part and click refresh data and that data will refresh on demand from an on-premises data source.
While I can get the Excel file to refresh by setting it up in Power BI and refreshing from Power BI. I still can't get the "refresh data" button in Excel Online/Excel Services Online to work. Fails everytime.
I have progressed quite a bit thanks to your help John. So thank you!
- Eric you mentioned earlier on that you are looking to avoid the power bi pro license. I thought that if you refresh via the gateway, then pro license becomes requirement?
The personal gateway does allow for refresh and scheduled. It is only daily and limited to 10K rows but it does work.
