This blog describes a few simple, easy-to-implement techniques that can be used to increase the performance and scalability of Power BI reports which utilize DirectQuery connections. This post focuses primarily on the visualization layer while another blog post, titled ‘Composite Aggregation Tables’, describes a more involved but systemic optimization at the data model and data source layer.
Performance in Context
When you’re building Power BI reports against fully in-memory data models you can generally focus on the user experience and analytics of the solution rather than performance. Particularly when the semantic model implements recommended star schema designs, single (not bidirectional) cross-filtering relationships, and reasonably efficient DAX logic, even very large models can deliver near-instant response times for many visualizations on a report page.
With DirectQuery models, performance becomes much more of a focus and an ongoing team effort is required at all layers of the solution – the source, the model, and the report/visualization. It’s critical that owners and/or subject matter experts (SMEs) at each layer understand the dependencies and implications of various design decisions as even small changes at one layer can materially impact the workload or performance of other layers.
For example, a Power BI report page that generates four (4) SQL queries (after being converted from DAX queries) rather than one that generates eight (8) could lead to dramatically fewer queries and thus less concurrency or waiting issues once many users begin accessing these reports regularly. Likewise, switching a normal clustered columnstore index (CCI) to an ordered CCI could greatly improve query performance and help report authors develop more robust and interactive experiences.
Example Source and Model
For these examples, I’m using the Reseller Sales schema in the AdventureWorksDW2017 sample database and its running in database compatibility level 140. The dbo.FactResellerSalesXXL_CCI fact table has almost 600 million rows and uses the clustered columnstore index (not ordered). The server has 4 cores, 2 threads per core, and does not limit parallelism so 8 threads can be used on the queries.
The data model has five dimension tables related directly (star schema) to the reseller sales fact table and ‘assume ref integrity’ is enabled thus sending inner join queries. All relationships use single direction cross-filtering.
The individual numbers themselves are not important – it’s the percentage difference between the numbers as well as the difference in number of queries generated that can be extrapolated to other systems and scenarios.
Performance Analyzer and SQL Server Profiler were used to capture the performance and query characteristics described in this post.
Example: Table Visual
The following simple table visual with three metrics (all sums) and one dimension (Year) has no filters applied and requires 10.9 seconds to render:
Moreover, a trace (or Performance Analyzer) reveals that three SQL statements were sent to the source for this one table:
One query retrieves the date dimension table, one query handles the groupings (2011 through 2019) and another query handles the subtotals.
Technique 1: Isolate Totals from Details
In this visual example and many table visuals, particularly those which uses often export out anyway, the report consumers either don’t require to see the total row at all or they would be ‘okay’ if they can optionally access the totals when they want to.
To isolate the totals from details, you simply A) turn off the totals via the Formatting pane and B) expose the totals in a different visual which the user can access via either a bookmark or a different report page, perhaps linked via a button.
Performance Results:
With totals turned off, the table renders in 8.5 seconds and only 2 queries generated. Performance has been improved by 22% and the query volume sent to the source is reduced by 33% for this one visual.
Technique 2: Date Dimension Filter
Does the user ‘need’ to see all the history (e.g. back to 2011)? In most cases the user is only going to compare the last 2-3 years at most – the history is purely a ‘nice-to-have’.
With a relative date filter applied to the date attribute for the last three calendar years, the subtotals can be turned back on and performance is still improved to only 6.9 seconds.
However, with the date filter, four SQL queries are generated – two date dimension queries, the grouped/aggregation query (for 2017-2019 rows) and the total row query.
Performance Results:
So even with subtotals turned back on, performance from the original state has improved by 4 seconds, a 37% improvement.
With both the three year date filter and the removal of the subtotals, the table renders in only 4.6 seconds, a 6.2 second and 57% improvement from the original table visual.
Additionally, the three year query with no subtotals only generates two (2) SQL queries sent to the system – half as many queries versus the table with subtotals.
Technique 3: Filter via Source Query
Rather than implementing the date filter at the report level with the date column, you can improve the performance of the table (with totals displayed) by implementing the date filter within the fact table source query or view object such as the following predicate:
FROM DBO.FactResellerSalesXXL_CCI AS F
WHERE F.OrderDateKey >= ((YEAR(CURRENT_TIMESTAMP)-3)*10000)+101
AND F.OrderDateKey <= CAST(CONVERT(varchar(8),CAST(CURRENT_TIMESTAMP AS date),112) AS int)Of course, this change implies that all report visuals built against this model will be limited to no more than this date range. You’d want to check the downstream impact and get approval for a systemic model-level change like this. (Note: This particular fact table doesn’t have data for 2020.)
Performance Results:
With the filter applied at the source, the subtotal row can be resolved with a single, simple query against the fact table. Since this query is faster than an alternative involving a join to the date dimension, the three-year table with subtotals is improved from 6.9 seconds to 5.9 seconds or 14.5% faster.
Table Visual Takeaways
- Disabling totals reduces queries generated and hence performance of the visual
- You can always display the totals via isolated bookmarks or report pages
- Filter the date range down to what’s actually needed
- Displaying historical data implies generating much larger, more expensive queries
- Similar to totals, you can split date ranges up into different bookmarks or different report pages such that the user can access these date ranges i
- Implementing a date filter in the source fact table query can improve performance versus filtering via the date dimension only
Example: Card Visuals
It’s common for a Power BI report to use several distinct card visuals to display a well-formatted aggregation for a given filter context. The following example of three card visuals is filtered (via the date dimension) for the last three calendar years:
You might think that these are only individual numbers and thus can’t be nearly as expensive as the table visual. However, rendering these three visuals takes 6.9 seconds and generates six T-SQL queries – three against the date dimension and one for each of the three metrics.
Technique 1: Replace Cards with Table
A table visual (with totals on) can contain all three metrics and header and field formatting can be used to provide something similar to Cards:
With more visualization formatting effort, such as replacing column headers with text boxes, you could create something almost identical to Cards.
Performance Results:
The table visual renders in only 4.4 seconds. This represents a 2.4 second and 35% improvement from the three distinct Card visuals. Additionally, only two SQL queries are generated instead of six via the cards. One query retrieves the filtered date dimension and the for the table.
If you move the three year filter from the date dimension to the fact table source query or view object in the source database, performance improves to only 3.2 seconds. This would represent a 3.7 second improvement and 53% reduction in query duration. Additionally, only a single SQL query is required to resolve the table visual if the three year date filter is implemented in the source query.
Card Visual Takeaways
- Be aware that each card visual you create is generating at least one query against the source system.
- All of these visuals refreshing/querying at the same time can easily lead to waits and poor user experiences
- Our goal is to get more value out of fewer queries
- You can consolidate multiple card visuals into a table visual to reduce query duration and volume without significantly degrading the look and feel.
- Like the table visual, implementing a date filter in the source fact table can improve performance and further reduce the volume of queries generated.
Wrapping Up
If you’re working (or struggling) with performance of your DirectQuery-based Power BI reports I hope this blog gave you a few ideas that can be applied quickly and easily. There are many, many factors at play in performance tuning of DirectQuery solutions – a few of which I’ll be discussing in depth in upcoming posts such as aggregation tables.
A main point for this post is just that efficient report authoring has a significant role to play in DirectQuery performance. In other words, don’t let Power BI report authors blame all performance issues on the DirectQuery source system or on the data model they’re using. The report author ‘could’ be right or their ‘could’ be issues at every layer of the stack but given the extra time/effort to optimize these systems it’s a good idea to at least check if the reports/dashboards can be re-factored with minimal compromise to end users. Remember that report design practices which perform adequately against in-memory tables absolutely may not perform or scale over DirectQuery.
Thanks for visiting Insight Quest and feel welcome to subscribe if you’re interested in being notified of future blog posts.
Insight Quest 