Excel Services and On-Premises SQL Data

Steel Contributor

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!

30 Replies
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

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.

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!

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

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.

That is excellent news! Thank you very much John! This really does bring BI to SharePoint on Office 365.

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"

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 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-8d7f5dc...

 

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.

Thanks, that's good to know!

 

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-a...

 

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-a...

 

I have tried this multiple times. If you still are monitoring this thread I sure would appreciate some help to understand how you accomplished this.

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

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