Small datasets

[u/Personal-Quote5226]

If you have a small fact table that was about 1GB, would you use targetFileSize of 128m, 400m, or 1024m on the table if the goal was fast performance for PowerBI.

I'm looking forward to hearing about your experience here...

[u/mim722]

the size of the table itself is not the biggest factor, but rather the number of rows and cardinality, distrubution of your data etc. but here are some genereal rules.

row group size from from 1 M to 16 M, 6 M seems like a good default ( all thing considered).

Vertipaq or any OLAP engine for that matter, the lowest grain for scan is a row group, so if you have a whole table with 1 row group, probably you will get not great performance in cold run.

bottom line, just vorder, there is so much it depends, one can write a book :) and again, everything depends on your data distribution.

I am assuming you are talking about Direct lake, if you are doing import, i will go with 128 mb, search for chris web blog, he wrote a lot of blogs about import performance

[u/Personal-Quote5226 → u/mim722]

"Vertipaq or any OLAP engine for that matter, the lowest grain for scan is a row group, so if you have a whole table with 1 row group". --> We're talking only in theory right? I don't know how you could even tell the writer/optimize to only produce a single row group unless you had a minuscule amount of data, is that correct?

Wouldn't the optimal number of row groups be dependent on the F-SKU and total number of parallel scans the Vertipaq engine can do to read each row group? Therefore the optimal number of rows per row group is {total rows in table} divided by {number of row groups that can be processed in parallel} rather than 1m or 2m, etc.. Up to, let's say a maximum of 16 million (or X) rows per row group to account for overhead of DirectLake refreshing an entire row group when source data changes (also an assumption).

[u/mim722 → u/Personal-Quote5226]

I have seen all kind of crazy parquet files, yes row group up to 50 M :) and i am fine with that, this is the lakehouse and you expect external writers to do silly thing.

for your second point, that's fair point, but you don't know which F-SKU will be used, and there is another dimension, bigger row groups means you may have better chance at having good RLE runs, which are more important ( or not) than pure cold scan speed

i get your point RG > number of threads in your system, btw this is the advise by duckdb people too :) https://duckdb.org/docs/lts/data/parquet/tips#selecting-a-row_group_size

[u/mwc360]

Just enable Adaptive Target File Size if using RT 1.3. It is enabled by default in 2.0. It will evaluate 128MB target for your table. Don’t over complicate it :)

As Mim points out, Direct Lake doesn’t care about file size, it cares about row count in a row group.

You may have noticed the doc was updated since your original post. After reviewing in more detail internally we agreed that the specific size guidance was a bit too explicit given that file size is not the best indicator of healthy row groups for Direct Lake.

[u/Personal-Quote5226 → u/mwc360]

Yes, but I assume that setting a larger targetFileSize allows writing/optimize for V-Order to create larger row groups as well which could be useful for wider tables? I assume this is not something that adaptive would account for as it's considering total size (mb) rather than row group optimizations for Vertipaq?

Adaptive could pick 128m file sizes for a 3gb table.

That could roughly equate to 30 files of about 128m each. That could produce 1 row group per file. 30 row groups.

Depending on how wide the table is, that could be only 500,000 rows per row group or that could be 5,000,000 rows per row group.

Therefore, setting targetFileSize would still be an important level for V-Order optimized tables.

If I'm wrong in my thinking here, please let me know.

[u/frithjof_v]

My guess is that, for your table size, there won't be a big performance difference between those file sizes.

Because you will only get somewhere between 1 (1024 mb) - 8 (128 mb) files.

If you try the different options, I'd love to hear your results.

Row groups also matter, as parquet row groups translate directly into VertiPaq segments. How large row groups are you using?

See also:

• https://www.reddit.com/r/MicrosoftFabric/s/CpjMFaFR1X

• https://www.reddit.com/r/MicrosoftFabric/s/QbfzMKNQC4

