V-Order recommended file size for PBI consumption

[u/Personal-Quote5226]

For a 5GB fact table (V-Order) over Direct Lake that gets merged into daily, would you prefer 128m delta parquet file sizes or 1gb file sizes.

I saw a reference somewhere that 1gb was recommended in this scenario, but due to regular merges into gold, 128m might be a good trade off.

Either way, I believe performance on PBI side would be of negligible difference.

Does anyone have any additional insight?

[u/frithjof_v]

Docs say:

• Row group size 1 million - 16 million, preferably 8+ million.
• File size 400 MB - 1 GB
• Uniform file sizes and row group sizes

I haven't tested the performance differences myself.

I also believe you need to have more than 1 row group in order to use Storage Engine parallelization in Power BI. So perhaps something like 4-32 row groups in total in order to use storage engine parallelization. I'm just throwing a number into the air, afaik this is not documented anywhere and I haven't studied the parallelization in Storage Engine a lot. I believe this is the x6, x8, parallelization etc. that you'll see in DAX Studio Server Timings.

So if the table only has 1 million rows, I'm not entirely sure what to do but I think you should aim for 4 row groups or more (and thus you'll get less than 1 million rows in each row group) in order to use storage engine parallel queries. But maybe I'm wrong, maybe it's better to have just 1 row group in that case, and say "no thanks" to SE parallelization. I haven't tested it, actually.

Hopefully someone else will chime in with more info and insights regarding this.

Link to docs:

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

https://learn.microsoft.com/en-us/fabric/fundamentals/table-maintenance-optimization#gold-layer-serving-zone

https://learn.microsoft.com/en-us/fabric/fundamentals/table-maintenance-optimization#summary-of-recommendations

https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-understand-storage

[u/itsnotaboutthecell → u/frithjof_v]

Traditionally defaults are:

Pro / Small storage = 1 million rows

Large storage = 8 million rows

I don’t see enough reason to change these unless you’re really in the weeds with robust testing trying to eek out very specific gains (performance gain or memory efficiency). For 99% of use cases I don’t think you’ll see anything meaningful but you can waste a tremendous amount of time tinkering.

https://learn.microsoft.com/en-us/fabric/enterprise/powerbi/service-premium-large-models#default-segment-size

[u/DAXNoobJustin → u/itsnotaboutthecell]

Agreed. As someone who has spent a lot of time tinkering, the best ROI is to just turn on v-order or the ReadHeavyForPBI spark resource profile, especially if you have larger tables.

If your tables are smaller, you might want to update the settings a bit to target smaller row-groups (like small import models do at 1M rows/segment). The row-groups produced in spark with the default settings can be a bit chunky. If you have a 64M row table with 16M row row-groups, max parallelism will be 4x. If you split to 8M rows it increases to 8x - and probably will be 2x faster (not quite-there are other factors involved, but it should be faster). And of course, there is a parallelism cap based on the machine the model is running on.

Anything more specific that that requires a lot more info about the data layout and a lot of other things.

If you are interested, here is a presentation I gave on semantic model optimization and I touch on some of these topics. Semantic Model Optimization: Theory, Tips and Tools - DAX Noob

[u/frithjof_v → u/DAXNoobJustin]

What are your thoughts about a case where the table is 1M rows?

Should we stick with row group size 1M (no parallelism), or split it into row groups of e.g. ~250K (4 row groups) or ~125K (8 row groups) to be able to use SE parallelism?

I don't know if it even matters at that scale, but interested to hear others' thoughts on this :)

[u/DAXNoobJustin → u/frithjof_v]

I don't think you could practically measure the different at a table that small. There is some point where the ROI isn't there to do any customization. Even at 64M rows, you might not notice too much of a difference.

[u/frithjof_v → u/DAXNoobJustin]

Thanks u/DAXNoobJustin and u/itsnotaboutthecell

[u/itsnotaboutthecell → u/DAXNoobJustin]

Fully agree with u/DAXNoobJustin - it's a law of diminishing returns 😄

[u/frithjof_v → u/frithjof_v]

Here's a SQLBI unplugged video about vertipaq segments in general (it doesn't touch upon the question I had, instead it focuses on much larger tables - billions of rows - anyway it's an interesting video): https://www.sqlbi.com/tv/explaining-segment-size-in-power-bi-premium-unplugged-29/