Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I implemented a incremental refresh in a report that gets a lot of data from Google BigQuery. The issue is that each incremental refresh makes duplicated queries, and one of them is pretty expensive.
As Chris Webb's described in his article, power BI makes two queries in order to import data from a SQL database; one that returns a limited number of rows just to discover the table schema and another to get the actual data.
In many SQL servers, this only compromise performance, but in BigQuery, where the charge is made for the amount of data processed, regardless of the number of resulting rows, this first query is costing us a lot, because query folding is not taking place.
here I have some experiments with smaller datasets to show what is happening:
Notice that the "discovery query" above is processing 121.66 MB to return just one row, while the "data query" below, where the query folding is taking place, is processing just 7.35 MB.
I have implemented Chris solution modifying my M code, but BigQuery are still receiving duplicated queries. Is there any way to avoid the "discovery query"??
Solved! Go to Solution.
Guys, it turns out that its not possible to avoid those duplicate queries, but there is a way to clusterize BigQuery tables to reduce the amount of processed data!
For more details check this post: https://stackoverflow.com/questions/68956799/how-to-avoid-power-bi-incremental-refresh-duplicated-qu...
Hi Vitor, we've had the same issue with discovery queries being issued to big query, even with the "new" Bigquery native connector. Have you found an elegant workaround ?
Guys, it turns out that its not possible to avoid those duplicate queries, but there is a way to clusterize BigQuery tables to reduce the amount of processed data!
For more details check this post: https://stackoverflow.com/questions/68956799/how-to-avoid-power-bi-incremental-refresh-duplicated-qu...
Hi, @VitorAzevedo
You can see if these related tutorials can help.
incremental-refresh-in-powerbi-with-data-from-bigquery
partitioned-tables-bigquery-power-bi-incremental-daniel-lennartsson
Best Regards,
Community Support Team _ Eason
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |