SSAS tabular model timeout raised during processing

Question

When doing a Full Process on a tabular model to an Azure Analysis Service model I get the following error after 10 minutes into the processing:

Failed to save modifications to the server. Error returned: 'Microsoft SQL: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.. The exception was raised by the IDbCommand interface.

Technical Details:
RootActivityId: cd0cfc78-416a-4039-a79f-ed7fe9836906
Date (UTC): 2/27/2018 1:25:58 PM
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.

The data source for the model is Azure Data Warehouse and SSAS authenticates to it via SQL authentication. When the Timeout occurs some partitions have retrieved all their rows but the others are still processing. The model contains 11 tables each with a single partition.

I get the error both when processing with Visual Studio 2015 and SSMS 2017. I can't see any SSAS server properties with a 10 minute (600 second) timeout. Individual table processing can be done without the timeout issue since individually they all complete in under 10 minutes.

I've tried setting the timeout property in the dataSources.connectionDetails object in my Tabular Model Scripting Language json file (i.e. Model.bim). But editing it drops the authentication credentials, and then resetting the credentials drops the timeout property. So I don't know if that property is even relevant to the timeout error issue.

An example of a partition query expression I'm using:

let
    Source = #"SQL/resourcename database windows net;DatabaseName",
    MyQuery = 
    Value.NativeQuery(
        Source,
        "SELECT * FROM [dbo].[MyTable]"
    )
in
    MyQuery

Are you using the Modern Get Data experience (Power Query) or the legacy experience? — GregGalloway
@GregGalloway I'm using the modern Power Query approach which contains a native SQL query in it. — Peadar Doyle

Peadar Doyle Peadar Doyle · Accepted Answer · 2018-03-01T08:21:17

So thanks to GregGalloway's prompting I've figured out the timeout can be set on a per Partition basis using the Power Query M language.

So the data access parts of my TMSL object now look like so...

The model.dataSource is as so:

"dataSources": [
  {
    "type": "structured",
    "name": "MySource",
    "connectionDetails": {
      "protocol": "tds",
      "address": {
        "server": "serverName.database.windows.net",
        "database": "databaseName"
      },
      "authentication": null,
      "query": null
    },
    "options": {},
    "credential": {
      "AuthenticationKind": "UsernamePassword",
      "Username": "dbUsername",
      "EncryptConnection": true
    }
  }
]

And the individual Partition queries are as so (note the CommandTimeout parameter):

let
    Source = Sql.Database("serverName.database.windows.net","databaseName",[CommandTimeout=#duration(0, 2, 0, 0)]),
    MyQuery = 
    Value.NativeQuery(
        Source,
        "SELECT * FROM [dbo].[MyTable]"
    )
in
    MyQuery

So now I'm explicitly setting a timeout of 2 hours for the Partition query.

SSAS tabular model timeout raised during processing

1 Answers