Issues with the performance of the report.

[Tech02]

I am currently working with a Power BI report that includes a table with 21 million rows sourced from BigQuery. The table is connected via DirectQuery mode, but we are experiencing significant performance issues with the report.

Given the size of the dataset, I am considering switching from DirectQuery to Import mode to see if it might improve performance. However, before making any changes, I would like to get expert opinion on whether switching to Import mode would likely result in a performance improvement. Additionally, if Import mode may not be beneficial, could you please advise on other strategies or best practices for improving performance with such a large dataset?

[suparnababu8 · Accepted solution]

Hi @Tech02 

I think you can use Composite Mode  which is combination of import and direct query. Let me explain how it will works.
Let's assume you have 10 Dim tables and 2 Fact tables. Now you can set the storage mode of Dim tables as Dual mode and keep you fact tables as direct query storage.

In this case, if users will do any slice and dice, based on the selections tables will act accordingly and will improve the performance. 

If you need more information pls go through following documentation.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

https://learn.microsoft.com/en-us/power-bi/connect-data/service-dataset-modes-understand

Thanks!

[anmolmalviya05 · Accepted solution]

Hi , Hope you are doing Good!

For your Power BI report with 21 million rows from BigQuery, switching from DirectQuery to Import mode will likely improve performance. Import mode loads data into memory, eliminating the need for real-time queries, which significantly speeds up large datasets. However, memory constraints could be an issue, so ensure your dataset can fit in memory.

Alternatively, Direct Lake mode in Microsoft Fabric offers a middle ground. It allows Power BI to directly access data from OneLake without querying the source or loading it into memory, offering performance benefits without the heavy memory load.

Other Performance Optimization Strategies:

• Reduce Columns: Import only necessary columns.
• Pre-Aggregate: Use summary tables.
• Optimize Source: Ensure BigQuery uses partitioning and clustering.
• Query Folding: Push transformations to the source.
• Hybrid Mode: Mix Import for historical data and DirectQuery for real-time data.

  In summary, Import mode is likely to improve performance, but Direct Lake mode in Microsoft Fabric could provide an optimal balance for large datasets.
  
  

  Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

  Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

@Tech02

[suparnababu8 · Accepted solution]

Hi @Tech02 

I think you can use Composite Mode  which is combination of import and direct query. Let me explain how it will works.
Let's assume you have 10 Dim tables and 2 Fact tables. Now you can set the storage mode of Dim tables as Dual mode and keep you fact tables as direct query storage.

In this case, if users will do any slice and dice, based on the selections tables will act accordingly and will improve the performance. 

If you need more information pls go through following documentation.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

https://learn.microsoft.com/en-us/power-bi/connect-data/service-dataset-modes-understand

Thanks!

[Anonymous]

Hi, @Tech02 

You can also refine your data model by removing unnecessary columns and tables and using aggregations to reduce the amount of data processed.
Use performance tuning techniques such as indexing in the source database, optimizing DAX queries, and leveraging query folding in Power Query. You can check the following link:

Optimization guide for Power BI - Power BI | Microsoft Learn

Optimize a model for performance in Power BI - Training | Microsoft Learn

Or consider using a composite model, which allows you to combine import and DirectQuery modes. This helps balance performance with real-time data needs.

Semantic model modes in the Power BI service - Power BI | Microsoft Learn

You can also learn about import mode and DirectQuery mode through the documentation below

Power BI Import vs Direct Query: Which is Better & Why? – Master Data Skills + AI (enterprisedna.co)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Anonymous]

Yes, Import mode is faster than any other Mode. The use of DirectQuery is to allow more current data into the report. The report has to be refreshed when new data is in the table