Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rademar
Frequent Visitor

Auto Refresh - SQL Server Data Connection

Hi everyone,

 

This is the current setup in our small company:

I've made a few Power BI reports and dashboards in PowerBI Desktop.

*** I put *.pbix files of the report in a shared Dropbox folder and other users in our organisation open those reports from Dopbox folder, directly in PowerBI Desktop.

*** All the data in the reports is from our SQL Server database. I've used "import", not "direct query" when making connection, and wrote an sql query for each table in data model. There are a few tables that use data from excel tables. The rest (about 20 of them) are all sql server connections.

*** I refresh the data each morning manualy.

 

This is what I would like:

*** To publish the data to PowerBI Service and all the users to access directly via PowerBi Service

*** To automaticly refresh data (5-6 times a day is enough)

 

This is what I need help with:

*** What kind of subscription do you recomand? Pro or Premium per User? Can the same user be logged in on multpile locations at the same time? 

*** What other steps do I need to take to be able to sync data with our sql server?

*** Do I need to instal gateway on our server? Or to make dataflow? 

*** Do I need to make some changes in the report that I've built in PBI Desktop?

*** What is the optimal solution and what is best practice for small companies (let's say up to 6-7 users maximum)

 

Thanks!

 

1 ACCEPTED SOLUTION

yes, the server admin is correct. Create a new server and host the gateway on that new one as per MSFT stds rather than on the same server as database.

 

Yes, file can be in google drive :

https://community.fabric.microsoft.com/t5/Desktop/Connect-Shared-Excel-File-from-Google-Drive-to-Pow...




----------------------------------------------------------------------------------------------
Nandan Hegde (MSFT Data MVP)
LinkedIn Profile : www.linkedin.com/in/nandan-hegde-4a195a66
GitHUB Profile : https://github.com/NandanHegde15
Twitter Profile : @nandan_hegde15
MSFT MVP Profile : https://mvp.microsoft.com/en-US/MVP/profile/8977819f-95fb-ed11-8f6d-000d3a560942
Topmate : https://topmate.io/nandan_hegde
Blog :https://datasharkx.wordpress.com

View solution in original post

3 REPLIES 3
NandanHegde
Super User
Super User

Hey,

Below are the details :

1) For org with low number of users and based on the use case of just accessing the reports, pro license is optimal for cost savings

2) Is it Azure SQL database or an Onprem/IaaS database?

If OnPrem/IaaS, you would need an onpremises data gateway installed to scheudle the auto refresh of reports in Powerbi service

3) It is better to have the excel file loaded in sharepoint or loaded in database and consumed from PowerBI for better automated refresh




----------------------------------------------------------------------------------------------
Nandan Hegde (MSFT Data MVP)
LinkedIn Profile : www.linkedin.com/in/nandan-hegde-4a195a66
GitHUB Profile : https://github.com/NandanHegde15
Twitter Profile : @nandan_hegde15
MSFT MVP Profile : https://mvp.microsoft.com/en-US/MVP/profile/8977819f-95fb-ed11-8f6d-000d3a560942
Topmate : https://topmate.io/nandan_hegde
Blog :https://datasharkx.wordpress.com

Thanks for reply!

 

It's on prem database. Our server admin is suggesting to setup a new virtual machine on server and have gateway running there. 

 

For excel files I assume dropbox or google drive can be used instead of Sharepoint?

yes, the server admin is correct. Create a new server and host the gateway on that new one as per MSFT stds rather than on the same server as database.

 

Yes, file can be in google drive :

https://community.fabric.microsoft.com/t5/Desktop/Connect-Shared-Excel-File-from-Google-Drive-to-Pow...




----------------------------------------------------------------------------------------------
Nandan Hegde (MSFT Data MVP)
LinkedIn Profile : www.linkedin.com/in/nandan-hegde-4a195a66
GitHUB Profile : https://github.com/NandanHegde15
Twitter Profile : @nandan_hegde15
MSFT MVP Profile : https://mvp.microsoft.com/en-US/MVP/profile/8977819f-95fb-ed11-8f6d-000d3a560942
Topmate : https://topmate.io/nandan_hegde
Blog :https://datasharkx.wordpress.com

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.