Operation timeout. Error code 403 in PowerBi Service

Google Cloud Data Analytics
,
1

Hello,

I started getting the following error a few days ago in PowerBi Service:

[BigQuery] (115) Operation timeout. Error happened in ExecuteRetriable. Error Code:403. Error Message:Quota exceeded: Your project exceeded quota for free query bytes scanned.

The account which contains the project has billing activated and also enabled the “Quota adjuster”.

I do not have a lot of experience with BigQuery so any help would be appreciated.

Thank you,

NK

2

The error “Quota exceeded: Your project exceeded quota for free query bytes scanned” indicates that your BigQuery project has gone beyond the free-tier limit of data processing allowed by queries in a given period. This limit exists even with billing enabled to prevent unexpected costs, offering a generous amount of free data processing each month that resets at the start of each billing cycle.

Possible Causes and Solutions

Recent Data Spike:

  • Investigate any significant increases in data volume within your BigQuery tables, which could lead to hitting quota limits.

  • Examine dashboard usage and query patterns in Power BI for any new reports, visualizations, or data refreshes that might be generating larger-than-expected queries.

Inefficient Queries:

  • Review and optimize the queries behind your Power BI reports. Inefficient queries that scan large amounts of data unnecessarily will consume more of your quota.

  • Optimization strategies include:

    • Utilizing WHERE clauses to narrow down data.

    • Selecting only necessary columns instead of using SELECT *.

    • Considering the implementation of materialized views for complex, frequently executed queries.

Quota Management:

  • Ensure you have requested an appropriate quota increase through the Google Cloud Console if your project’s needs exceed the default limits. Note that what was referred to as “Quota Adjuster” should more accurately be described as the quota management or request process in the Google Cloud Console.

BigQuery Slots for Predictable Costs:

  • For environments requiring predictable cost models and high-volume data processing, BigQuery Slots offer dedicated query processing capacity. For more information, consult the BigQuery documentation on purchasing and managing slots.

Steps to Troubleshoot

  1. Check Your Usage: Use the “Query History” feature in the Google Cloud Console under BigQuery to review the size and cost of your recent queries.

  2. Optimize Queries: Refactor PowerBI-generated queries where possible, adhering to best practices for query optimization as outlined in BigQuery’s documentation.

  3. Adjust Quotas: If necessary, adjust your project’s quotas through the Google Cloud Console to better align with your usage patterns.

  4. Monitor: Keep an eye on your query usage and costs, especially after implementing changes, to ensure you remain within your desired quota.

Additional Tips

  • Caching: Investigate if your Power BI dashboards can leverage caching mechanisms to store and reuse query results, reducing the frequency of identical queries.

  • Scheduling: Schedule data refreshes during off-peak hours to distribute resource usage more evenly and potentially stay within quota limits.

3

Hello,

Thanks a lot for your reply.

I cannot find the “Query history” section. Could you kindly tell me exactly where to find it?

Many thanks

4

Here’s how to find the “Query History” section in the Google Cloud Console for BigQuery:

  1. Go to the Google Cloud Console: Visit https://console.cloud.google.com/ and sign in to your Google Cloud account.

  2. Navigate to BigQuery:

    • In the navigation menu on the left side, locate the “BigQuery” entry. You might need to click “More Products” if it’s not immediately visible.

  3. Access Query History:

    • You’ll see two options in the BigQuery section:
      • Personal History: Contains queries you have run yourself within the Google Cloud Console.
      • Project History: Contains queries executed from all sources within your project, including Power BI, other applications, and manual queries.

  4. Examine Queries:

    • Click on either “Personal History” or “Project History” (depending on where you suspect the problematic query might be).
    • This will present a list of recent queries. You can see the following information:
      • Start time
      • User who ran the query
      • Query text itself
      • The Job ID
      • Data processed (this is crucial for checking against quotas)
      • Query status (success, failed, etc.)