- The speed and cost of Direct Lake also depend on how the data is stored in files, not just on the model itself.
- The same data stored two different ways can cost several times more to load.
- What helps most is storing data in fewer, larger blocks. Fabric offers a ready-made setting for Power BI that stores data this way.
- The change usually doesn't require touching the reports or the model.
Vojtěch Šíma compared 64 Direct Lake models to see how the way data is stored changes speed and capacity consumption. You'll find the results, the numbers and the settings for your team in the article on his blog.
Contents 5 sections
- 01Where does Direct Lake lose speed?
- 02What slow loading costs
- 03How do you make Direct Lake faster?
- 04What to ask your team
- 05FAQ
Where does Direct Lake lose speed?
Picture a warehouse. When the model has to answer a query, it first has to bring the data it needs from the files into memory. Data in the files sits in blocks, a bit like goods in boxes, and the model carries them one box at a time. A few big boxes come in quickly, while hundreds of small boxes holding the same goods slow it down and take more work.
How many boxes you end up with depends on the tool that writes the data to the files, and on its settings. A data engineer won't see the difference at first glance: the data is the same and the report shows the same numbers. The difference only shows up in speed and on the capacity bill.
What slow loading costs
Every data load uses up a piece of the capacity you pay for, whether you have it as F2 or F64. With one user the difference is tiny, with dozens of people and several refreshes a day it adds up. In our measurement, the same data cost up to four times more to load, depending on how it was stored.
How do you make Direct Lake faster?
In most cases it's enough to change the settings the data is written with, and the reports stay as they are. Fabric offers a ready-made profile for Power BI, readHeavyForPBI, which stores data in larger blocks and also turns on V-Order, a feature for better compression. Data written by your own Python scripts often comes with default settings that produce many small blocks, so that's worth checking first.
The second thing is columns. The model loads every column in it, even one nobody works with. So for me, the first question with a slow model is which columns really need to be in it.
What to ask your team
- What settings are used to store the tables Direct Lake reads from?
- Are they written by Spark, or by your own Python script?
- Which columns does the model contain that nobody uses in reports?
- When did you last measure how much capacity your models consume?
FAQ
Do we have to change the reports or the model?
Usually not. The change concerns how the data is stored in files, and the reports and the model on top of them stay the same.
Why is a report slow the first time it opens after a data refresh?
After a refresh, the model loads the data from the files into memory again. Later queries read from memory, so they're faster.
Does V-Order always help?
Not always. For most fact tables it makes the model cheaper, but for data that's already sorted and has few distinct values it can add a little instead. That's why it pays to measure how it behaves on your data.
Where do you stand?
Performance Audit
€2,950excl. VAT
Do your visuals take seconds to load instead of milliseconds, or does refresh take tens of minutes? We measure before and after, and you see the actual numbers.
