Forum Discussion

amilpbi's avatar
amilpbi
Frequent Visitor
10 months ago
Solved

Big Qyery Direct Query Error

Google Analytics data was exported to Big Query that I intended to Visualize on power Bi becasue the stakeholder likes cross table selection feature of power bi,  Now this data has grown to a massiv...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    10 months ago

    Hi amilpbi 

    The message shown is a Quota Exceeded error in BigQuery, error code 403.
    Meaning: the queries Power BI generates in DirectQuery mode have exceeded the Free Tier quota or the quota defined for the BigQuery project (amount of bytes scanned per month).

    The cause:

    • DirectQuery does not pre-aggregate but instead fires queries directly to BigQuery for every report interaction.

    • With datasets of ~10GB or more, every filter or slice in Power BI scans a large amount of rows, which quickly burns through the scan quota.

    Root of the problem

    • Not a Power BI technical bug, but a BigQuery limitation when used with DirectQuery.

    • Free Tier gives only 1TB of query scans per month. With raw Google Analytics export tables, this is consumed very quickly.

    • Without advanced IAM permissions, it’s hard to set up ETL that aggregates the data beforehand.

    Possible solutions

    1. Switch from DirectQuery to Import:

      • Not feasible to import the full 10GB, but you can:

        • Build reduced tables in BigQuery (partitioned/aggregated).

        • Import only the required aggregations into Power BI instead of the raw events.

    2. Materialized Views or intermediate tables in BigQuery:

      • Create views that pre-calculate metrics like “avg time per user per module” or “articles clicked.”

      • Point Power BI to these smaller tables instead of the raw export.

    3. Partitioning & Clustering:

      • If staying on DirectQuery, partition by Date and cluster by User ID/Module.

      • This greatly reduces bytes scanned for each query.

    4. Dataflows or external ETL (Power BI Dataflows, Fabric, or other ETL tool):

      • Pull only the required fields from GA Export, not the entire dataset.

      • Store daily/monthly aggregations.

    5. Upgrade BigQuery Billing:

      • On Free Tier alone, there is no full solution. A paid project is required to avoid constant query failures.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
10 months ago

Hi amilpbi,

Thanks for sharing the details. Working with Google Analytics data in Big Query can get heavy very quickly, and Power BI needs some tuning to handle this well.

What’s happening here is: Importing all ~10GB of raw GA data into Power BI isn’t practical, since the model will be too large and refreshes will be slow. Switching to Direct Query means every visual and slicer sends live queries back to Big Query. That is why it quickly exceeded the free tier it is scanning a lot of data in the background.

You do not need to choose between “slow imports” and “expensive Direct Query.” A more efficient approach is to pre-aggregate the data in Big Query (e.g., create smaller tables with session-level or user-module metrics). You can then:
Import these smaller tables into Power BI for fast, interactive reports. Use Incremental Refresh so you only refresh recent data, not the entire history. If you still need detailed drilldowns occasionally, you can set up a composite model import for aggregates and Direct Query only for detail queries.

This way, you will be able to visualize individual user journeys (time spent on modules, articles clicked, etc.) while keeping costs and performance under control.

Refer these links:
1. https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery 
2. https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about 
3. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models 
4. https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced 
5. https://learn.microsoft.com/en-in/power-bi/connect-data/incremental-refresh-overview 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
10 months ago

Hi amilpbi,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    10 months ago

    Hi amilpbi,

    Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

    Thank you.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      10 months ago

      Hi amilpbi,

      Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

      Thank you.