Optimising a Slow Power Bl Report
Performance analysis using DAX Studio
Overview:
A Power BI report suffered from performance bottlenecks. Visual rendering and data filtering took an unacceptable 6-13 seconds, impacting user experience and potentially straining Power BI resources. The culprit? A complex DAX query calculating relative gross margin. The article describes how the bottleneck was identified using the Vertipaq Analyzer and the solution.
Challenge:
A Power Bl report designed to categorize customers based on year-on-year revenue growth and relative gross margin performance suffered from significant performance issues. Rendering visuals and filtering data took between 6 and 13 seconds, impacting user experience and potentially straining Power Bl service resources, especially with concurrent users. The core issue was identified as a complex DAX query calculating relative gross margin performance.
Analysis:
Performance analysis using DAX Studio revealed the following:
The visual displaying the intersection of year-on-year revenue growth and relative gross margin performance was the primary bottleneck.
Over 95% of processing time was attributed to the formula engine, with over 60 queries executed per visual render.
The formula engine was processing data in subsets of 2,000 rows for over 4 million rows, performing complex calculations repeatedly.
Three measures creating variable tables with calculated columns and iterated sums were identified as the root cause of the complex calculations. These were then used in a ratio calculation.
Solution:
While reducing the data volume was considered, the business requirement to display the entire book of accounts prevented this. The optimal solution was to offload the complex calculations to the database. This allowed the storage engine to handle the heavy lifting, significantly reducing the burden on the formula engine.
Results:
By pre-calculating the relative gross margin performance in the database, the report's performance was dramatically improved. Rendering visuals and filtering data became significantly faster, enhancing user experience and minimizing potential strain on Power Bl service resources. This also reduced the number of queries and processing time required, improving overall efficiency.
Key Takeaways:
Complex DAX queries, especially those involving iterated calculations and
variable tables, can severely impact Power Bl report performance.
Offloading complex calculations to the database can significantly improve
performance by leveraging the storage engine's capabilities.
Thorough performance analysis using tools like DAX Studio is crucial for
identifying bottlenecks and guiding optimization efforts.
