Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mim
Advocate V
Advocate V

Control which query go to BigQuery

I am connecting to Bigquery, all good, but for some reason everytime PowerBI send two queries instead of one for example.

 

select x,y from dd LIMIT 1000 OFFSET 0

select x,y from dd

 

how I stop PowerBI from issuing the first query with Limit 1000 , as BigQuery is usage based, the cost does add up.

 

regards

Mim

1 ACCEPTED SOLUTION
tonmcg
Resolver II
Resolver II

The short answer is: you cannot. The longer answer is: you can set configurations in Power BI and develop your queries in a way that increase the likelihood that your data source API is only called once.

 

The reason is that Power Query's persistent cache takes some time to load the data from an initial query. Unfortunately, other queries that rely on the results of that query may set off their own separate processes before the persistent cache is finished storing the data. This exceprt from Chris Webb's blog explains what's happening:

 

Power Query’s persistent cache (which stores data on disk during a particular refresh) is updated via a background thread. And separate evaluations (i.e. separate Microsoft.Mashup.Container.*.exe processes) running at the same time are not coordinated; when evaluation A is accessing the persistent cache (including updating it), this doesn’t block evaluation B from accessing the cache. This means that even when using a shared persistent cache, PQ can potentially end up requesting the same data twice. It depends on the timing of the various requests.

 

Read the whole post to understand more:

https://blog.crossjoin.co.uk/2019/03/26/power-bi-caching-parallelism-and-power-query-refresh-perform...

View solution in original post

4 REPLIES 4
tonmcg
Resolver II
Resolver II

The short answer is: you cannot. The longer answer is: you can set configurations in Power BI and develop your queries in a way that increase the likelihood that your data source API is only called once.

 

The reason is that Power Query's persistent cache takes some time to load the data from an initial query. Unfortunately, other queries that rely on the results of that query may set off their own separate processes before the persistent cache is finished storing the data. This exceprt from Chris Webb's blog explains what's happening:

 

Power Query’s persistent cache (which stores data on disk during a particular refresh) is updated via a background thread. And separate evaluations (i.e. separate Microsoft.Mashup.Container.*.exe processes) running at the same time are not coordinated; when evaluation A is accessing the persistent cache (including updating it), this doesn’t block evaluation B from accessing the cache. This means that even when using a shared persistent cache, PQ can potentially end up requesting the same data twice. It depends on the timing of the various requests.

 

Read the whole post to understand more:

https://blog.crossjoin.co.uk/2019/03/26/power-bi-caching-parallelism-and-power-query-refresh-perform...

If you use the ODBC connector (instead of the official Bigquery connector) it will do the same, but without the "LIMIT 1000 OFFSET 0" clause. Then, even though there are two identical requests being made to the engine,  Bigquery will only charge you once since it'll run it from cache the second time.

Hope this helps!

v-juanli-msft
Community Support
Community Support

Hi @mim 

Which connector do you use? Bigquery or ODBC?

What is your queries  in Advanced editor?

As searched, there are some limitation of big query api.

https://cloud.google.com/bigquery/quotas

Destination tables in a query job are subject to the limit of 1,000 updates per table per day. Destination table updates include append operations and overwrite operations performed by a query using the console, the classic BigQuery web UI, the bq command-line tool, or by calling the jobs.query and query-type jobs.insert API methods.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mariusz
Community Champion
Community Champion

Hi @mim 

 

Don't know if that is exactly what you after but, watch this video.

https://www.youtube.com/watch?v=3uKNNZqBIkg&t=398s

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors