direct query data refresh

[space83]

i am using mssql as on premises database and applied direct query in power bi datasource.

really not understand the frequencies of data refresh (from mssql to power bi datasource).

refer to pic below, this is for cache refresh, not directly from mssql.

the data refresh documentation also is bring more questions than answer.

the issue is, if u take look at refresh history, the status of data refresh is show Completed although my gateway is offline. that is weird situation. 

[v-janeyg-msft · Accepted solution]

Hi, @space83 

It’s my pleasure to answer for you.

According to your description,you used DQ mode to connect the datasource.In this way, the data refresh type is schedule cache refresh.You should know that when you first open the report or interact with the report, you will get the latest data from the data source if you use DQ mode regardless of setting data refresh.This is the benefit of DQ.

You can use schedule cache refresh to speed up the refresh frequency of tile cache(default is one hour in dashboard).

If you use standard gateway,it will run as service even without logging in again ,unless you stop it or shut down computer.

You can parameterize the data source in PQ, then change the parameters in the service, change the data source path, or use the parameter filter rows and replace values.

link:https://docs.microsoft.com/en-us/power-bi/connect-data/service-parameters 

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jeanxyz]

I have created a test reporting using SQL data under direct query mode. Here is my observation:

when I open the pbix online file, the data didn't get refreshed automatically. If I edit the file or click into a visual, indeed the file gets updated. But the update is not saved, which means, when I exit and reopen the online file, I see see old data.

I think there are two ways to keep data updated:

1. open the file online, and click refresh button in the explorer browser, or

2. create scheduled data refreshment via gateway

[v-janeyg-msft]

@Jeanxyz  If you use dq mode, when you open the report in service, It will perform a query to get the latest data. When you interact with the report, the query will also be sent. And don’t need to save. DQ refresh refresh the cache. It is only possible that the original data has changed after you open the report, and the data has not been refreshed in time due to the browser’s cache settings.

Secondly, there is no pbix file in the service, it will be divided into two parts, report and dateset.

Best Regards

Janey Guo

[lbendlin]

viewing a report is not automatically interacting with the report. Let's assume the user opens the report. At that point the direct query connection refreshes all the visuals in the page that the user is opening per default.  Then the user just stares at the screen for an hour, thinking of a nice vacation or a drink.  Even if the data source changes the report will not change (unless you have auto page refresh enabled).  Only when the user interacts with the report again (switches pages, changes filters etc) will a new batch of direct queries be fired.

[space83]

thats mean user need to refresh the Report page in order to get the latest visual from data at on premise database. is it?

2 questions:

1. How about if gateway is offline?
2. What refresh frequency actually did in Parameter settings? (as pictures in 1st post)

[lbendlin]

Direct Query means that any user interaction (clicking on a report visual, changing a filter etc) will result in a new query against the data source.  Dashboards are not interactive, therefore you need to decide when to refresh the tiles, even with Direct Query .

[space83]

Hi @lbendlin,

Thats mean every time user view report, the data will populate from on premise database?