1GB Limit Explained

[Anonymous]

Hi All,

This is a simple question, yet talking to a few people they had different opinions.

The whole confusion is coming when talking DISK vs MEMORY with Power BI Solutions.

When using connectivity type 'Import' and you load data into your Power Pivot Model (which is an In-Memory Engine) and save your .pbix file you see that the DISK consumption increases.

Questions 1) After clicking 'Close and Apply' from Power Query and start loading data into the Power Pivot Model, the data all gets stored into RAM. However, is it safe to say that all the data gets stores to disk too? The only difference being, that the data in RAM is the one queried for reporting.

Question 2) When we publish the .pbix file to the Power BI Service, a Power BI Pro license user can only publish a dataset that is no more then 1GB. What does this means exactly, does this mean 1GB Disk or RAM? So in other words. when we go the the Power BI Service and we see the Storage Consumtpion increasing in a Workspace, is this from DISK or RAM?

Thanks,

Laz

[rwyoung01]

I have gone into the report server through SSMS and increased the MaxFileSizeMB to 2000 and I still cannot upload a pbix of 637,830 KB.  

[v-frfei-msft]

Hi @Anonymous ,

Q1.

For import, all the data are stroed on premise. So it is safe to say that all the data are stored in disk. You can refer to the bolg to know about what makes up a pbix to help you understand that.

Q2.

Also the blog , that means the file size should not be more that 1 GB. So this mean 1GB to disk.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft ,

Thank you for the reply.

In regards to Q1) I understand that loading data using the connectivity type Import, loads on premise. The article you shared is helpful in understanding what exactly gets stored to disk. The curiosity was around how does this play with the RAM. But this was answered below by @d_gosbell  when mentioning that when you initiate a PBIX file the data gets stored to memory and when closing the PBIX file it gets cleared from memory and so on.

In regards to Q2) I always made the assumption it was Disk and not Memory that gets consumed in the Cloud. However, going back to what @d_gosbell mentioned below, is it correct to say after we publish a PBIX file to the Power BI Service, yes the storage on Disk is what gets consumed as part of the 1GB limit, but other factors regarding Memory need to be taken into consideration. For instance, i recently pubclihsed a PBIX file less then 1GB, yet i got a Memory Exceeded limit error on a single visuailiation. Would you know any other good articles that highlight other data limitations in a PBIX file?

Thanks,

Laz

[d_gosbell]

> For instance, i recently pubclihsed a PBIX file less then 1GB, yet i got a Memory Exceeded limit error on a single visuailiation

This could be a different memory limit. From memory I think they have some sort of "per query" memory limit on the service to prevent a single visual from consuming too much memory. It's possible that you may have one or more measures that due to the way they are coded need to create materialized temp tables. 

Marco Russo did a great demo on using DAX Studio to capture and tune slow running queries here https://www.youtube.com/watch?v=B-h3Pohtn1Y you could use the same technique, but keep an eye on Task Manager to get a rough idea of how much memory your query uses (Marco does show this during his video)

[Anonymous]

HI @d_gosbell 

Yes, the visual was consuming too much memory ad an individual query.

This was due to using a SUMX in a way that made it consume too much data, especially as the SUMX works on a row by row basis. Full details if interested here:

https://community.powerbi.com/t5/Desktop/SUMX-Too-Slow-Solution-is-SUMMARIZE/m-p/627654#M299865

Resolved this by using SUMMARIZE.

I will be certain to view the below video so big thank you for sharing! i saw that floating around and its time i view it.

Laz

[d_gosbell · Accepted solution]

Just to expand and clarify on Q1.

When you click "close and apply" the data is stored in RAM. When you click SAVE to save your pbix file the data from RAM is written into the .pbix file (and conversely loading a PBIX reads the all data from disk and loads it into RAM). Technically you will also see some temp files used when you click "close and apply", but those are never permanently stored anywhere.

Note that the size of the data stored inside the pbix file is likely to be slightly smaller than the memory footprint as there are some objects like dictionaries that are stored uncompressed in memory, but have additional compression applied when writing them to disk. And your pbix includes the metadata for your report visuals (the blog Frank linked to breaks down the structure of a pbix file). 

[Anonymous]

Hey @d_gosbell ,

Thanks for expanding on Q1. The whole reason for these questions is due to wanting to find as much information as possible, more towards the finer details and you certainly assisted on that.

I undertand that when we click ‘Close and Apply’ that Power Query loads the data into our Model. I also undertand that when clicking ‘save’ that the PBIX file saves to disk.

The connection that you made for me was that once you close a PBIX file, the data is cleared from memory and once you open the PBIX file, the data is once again loaded to memory for querying the data.

Also, after reading the blog that was shared by @v-frfei-msft  it was recommended to read more about Vertipaq Engine which is what I will be doing in order to get much deeper into the details.

Thanks,

Laz

[d_gosbell]

Yes, the whole memory size vs disk size can be confusing.

And it actually gets measured differently in different parts of Power BI. For example the 1GB model limit in Power BI Pro is based on the size of the pbix file (mainly because that is easy for the user to measure)

Whereas if you have a Power BI Premium capacity of 16Gb that is the total amount of memory that models can use when loaded into RAM (although premium capacities can also unload models from RAM that have not been used recently so the total size of all your models can exceed 16 Gb)...