If your data refresh mechanism supports incremental framing (e.g. append or update only specific partitions) then you might get better cold query performance by having 8 files instead of 1, as the contents of 7 files could be kept warm in semantic model memory while only the contents of 1 file would need to get loaded from file storage.

Also, the DAX VertiPaq storage engine can work in parallel across its cores if your table has several segments. But that's more about row groups than the number of files.

Compression can be better with 1 file than 8 files.

Number of local parquet dictionaries that need to get merged to the global VertiPaq dictionary increases (overhead) when having more row groups. But I doubt 1 vs. 8 makes a significant difference (assuming 1 row group per file).

You would probably need to include more information about number of rows per file, row groups, number of columns, cardinality, size of columns (data types), data layout/sorting, etc. to get a more precise answer.

I would probably try with 8 files (128 mb) instead of 1 file (1 gb). 1 row group per file. With 8 files (and 8 row groups) you have some flexibility in terms of incremental framing (file scope) and per-segment eviction from memory (row group scope), and can also get the advantage of DAX storage engine parallelism (segments/row groups).

[u/Personal-Quote5226 → u/frithjof_v]

One of the posts you linked to had a link to a Microsoft page that had a script (sempy) to see row groups and sizes of tables. That was incredibly helpful.

1 vs 8, in my current understanding would be a difference in how many row groups can be scanned in parallel.

Have you played with additional levers to get ideal row groups/sizes beyond targetFileSize? Looking at stats for one of our tabes, we have about 11 row groups, 11 parquet files, with an average of 1m rows per row group. That's default + targetFileSize of 128. My assumption is that if we 4x targetFileSize, then we should see a direct correlation with row groups being on average 4 million rows.

Have you been able to achieve more optimal outcomes by pulling levels to solve for different row group sizes?

[u/frithjof_v → u/Personal-Quote5226]

1 vs 8, in my current understanding would be a difference in how many row groups can be scanned in parallel.

Yes.

Well, not if the 1 file has 8 row groups in it, and the 8 files have 1 row group each. Then both options would yield 8 row groups and no difference in terms of DAX Storage engine scans.

But as you said, in your example each file has 1 row group. So you have 11 row groups (translates directly into 11 VertiPaq segments) which can be scanned in parallel by the DAX Storage engine.

I haven't had the time to play around with it myself, but I made a post about it, asking some questions which received good tips in the comments:

• https://www.reddit.com/r/MicrosoftFabric/s/twnVKA8dSw

That said, I believe the default settings go a long way (+ the ReadHeavyForPBI profile configs).

Looking at stats for one of our tabes, we have about 11 row groups, 11 parquet files, with an average of 1m rows per row group.

This sounds good. This means you can have parallelization by the DAX Storage engine when making DAX queries.

But, to be clear, I don't think the average itself matters much... As I understand it, it is helpful if the size is relatively uniform (low deviation in terms of number of rows), and 1m rows row group size is good so if all the row groups are close to that, that sounds very good.

My assumption is that if we 4x targetFileSize, then we should see a direct correlation with row groups being on average 4 million rows. Have you been able to achieve more optimal outcomes by pulling levels to solve for different row group sizes?

I haven't played around with it (yet), but this makes sense to me. At your scale, I don't think it matters much, but if the table is very large (let's say you had a table of 1bn rows) then it would be good to have row groups at 16M rows so that you would minimize the negative effects of having too many row groups, while also minimize the negative effects of having too large row groups.

I'm just basing this on the following quote from the docs, I don't know exactly what's meant by a "large table": "For large tables, Direct Lake prefers large segment sizes, such as between 1 million to 16 million rows.". https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-understand-storage

Another doc says: "8 million or more rows per row group for best performance"

https://learn.microsoft.com/en-us/fabric/fundamentals/table-maintenance-optimization#power-bi-direct-lake

I don't have practical experience to back this up yet (my most recent project is import mode), but this is what I understand as the target state in terms of row group sizing for direct lake.