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
Jul 26, 2016

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!

John White's avatar
John White
MVP
Jul 27, 2016

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.

  • Eric Adler's avatar
    Eric Adler
    Iron Contributor
    Aug 10, 2016

    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

    • John White's avatar
      John White
      MVP
      Aug 11, 2016

      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"

      • MATT DRYFHOUT's avatar
        MATT DRYFHOUT
        Copper Contributor
        Jan 25, 2019

        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

  • Eric Adler's avatar
    Eric Adler
    Iron Contributor
    Jul 27, 2016

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

Resources