Slow data loading time on PBI with Bigquery GCP

[Rinn]

Hello,

I created a Power BI report using BigQuery as the data source in import mode, and I published it on my company's Power BI Services. I find that the refresh time for the semantic model is very long.

In my case, I use a table with 30 million rows and 40 columns (no Power Query transformation steps), and it takes more than 5 hours to refresh the data, whether it's on Desktop or online.

I would like to hear your feedback and know how long it takes on your side to refresh data and how many rows you're working with, in order to know if there is an issue with PBI Services in my company.

Best Regards.

[Deku · Accepted solution]

If you set the year to a datetime, say the first day of the year, then you can use incremental refresh. Then only need to load all once, and just this year for every other refresh 

[v-kpoloju-msft · Accepted solution]

Hi @Rinn,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @rohit1991, @Deku, for your inputs on this issue.

Thank you for providing the details. Working with large datasets like yours (30M rows × 40 columns) in Power BI Import mode with Big Query can indeed present performance issues.

Since the refresh is taking over 5 hours on both Desktop and in the Power BI Service, the problem is likely due to the volume of data being retrieved and the way Big Query handles full table scans during each refresh.

Use Views in Big Query: Create a SQL view that filters or pre-aggregates your data to reduce the volume imported into Power BI.

Implement Incremental Refresh: This is especially effective for large datasets. It allows Power BI to only refresh new or changed data rather than pulling the entire dataset each time. You'll need a date/timestamp column and to configure RangeStart/RangeEnd parameters.
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Big Query Table Optimization:

• Use partitioned tables (on date fields).
• Use clustered tables to speed up query filtering.
• Avoid SELECT * – only import necessary columns.

Review Network and Gateway Performance (if applicable): If you're using an on-premises gateway, ensure it’s up to date and hosted on a high-performance machine.

Kindly refer to the below link for better understanding:
Google BigQuery connector - Power Query | Microsoft Learn
Data refresh in Power BI - Power BI | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Rinn,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Rinn,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @Rinn,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[rohit1991]

Hi @Rinn ,
Thanks for sharing your experience. Refreshing a dataset of 30 million rows and 40 columns from BigQuery in import mode taking over 5 hours does seem unusually long, especially given that there are no Power Query transformations involved.

While refresh times can vary based on network latency, gateway performance, and BigQuery's response time, such a delay might point to a bottleneck in one of these areas. In general, others working with similar volumes (tens of millions of rows) often see refresh times ranging from 30 minutes to 2 hours, depending on optimization. You might want to check if your gateway is up to date and whether any throttling or regional restrictions are impacting performance. 

Also, consider partitioning or aggregating data in BigQuery before loading into Power BI, or using DirectQuery for better control over performance. Reaching out to others for benchmark comparisons is a great idea, but you might also want to monitor query performance directly within BigQuery to pinpoint where the delays are occurring.

[Deku]

That does seem slow. Can you use incremental refresh so partitons load in parallel. There are likely some settings you can make in big query to improve. If you have fabric you can mirror the data to help as well 

[Rinn]

Thanks for your reply, 

About the incremental refresh, my table does not have any historical data, it's some kind of modelization and I have to reload entirely for each refresh, however, it contains a year column, can i still do that ?

[Deku · Accepted solution]

If you set the year to a datetime, say the first day of the year, then you can use incremental refresh. Then only need to load all once, and just this year for every other refresh