By Vishnuvardhan N, Analytics team CloudMoyo
A growing number of organizations are investing in advanced technologies to empower their business intelligence programs. There’s an industry-wide shift in the way we understand and utilize data today and data visualization tools have a lot to do with this shift. Advanced data visualization tools offer a suite of sophisticated features along with the data analytics capabilities. Power BI is among the most powerful tools available in the market space. It is enabling organizations to make time-sensitive decisions by providing actionable insights from enterprise data within a short time.
With Power BI, you can generate interactive visuals in reports and dashboards to understand relationships between parameters, capture the latest trends, and track projects or campaigns. One of the best practices to follow while using Power BI is to undertake some level of performance tuning for your reports. It could be for a variety of reasons like Power BI got updated with the latest features and you want to assess/enhance the performance, or you encountered performance issues and want to identify the bottlenecks.
To optimize the performance of Power BI reports, you can explore some of the inbuilt features and take advantage of a few open-source tools available in the market. The table below highlights five tools to assess the performance of your Power BI reports and provides ratings for their features as well.
Let’s dive into the features of these tools.
It’s a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular. Analyzing DAX queries can be helpful to understand the performance issues and to improve the writing of DAX queries. DAX Studio includes object browser, query editing and execution, formula and measure editing, syntax highlighting and formatting, integrated tracing, and query execution breakdown. Some of the features include:
- Internally uses daxformatter.com, which allows the users to keep DAX measures or columns formatted, making the code easily readable.
- Easily swaps the delimiter style for DAX expressions.
- Integration with VertiPaq Analyzer 2.0 (preview feature).
- Connects with a variety of data sources, as shown in figure 1.
- Only calculates the time taken by the DAX query to generate results on Power BI. It does not consider the time required for loading visual and network processing.
- One of the popular features is ‘Load Perf Data’ which allows DAX studio to import the JSON file from Power BI Performance Analyzer. Once the PowerBIPerformanceData.JSON file is imported, the PBI Performance pane in DAX displays all the queries captured by the Power BI Performance Analyzer. Figure 2 displays various components in the DAX Studio.
Performance Analyzer in Power BI Desktop
It evaluates and displays the duration required for updating or refreshing the visuals, helping the user to identify which visual or element is impacting the performance of the report. Some of the features of Performance Analyzer are:
- Capability to record the user actions and log time for each activity which allows users to drill down and find the bottleneck.
- Collects and display real-time information related to performance.
- Captures the duration time in milliseconds for each visual on the page which helps to tune the performance of the report.
- Each visual’s log information comprises of the duration to complete the following types of tasks – DAX query, visual display, and others (waiting time for pending operations on a page).
- Allows saving the performance analyzer list generated by the tool in the JSON format using the ‘export’ option. It helps to compare operations after completing multiple required actions.
Vertipaq engine stores the data column-wise as opposed to the row-wise data storage in SQL databases. It uses internal algorithms to avoid duplicates in the columns which reduces the size of the data and the time to query. Some of the features of Vertipaq Analyzer are:
- Provides insights into queries, columns, and the size of the relationships which enables the user to document the model. Analyzing the size of the table and columns is an important step in optimizing the data model for Power Pivot, Power BI, or Analysis Services Tabular.
- Shows the cardinality of columns in the table which helps to identify areas in the model that take up space.
- Provides a hierarchy list of all columns and size of the rows.
- Provides many ‘dynamic management views’ to collect information about the memory used by a data model.
Power BI Helper
Power BI Helper is a free tool available to the Power BI community to develop and enhance the performance of Power BI reports. It can help you to find all the details about DAX measures and all the expressions with syntax errors. Few of the features are:
- Allows hiding the tables from the report view that are not used in any of the visuals.
- Gives directional filters, inactive relationships, and modeling advice.
- Shows the list of tables and bookmarks, where the tables are being used, and if there are any filters used. It also provides information on the measures that are not working.
- Allows exporting data in the CSV file format irrespective of the rows count.
- Provides information about all service objects like apps, gateways, data sources, and service configurations.
Power BI Field Finder
This tool can be used alongside DAX Studio and the Power BI Helper to draw a complete picture of your model and the report. However, it shows the areas where fields are used only in the visuals and filters, but not in the relationships or measures. As a result, exercise caution before deleting any columns or measures, especially if your model is being used in other reports.
A typical report generated by Power BI Field Finder (shown in figure 3) consists of multiple tabs where each tab gives results related to columns and measures and page details. It shows you how often each column or measure is used on each page. You can investigate each column from the table to see each visual on the page and even take your investigation further to check out where the columns and measures are used in sections of the visual.
When professionals are generating Power BI reports, if there’s a bottleneck that leads to slow performance, it can seem challenging to drill down and find the exact area that needs attention. Designing reports for optimal performance requires performance tuning and it’s not always the simplest part of using Power BI. It is so because multiple factors can affect the performance and they need to be investigated. The tools covered in this blog post can enable users to identify the source of performance issues at a granular level.