While refreshing your PowerBI semantic model in the Desktop always succeeds, you might experience that once you deploy it to the service some of the bigger tables intermittently start to give you timeout errors.
An error occurred while processing the data in the dataset.
Refresh failed:MyDataSet has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.
{"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":"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"11"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"servername;adventureworks"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"-2"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: FIN Fact_Sales_Orders.
This could be because it is finally running on a production environment where many concurrent queries are run and it wasn’t foreseen to add your big query on top of it, or suddenly it is refreshed at night with concurrent batch jobs, which you don’t experience while developping during the day.
A lot of mid term solutions are possible to address this, but if you need a short term one, this could be it:
= Sql.Database("servername", "adventureworks", [CommandTimeout=#duration(0, 0, 20, 0), Query="SELECT ...This increases the timeout from standard 10min to 20min. You can also specify this in the “Get Data” wizard, under Advanced options.
