cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
365Questions Frequent Visitor
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

7 REPLIES 7
Super User
Super User

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

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

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

365Questions Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

365Questions Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

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


Near SE WI? Join our PUG Milwaukee Brew City PUG
365Questions Frequent Visitor
Frequent Visitor

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

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

 

Super User
Super User

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

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


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,770)