Optimising DAX: Data Materialisation
Hello again. In the previous post, we covered the two-engine architecture: the fast, multi-threaded storage engine, and the slow, single-threaded formula engine. The goal is to push as much work into the storage engine as possible.
But what happens when you can't? That's where data materialisation comes in, and it's one of the main causes of slow DAX queries.
What Is Data Materialisation?
The building of the data cache (the handoff between the storage engine and the formula engine) is called "data materialisation".
In the ideal case, the storage engine filters, groups, and aggregates your data efficiently, and passes a small, pre-processed result set to the formula engine. The formula engine does a bit of final calculation and you get your answer quickly.
In the worst case, the storage engine decides it can't do any useful pre-processing and materialises the entire table - rebuilding it from the compressed column storage and dumping all of it into the cache for the formula engine to sort through.
Why It's Expensive
When the storage engine materialises a large table, you pay for:
The cost to reconstruct the data from compressed columns - undoing all that nice column-oriented storage. The cost to "decompress" - reversing the value, hash, and run-length encoding we covered earlier in the series. Poor memory-bandwidth utilisation - large amounts of data being moved between engines.
And then the formula engine has to process all of this data single-threaded. It's not great.
What Triggers It?
The storage engine can handle simple operations natively (scans, filters, GROUP BY, basic aggregations - as covered in the previous post). Anything beyond this can trigger materialisation.
Simple columnar filtering is fine. Each filter condition becomes a bitmap, the bitmaps are combined with a logical AND, and the result is applied to the data. Very efficient, all in the storage engine.
Cross-column comparisons are not. Something like Column A > Column B can't be resolved column-by-column - it requires comparing values across columns, which means materialisation.
Single-column DISTINCTCOUNT is fine. It's just a scan and count.
Multi-column DISTINCTCOUNT is not. Something like DISTINCTCOUNT(Column A + Column B) requires the storage engine to compute the expression first, which it can't do, so: materialisation.
The Goal
You want to materialise as late as possible - do as much filtering, grouping, and aggregation within the storage engine before the data cache is built. The less data that lands in the cache, the less work the formula engine has to do.
If you find yourself in a situation where a cross-column comparison is being done frequently, a practical workaround is to create a calculated column with just TRUE/FALSE for that comparison. The calculated column can then be filtered using the efficient bitmap path. It increases model size slightly, but can dramatically reduce query time.
What's Next
So now we understand why certain patterns are slow (they trigger materialisation). Look out for the final post in the series, where we'll look at specific practical examples - including the classic CALCULATE trap, variables, slicer costs, and how to isolate slow queries in the real world.
