Gateway Refresh Error SharePoint: Request failed

[fengwufangzhu]

Hi Folks,

We have a report runs well on desktop and primary gateway and now we would like to set up a backup gateway connection. The refresh cannot get through after running for about 1 hour and the same error always appear:SharePoint: Request failed

We tried many time, such as switching between gateways, the old one always work, the new one always fail. Also tried clear permission, re-login, publish to a different workspace...

Our PBI gateway admin could not figure out why. I suspect this could be some version or compatibility issue?

[fengwufangzhu · Accepted solution]

We raised a ticket to Microsoft and comfirms it is due to set up of gateway. When no mash of data is done, the access of to sharepoint route via "Online", hence by default there is access. However in case of mash up data, access to sharepoint route via gateway, hence gateway must have access to both sharepoint and database. This is confirmed when we try to create a pbi data connection to sharepoint, it could not go through. This particular gateway was set up outside of the organisation network unlike the other 2.

[arvindsingh802]

This could be because of SharePoint request limit
Can you answer below to help better

1. Are you having multiple tables connecting to SharePoint

2. Are you merging tables in same dataset

3. What is the size of file/data that you are pulling from SharePoint

4. As this is SharePoint online, why are you using gateway

[fengwufangzhu]

1. Are you having multiple tables connecting to SharePoint

-2 table connect to 2-3 tabs in sharepoint.

2. Are you merging tables in same dataset

-Have some joins, append, relationships happen to the same dataset.

3. What is the size of file/data that you are pulling from SharePoint

-Very small, 1000 rows

4. As this is SharePoint online, why are you using gateway

-I have main data source connect to database.

Thing i dont understand is that it only happen to 1 out of 3 gateway connection.

[arvindsingh802]

This happends because suring merge,append the Power Query send multiple request to SharePoint thus it will be hitting SharePoint concurrent request limit and slow down the refresh
Please follow below and see the difference

1. Create saperate dataflow for database and SharePoint (Just inital load, no transformation)
2. Use above dataflow as source for your dataset

[fengwufangzhu]

Nope, not working. I created a 1 DB+1 sharepoint test file with reduced rows. Created a new dataset use merge as new, and still fails.

Logically if this is the cause, it should not only happen to 1 out of 3 gateways.

[arvindsingh802]

Please try creating 2 saperate Dataflwo (1 for DB and 1 for SharePoint)
then use as source in your Dataset to perform merge.