Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

GA4 User Aggregation in BigQuery Without Over-Counting in Power BI

Hi all, I’m building a Power BI dashboard using GA4 data exported to BigQuery, and I’m struggling to accurately calculate the number of unique users over different time periods. Since I’m working w...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous ,

Thanks for Sahir_Maharaj's reply!
And Anonymous , you can try to use partitioning and clustering on your BigQuery tables to speed up queries.
For example, you can partition your table by date:

CREATE TABLE `your_project.your_dataset.your_table`
PARTITION BY date
CLUSTER BY user_id AS
SELECT
  date,
  user_id,
  -- other columns
FROM
  `your_project.your_dataset.your_source_table`


And if your dataset is too large to refresh entirely each time, consider using incremental refresh in Power BI. This way, only new data is loaded, reducing the refresh time and resource usage.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous,

    Thanks for the input, but I already clastered and partitioned the table in Big Query, there are no issues in the query execution in BQ. The issue is in Power BI side, I tryed to set up incremental refresh with my semantic model using the Google BQ connector, but it doesn't seem to work, because the refresh times are very high and from time to time the refresh fails. 
    I also tryed to use dataflow, and incremental refresh. The incremental on the dataflow side works great, but the refresh of the semantic model connected to dataflow has the same issue as before: huge incremental refresh time.

     

    Hope you can give me some other imputs.

    Best,

    Riccardo