Forum Discussion
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 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!
- Actually, SPO has been using Excel Online (not Excel Services) for quite some time now. The Excel Services capabilities were moved over. Excel is still totally viable (and will be moving forward). It's a first class citizen and in most cases the preferred one for analysts. It works quite well with Power BI - it's really a better together story.
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.
Hey John, did you get around to writing that blog? ;)
We are working with a client that has an extensive powerpivot dashboard system that pulls data predominantly from a LOB on prem SQL database (syspro). The goal would be that we can connect to the same SQL database (through means that you describe in place of the current odbc connections in the power pivot data model) and refresh on schedule so outside sales team can be viewing dashboards in sharepoint with daily current data. Looking forward to your response!
Thanks! Matt@scouttg.com
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.
If you're talking about Excel Online in Office 365 connecting to on prem data, that is possible with the Gateway if you're using PowerPivot. You can use Power BI to schedule an Excel Workbook refresh from on premises data.
If you're talking about on prem Excel Services or Excel Online, you can configure the data connection to refresh on open.
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.
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
How Excel is connected, Power Query?
- Definitively, Power BI has to be the way to go since I expect Excel Services will dissapear at some time from SPO as it happens in SP 2016
