Forum Discussion
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
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-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/td-p/712871
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-fast-and/m-p/712871Hi 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.
2 Replies
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-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/td-p/712871
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-fast-and/m-p/712871Hi 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.