Forum Discussion
Referenced Query and Caching question - explanation/confirmation required
Although, no response has been issued since my last post, I'm hoping this new info may prompt a response.
In my previous messages, in this thread, I've questioned how when I refresh my data - why does the source database get queried (hit) multiple times when I've placed a Table.Buffer() on the only query that actually connects to the source. I'm still wishing to understand this.
However, I would like to add something further...
In PQ, when I make a change to the single query, the only query, that connects to the source database - why is the source database being queried (hit) multiple times. I'm not performing a full refresh where all queries are refreshed (still need to understand why Table.Buffer() isn't doing it's job of hitting the source database just the once), instead this is a change to the single query within PQ.
My understanding is if I've made a change to the SQL statement in one query then only that query is updated/refreshed (you get those yellow dots swooping across the top of the query editor screen whilst it's updating). I'm not clicking of refresh preview, or refresh all, or close and apply. I'm simply making a change to the first step in the query.
Is my understanding correct on this?
If it is correct, then why am I seeing multiple hits to the source? It seems all the other queries that reference the single query, the only quey, query that connects to the source database, are being refreshed too? I know this as I can see the source database being queried multple times, vis SSMS, with the SQL statement that resides in the single query.
Hope this makes sense.
Please help with my understanding?!
Thanks.
I cannot help, but just wanted to point out that the issue described above causes a data accuracy issue as well. My use case is that I want to return the top N values from a SQL query, but selected randomly server-side. Then, similar to as described above, I split the data into several different parts and perform transformations, before merging them back together again. The problem this causes is that, because each PQ table seems to query SQL server again, rather than using the first query as a true reference, I get different observations randomly selected in each table! Would love a response from Power BI team as to whether I'm doing something wrong, or this is expected behaviour.