Today, Microsoft’s Power BI is one of the most widely used business intelligence (BI) and data analytics applications. With Power BI, you can analyze data from different sources and visualize your data.
After developing our reports, however, we find ourselves waiting a long time for them to load, and we wonder, “Why is Microsoft Power BI so slow?”. You will learn several best practices one should follow to avoid this as you read on. Let’s have a look at a few:
Less visual in your Power BI Reports
We often think more visuals equals a better report. However, using a calculation group and having a great UX in mind, you wouldn’t need as many visuals as you initially thought.
Each visual on your page needs at least one query per interactive filter. Thus, having too many visuals on a page can be a burden to the performance of your report. Therefore, we must limit the number of visuals per page for better performance.
Still, on the topic of visuals, you should disable any unnecessary interactions. Less visual interaction will reduce the number of queries needed and thus improve your report performance.
Power BI Data Types
Selecting the correct data types for every column in your dataset will significantly reduce the size of your report. Text data is known to take up more space compared to numeric data types. Also, remove the time element from your DateTime tables, preventing cardinality, saving memory, and increasing performance. Split the date and time if you need the time element in your report, so one column has just the date, and another has just the time.
Roche’s Maxim of Data Transformation
Roche’s Maxim of Data Transformation states that “you should transform data as far upstream as possible and downstream as necessary.” Upstream refers to the location where the data is created or obtained, whereas downstream refers to the site consumed by the data.
Thus, because data transformation is expensive, you should transform your data close to your source to lower the cost and increase the value of this process.
So should I do data transformation in DAX or Power Query? It would be best if you did it in Power Query.
Should you do data transformation in Power BI or the data warehouse? Data warehouse.
Columns are expensive…Measures are better
Measures are calculated at runtime and are not saved in memory. Thus, they are generally faster. However, the data is stored in the xVelocity in-memory database in calculated columns. Thus, from a performance perspective, measures are better.
Optimized DAX Formula
This is an obvious one. The more efficient your DAX formula is, the less likely you are to have slow performance. This article delves deeper into the topic.
Use an on-premises data gateway instead of a personal gateway (Enterprise Gateway)
To access data that isn’t accessible directly over the internet, you need a gateway. However, while a personal gateway imports data into Power BI, an on-premises data gateway imports nothing. This is something you should keep in mind next time you’re working with large databases.
There are a lot more ways out there to improve the performance of your Power BI report. You should watch this video where our guest speaker, Sanjay Chandra talks about some best practices during one of the Power BI community Meetup sessions.