Forum Discussion
large datasets - incremental refresh
Hey jessegorter ,
it's simple not possible that a dataset can outgrow the available memory of your P1 capacity.
The table provided in the latest post by solvisig inidcates a 400GB dataset size limit. This is absolutely true - but for a P5 capacity.
This article What is Power BI Premium Gen2? - Power BI | Microsoft Docs shows a table that has the dataset size limits per capacity.I assume that you have transformed you P1 capacity to the new Gen 2 architecture (simply by toggling a button). one of the benefits of Gen 2 is that each artifact can grow to the available memory.
In regards to your initial question about your P1 capaciy - the dataset can not outgrow the available memory.
Memory is of course not the same as storage. Storage is 100TB per capacity.Sometimes I got asked why there is so much storage in comparison to the memory.
The answer is simple, the underlying assumption of the Power BI Premium (Gen 1 and Gen 2) is that there are many datasets and not just one. Power BI is juggling the datasets that are loaded to the available memory.
Regards,
Tom
Hey jessegorter,
it's difficult to estimate the required memory for a fact table with a billion rows, this is due to all the compression that happens in the in-memory colum-oriented world of the Tabular model. This article will get you started: Inside VertiPaq - Compress for success - Data Mozart (data-mozart.com)
If the origin of the fact table is relational it will be likely that there is a column that has a unique identifier for each row inside a fact table, most of the time this unique identifier is of no use, but can add a tremendous memory footprint inside Tabular models.
The most problematic issue with large datasets is the initial load. I doubt that a fact table with a billion (10^9) rows requires a P5.
Fact tables with that number of rows require some thinking, e.g. considering using measures instead of pre-computed columns can create a lot of unique values. These columns will burn your memory in seconds 🙂
Good luck,
Tom
Hey solvisig ,
unfortunately the dataset size can not outgrow the available memory of the capacity P1 eq 25GB.
This article https://docs.microsoft.com/en-us/power-bi/enterprise/service-premium-large-models has a Considerations and limitations section, unfortunately it is not explicitely mentioned bullet point 5 is relevant here (next to my personal experience).
Regards,
Tom
"Refreshing large datasets: Datasets that are close to half the size of the capacity size (for example, a 12-GB dataset on a 25-GB capacity size) may exceed the available memory during refreshes. Using the enhanced refresh REST API or the XMLA endpoint, you can perform fine grained data refreshes, so that the memory needed by the refresh can be minimized to fit within your capacity's size."
I don't think this is referring to storage. Only memory on the capacity processing the dataset.
On the subscription comparison table you can see the model size limit(dataset size limit)
Hey jessegorter ,
it's simple not possible that a dataset can outgrow the available memory of your P1 capacity.
The table provided in the latest post by solvisig inidcates a 400GB dataset size limit. This is absolutely true - but for a P5 capacity.
This article What is Power BI Premium Gen2? - Power BI | Microsoft Docs shows a table that has the dataset size limits per capacity.I assume that you have transformed you P1 capacity to the new Gen 2 architecture (simply by toggling a button). one of the benefits of Gen 2 is that each artifact can grow to the available memory.
In regards to your initial question about your P1 capaciy - the dataset can not outgrow the available memory.
Memory is of course not the same as storage. Storage is 100TB per capacity.Sometimes I got asked why there is so much storage in comparison to the memory.
The answer is simple, the underlying assumption of the Power BI Premium (Gen 1 and Gen 2) is that there are many datasets and not just one. Power BI is juggling the datasets that are loaded to the available memory.
Regards,
Tom
thank you!
It makes sense it cannot grow beyond the capacity limit, I was confused by some of the documentation. I had a large fact table around 1 billion rows, and it seems I could not even fit that into a p5 without incremental refresh
Hey jessegorter,
it's difficult to estimate the required memory for a fact table with a billion rows, this is due to all the compression that happens in the in-memory colum-oriented world of the Tabular model. This article will get you started: Inside VertiPaq - Compress for success - Data Mozart (data-mozart.com)
If the origin of the fact table is relational it will be likely that there is a column that has a unique identifier for each row inside a fact table, most of the time this unique identifier is of no use, but can add a tremendous memory footprint inside Tabular models.
The most problematic issue with large datasets is the initial load. I doubt that a fact table with a billion (10^9) rows requires a P5.
Fact tables with that number of rows require some thinking, e.g. considering using measures instead of pre-computed columns can create a lot of unique values. These columns will burn your memory in seconds 🙂
Good luck,
Tom
