This post is aimed towards Power BI DirectQuery semantic models. However, a lot of the information in this post is also viable for import mode semantic models. This is a short summary with the purpose of giving you an overview of some important areas that can impact the performance of your Power BI DirectQuery semantic models and reports.
This list is based on my article series (Part 1, part 2 and part 3) about how to master performance optimization in Power BI DirectQuery semantic models.
Each tip is given a short amount of info with a link where you can read more and learn about that specific part.
- Understanding where your potential bottlenecks are located
Before fixing the problem, it’s crucial to find the origin of the issue. Is it in your underlying data source, potentially a Power BI on-premise gateway or in DAX? - Improve data source performance
If your underlying data source is causing performance issues. Then most likely this is where you need to turn your focus before tuning your Power BI model. Make sure that your data model is in shape. Missing indexes or poor maintenance of existing ones can cause poor performance on any query. - Power BI on-premise gateway
Are you using a gateway for your sematic models? Make sure that it’s set up correctly according to the recommendations listed by Microsoft. - Data modeling
Understanding star schema (dimensional) modeling is crucial and a best practice for any serious Power BI solution. Failing to follow this recommendation could bloat your model in many ways affecting many other areas if performance optimization listed on this page. - Limit data transformations in Power BI
It’s always recommended to transform your data as far upstream as possible in a data solution. The same goes for Power BI semantic models and especially DirectQuery models. Adding more complex and heavy logic in either DAX of PowerQuery will have a negative impact on your Power BI DirectQuery performance. - Keep DAX measures simple
Keeping your data model clean and according to best practices will allow you to write simpler DAX code. Minimizing the DAX logic will have a direct positive impact on your DirectQuery performance - Limit number of visuals on a report page
Every visual on a report page sends a minimum of one query to your underlying data source, and often more. Therefore, you should carefully plan your report layout and try to limit the number of visuals on a single page.
If possible, try and split your reports into multiple pages or combine data from multiple visuals into single visuals. - Optimizing DirectQuery report filtering experience
There are some built in features in Power BI that can help you in optimizing your DirectQuery reports. For example query reduction features and adding an Apply all buttons to your report filters - Minimize cross source group queries
Try to avoid relationships between Import and DirectQuery sources, or between multiple DirectQuery sources. These relationships are called limited relationships and will have a negative impact on the performance of your semantic model. - Configure parallelism for storage engine queries
Parallelism for underlying queries can be configured in different ways for different scenarios. For example, it’s possible to change the number of parallel queries sent from a single Power BI visual. You can also change the maximum number of connections for a single data source. - Changing join types for DirectQuery source queries
When working with DirectQuery semantic models, each interaction generates a query to the underlying data source. As a default behavior, If a query includes columns from multiple tables, then those queries are generated with outer joins.
An outer join means that all results are returned from table A with matching rows from table B. This is not always ideal and could potentially cause performance issues in your model, depending on what your data looks like.
This behavior can be changed to use inner join instead, which could potentially speed up your queries. In order to do that, you need to enable referential integrity setting for you relationships. Be aware, this comes with a risk if your data is not in order! - Using DAX variables
Using DAX variables in your DAX code can increase the performance of your DAX significantly. Especially on DirectQuery semantic models as all queries are sent to the underlying data source. The fewer queries generated = faster semantic model. It’s as simple as that!
Thank you!
Thank you for reading. I hope that you learned something from this and that these tips help you to improve your Power BI DirectQuery semantic models.
Please comment and share if you liked these tips.
Do you have other tips than the ones listed above? Please share them below. I would love to hear them!