Power Bi refresh error

[Anonymous]

Hi ,

I have facing the below error when the refresh is happening on power bi Service. We're using Amazon redhsift database and using gateway to refresh the data. Also the data huge to load. Attaching the error message

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [HY000] [Microsoft][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState HY000] out of memory for query result\n"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"AmazonRedshift"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"rsw-bi-pr-euwe01-gdn-01.cbowvdy9ja9x.eu-west-1.redshift.amazonaws.com:5439;gdnprod"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}}

Thank you!

[v-chenwuz-msft]

Hi @Anonymous ,

The reason for the failure to refresh is a timeout, consider reducing the size or complexity of your dataset, or consider refactoring the large dataset into multiple smaller datasets.

And only refresh the last part, you can use Incremental refresh. Only the most recent data that has changed needs to be refreshed.

Refer:

Troubleshooting refresh scenarios - Power BI | Microsoft Docs

Optimize Microsoft Power BI Premium capacities - Power BI | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Hi @lbendlin ,

We're using Pro account with Premium workspace. 

[lbendlin]

Which Premium Capacity? P1, P2, or P3?

[Anonymous]

Hi @lbendlin 

I just wanted to give a brife explnation on my scnerio,
Initialy we  load only 2 months of data on the materialised views and publishing the report. Once the report is published we ask data engineers to load the remaining data which is 2 years of data nearly 8gb. After the 2 years of data ingested, trying to do the refresh on power bi service using gateway but is is throwing an error. Please suggest what should follow here? And also, for an example, if we're loading the 2 months of data initially. And then adding 3 more months of data additionally on the same view once after the report published with 2 months data. Is it possible to do refresh for newly added data without refreshing the last added 2 months data.
Kindly help

[lbendlin]

Read about bootstrapping

Troubleshoot incremental refresh and real-time data in Power BI - Power BI | Microsoft Docs

[Anonymous]

Hi @lbendlin 

I honestly not sure about the capacity. But today we got time out once the dataset is tried to refresh for 5 hours.

please find the error message:
{"error":{"code":"DM_GWPipeline_Gateway_TimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_TimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: mv_mo_v1

[lbendlin]

That is a different issue.  Power BI Service has a hard limit of 300 minutes for dataset refreshes.

[lbendlin]

out of memory for query

You need a better capacity SKU for that dataset, or you need to enable Gen2 auto sizing.  Which SKU are you running on now?