SSAS 表格模型超时在处理过程中引发 [英] SSAS tabular model timeout raised during processing

问题描述

在表格模型上对 Azure 分析服务模型执行完整过程时，我在处理 10 分钟后收到以下错误:

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.

模型的数据源是 Azure 数据仓库，SSAS 通过 SQL 身份验证对其进行身份验证.当超时发生时，一些分区已经检索了它们的所有行，但其他分区仍在处理中.该模型包含 11 个表，每个表都有一个分区.

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.

我在使用 Visual Studio 2015 和 SSMS 2017 处理时都收到错误消息.我看不到任何超时 10 分钟(600 秒)的 SSAS 服务器属性.单个表处理可以在没有超时问题的情况下完成，因为它们都在 10 分钟内完成.

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.

我已尝试在我的表格模型脚本语言 json 文件(即 Model.bim)中的 dataSources.connectionDetails 对象中设置 timeout 属性.但是编辑它会删除身份验证凭据，然后重置凭据会删除 timeout 属性.所以我不知道该属性是否与超时错误问题有关.

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

推荐答案

感谢 GregGalloway 的提示，我发现可以使用 Power Query M 语言在每个分区的基础上设置超时.

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.

所以我的 TMSL 对象的数据访问部分现在看起来像这样......

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

model.dataSource 是这样的:

"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
    }
  }
]

并且各个 Partition 查询也是如此(注意 CommandTimeout 参数):

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

所以现在我明确地为分区查询设置了 2 小时的超时.

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

这篇关于SSAS 表格模型超时在处理过程中引发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！