Here is some basic guidance for getting the best performance possible out of Power BI reports and dashboards.
Understand your architecture
Your first step is to understand the architecture of the reports and dashboards you create. What you need to do will vary depending upon your architecture. If you haven’t established an architecture yet, consider one of these 2 recommended approaches:
|<1GB||Load all the data into the Power BI service||· Load all the data into Power BI Desktop · Build your data model & reports · Publish to the Service · Establish refresh|
|>1GB||Keep your data in a backend – specifically use – SQL Server Analysis Service (SSAS) tabular model||· Use SQL Server Analysis Service 2016 · Use Direct Query or Live Connection to connect to the SSAS backend · Build your reports · Publish to the Service|
Make sure you understand all the characteristics of the dashboards you are building:
- What are your data sources?
- SQL Server Analysis Services? Tabular or Multidimensional models?
- SQL Server database?
- Excel files? Flat files?
- Are you loading all the data into the service, or are you leaving the data in the underlying backend data sources and running your reports against it?
- What tool are you using the build the reports? Power BI Desktop? Excel?
- How are you connecting to the underlying data source?
- Enterprise Gateway?
- Personal Gateway?
- OneDrive for Business?
- Where is your backend data stored?
Test your performance before you go into production
A common mistake people make is one of treating Power BI like it’s not a real production reporting system. In a real production environment, you would test your performance before going into production with live end-users. Be sure to do the same with Power BI. While Power BI doesn’t provide you with a separate test environment, you can still do performance tests with your developers, test team or test users on the service before you go into production with actual end users. Perform the same kinds of performance tests you would on Power BI that you would on your standard (old) reporting platform whenever possible. That includes, but is not limited to:
- Time your reports
- Compare report rendering time with query completion time
- Schedule a bug bash with testers and assess performance
- Examine the performance of your backend system before you add Power BI load
- Assess if there is any additional capacity on your backend for the additional load
Before you start, there are a couple diagnostic steps to direct your investigation.
Step 1 – Ensure your backend is sufficient
As with any reporting platform, you need to ensure that the backend data source system can support the reporting load you are placing on it. You will be placing additional load on the backend system with your Power BI reports and dashboards so you need to ensure it has the capacity to handle the additional load.
Understand the performance characteristics of your backend system:
- Does it have the capacity to support the additional load you will be placing on it with your Power BI reports and dashboards?
- Is it running on sufficient hardware?
- It is running with the latest service packs?
- Is it optimized for performance?
- Has anyone done any performance tuning on it?
- Is there any new performance tuning guidance from Microsoft on the backend software you are using?
Step 2 – Describe the performance problem
Your next step is to characterize the performance problem you are having. Understand exactly what is underperforming, under what conditions and by how many users. This alone may help you discover on your own how to solve the problem. Ask yourself these types of questions:
- What is your performance expectation?
- Is performance consistent at different time of day? With different # of users? How many users do you think are using this report/dashboard at the same time?
- Is this a new issue? Or has it always performed this way?
- Is it one dashboard or report?
- Is it all reports and dashboards using a dataset?
- Does the report have filters? Slicers?
- Does it happen for all users? Or only users with a security role?
- It is only when you pin the entire report as a dashboard?
- Did you test this report on a test system? Is it performing the same or different than the test system?
Here is a list of remediation steps to common performance issues found with Microsoft employees in 2016.
Step 1 – Temporarily Remove Custom Visuals.
Some custom visuals have been known to introduce performance issues with Power BI. If your dashboards or reports are experiencing performance issues, temporarily remove them.
- Remove custom visuals from all dashboards and report
- Remove the custom visuals from the PBIX file – you will need to rebuild the PBIX file to do this until May/June when a bug removing custom visuals is fixed in the Power BI Desktop.
- Replace custom visuals with standard visuals from Power BI
- See if performance is improved
Step 2 – Optimize your reports
To create a report with optimal performance, you might have to limit the data you bring into the report. If you have reports that contain tables with rows of details in them, you may be creating a performance problem.
- Limit your report to the data that is necessary
- Look at reports with lots of slicers, move slicers to filters to eliminate unnecessary data loads
Step 3 – Upgrade to the latest version of the Enterprise and Personal Gateways
The Power BI team is constantly improving the performance of the gateways. If you are experiencing performance problems, the problem may be resolved for you already.
- Old versions of the gateways may be slower with your data
- The latest versions of the gateways have performance fixes and optimizations
- The older SSAS connector is slower and is replaced by the Enterprise gateway
Step 4 – Optimize SQL Server Analysis Services (SSAS)
The highest performing backend for Power BI is SSAS. By optimizing your SSAS backend, you will get the best performance possible with Power BI.
- Upgrade to SQL Server 2016 RC0
- SQL Server 2016 will ship later this calendar year (2016)
- SQL Server 2016 is optimized for the DAX queries that Power BI sends
- Optimize your SSAS model
- Get better hardware
- Adding new users or report demand on a system that is already loaded may exceed its hardware capacity
Step 5 – Examine the performance on the gateways
If you are using gateways to access your data, make sure you are running the latest versions.
- If you are on the old Analysis Service connect, migrate to the Enterprise Gateway
- Look at gateway performance counters
Step 6 – Examine & optimize the performance in your backend
If your data is in the backend server, and not loaded directly into the service, you will need to examine and optimize the performance in the backend system. Power BI sends the queries from your report to the backend system and, therefore, is dependent on the performance of the backend system. If your backend is not SSAS, consider building a DataMart using SSAS tabular models with your data.
For SSAS Tabular Models:
- Identify the worst performing reports
- Use performance monitor (SQL Profiler)
- Run the report
- Get the start time of the DAX query
- Get the completion time of DAX query
- Compare query time (completion time – start time) to the time it takes to run the report in the service
- Watch the CPU and memory utilization
- Determine if the performance problem is on SSAS backend or in the service For other backend system:
- Use similar performance troubleshooting techniques as outlined above for SSAS
- Upgrade to the latest service packs for any possible performance fixes
- Use the backend service performance tools to analyze performance
- Compare query time in the backend to report completion time in the service
- If the query time is too slow, optimize the backend
- If the report time is too slow, but the query time is fast, it may be a bug in the service
- Consider upgrading your hardware
Step 7 – Examine the performance in the service
If performance problem isn’t the backend or in the gateways, it could be in the service itself.
- Measure the time it takes to run the report service to
- Measure the time it takes to run the query for the report query time in backend
- Compare the two times
- If the times are different
- There may be a bug in the service
- Contact the IT Helpdesk to log a bug
- Try moving slicers to filters which will bring less data into the service
Step 8 – Understand the impact of security
Implementing security on the data can impact performance. If you are using role based security, or role level security (RLS) on your data, you may see a performance impact.
- Reports without RLS or security limitations will run faster
- With RLS:
- Admins and users with access to all data – your report will run faster than your users who see limited data
- RLS will make it slower for your users to see only their data (limited data)