Weird Semantic Model Refresh Issue

[id013]

Hi,

So I'm having an intermittent refresh issue with a semantic model. The dataset isn't very large and normally takes 2-3 minutes to complete when it runs nightly. However lately I've been noticing it fail. I finally had time to try to troubleshoot it and it looks like it was due to a specific table. 

I checked the table in Power Query editor in PBI Desktop and noticed that when I try to refresh the preview for that table it takes a long time and eventual fails due to timeout. However, if I take the SQL query that it's running and run it in SSMS it takes 3-5 seconds to run and returns ~2200 records. What I've found however is if I make ANY change to the query, for example add in a single commented line which wouldn't affect the query at all (ex -- Comment for Testing), save it then try to refresh the preview for that table it runs perfectly and only takes 5 seconds to refresh. 

Wierder still is if I edit the query again and remove the commented line so it's back to it's previous state, save it then try to refresh the preview, it returns to its' previous behavour  where tie takes a long time and fails due to a timeout. Refreshing on Power Query Editor\PBI desktop shouldn't go through the gateway right? I'm going crazy trying to figure out the issue. So normally I would just assume it's a network issue but like I said if I make an innocuous change to the query like add a dummy comment it refreshes fine when I remove the comment it stops refreshing.

Just seeing if anyone else has any ideas about what's going on. I tried doing a diagnostic when it was running but the only error I get is this:

Exception:
ExceptionType: Microsoft.Data.SqlClient.SqlException, Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxx
Message: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The network connection was aborted by the local system.)
StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at Microsoft.Data.SqlClient.TdsParserStateObje...

[Anonymous]

Hi @id013 ，

Thank you @SaiTejaTalasila  very much for the solution, I have some other thoughts on the subject:

Thank you for your detailed description of the problem encountered. Based on your description, the issue may be related to Power Query's Query Folding and caching mechanism:
Query Folding is an important optimization mechanism in Power Query that pushes the data transformation step back to the source system so that the query can be executed using the computational power of the source system. This can significantly improve query performance, especially when working with large amounts of data.
Checking Query Folding Status
In the Power Query editor, right-click on a query step to see if the “View Native Query” option is available.
Make sure that as many steps as possible can be collapsed. For example, simple actions such as filtering and selecting columns can often be collapsed. Here are some suggestions that can help optimize query folding:
1. Use simple steps: Prioritize steps that can be collapsed, such as filtering, selecting columns, and sorting.
2. Avoid complex calculations: Try to avoid complex calculations in Power Query and instead push these calculations to the source system.
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Caching Issues
Power Query uses caching to improve performance when previewing data, but sometimes caching can lead to inconsistent behavior; clearing the cache can help troubleshoot this issue.
Use storage mode in Power BI Desktop - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[SaiTejaTalasila]

Hi @id013 ,

Republish your report and see whether the same issue occurs or not.

Thanks,

Sai Teja 

[id013]

@SaiTejaTalasila 

So I'm replicating the issue within PBI Desktop not on PBI service. Regardless republishing the model as is did not make any difference, it still timed out. But as I said earlier if I make any change on the query (ie add in a commented line) PBI desktop will be able to load the data successfully. IF i then rever that change, in this case remove teh commented line, PBI Desktop will once again time out loading the data. I'm not discounting an issue with our source data as it seems to happen with a specific table. I'm just trying to understand why making any change to the query results in a successful data load on PBI Desktop and reversion of that change results in the data load time out again. And just to clarify if I take the data model that is loading correctly on PBI desktop (with the superflous comment added) it loads correctly on PBI desktop as well. 

[Anonymous]

Hi @id013 ,
In response to the query loading timeout issue you are experiencing in Power BI Desktop, I have reviewed the official documentation and the following may be the reason why you are experiencing this issue:
1. When you make any changes to the query (adding comment lines), Power BI may recompile and optimise the query execution plan. At this point, the data will load successfully, but if the changes are undone, Power BI may use the old query plan that was previously cached, which may cause a data timeout.
2. Changes made to the query cause Power BI to reevaluate the query's metadata, which may regenerate the data model, thus avoiding the previous timeout issue.
3. You can try splitting the query step into multiple simple steps and then loading the data incrementally to try to locate the part where the problem occurs.
4. You can use SQL Server Profiler to monitor what happens when the query is executed with and without comments, and you can view the differences and errors in the plan.
Below are the documents that I have found for you related to the case, I hope they are helpful.

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Use storage mode in Power BI Desktop - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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