Direct Query Caching Issue

[Anonymous]

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?

[V-lianl-msft · Accepted solution]

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:

1. Set the URL with the string “?chromeless=1” added to the end.
2. Use the Super Auto Refresh extension to schedule a refresh interval
3. 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.

[Anonymous]

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. 🙂 

[lbendlin]

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

[Anonymous]

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. 🙂