Forum Discussion

Eric Adler's avatar
Eric Adler
Iron Contributor
Jul 25, 2016

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 ...
sharepoint
Eric Adler's avatar
Eric Adler
Iron Contributor
Aug 24, 2016

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!

Brian Mather's avatar
Brian Mather
Brass Contributor
Sep 09, 2016
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?
  • Brian Mather's avatar
    Brian Mather
    Brass Contributor
    Sep 09, 2016

    Thanks, that's good to know!

     

  • Eric Adler's avatar
    Eric Adler
    Iron Contributor
    Sep 09, 2016

    The personal gateway does allow for refresh and scheduled. It is only daily and limited to 10K rows but it does work.

Resources