Refreshing data from SQL Server

[Anonymous]

I have imported data from the last 6 months into a Power BI desktop file using an SQL Query. In desktop, when I manually click refresh it reruns the query and gets the latest data for today. However, when I click refresh on Power BI service it does nothing and I get a warning message next to the dataset saying "scheduled refresh has been disabled". Anyone know why this is and how to get the data to refresh on power bi service?

[Thejeswar · Accepted solution]

Hi @Anonymous ,

The Refresh of data source from Power BI Service requires to set up a on premise gateway in your machine/ server. This will help establish connection between your Power BI reports and SQL Server dataset in Power BI Service.

More information on how to set up a Gateway is provided in the below link. The Link also provides next steps on the same

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem 

Unless we setup a gateway, we might not be able to refresh the dataset from Power BI Service

[Anonymous]

Hi @Thejeswar 

What about connecting using directquery instead? My dataset has 350k rows so will it be suitable? From what I've read online, directquery will just update itself automatically when I click on my visual. Is this how it works?

[Thejeswar]

Hi @Anonymous ,

Irrespective of when your dataset is Import Mode or Direct Query mode, the dataset would require a gateway to connect to On-premise database.

Imagine this as your reports are published to Cloud Power BI Service, while your data is in your On-premise Server. This Gateway is more like a path between the two environments. 

In Direct QUery mode, when you run the report, using the gateway the query is sent to the SQL server which sends back the data using the gateway to your reports.

In case of Direct Query report, you might not require to setup a Scheduled Refresh as with every hit the report is going to get data directly from SQL server

Best Regards,

[Anonymous]

Ah ok. If I set up a gateway in personal mode, then have a scheduled refresh set up 7am every morning, what will happen if I leave my pc off overnight? I am guessing it won't refresh until I open up my pc again the next day, right?

[Thejeswar]

Yes that's right!!

[Anonymous]

Ok, I have installed the gateway but not sure how to proceed. The guide says to go to manage gateaways but on there it says I have no gateway installed. Is there something I need to do to get it to appear there?

[Thejeswar]

Hi,

Check the below link. This might help!!

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial