Big Qyery Direct Query Error

[amilpbi]

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 massive size ~10GB and keeps growing. making it impossible to import
and direct query blew through the free tier we had (still unsure about this error).
How to efficeintly use massive data sets like this in power bi?

The end goal is to visualize individual user journey like avg time spent on a single module, time spent on news module, what news article was clicked etc?' (yes indivual users not the aggregated user time)

How to solve this problem, do not have necessary IAM roles to make better ETL for BIQ QUERY, or necessary know how to make if optimized in power Bi 

[v-kpoloju-msft]

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]

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]

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]

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.

[v-kpoloju-msft]

Hi @amilpbi,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Ritaf1983, for her inputs on this thread.

Has your issue been resolved? If the response provided by the community member @Ritaf1983, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[Ritaf1983 · Accepted solution]

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

[amilpbi]

Aggregation would remove the granularity of users.... essentially what the stakeholder hopes to see is an individual user's journey... avg time is directly visible on Google Analytics but that was not completely helpful for them , hence they hope to see the individual level users journey given "the app" has multiple modules like news, Chat, govt schemes, jobs module for both advertising services and offering jobs. 

What they hope to visualise is User retention, user engagement, most clicked news by their area of interest, most applied jobs by State, gender, district, age group, income, education, occupation (Slicers) etc.

Its a govt app, so the slicers must stay and they want the insights real time on a PPU license and GCP (which is maintained by some third party business for the govt, plus communication with GCP controllers is non-existent)

[Ritaf1983]

Hi again @amilpbi 

In this case, there is really no way to “bypass” aggregation – if the goal is to see an individual user’s journey with slicers by gender, age, education, etc., it means working with extremely large volumes of data and very high resource consumption.

With DirectQuery against BigQuery this is almost unfeasible:

• Every filter/slicer sends a heavy query to GCP.

• If the data is stored at the individual user level, the tables become so large that you hit quota errors and/or very long response times.

• Power BI with PPU is not designed for real-time analysis at this level of granularity.

In practice, there are two main alternatives:

1. Build a processing layer in GCP – create aggregated tables at the level of interest (e.g., “daily usage by age/gender/state”) and connect Power BI to these instead of the raw clickstream.

2. Use a dedicated behavioral analytics tool (such as GA4 or Mixpanel) that is built to capture and analyze user journeys at the individual level.

Power BI is not meant to be a clickstream analysis tool at individual user granularity but rather a BI tool that works best with aggregated data. If the requirement is both “real-time” and “per-user journey,” it conflicts with the limitations of DirectQuery and PPU.

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

[amilpbi]

The Data is exported from GA4 as I understand. however the clients are unable to use it like power bi "select one value, rest of the visuals get sliced based on that summation ease", that's where i come into picture. 
These are GA4 logs exported to Power bI then somehow shown in power bi based on modules and other user metrics.
That's also the initial phase, I'm also tasked with making advanced ML concepts to show userbase grouping, recommending govt schemes to user based on their area of interest. 
They have not provided any ETL tool or databricks to do it either to somehow implement on this massive chunk and then visualise that data again