Search code examples
powerbipowerbi-embedded

Power BI Capacity failed to refresh dataset after capacity size increased

I have a power bi dataset with aprox. 300MB in pbix file size and 1.2GB RAM memory size (according to Vertipaq analyser in dax studio).I've always been able to refresh this dataset directly from power bi website using its own servers without using premium capacity.

When I try to refresh this dataset that is deployed by the capacity, I get the following error:

"This operation was canceled because there wasn’t enough memory to finish running it. Either increase the memory of the Premium capacity where this dataset is hosted or reduce the memory footprint of your dataset by doing things like limiting the amount of imported data. More details: consumed memory 1764 MB, memory limit 1764 MB, database size before command execution 1307 MB. Learn more, see https://go.microsoft.com/fwlink/?linkid=2159753."

What sees strange is the total memory description in the error, aprox. 3GB that would be the A1 capacity, but I'm 100% sure that I've changed the capacity to A4 (25gb RAM) before the refresh started using microsoft API (waiting 60 seconds to trigger refresh after capacity changed)

Also, it's worth mentioning that I'm able to refresh this dataset using the same strategy when we first turn the capacity on (its early in the day about 06:30am and no one is actually consuming resources from capacity).

Am I doing something wrong changing the capacity before trying to update? It seems like it has no effects on the total RAM available to refresh.

Solution

  • Not sure why the capacity change isn't effective. It should be instant.

    You can try to optimize your model design using the tips here: Whitepaper on modeling for AS tabular scalability.

    By default 10 objects are processed in parallel, and a memory for full copy of the uncompressed data, and two full copies of the compressed data (the old one and the new one) are needed for each object. Reducing the parallelism, and introducing partitioning to large tables can greatly reduce the memory requirements of the refresh. Also for cloud sources the Power Query engine is also running and has its own memory requirements.

    So here's some things to try;

    Reduce the granularity of the refresh by

    • reducing maxParallelism setting
    • refreshing individual table or partitions,
    • using Incremental Refresh (which uses partitioning)
    • changing the "commitMode" to partialBatch

    or

    • using a Gateway VM to offload the Power Query processing off the premium capacity.

    You can attach SQL Server Profiler (or other tools) to the capacity's XMLA endpoint to see the details of the refresh, which might give you some hints about which strategies to try.