topic Direct Query Caching Issue in Developer

Direct Query Caching Issue

Anonymous — Wed, 16 Sep 2020 17:43:04 GMT

We're doing some work with a newer system and have it setup to put data into a database that will be used to modify and update calculations inside some pbi visuals. The visuals will have:

A mass dataset of current and some historical data, and...
This newer 'settings' data

The mass dataset is backed by a Synapse on-demand instance and we have that set to import in. The 'settings' data is currently in a AzureSQL db, and we have that setup for direct query.

Our issue is that we need the 'settings' data to be as near real-time as possible, or have some way an end-user (ISV embedding scenario) can hit a button to get new data. What's currently happening is it gets queried and lays around in cache for an indeterminant amount of time. As I write this, I keep going to a test report I setup just pulling the current db table and it's still reading half hour old data, even though I setup Scheduled Data Cache Refresh to 15 minutes.

Any options?

Re: Direct Query Caching Issue

lbendlin — Fri, 18 Sep 2020 02:43:38 GMT

Have you played with the Auto Page refresh feature for Direct Query sources? That seems to discard the cache.

Re: Direct Query Caching Issue

V-lianl-msft — Wed, 23 Sep 2020 07:08:10 GMT

Hi @Anonymous ,

This blog may help you:

https://www.credera.com/insights/power-bi-directquery-automatically-refresh-less-than-every-15-minutes/

Or try to get the page refreshing while the report is in Full Screen mode:

Set the URL with the string “?chromeless=1” added to the end.
Use the Super Auto Refresh extension to schedule a refresh interval
Set your browser in Full Screen mode by pressing F11.

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

Re: Direct Query Caching Issue

Anonymous — Wed, 23 Sep 2020 15:25:26 GMT

Yeah I was messing around with those, but didn't know that the min limits are different on shared capacity with the workspace, that was throwing me off target. Thanks for the info. 🙂

Re: Direct Query Caching Issue

Anonymous — Wed, 23 Sep 2020 15:27:25 GMT

That's extremely helpful. That link is super interesting. We might implement a user button to refresh for now, that would certianly be a good fix for this until we can do change detection on paid capacity.

Thank you for the input, super helpful. 🙂