Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
manoj_0911
Advocate II
Advocate II

Seeking Advice: Optimizing Performance with Large Datasets and Complex Joins in Power BI

Hello Power BI Community,

 

As I transition from Tableau to Power BI, I'm encountering challenges related to optimizing performance, especially when working with large datasets and complex joins. In Tableau, I've explored various strategies and techniques to enhance performance, considering the impact of join types on query times.

 

Could you please share insights or best practices for optimizing performance in Power BI under similar circumstances? Specifically, I'm interested in techniques to improve performance when dealing with large datasets and complex join operations.

 

Your expertise and guidance would be greatly appreciated!

 

Thank you for your assistance.

 

Best regards,
Manoj Prabhakar

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@manoj_0911 ,

1. Use star Schema

2. Use only numeric keys to join

3. Do not use bi-directional joins 

4. Prefer measures whenever possible.

5. For complex calculation check if part of that can be moved as calculated columns

6. Try not to use related in measure unless it is most needed

7. Prefer Window, Offset, Index, Rank and Rownumber, wherever possible

8. If measures are too complex, try to break the calculation and move part of it a calculated column

 

others

Best Practices
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Rel...
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-sho...
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...
direct query performance
https://insightsquest.com/2020/05/25/simple-directquery-performance-tips/
https://locusit.com/performance-management/power-bi-directquery-performance-improvements/
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

View solution in original post

v-jialongy-msft
Community Support
Community Support

Hi @manoj_0911 

 

Good answer! @amitchandak 

 

I have some suggestions that I would like to add:

Optimize your visualizations by limiting the number of visuals per page, using slicers sparingly, choosing the right chart types, and avoiding unnecessary formatting and interactions. Too many visuals can slow down the data processing and rendering, Slicers are useful for filtering data, but they can also generate many queries and affect performance, so use them only when necessary. Chart types can also impact performance, as some are more complex and resource-intensive than others, such as maps, gauges, and waterfall charts. Formatting and interactions can also add to the complexity and load time of the visuals, so avoid using them unless they add value to the report.

 

Optimize your environment by using separate gateways for live connections and scheduled refreshes, configuring the refresh frequency and retention policy, and monitoring the capacity and network performance. Using the same gateway for both live connections and scheduled refreshes can overload it and slow down the performance, so it is better to use separate gateways for each function. Refresh frequency and retention policy can also affect the performance, as they determine how often the data is updated and how long it is stored. You can adjust these settings according to your needs and data source characteristics. Capacity and network performance can also influence the performance, as they determine the resources and bandwidth available for Power BI. You can monitor these factors using the Power BI admin portal and the Power BI Premium metrics app.

 

 

 

 

Best Regards,

Jayleny

 

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

View solution in original post

2 REPLIES 2
v-jialongy-msft
Community Support
Community Support

Hi @manoj_0911 

 

Good answer! @amitchandak 

 

I have some suggestions that I would like to add:

Optimize your visualizations by limiting the number of visuals per page, using slicers sparingly, choosing the right chart types, and avoiding unnecessary formatting and interactions. Too many visuals can slow down the data processing and rendering, Slicers are useful for filtering data, but they can also generate many queries and affect performance, so use them only when necessary. Chart types can also impact performance, as some are more complex and resource-intensive than others, such as maps, gauges, and waterfall charts. Formatting and interactions can also add to the complexity and load time of the visuals, so avoid using them unless they add value to the report.

 

Optimize your environment by using separate gateways for live connections and scheduled refreshes, configuring the refresh frequency and retention policy, and monitoring the capacity and network performance. Using the same gateway for both live connections and scheduled refreshes can overload it and slow down the performance, so it is better to use separate gateways for each function. Refresh frequency and retention policy can also affect the performance, as they determine how often the data is updated and how long it is stored. You can adjust these settings according to your needs and data source characteristics. Capacity and network performance can also influence the performance, as they determine the resources and bandwidth available for Power BI. You can monitor these factors using the Power BI admin portal and the Power BI Premium metrics app.

 

 

 

 

Best Regards,

Jayleny

 

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

amitchandak
Super User
Super User

@manoj_0911 ,

1. Use star Schema

2. Use only numeric keys to join

3. Do not use bi-directional joins 

4. Prefer measures whenever possible.

5. For complex calculation check if part of that can be moved as calculated columns

6. Try not to use related in measure unless it is most needed

7. Prefer Window, Offset, Index, Rank and Rownumber, wherever possible

8. If measures are too complex, try to break the calculation and move part of it a calculated column

 

others

Best Practices
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Rel...
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-sho...
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...
direct query performance
https://insightsquest.com/2020/05/25/simple-directquery-performance-tips/
https://locusit.com/performance-management/power-bi-directquery-performance-improvements/
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.