Auto Refresh - SQL Server Data Connection

[rademar]

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!

[NandanHegde]

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

[rademar]

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?

[NandanHegde · 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...