Forum Discussion
Excel Services and On-Premises SQL Data
I should also add, because I just tested it, and it works.
An Excel workbook that is connected directly to a SQL server, and then connected to Power BI (either from OneDrive or a SharePoint library) will be able to connect to the data through the on On-Prem Data Gateway.
In short, it works. I think that I need to do a blog post on this one - there are a few intricacies involved.
Are you opening the Excel file on the desktop to refresh it?
The trick I still haven't figured is being able to refresh the Excel file in Excel Services Online, connected to on-prem SQL, without opening the Excel file in the client application to get the data refresh.
For example refreshing from the Excel Web Part on a SharePoint page. A great SharePoint BI tool!
It works great if everything is on-prem or everything is in the cloud (Excel file on One Drive or SharePoint Online or SQL Azure).
It is Online to On-Prem just using Excel Services Online that is still getting me. Trying to accomplish without the Power BI Pro license too.
I greatly appreciate your time and the info you provided!
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"
That is excellent news! Thank you very much John! This really does bring BI to SharePoint on Office 365.
That gets tricky as the Excel Services Service Application is actually performing the refresh in this case. It would typically use the Secure Store Target Application profile configured for data access as designated in the Excel spreadsheet. This is basically how reporting works in Project Server.
I haven't tried to do this setup with Office 365 but I think you can set up a SSS profile in the SharePoint Admin portal. I'd be curious to see if it works with the gateway. If it does, this opens up some new possibilities.
--Treb
