Power BI Scheduled Refresh issue

[Ganeshwar]

Hello there!!!

We are encountering refresh error(Memory issue) in powerbi premium capacity(p1) for one of the dataset, where other datasets are refreshing successfully. For the past couple of months its been refreshing successfully without issues, I have checked historical data, DAX queries, large dataset format in premium capacity, everything seems to be normal. There were no data model changes have been done.  As a quick workaround, we are refreshing the dataset manually in VM.

Is it required to implement incremental refresh or change/merge the dax queries or reduce the number of transformations in pbi or increase the capacity???

Any ideas/suggestions would help us.

Details

PBI Dataset size:-170 MB

license:PPU

Thanks in advance!!!

[Nick555]

Issue resolved.
A user entered a date on the absolute last row of the excel spreadsheet. Once I deleted it the refresh had no issues. 
Thanks for your help.

[Nick555]

I will have to see if I can find an IT guy to answer that. 

What I do know is that the data source is an excel spreadsheet stored on sharepoint online. The columns have not changed but new records are entered during the week. 
Once I find out from IT I'll let you know.

Thanks!

[PBIandME]

Also plese check how long manual refresh takes, and how much memory it consumes. 

[Nick555]

We are having some similar issues.  I'm confused for a few reasons.
     1. The dataset iscurrently less than 1,000 records. 
     2. The problem just started this week. On Monday and today. Prior to this it has been running with no issues for the past 3 months or so.
     3. If I manually refresh it it works.
    4. It's currently set to refresh at 3 am local time so there shouldn't be any users entering new data.
So did the suggested fix above resolve the issue or is there sonmething else I'm overlooking?
Thanks

[PBIandME]

Hi @Nick555 

Ask your IT team to check the power bi gateway version:

https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

how much memory is your machine using when running the manual refresh? 

Maybe a new column you created is itterating complicated DAX. Did you make any edits to the dataset

Let me know

[PBIandME · Accepted solution]

Hey @Ganeshwar , We were having the same problem for the past 2 months.

2 things solved this for us.

1- UPDATE the on Prem gateway!

2- When you run the refresh on VM open up task manager and see how much memory it is using. Then Try to optimize the DAX queries so they are more efficent if its using a lot of memory. Not sure what the memory capacity is for Premium   

If your model has not changed then the problem is most likely from the gateway not being updated. Or the Dataset is growing and consuming more memory then it did a couple of months ago

Hope this helps

[GilbertQ]

Hi @Ganeshwar 

I would strongly suggest using Incremental refresh, this uses a lot less memory and also refreshes faster.

When refreshing without incremental refresh it has to refresh the entire dataset, and if you have got calculated columns in your dataset this can increase the memory footprint when refreshing.

Do you get an error when refreshing which you could put in the past.

[Ganeshwar]

@GilbertQ Thank you for the quick response

We are getting memory time out issue when refreshing which we didnt got in past couple of months

screenshot

[GilbertQ]

Hi @Ganeshwar 

You would have to look at the Power BI Premium Metrics App to see which datasets are consuming all the memory and see if some datasets can have their sizes reduced?

Also make sure that your current dataset is as small as possible.

If you cannot reduce the size you would then need to get a larger capacity.