CODEWITHSUNDEEP
powerbiout-of-memoryssmspowerbi-embeddedssas-tabular
Barak Kababia
Barak Kababia

Reputation: 31

Resource Governing: This operation was canceled because there wasn't enough memory to finish running it

We have Power BI Embedded SKU "A2" in our Tenant.
A2 Tier has 5GB of memory in her capacity.

We have a power bi model that we created, and we want to use incremental refresh on 2 of out tables, which contains a large amount of records.
We published the report and created for each of the 2 tables month based partitions, gathering data for the last 36 months.
When published, the tables contains only 1 day of data, so we wont have problems with the publishing process, and we intend to get the remaining data once its published. B We planed to load each partition on his own, for the initial load and after the initial load get data by the day, on our daily load.

When we started loading the partitions we were able to load some of them and after a few loads, we encounter problems and failure, with an error message of:

Resource Governing: PBI This operation was canceled because there wasn't enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory 1120 MB, memory limit 801 MB, database size before command execution 4318 MB. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more.

This is pretty odd because its says the limit is 801MB which is not true, because in SKU "A2" we should have 5GB of memory available. Take in notice this is the only report that is using the PBI Embedded Capacity.

We tried to change the SKU Tier to "A3" which has 10GB of memory, but it gave us the same error message of out of memory, even after the memory got doubled...

More over, when we started loading the partitions first we tried to load the biggest partition of them all to see if the load can handle it. The load was able to withstand it, and all the other partitions are smaller so it should be able to load them as well because they built the same just based on other months.
Its seems like when we load "X" amount of records, doesn't matter on which partition we are selecting we are not able to load more data after. There are small partitions that I was not able to load once we had like 15M of records in total in the table, but once I emptied the table and tried to load the partition it suddenly succeeded.

We are trying to figure out why the load is failing and how our Capacity memory is used, and if we have a data size limit on the capacity (like normal workspaces have 10GB size limit).
We also using a "Large Semantic Model storage format" for the dataset so the amount of records we intend to load (70M) should not have a problem.

Please let me know if anyone encountered a similar problem before and how you solved it.
Or if anyone knows how the memory is used in PBI Embedded Capacity.

Upvotes: 3

Views: 565

Answers (1)

minnya
minnya

Reputation: 11

I also encountered the same issue while trying to update the Semantic Model for Power BI Embedded SKU A1. Despite the dataset being around 300MB, it consumed 2.7GB of memory and resulted in an error.

Upon investigation, I found a thread where the following explanation was provided:

"When many relationships are defined, it consumes more memory.Additionally, if data refresh and relationship calculations are performed simultaneously, a large amount of data is loaded into memory at the same time, leading to memory exhaustion."

Based on this, I followed these steps, and I was able to successfully perform the update using PowerBI API:

  1. Perform an update using the following endpoint

    POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes { "type": "DataOnly" }

  2. Monitor the  endpoint and wait for the dataset refresh to complete.

    GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes

  3. Perform an update using the following endpoint

    POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes { "type": "Calculate" }

I hope this method works in your environment as well.

Reference:

https://learn.microsoft.com/ja-jp/rest/api/power-bi/datasets/refresh-dataset

https://learn.microsoft.com/ja-jp/rest/api/power-bi/datasets/get-refresh-history

Upvotes: 1

Related Questions