Microsoft Power BI Performance Practices - Locus IT Services

Microsoft Power BI Performance Practices

Locus IT ServicesPerformance ManagementMicrosoft Power BI Performance Practices

Microsoft Power BI Performance Practices

In this Article, we’ll discuss about Microsoft Power BI Performance best practices.

Use Filters to Limit Report Visuals

The large number of data that a visual needs to display about Microsoft Power BI Performance, the slower that visual will be to load. While this line seems obvious, it can be easy to forget. For example: suppose you have a large dataset. At the top you build a report with a table. End users can utilize the slicers on the page to get to the rows they wanted – typically they’re only interested in a few dozen rows.

Limit Visuals on Report Pages

The above line applies equally to the number of visuals on a particular report. Drill-through pages are a good way to provide more details without jamming more visuals into the report.

Optimize your Model in Microsoft Power BI Performance

Some best practices:

  • Columns or tables that are unused should be removed if possible.
  • Avoid distinct counts on fields with very high cardinality.
  • Take necessary steps to avoid fields with unnecessary precision and high cardinality. For instance, you could split highly unique datetime values into separate columns – e.g. month, year, date, etc. Or use rounding on high-precision fields to decrease cardinality.
  • Use integers instead of strings, where possible.
  • Be careful with DAX functions, which need to test every row in a table – e.g. RANKX – in the worst case, RANKX functions can exponentially increase run-time and memory requirements given linear increases in table size.
  • When you’re connecting the data sources via DirectQuery, consider the indexing columns that are commonly filtered or sliced again and this will surely improve report responsiveness.

DirectQuery and Live Connection

In the live connection case or DirectQuery, when users visit a Power BI report, Power BI sends queries in real-time to the underlying data source. Once the data source comes with the query data, then the report is generated. Therefore, your report performance in these cases depends largely on the performance of the underlying data source.

Understanding Dashboards and Query Caches

Visuals attached to dashboards are served by the query cache when the dashboard is loaded. Conversely, when visiting a report, the queries are made on-the-fly to the data source – either the Power BI service or the data source that you specify.

Understand Custom Visual Performance

Make sure to put each custom visual through its paces to ensure high performance. Badly optimized custom visuals can negatively affect the performance of the entire report.

Deep-dive into Query Performance with Power BI Desktop and SQL Profiler  

For a deep-dive into which visuals are taking up the most time and resources, you can now connect SQL Profiler to Power BI Desktop to get all full clear view of query performance.

Instructions as follows:

  1. Install SQL Server Profiler then run Power BI Desktop SQL Server Profiler is available at SQL Server Management Studio.
  2. Determine the port that is used by Power BI Desktop Run the command prompt or PowerShell with administrator privileges, and use netstat to find the port that Power BI Desktop is using for analysis. The output should be a open ports and their list of applications .
  3. Connect SQL Server Profiler to Power BI Desktop
    • Start SQL Server Profiler from the Start menu
    • File > New Trace
    • Server Type: Analysis Services
    • Server name: localhost:[port number found above]
    • At the next screen, select Run
    • The SQL Profiler is live, and now actively profiling the queries that Power BI Desktop is sending.
    • As queries are executed, we can see their respective durations and CPU times – using this information, we can determine which queries are the bottlenecks.

Gateway Best Practices

The On-premises data gateway is a powerful tool for connecting the Power BI service with your on-premises data. The following are the best practices for ensuring highly performance gateways:

  • Use Enterprise mode, as opposed to the personal mode.
  • Recommended hardware specifications for the gateway – 8 CPU cores, 16 GB RAM.
  • Set-up performance monitoring – set-up performance monitoring on the gateway machine understands whether the gateway is becoming overloaded and becoming a bottleneck.
  • Scale out or scale up – if the gateway is indeed becoming a bottleneck, then consider scaling up or scaling out. For example, splitting out datasets onto different gateways.
  • Separate import vs. DirectQuery – if it is scaling out, consider separating the gateways responsible for import vs. and those responsible for DirectQuery.

Network Latency

Network latency can affects the report performance by increasing the time required for requests to reach the Power BI service, and for responses to be delivered. Tenants in Power BI are assigned as a specific region. You can now see your tenant’s “home” region by navigating to powerbi.com, By selecting in the top right and then About Power BI.

Locus IT has a thorough knowledge of some of the well-known industries and the particular challenges that currently exist in this industry. We provide Power BI staffing services and support and can help you to plan and design a solution using proven tools that meet your specific needs, streamlining operations, and increasing profit.

Locus IT Project Management Office
What’s it?