Simple Ways to Improve the Performance of your Dashboards and Reports
One of most common complaints we hear from clients is that their dataset has grown and now their visualizations are taking much longer to load than they use to. This can be frustrating for end users if they are waiting for a visual that is linked to 100 million rows to refresh. It is important to follow some best practices from a design perspective to improve performance. These best practices are related to data modeling, DAX and report creation.
One of the easiest ways to improve performance is to make sure all your report visuals only display a subset of the dataset by default. What that means is if the underlying dataset has a 100 million rows, then set the visualization to default to a subset of that such as 20,000. You can use the Top-N rows feature to do this. This avoids all 100M rows being loaded by default every refresh. You will instantly see a quick performance improvement by doing this. Users typically only need to view a subset of the data, which they will filter using slicers.
From a data modeling perspective, for complex large models where you are connecting to Analysis Service, if possible Visual Studio can be leveraged for the model creation, thus offloading it from the Power BI Service. Doing this you will see a tremendous performance improvement.
On the DAX side of things there are small tweaks within DAX you can make. Here are a few:
- Be prudent when using calculated columns. They operate on every row in your dataset using the DAX specified. Depending on the calculation and the number of calculated columns, this can be a huge performance hit.
- Use variables as they will be calculated once and can be used throughout, as opposed to using the same measure more than once. The measure will be calculated dynamically each time.
Leveraging some of these techniques can go a long way in improving the performance of your dashboards and reports!
If you are looking for Power BI implementation assistance or training, feel free to email us at info@longieng.com or give us a call at 212-997-9444.
