Error while refreshing from service

[GuidoMS]

Hi!

We are having a problem when trying to refresh our data set from power BI service. When we do it from desktop there's no error.

Said dataset has data from our database (from created views) and many metrics done in PBI. The connection is made through ODBC and we usually download the dataset to add new tables or columns then upload it to service again.

We were not having issues for like 1 year but now we are getting the following error:

• Data source error{"error":{"code":"DM_GWPipeline_Gateway_TimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_TimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: VW_PROTOCOLOESTUDIOS.
• Cluster URIWABI-SOUTH-CENTRAL-US-redirect.analysis.windows.net
• Activity ID9bd36253-4e95-4569-9527-dacfcf061d53
• Request ID73b29dc9-c728-782c-2007-6a5bb09b7bbb
• Time2023-11-27 09:18:22Z

I tried creating a new data set with just two tables (one being VW_PROTOCOLOESTUDIOS) but i get the same error. 

VW_PROTOCOLOESTUDIOS is a table that contains 3.4kk rows and it has 3 columns with INT, VARCHAR(10), VARCHAR (80). What i'm going with this is that we have other tables with more than 8kk rows and many columns each. I am not seeign any strange or unique thing about it. The view is very simple also as it just searches for 3 fields from 2 tables and creates the queried one. 

I searched everywhere and tried a few things but nothing worked. I also found a thread where people was saying this is an issue with pwoer BI. It seems odd to be referencing a specific table though.

Any help would be greatly appreaciated.

Thanks

[GuidoMS]

I want to add that we tried to refresh a new dataset only containing the table that's giving timeout error.

This is the error we get:

{"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":"La tabla no tiene ninguna columna visible y no puede consultarse."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"VW_PROTOCOLOESTUDIOSCOPIA"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: VW_PROTOCOLOESTUDIOSCOPIA.

That's spanish for "The table does not contain visible columns and the table can't be queried"

The problem is that the table DOES have data and visible columns. There's no transformation applied to it.

Any hint?

[ibarrau]

Hi. You can try expandind the timeout window. By default, the odbc datasource has 10 minutes timeout, if your source takes longer then it throws the error. You might have been ok for 1 year, but the table was probably growing all that year. You can open the original pbix file with Power Bi Desktop and change it at Power Query Editor. Check the source step and the docs:
https://learn.microsoft.com/en-us/powerquery-m/odbc-datasource

You can change the commandtimeout. Add a parameter like [ CommandTimeout = #duration(0,0,20,0) ]

That is a 20 minutes duration for the timeout.

I hope that helps,

[GuidoMS]

I am trying to answer with a new reply but i think i'm adding symbols that seem like HTML and my answer is getting erased.

We did a new test with trying to refresh from service a new dataset with only 1 table (the one referenced in the error)

We are getting an error that indicates that the table is empty. "there's no visible columns in the table and data cannot be queried" (translated from spanish). Pretty sure this is whats causing the error as it times out trying to read data thats apparently not there.

The thing is that the table HAS data and there are no transformations applied to it. 

Any hint?

[ibarrau]

Ok, now it sounds like there is a problem on the View you are getting from the source. You could refresh the view at Power Bi Desktop and check if it has data and how long it takes to know the timeout too.

But if it's coming blank, it sounds like a process on the backend was't ready or there is an issue with it.

Regards

[GuidoMS · Accepted solution]

We found the issue. We never stopped a second to check the basic configuration. The problem was the configuration of the ODBC where the Gateway is located.

Thanks!

[GuidoMS]

I dont know if I am setting it wrong. I tried to do it in the specific table that's refrenced in the timeout error. I entered the advanced query editor and added the parameter. It looks like this:

let
Source = Odbc.DataSource("dsn=RamosDesa", [HierarchicalNavigation=true], [CommandTimeout=#duration(0,0,40,0)]),
VW_PROTOCOLOESTUDIOS_View = Source{[Name="VW_PROTOCOLOESTUDIOS",Kind="View"]}[Data]
in
VW_PROTOCOLOESTUDIOS_View

When I save that change however, I see an error with the table itself:
Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]

The Hierarcy function is already there in every query. Should i add the commandtimeout in another instance?

[ibarrau]

It's 2 parameters that the function has. So when I was showing the example of []. It's all options inside that. You can try adding those together

[HierarchicalNavigation=true, CommandTimeout=#duration(0,0,40,0)])

Regards

[GuidoMS]

My bad. Thanks for the clarification

[shadow9600]

All of my Semantic models just started having Timeout errors about 10 minutes ago.

[shadow9600]

My datasets have started to complete their refreshes now. Looks like it was a transient service issue.

[GuidoMS]

I am still not able to refresh my data set. This problem started a few days ago and that's why I'm sure is something deeper actually.