Power BI DirectQuery Performance Improvements - Locus IT Services

Power BI DirectQuery Performance Improvements

Locus IT ServicesPerformance ManagementPower BI DirectQuery Performance Improvements

Power BI DirectQuery Performance Improvements

The latest update includes a large number of improvements to the performance of reports using Power BI DirectQuery.

Power BI DirectQuery KPI’s are : 

  • Combining Additive and Non-Additive Aggregates into Single QueryPower BI will now combines with both additive aggregates (e.g., SUM, MIN) and non-additive aggregates (e.g., DISTINCTCOUNT) into a single SQL query to the underlying source, rather than sending two queries. This will improves the performance of any visual containing both additive aggregate and DistinctCount.
  • Improved Handling Queries with a Large Number of LiteralsThe performance of a query involving a large number of literals has been improved, such as queries that involve TopN filtering, measure filtering, or bi-directional relationships.
  • Use of a Single SQL Query Rather than Multiple for Some Calculations and VisualsPower BI is used to send a several SQL queries using DISTINCTCOUNT for certain kinds of visuals and calculations. For instance, this could occur when using quick measure, or by using a bi-directional relationships, or in a measure using CALCULATE of DISTINCTCOUNT. Additionally, a single SQL query is sent, resulting in an order of magnitude improvement in some cases.
  • Optimized Queries to Reduce Post-AggregationIn some instances, Power BI is used to send a SQL queries at a lower granularity than needed for the visual and perform post-aggregation locally. Literally, it pushes all the joins and post-aggregations down to the underlying source, improving performance. For Instance, this applies to any visual where the underlying measure column is directly related to only a subset of the columns being grouped by (and there is some non-trivial filtering), and most quick measures.
  • Optimized Use of TREATAS in CalculationsPower BI will optimize patterns for the use of TREATAS in calculations to combine into a single query. For instance, a visual containing the following two measures:

CALCULATE(SUM(Table1[Col1], TREATAS(VALUES(Table2[Col3]), Table1[Col3])) and
CALCULATE(MIN(Table1[Col2], TREATAS(VALUES(Table2[Col3]), Table1[Col3]))
This would previously have resulted in two separate SQL queries, but will now result in just one.

  • When there is Multi-Column Tuple Filtering the Fewer SQL Queries are Sent For instance, Include or Exclude is used to filter out data items based on multiple columns, e.g., to exclude the items (“USA”,”Old”) and (“France”,”Young”) from a visual.

Locus IT has worked with many businesses to develop, implement, and improve on their BI solution. We provide Business Intelligence training, Business Intelligence support, Business Intelligence implementation, Business Intelligence migration. For more information please contact us.

Locus IT Project Management Office
What’s it?