Same data different load (views vs table) and different pbi file size

[francoisl]

HI all, 

I have a weird issue and maybe somebody has a clue here on the community board. 

I have loaded 175M rows from a sql server table.

Those rows are shorts so the total footprint of the power bi file is 60 megs. 

The sql server table was created with a view so I sql the view to see if it the loads was faster and if we can spare the 176M rows table. 

Result my power bi file is now 1.2GIG which I cannot load in power bi service. 

Anybody has a clue on this ? 

Thanks

Francois

[francoisl]

Thank you all for the answers. 

Sorry for the late response, I was dispatched to another project. 

I created multiple pbi samples

1) query on the table filed with the view

2) query on the view

3) query using the underlying sql of the view

The data definition is exactly the same in M between the view and the table. 

However 1) seems to be compressed data within pbi while the other two gives me uncompressed data. 

I will continue to investigate but since the table filed with the view works, I will probably put the inquiry as a side dish. 

If anybody has any other thoughts, please let me know. 

F

[v-linhuizh-msft]

Thanks for the reply from parry2k.

Hi @francoisl ,

Allow me to offer another thought, have you compared the code of your view query with the code of the query table, which can be run inside Sql Server for troubleshooting, focusing on these two aspects:
The view may contain multiple table joins or complex calculations, which can lead to data bloat.
In a view, certain data types may have been converted to a larger type. Be sure that the data types in the view match the data types in the original table.

Some links that will help you:

Views - SQL Server | Microsoft Learn

3 reasons to use views instead of tables in Power BI! - Data Mozart (data-mozart.com)

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[parry2k]

@francoisl A lot of it depends on the type of the columns. Do you have date/time columns? do you have a lot of text columns? Do you have lot of decimal columns? Can these decimal columns be changed to fixed (currency)?