Pbix file size: Big Flat table vs denormalised Schema

[Anonymous]

Hi,
I need some help understanding what impacts pbix file size.

I have a data model with one big fact table (5mn rows), a few large dimension tables and around 5 smaller dimension tables. The pbix file size is around 400 megabytes.

I was learning how to use Append queries feature and basically put all tables into one large flat table (apart from calendar table) at Power Query step. To my surprise - the file size dropped by a third - to 280megabytes. How come - in effect I've made the storage less efficient as I've normalised the schema. If anything - I was expecting an increase in pbix file size.

Also, I've noticed that visuals (even if I have a 10 page report ) - doesn't increase file size too much.

So the question is - what does impact the pbix file size. Also, if let's say I have 400megabytes pbix - is there a way to check how much RAM does it use? I know it compresses pretty well, but would like to see the actual numbers (ones in Task Manager might be impacted by the application itself).

Thank you,

Dovilė

[v-easonf-msft]

Hi , @Anonymous 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Anyway you need to understand the composition and content of pbix first.

You can refer to this article (Here is a lot of what you want to know)

What makes up a powerbi desktop pbix file

exposing-m-code-and-query-metadata-of-power-bi-pbix-file  

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Anonymous ,

the number one point that determines the size of the data model is the number of unique values in each column, the more unique values, the larger the model.

You can inspect the data model, using the vertipaq analyzer, that can be found here:

https://www.sqlbi.com/tools/vertipaq-analyzer/

Since version 2.9.4 also DAX Studio can answer the questions of what structure exactly is responsible for the size of the data model: https://www.sqlbi.com/blog/marco/2019/09/15/vertipaq-analyzer-2-0-preview-1/   

Why is the file size shrinking?

Due to creating a single table, you also reduce the number of columns, as there is no longer any need for 2 key columns (dimension and fact table). As there is just one table, there are no longer relationships, but from my experience relationships are never that big.

Please be aware that, a star schema is the design to go for, as a one-table solution might create unexpected results: https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Regards,

Tom