Data Refresh : memory Issue

[nagaraj]

Hello,

 We have a report with size 150 MB and i have enabled Incremental refresh for one of our FAct table. When i process the data using power bi service, the refresh is frequently failing with below error. Please help, below are the details 

 1. File Sixe : 150 MB

 2. Workspace : A1 

 3. No other major datasets in the workspace

You have reached the maximum allowable memory allocation for your tier. Consider upgrading to a tier with more available memory.

[mwegener]

Hi @nagaraj ,

what are your settings for incremental import?

What are the values of the parameters in the Power BI Desktop file?

Have you tried an A2 capacity?

[nagaraj]

Hi @mwegener   

Thanks for your reply.  For Incremental refresh , i have setup these parameters   

RangeStart : 2017-01-01  

RangeEnd: 2021-01-01

Total Rows retrived from the table / view : 25 Million

And below are the incremental settings on the Fact table 

As we have only A1, we haven't tried on A2. But expectation is it should have run as you see the total data size not more than 25 Million rows and in the workspace we dont have any other dataset more than 10 MB and that too we have only 2 other datasets

Note: My total size of the PBIX is 150 MB when we refresh it from Desktop.          

          So by considering this size and A1 supports 3 GB Cache hope this should be good for Data REfresh 

[mwegener]

Hi @nagaraj,

is it a local or cloud data source?

How is the date "FirstDayofMonth" defined?

Do you possibly reduce the result in the last step to 150 MB?

[nagaraj]

HI @mwegener 

 The database is Azure SQL database and the column "FirstDayofMonth" is the datetime field.

 The fact table for which we setup Incremental Refresh is having granularity by month and not by day.

 The column "FirstDayofMonth" refers to the starting day of the month.

[mwegener]

Hi @nagaraj,

i assume that your power query transformations use more memory than the query result reflects.

Monitoring Power Query Memory Usage With Query Diagnostics In Power BI

https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-i...

[nagaraj]

Hi @mwegener 

Thanks for that, but i don't have any transformations in the Power Query.

Reading the data from a view and applying Rangestart and RangeEnd filter.

When i try for 2 years data in the Incremental data Data Refresh is working fine.

But incase of increasing it to 3 and 4 years this is going off...

But our requirement is to display atleast 4 years of data

Regards

nagaraj

[v-deddai1-msft]

Hi @nagaraj ,

As the error message suggests, you need to upgrade to a tier with more available Memory.

Please check the size of your dataset. When you perform incremental refresh and report interaction, you need to load the entire dataset and incremental refresh data into memory. I think this may be the reason why your memory reaches the upper limit.

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

Best Regards,

Dedmon Dai

[nagaraj]

Hi @v-deddai1-msft

My dataset statistics are below 

 Sixe : 150 MB

 Total Rows : 24 Million

 Data starting from Year : 2017

 Workspace : A1

 I was under impression that i have the dataset with 150 MB and as A1 supports 3 GB Cache and 30 GB capacity, this data refresh should run with out any issues. But not sure why it is reaching the memory limit on INcremental Refresh.

Any suggestions to resolve this

[mwegener]

Hi @nagaraj ,

maybe this helps

https://www.youtube.com/watch?v=C6vk6wk9dcw