On-Demand refresh failed - Gateway Timeout - SharePoint Document Library

Hello everyone,

I am facing this initial refresh failing when trying to do an on-demand refresh after configuring incremental refresh settings and publishing report to power bi servive.

I have 6 Document Libraries (SharePoint On-Prem) from where I'm fetching metadata using OData feed. I also limited the number of columns I need on the report

http://<sharepointsite>/_vti_bin/listdata.svc/Documents?$select=ContentType,Path,Id,Document_ID,Title,Division,Department,Section,FileUrl,Year,ExpiredDate,Modified

Later I append all queries as new to get a combined table for the sake of reporting.

The total number of rows I get is around 400,000 after loading into power bi desktop. It takes more than 1 hour to load and shows size of 500+ mb

When I try to do the initial on-demand refresh, it runs for about 2 hours and then fails. Following is the error detail

Data source error:
{"error":{"code":"DM_GWPipeline_Gateway_TimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_TimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}}

Cluster URI: WABI-WEST-EUROPE-D-PRIMARY-redirect.analysis.windows.net

Activity ID: 78cf2d1a-92e2-4d7f-8b72-0e29ecefe0ba

Request ID: 899163c9-e83a-2f8f-2d21-b4d09cee74b0

Time: 2022-08-31 07:30:29Z

Not sure how CommandTimeout or ConnectionTimeout will work with Odata and will really fix it?

Is there anything else I can do to fix this? 

Any help is appreciated, Many thanks. otravers GilbertQ @ibarrau lbendlin Hariharan_R