如何高效实现基于语义模型的跨表格模型查询？大数据量优化方案

问题背景 #

需要从第三方语义模型获取动态过滤数据（数十万行级别），以此过滤两个独立的表格模型并返回汇总结果。当前采用M语言构建动态DATATABLE的方式实现，在数据量较大时性能急剧下降。

核心性能瓶颈分析 #

当前方案将过滤数据拼接成文本形式嵌入DAX查询，存在以下关键问题：

• 数十万行的文本拼接会产生超长查询语句，大幅增加网络传输开销与SSAS服务器的解析负担
• DATATABLE在内存中构建临时表时无法利用索引优化关联计算，导致后续CALCULATE的过滤逻辑效率低下

优化方案 #

1. 使用外部共享表格作为过滤源（推荐） #

若SSAS服务器环境支持，可将第三方语义模型的过滤数据导出到共享数据源（如SQL Server临时表、Azure SQL数据库、共享目录下的CSV文件等），然后在两个目标表格模型中通过DirectQuery或导入模式引用该过滤表：

• 在目标模型中创建与过滤表的逻辑关系，或在DAX中使用TREATAS实现关联
• 直接在DAX查询中使用过滤表作为筛选条件，彻底避免文本拼接与动态DATATABLE的开销

示例DAX查询（替代原动态生成逻辑）：

EVALUATE
ADDCOLUMNS(
    FilterTableFromThirdModel,
    "Measurement",
    CALCULATE(
        [SomeMeasure],
        TREATAS(
            SELECTCOLUMNS(FilterTableFromThirdModel, 
                "Column", [ColumnName], 
                "Date", [Date], 
                "StartTime", [StartTime], 
                "EndTime", [EndTime]),
            local_table[Column],
            calendar[Day],
            time_table[Period],
            time_table[Period]
        )
    )
)

2. 分批次处理过滤数据 #

将数十万行的过滤数据拆分成多个小批次（如每1万行一批），分别执行查询后再合并结果：

• 在M语言中用Table.Split拆分_filter_table
• 循环处理每个批次，生成对应DAX查询并执行，最后合并所有批次结果
• 降低单条查询的长度，减少服务器解析压力与内存占用

示例M语言片段：

batched_fact1 = List.Transform(
    Table.Split(_filter_table, 10000),
    (batch) =>
        let
            batch_rows = Text.Combine(batch[row_string], ","),
            qrytext = "define table filter_table = datatable(""ColumnName"", STRING, ""Date"", DATETIME, ""StartTime"", DATETIME, ""EndTime"", DATETIME, {" & batch_rows & "}) evaluate ADDCOLUMNS(filter_table, ""Measurement"", CALCULATE([SomeMeasure], local_table[Column]=[ColumnName], time_table[Period]>=[StartTime], time_table[Period]<=[EndTime], calendar[Day]=[Date]))",
            result = AnalysisServices.Query("my source", "widgets", [Query=qrytext])
        in
            result
),
fact1 = Table.Combine(batched_fact1)

3. 利用SSAS外部上下文传递（仅限高版本支持） #

若使用Azure Analysis Services或SQL Server 2022+的SSAS Tabular，可通过外部上下文参数传递过滤数据：

• 将过滤数据序列化为JSON格式，作为参数传递给DAX查询
• 在DAX中使用OPENJSON解析参数并构建过滤逻辑，避免直接拼接大量文本

示例DAX查询：

DEFINE
VAR FilterData = OPENJSON(@FilterParameter)
WITH (
    ColumnName STRING,
    Date DATETIME,
    StartTime DATETIME,
    EndTime DATETIME
)
EVALUATE
ADDCOLUMNS(
    FilterData,
    "Measurement",
    CALCULATE(
        [SomeMeasure],
        local_table[Column] = [ColumnName],
        calendar[Day] = [Date],
        time_table[Period] >= [StartTime],
        time_table[Period] <= [EndTime]
    )
)

方案选择建议 #

• 优先选择外部共享表格作为过滤源，性能最优且维护成本低
• 若无法实现数据源共享，采用分批次处理作为折中方案
• 若使用高版本SSAS环境，可尝试外部上下文传递进一步优化性能

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