Dynamically display on-premise SQL data in Excel spreadsheet hosted in SharePoint

[365Questions]

I have some local on-premise Excel files that pull data directly from a SQL database that have been moved to a document library in SharePoint. Currently the Excel file opens as expected in SharePoint Online, except that the data is static.  We want it to update with current information from our on-premise SQL server.  Can the Excel file in SharePoint Online connect to our on-premise SQL database and update the file?  For example, I open the Excel file and it queries our on-premise SQL database and the spreadsheets are updated.  Thanks

[Greg_Deckler]

The answer is *yes* but you have to do it via the old way of doing things. You have to have Power BI for O365, set up a data management gateway and register the unique ID's for your queries in the Power BI Admin center. Here is something to get you started:

https://support.office.com/en-us/article/Use-external-data-in-workbooks-in-SharePoint-Online-8D7F5DC...

[365Questions]

Is this same capability in the new Power BI?  How does one go about getting the "old" Power BI for Office 365 if they are purchasing for the first time?  It appears the only option is to purchase the new Power BI in the portal.

[Seth_C_Bauer]

@365Questions Even if you could get the old version, I would suggest you don't go that route because it will be an excercise in futility. The old version is being phased out and you have to migrate to the new version by Dec 31st.

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-migrating-to-the-new-power-bi-experi...

[365Questions]

Ok, thanks that's good to know. 

What about the functionality that allows for Excel files hosted in SharePoint Online to query on on-premise SQL server, is that an option in the new Power BI?

I see this quote stating external data sources available for workbooks:  "SQL Server data that is available in the Power BI Admin Center (this requires a subscription to Power BI for Office 365 and an administrator to configure the connection)" at this link.  But that is a little too vague and am looking to clarify (EG, can I access the database or does it have to analysis services, etc).  Thanks

[Seth_C_Bauer · Accepted solution]

@365Questions If I understand you correctly, you want to just use an excel file "as-is" and not load it in to Power BI..  You can do that, and the Excel file can be connected to a datasource. The only caveat is that the Excel file needs to be on OneDrive.

More detail here

Otherwise you have the option to pull that data in to Power BI via the desktop or Service. The Desktop will allow you to create a seperate model to bring in other data sources etc... Hope that answers your Q.

[365Questions]

Thanks.  Along the same lines, who needs to have an assigned Power BI License.  Is it everyone who wants to dynamically refresh and view the updated data or is it the creators and editors of the Excel file?

[Greg_Deckler]

Not sure about licensing, I think that under the old model, everyone needs a Power BI license to get to the Power BI site. However, that being said, the old way just stored the documents in SharePoint so as long as the people had access to the document in SharePoint, they should be able to view it.

Note all of the "the old way" phrases here, if you build anything using "the old way", there is no guarantee it is going to work moving forward. Now, I would anticipate that since the new way allows you to store Excel on OneDrive and OneDrive is essentially SharePoint that the new way will eventually allow you to use a SharePoint Online tenant just like how you use OneDrive today, but no guarantees.