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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
manoj_0911
Helper IV
Helper IV

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.