What is the mechanism of data compression in PBIX file?

[Needn]

Hello,

I'm doing a test about the data compression ratio of PBIX file.

I've created 2 tables with the same schema in SQL Server, wide_fact1 and wide_fact2 and import them into Power BI desktop.

The difference is only the order of data between the two.

Then I used VertiPaq Analyzer in DAX studio to analyze data size, got the same total size value.

But large difference in PBIX file as following.

I want to know what happened.

I've posted a github discussion, I'm appreciated @marcorusso  gave me some good idea.

See here https://github.com/DaxStudio/DaxStudio/discussions/541

But my question has not been resolved, that's why I post this topic here.

Please feel free to download PBIX files if you want

https://neednlab.blob.core.windows.net/public/wide_table1.pbix
https://neednlab.blob.core.windows.net/public/wide_table2.pbix

Thanks

[negi007]

@Needn there are various tricks and techniques you can use to reduce your datasize. I hope you would have done some simple things like

1. Not loading tables in powerquery which are being used only for support or aggregation purpose

2. Removing columns not being used

3. Using integer instead of text wherever possible

4. Creating one date table for all all time intelligence calculation instead of having it in multiple tables

5. Importing data at aggregate level if possible

You may refer to below links for help

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

[Needn]

Yes, I've read power bi docs about the data reduction techniques and it's helpful.

But in this topic, I just want to focus on the question: why there is a big difference of compression ratio between the 2 PBIX files. Thanks

[negi007]

@Needn I can't download PBIX file. there seems to be an error with the link. Please check and share the correct link.

[Needn]

oops. please try again @negi007 

[negi007]

@Needn Yes, now I can access both the files. I did lot of reasearch on both the files and try to keep the settings same however it did not result in any change in the size of the file. I even open both the files in the dax editor to compare table and other properites. but everythinng looks same for both the files.

You can try reading below blog as well it will give you some information about various information about the pbix file. It might take you in the right direction

https://www.fourmoo.com/2017/05/02/what-makes-up-a-power-bi-desktop-pbix-file/

However, I was able to reduce the size of the file1. To reduce the size of the file 1 this is what I did. I ziped both file 1 and file 2 just by chaning the extension of the file from pbix to zip. After you rename the file to zip file and when you open it you get below information. As you can see DataModel is the one having bigger size then others. 

So next I replaced the DataModel file from my File2 to File1. After this I changed the extension of the file back to pbix. Below is the final size of both the files

However, to your question why files sizes are different I still could not find out the reason. May be there are few properties in the powerquery window that is determining the file size or may be not. Let's hope someone will be able to find out the reason.

Cheers for asking interesting query.

[Needn]

Thanks for your idea. I guess the difference of file size is due to a further X9 compression. As mentiond by Marco Russo, it is not compressed at VertiPaq level. @negi007