Forum Discussion
Performance degradations for classic PBI Matrix visual, custom Matrix visual and Analyze in Excel
Hi there!
Over the last 3-4 months, we have been struggling with persistent performance issues with classic PBI Matrix visuals, custom Matrix visuals and Analyze in Excel for same semantic models - which worked without any issues until the end of 2023. Has anyone obeserved similar issues?
The issues are very random:
1) PBI Matrix visuals:
- In PBI Service a matrix visual with less than 100 data records produces a "Visual has exceeed the available resources" as soon as we make an additional drill-down on level 4 or 5 (until level 3 it does work).
- The exact same matrix visual with same drill-down does work for another filter selection, e.g. with more than 2.500 data records.
- A similar test on another data constellation produced the same "exceeded the available resources data" also in Power BI Desktop, even if we applied a Top 1 filter and therewith only tried to display one row! (according to official MS docu a recommended data reduction strategy: Apply data point limits and strategies by visual type - Power BI | Microsoft Learn)
- When we reproduce the issue in Power BI Desktop it sometimes crashes with a "WebView2" error
- Our test with a Premium per User license in a Premium per User Workspace did not produce the error message, but ended up with a very bad performance
2) Custom Matrix visuals (e.g. from Zebra BI):
- Custom Matrix Charts or Tables have extremely long loading times and in Power BI Service are often crahsing with a timeout notification, even if a Top 10 filter is applied. The issues are triggered when "bigger" dimensions are applied as Group into the Matrix. We were informed that this is affecting all custom Matrix visuals.
- Also in Power BI Desktop loading times are extremely long and Power BI Desktop is hanging up very often, producing a "WebView2" error.
- Strangely, the long loading tiems are not reflected in the Performance Analyzer: There is a lagging between response from Performance Analyzer and actual loading time.
- For this data constellation (e.g. Top 30 Customers on level one) we were able to reproduce the Use case with a classic Power BI Matrix (instead custom matrix visual) without any performance issues -> so it seems that custom matrix visuals are much more affected (performance issues triggered faster)
- It was already tested and proven that this issue also affects other customers
3.) Analyze in Excel:
- Performance issues are very random occuring even for small data selections (less than 80 data records) and only 2-3 levels -> producing a "Ressources exceeded" error message
- the issues are depending on the file: A file downloaded today might work for the exact same query and same connection string, but the file downloaded yesterday (with same query and same connection string) did not and work and still does not work today. Tomorrow the newly downloaded file for the exact same query and same connection string might not work, but the file from yesterday (with exact same query and same connection string) still does work.
- In other cases we have Users that created Analyze in Excel Reports which were uses on a regular basis during the last year, without any issues and now end up out of a sudden with "Ressources exceeded" issues for the exact same Analyze in Excel Report.
- We tested to download an Analyze in Excel file from the same semantic model from a Power BI Premium Workspace, created with a Power BI Premium per User account -> in this case we do not receive the "ressources exceeded" error message, but the Query run over 60 minuts without delivering a response.
- Latest Analyze in Excel updates and Excel version 64-bit were installed, but could not solve the issue
We are convinced that these 3 issues are related, since all issues are referring to matrix visualizations.
Our semantic models consists of:
- 1 or 2 fact tables
- modelled in star schema only
- with only 1:N relations
Have you observed similar issues?
Do you have any idea how we can solve these issues? We already have tickets open at Microsoft.
Thanks for your support!
4 Replies
It sounds like you're experiencing significant performance issues with classic Power BI Matrix visuals, custom Matrix visuals, and Analyze in Excel functionality for your semantic models. Here are some thoughts and potential solutions to address these issues:
1. **Performance Tuning**:
- Review the performance of your semantic model and consider optimizing it for better performance. This may involve optimizing DAX measures, reducing the complexity of relationships, and optimizing the data model structure.
- Use the Performance Analyzer in Power BI Desktop to identify bottlenecks and optimize query performance.2. **Review Data Volume**:
- Analyze the volume of data being queried by your Matrix visuals and Analyze in Excel reports. Large datasets can significantly impact performance, especially when using visuals with many data points.
- Consider implementing data reduction strategies such as applying filters, limiting the number of rows displayed, or aggregating data where possible.3. **Evaluate Custom Visuals**:
- Custom Matrix visuals, especially those from third-party providers like Zebra BI, may have different performance characteristics compared to standard Power BI visuals.
- Reach out to the vendor or developer of the custom visuals to inquire about any performance optimizations or updates that may be available.4. **Analyze in Excel Troubleshooting**:
- Investigate the specific queries being generated by Analyze in Excel reports and identify any patterns or commonalities that may be causing performance issues.
- Consider reviewing and optimizing the data source connection settings, including query folding and data load options, to improve performance.5. **Engage Microsoft Support**:
- Continue working with Microsoft Support to troubleshoot and resolve the issues you're experiencing. Provide them with detailed information, including sample files, query logs, and any error messages encountered, to help expedite the resolution process.6. **Consider Premium Capacities**:
- Premium capacities offer enhanced performance and scalability compared to shared capacities. If performance is critical to your organization, consider leveraging Premium capacities to host your Power BI workloads.7. **Stay Informed**:
- Monitor the Power BI community forums, blogs, and release notes for updates, announcements, and best practices related to performance optimization and troubleshooting.By following these steps and collaborating with Microsoft Support, you should be able to identify and address the performance issues affecting your Matrix visuals and Analyze in Excel reports. Remember to prioritize optimization efforts based on the specific requirements and constraints of your organization.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!Thanks johnbasha33, these are the tips we already received from Microsoft support. We checked and implemented all of them as far as possible. We also considered switching to Premium, but our tests with a Premium per User license were not successful. In addition, we had no such issues with the same (unchanged) semantic models until end of last year - so it seems that there has been a change in Microsofts underlying architecture, since all types of Matrix visuals are affected. This is why we would be interested to learn if there are others out there in the community facing similar issues?
Hi Kerstin111 ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.
Best Regards,
Neeko Tang
No, unfortunately my problem has not been solved yet.
