Getting error while refreshing Power BI dataset

[PK950309]

I am using Amazon Redshift as a data source and have implemented incremental refresh. However, the dataset refresh is failing with the below error.

 The underlying dataset contains approximately 100 million records. The same dataset loads in DirectQuery mode, although with some performance lag.

Could someone please help confirm whether this issue could be due to memory limitations, since both the workspace and the gateway are shared, even though the workspace is enabled with F64 SKU capacity?

[v-saisrao-msft]

HI @PK950309,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Than you 

[v-saisrao-msft]

Hi @PK950309,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft]

Hi @PK950309 ,

Thank you @Wikkleyn_81 @Natarajan_M  ,for your insights.The refresh may be failing because incremental refresh is creating large queries that Redshift needs to run, and the available memory per query is limited by WLM slots and concurrency. To help, try reducing the amount of data processed during each refresh by narrowing the refresh window, making sure RangeStart/RangeEnd filters are applied early and pushed down to Redshift (check View Native Query), and removing any unnecessary columns. On the Redshift side, you may want to optimize WLM memory and concurrency settings, and use more restrictive filters. If you’re using an on-premises gateway, make sure it has enough resources and avoid running several heavy refreshes at the same time.

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsof…

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Monitor and optimize on-premises data gateway performance | Microsoft Learn

Query performance improvement - Amazon Redshift

Implementing manual WLM - Amazon Redshift

Thank you,

[Wikkleyn_81]

Hi @PK950309 The error looks like an issue with the odbc driver or in Amazon. 

One option is to increase the cahce size on the odbc configuration:
https://stackoverflow.com/questions/39140513/redshift-odbc-errors 

[Natarajan_M]

Hi @PK950309 , It looks like out of memory issue PBI cant handle all the data at once . 

can you try to create the incremental refresh (Partitions) and load the data in batches ? 

create one parameter loadalldata True/False

let 

your query logic ,

#"FirstNRows" = Table.FirstN(Source, 10),
#"DataLoad" = if LoadAllData then Source else #"FirstNRows",

in #"DataLoad"

Keep this parameter to false and do a refresh so this creates the all the partitions then use the ssms studio to refresh partitions in batches . then the daily refresh will process only the selective partitions based on your setup.

Thanks.

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.