Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Direct Query Caching Issue

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?

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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