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, Commented Feb 27, 2018 at 17:02
@GregGalloway I'm using the modern Power Query approach which contains a native SQL query in it. — Peadar Doyle
– Peadar Doyle, Commented Feb 28, 2018 at 12:08

Peadar Doyle · Accepted Answer · 2018-03-01 09:48:20Z

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.

Umberto · Accepted Answer · 2021-10-21 10:18:33Z

2

Data Source -> Options: increasing Command timeout (default 600 secs) will also do the trick:

answered Oct 21, 2021 at 10:18

Umberto

2,0603 gold badges24 silver badges27 bronze badges

1 Comment

user2462054 Over a year ago

I'd really like to know where this option is. I'm editing in Visual Studio 2022 a model to be deployed to an SSAS 2022 server and I can't find this Options tab anywhere.

Collectives™ on Stack Overflow

SSAS tabular model timeout raised during processing

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related