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
John White's avatar
John White
MVP
Jul 26, 2016

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.

  • MATT DRYFHOUT's avatar
    MATT DRYFHOUT
    Copper Contributor
    Sep 28, 2018

    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

  • 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

    • TrebGatte's avatar
      TrebGatte
      MVP
      Jul 26, 2016

      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

Resources