SSAS表格模型自动刷新咨询：T-SQL存储过程调用TMSL可行性及示例

当然可以！通过T-SQL存储过程调用TMSL（Tabular Model Scripting Language）脚本是自动刷新SSAS表格模型的成熟方案，我这就给你详细的示例和注意事项。

完全没问题！SSAS支持通过EXECUTE XMLA语句执行TMSL脚本，而T-SQL存储过程可以封装这个逻辑，方便你通过SQL Server代理作业、应用程序调用等方式实现模型的自动刷新。

下面是一个完整的实现流程，包含存储过程创建、TMSL脚本示例和调用方式：

1. 创建T-SQL存储过程 #

这个存储过程接受模型名和刷新类型作为参数，动态生成TMSL脚本并执行：

CREATE PROCEDURE dbo.RefreshSSASTabularModel
    @ModelName NVARCHAR(128),
    @RefreshType NVARCHAR(32) = 'Full' -- 可选值：Full, ClearValues, Calculate, DataOnly, Automatic, Add
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- 构建TMSL刷新脚本
        DECLARE @TMSLScript NVARCHAR(MAX) = N'
        {
            "refresh": {
                "type": "' + @RefreshType + '",
                "objects": [
                    {
                        "database": "' + @ModelName + '"
                    }
                ]
            }
        }';

        -- 替换为你的SSAS实例和数据库名
        DECLARE @SQL NVARCHAR(MAX) = N'
        EXECUTE [YourSSASInstanceName].[YourTabularModelDB].$System.DISCOVER_XMLA 
        @Statement = ''' + REPLACE(@TMSLScript, '''', '''''') + ''';';

        EXEC sp_executesql @SQL;
        PRINT N'模型「' + @ModelName + '」已成功完成' + @RefreshType + '刷新';
    END TRY
    BEGIN CATCH
        PRINT N'模型刷新失败：' + ERROR_MESSAGE();
        -- 可选：添加日志记录逻辑，比如插入错误日志表
        THROW;
    END CATCH
END
GO

2. 刷新特定表的TMSL调整 #

如果你不需要刷新整个模型，只想刷新特定表，可以修改存储过程中的TMSL脚本，比如刷新Sales和Products表：

{
    "refresh": {
        "type": "DataOnly",
        "objects": [
            {
                "database": "YourTabularModelDB",
                "table": "Sales"
            },
            {
                "database": "YourTabularModelDB",
                "table": "Products"
            }
        ]
    }
}

3. 调用存储过程 #

直接执行以下语句即可触发刷新：

-- 刷新整个模型（Full类型）
EXEC dbo.RefreshSSASTabularModel @ModelName = 'YourTabularModelDB', @RefreshType = 'Full';

-- 仅刷新数据不计算（DataOnly类型）
EXEC dbo.RefreshSSASTabularModel @ModelName = 'YourTabularModelDB', @RefreshType = 'DataOnly';

• 权限配置：执行存储过程的SQL账号需要具备SSAS模型的Process权限，同时要有执行sp_executesql和访问SSAS DISCOVER_XMLA接口的权限。
• 刷新类型选择：不同类型对应不同操作，按需选择：
  • Full：完全重新加载数据并计算
  • DataOnly：仅刷新数据源数据，不重新计算DAX
  • Calculate：仅计算模型中的DAX表达式
  • ClearValues：清除模型中的所有数据值
• 自动化触发：可以创建SQL Server代理作业，设置定时任务调用这个存储过程，实现无人值守的自动刷新。
• 错误处理：示例中的TRY/CATCH块可以帮助你捕获并排查刷新失败的问题，建议结合日志表记录错误详情。

内容的提问来源于stack exchange，提问作者user6479259