Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
In desktop version and with all tables set as DirectQuery, how do I prevent caching data for each filtering? I've noticed that when doing exact the same filtering many times ("that genererates the exact same where-clause"), PowerBI uses the cache instead of a new database query.
We try to use PowerBI as an online query tool. Each time a user filters data, we want the latest data from the db, not "used, old" data from the cache .
Our SQL-server database has kind of those tables:
Order (OrderNo PK, CustomerId FK, ProductId FK, OrderDate, Amount)
Customer (CustromerId PK, Name)
Product (ProductId PK, Name).
If I use the tables as DirecQuery in PowerBI and filter the orders first with Date='2021-08-19', Customer.Name='A', then with Date='2021-08-19', Customer.Name='B' and then again Date='2021-08-19', Customer.Name='A'.
When I examine the generated SQL in Performance Analyzer window, no SQL query is generated the third time, when an old "where-clause" is reused. This should mean changes in Db since last first filtering is not shown. Or is the cache time-limited so after a certain time a new query is generated?
Am I right? Can it be changed by a setting?
/Jerome
Solved! Go to Solution.
If you need 100% fresh data then you should modify your query by adding a random, meaningless filter.
Let's say you know value A is never more than 50. Add a filter for A<100+RAND().
You can disable query caching in the dataset settings on the service.
Thank's but need more help:
I use only the desktop version. I can't find the setting you show me. I find in PowerQuery only the two table properties Activate import and Include in report refresh.
/J
On the desktop you can manually clear both caches and can restrict the permitted size. File...Options and Settings... Options.
Hi!
Found it, and I tried to set cache size to 0 (despite the warning in the system) to force system to make a new DirectQuery select each time, but it it became impossible to Refresh any data "maximum cahce size exceeded".
I did not found a feature for "clear cache older than x seconds" (for me 0 seconds :)), is there?
Maybe we must live with this: If one need 100% fresh data, one must use Refresh button? A user can of course not remember if a certain filter has been used (on now cached) since opened the report.
If you need 100% fresh data then you should modify your query by adding a random, meaningless filter.
Let's say you know value A is never more than 50. Add a filter for A<100+RAND().
Hi @jrmkjrm ,
Automatic Page Refresh should meet your needs.
For more details please check the document.
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-automatic-page-refresh
Best Regards,
Jay
better, but...
If i check for new max(OrderNo) each minute, my current page with a sum of all orders updates correct each minute (real nice!!!). But if I then (re-)filter on the customer with the new order, the old (cached) customer sum is shown. Only the "on screen" filtering was updatet after a minut. With the customer with a new order selected no new page refresh is done after an extra minute (cause no new OrderNo) so I must manually "Refresh" data anyway, to see the new customer sum.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
102 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |