Synchronisation Error with Data Gateway to SQL

[Luy]

Hello Folks, 

i have a Problem with a Timeout on Data Synchronisation and cant find the reason. 

We load lots of tables from an SQL Server with an on-premis Gateway, in some synchronisations we run into a timout after neraly 18 seconds. Its not allways the same table or the same source.

We have already checked the Parameters on the Source, Dataset, Workspace, Gateway and Capacity Level and cant find anything that is set to 18 seconds. 

Do you have any idea where the 18 seconds came from and where we can change it? Im runnig out of ideas 😕 

here are an error as example: 

Executing query (timeout=17949) "<ccon>let __AS_Query__ = Customer_kpi
,
__AS_Table__ = Table.FromValue(__AS_Query__),
__AS_Compact__ = Table.RemoveColumns(__AS_Table__, Table.ColumnsOfType(__AS_Table__, { type table, type record, type list })),
__AS_Effective__ = Table.TransformColumnNames(__AS_Compact__, Text.Clean),
__M = #shared[[List.ConformToPageReader],[Graph.Nodes],[Value.Lineage]]?,
__AS_TransformClassification__ = (value) => Value.ReplaceType(Table.Distinct(List.Accumulate(__M[Graph.Nodes](__M[Value.Lineage](value)), #table({"ColumnName", "LabelId"}, {}), (current, node) => Table.Combine({current, Table.SelectColumns(Table.ExpandRecordColumn(Table.SelectRows(Value.Traits(node), each ([Provider] = "SQL" or [Provider] = "MicrosoftInformationProtection") and [Identifier] = "Classification"), "Value", {"LabelId"}), {"ColumnName", "LabelId"}, MissingField.UseNull)}))), type table [ColumnName=nullable text, LabelId = text]),
__AS_WithClassification__ = __M[List.ConformToPageReader]({__AS_Effective__, __AS_TransformClassification__}),
__AS_Result__ = if __M[List.ConformToPageReader] = null then __AS_Effective__ else __AS_WithClassification__
in __AS_Result__</ccon>", requestId=b144462e-3717-476d-8a78-341290e97db4

Kind Regards

Marcel 

[Anonymous]

Hi @Luy ,

Here are a few potential areas to investigate to resolve the timeout issue:

1. Check your gateway configuration. Make sure your on-premise data gateway is up to date. Check for any network or firewall rules that may be causing timeouts.

2. Adjust the timeout settings in Power BI.

3. Ensure that the data privacy level is set correctly. Incorrectly configured privacy levels can sometimes cause data refresh issues.

Here are some links related to your issue that I hope will help you.

Solved: Power BI Report refrershes failing with Timeout Er... - Microsoft Fabric Community

Solved: Power Bi Data refresh error - Timeout Error - Microsoft Fabric Community

Troubleshoot the on-premises data gateway | Microsoft Learn

Best Regards,

Clara Gong

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

[Luy]

Hello @Anonymous,

unfotrunatly i have checked these points all befor i opened this Post. 

1. Gateway is running on the Server of the first SQL, 2. Server is attached to the Gatewaycluster. Port 80 is open for all Servers. Dateway is up to date and Timoeout is set to 2 min. We will try to setpup a new Gateway on an extra Server on Friday and will look if this helps.

2. Timeoutsettings are set to 2 min in Powerquery, still got the Error after 18 seconds. If a successfull Sync happens the sync for the whole Model is not longer than 4 min. 

3. Data Privacy Level is set on both Data sources to "organisational" so there shouldn't be a problem. 

I have read the tickets you mentioned, but the errors i got are all different. 

We got the Error in the Gatewaylogs mentioned above, in PBI Service it looks like this.

{"error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","parameters":{},"details":[],"exceptionCulprit":1}}}

Maybe this says more to you? 

Kind Regards

Marcel Luy

[GilbertQ]

Hi @Luy 

Would it be possible to split each of the data sources that you have into a single query because it could be one of them that is causing the issue?

[Luy]

Hey, thanks. i will try it. In this case, we only have 2 datasources from 2 diffrent SQL Server, Error is occuring on both Servers. 

But i will give it a try tomorrow.