Reports were once the standard way of presenting and receiving data analysis, but dashboards are now more commonplace and with good reason. Tools like Power BI make creating rich, deep, and full-featured dashboards accessible to all, but what happens when your dashboard is slow? What can you do to improve performance without administrator permissions? If you do nothing, you risk losing your audience to the dreaded "spinning wheel."
In this article, we'll share a couple tuning tools and tips to help improve your dashboard's performance.
Performance Analyzer: Power BI's not so hidden secret
Power BI Desktop is a powerful, flexible, and scalable reporting tool that puts the data back in the hands of the business. It is easy to use, intuitive, drag-n-drop, WYSIWYG (What you see is what you get) style report and dashboard creation experience. It will also grow with your team's technical needs enabling in-report scripting using the native Data Analysis Expression (DAX) scripting language, similar to Excel & VB, along with some great Enterprise ready functionality as part of the Microsoft Power Platform apps.
One of the best (and semi-hidden) features of Power BI is the Performance Analyzer. If you've ever struggled with a slow performing Power BI Report or Dashboard, this tool is your new reporting BFF (best friend forever). The tool can be found in Power BI Desktop under the View menu.
Evaluating Dashboards with Performance Analyzer:
Open the report or dashboard in question. For best results, create a BLANK tab
Save the dashboard (with the BLANK tab selected) and then Close it out.
Open the dashboard again. NOTE: The Dashboard should open to the BLANK tab showing no data.
Navigate to the View menu and click on Performance Analyzer to expose the menu
Click "Start Recording." Select each of the other tabs and watch the Performance Analyzer log the refresh details
Once all refresh details are captured, then start your analysis
The information provided will include a view into the initial query refresh times, visual refresh times (i.e. Slicers, Matrix, Tables, etc.), and any associated DAX script/query refresh times. Each of these will display a refresh duration in milliseconds (ms) so you can prioritize your research based on the slower performing components. Expanding each section will reveal further details; use the "Copy Query" option to drill down into additional details about each section, visual, or feature of your Dashboard.
DAX Studio: An External Tool to Dive Deeper
While Performance Analyzer is a great tool, it might not give you the full picture of your dashboard's performance, especially if you're using complex Direct Query sources or an integrated Power BI Data Model. To go deeper, you might need a third-party tool called DAX Studio, which allows for better telemetry into the query performance. DAX Studio works with the PowerQuery, which is available on Power Platform applications such as Power BI and Microsoft Excel.
To start, click HERE to learn more about the delivered functionality and to download the latest version.
Once downloaded and installed, you'll find DAX Studio added to both your Power BI Desktop and Power Query applications; you will be prompted to enable this new functionality the next time you open Power BI or Microsoft Excel. To open and use DAX Studio in Power BI, you'll need to navigate to the newly installed "External Tools" menu.
Once opened, you'll be presented with a new window and lots of new buttons. We won't cover each of these, but we recommend reading about them here on the DAX Studio's Features page to maximize your usage and understanding of the tool.
We will, however, discuss a couple that we use all the time:
Query Plan - If you're familiar with a SQL Query Plan, then this will be very comfortable for you. This feature allows you to take the Query you copied from your Performance Analyzer and evaluate it's individual performance as executed by the source system/database. This content can be very technical, so it is best utilized by folks with solid understandings of Database processing and optimization.
Server Timings - Similar to the Query Plan option, this functionality will display a granular breakdown of your query performance and run-time statistics, including a chart showing the amount of time spent by Power BI's Formula & Storage Engines. These will indicate how much time you're Dashboard is spending accessing data and/or processing the visuals.
Connect - The Connect option is a wonderful feature for anyone using a Power BI Data Model or Tabular Server as it allows you to directly connect to the Data Source from DAX Studio. From this connection, you can proactively evaluate the performance of your data model and make critical optimizations to the Joins, Cardinality, Table Structure, etc.
Final Word
Slow-running dashboards are almost as bad as dashboards with incomplete or incorrect information. Evaluating and optimizing performance speed is an opportunity to add value to your data operation and improve decision-making capacity for your business. Don't let bad dashboards ruin your next staff meeting, board meeting, or executive review.
If you need help tuning or query optimizing your dashboards, or you need new reports or dashboards created using these improved techniques, reach out to the Baleen Data team. We're here to help.
Comments