Power BI 中的 MDX 查询性能的优化

优化 Power BI 中的 MDX 查询性能：精简指南

在数据分析中，查询性能直接影响用户体验和决策效率。Power BI 分析服务表格引擎的最新更新显著提升了多维表达式（MDX）查询性能，尤其对 Microsoft Excel 用户。本文将简明介绍 MDX Fusion 的工作原理、其价值，以及如何利用它优化 Power BI 数据集，实现快速、高效的报表。

什么是 MDX 及其性能瓶颈

MDX 是 Excel 等工具用于查询多维数据模型（如 Power BI 数据集）的语言。创建数据透视表或使用“在 Excel 中分析”功能时，Excel 生成的 MDX 查询可能涉及多个粒度级别（子汇总、类别汇总、总计），触发大量存储引擎（SE）查询。每个 SE 查询扫描数据集，特别是在 DirectQuery 模式或大型数据集下，过多查询会导致性能下降。

MDX Fusion：性能优化的核心

Power BI 引入的 MDX Fusion 是一种公式引擎（FE）优化技术，类似 DAX Fusion，通过减少 SE 查询数量提升性能。其关键优势包括：

• 合并 SE 查询：传统 MDX 查询可能为不同粒度（如子类别、类别、总计）生成多个 SE 查询。例如，计算“平均单价”和“总销售额”可能需要至少六次 SE 查询。MDX Fusion 合并这些查询，基于子类别汇总直接计算类别汇总和总计，显著减少扫描次数。
• 优化 DirectQuery 模式：MDX Fusion 减少对数据源的查询，降低外部数据库负载，特别适合大型数据集。
• 自动适配常见模式：针对 Excel 等工具生成的常见 MDX 查询模式，MDX Fusion 无需手动调整即可提升性能。

案例分析：MDX Fusion 的效果

以 AdventureWorks 数据为例，Excel 生成以下 MDX 查询，展示 2014 年的平均单价和总销售额：

SELECT
   {[Measures].[Avg Unit Price],[Measures].[Total Sales Amount]} ON COLUMNS,
   NON EMPTY Hierarchize(DrilldownMember(CrossJoin(
{[DimProductCategory].[EnglishProductCategoryName].[All],[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].AllMembers}, {([DimProductSubcategory].[EnglishProductSubcategoryName].[All])}), [DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].AllMembers, [DimProductSubcategory].[EnglishProductSubcategoryName]
    )) ON ROWS 
FROM [Model] WHERE ([DimDate].[CalendarYear].&[2014])

传统模式下，该查询可能触发以下 SE 查询：

粒度级别	平均单价查询	总销售额查询
子类别汇总	FactInternetSales 按产品类别和子类别分组的平均单价（2014）	FactInternetSales 按产品类别和子类别分组的总销售额（2014）
类别汇总	FactInternetSales 按产品类别分组的平均单价（2014）	FactInternetSales 按产品类别分组的总销售额（2014）
总计	FactInternetSales 的总平均单价（2014）	FactInternetSales 的总销售额（2014）

这至少需要六次 SE 查询。以下是 SQL Server Analysis Services 2019 的查询跟踪，显示优化前的七次 SE 查询（包括日期维度属性的额外查询）：

MDX Fusion 优化后，公式引擎实现以下改进：

1. 基于子类别计算：类别汇总和总计直接从子类别汇总推导，无需额外 SE 查询。

2. 合并度量查询：由于两个度量访问同一表并使用相同条件，MDX Fusion 将查询合并为一次 SE 查询：

   • FactInternetSales 按产品类别和子类别分组的平均单价和总销售额（2014）。

优化后的查询跟踪显示，SE 查询从七次减少到两次：

结果：查询速度大幅提升，DirectQuery 模式下数据源负载显著降低。

如何应用 MDX Fusion

以下是利用 MDX Fusion 提升性能的步骤：

1. 部署到 Power BI：确保数据集部署在 Power BI 服务中，MDX Fusion 仅支持 Power BI 表格引擎（不适用于 SQL Server Analysis Services 2019 或更早版本）。
2. Excel 连接：通过“在 Excel 中分析”、Power BI 数据集功能或 Power BI Premium 的 XMLA 端点连接数据集，自动享受 MDX Fusion 优化。
3. 优化数据模型：尽管 MDX Fusion 降低了对度量优化的需求，合理的数据模型设计（如索引和分区）仍能进一步提升性能。
4. 性能监控：使用 Power BI 性能分析工具或查询跟踪，确认 SE 查询数量减少，验证优化效果。

注意事项

• 适用范围：MDX Fusion 针对常见 MDX 查询模式优化，复杂自定义查询可能需要额外调整。
• 模式差异：DirectQuery 模式下性能提升更明显，因其减少了数据源查询；导入（Vertipaq）模式同样受益。
• 工具限制：目前主要惠及 Excel 等 MDX 客户端，其他工具可能需不同优化策略。

结论

MDX Fusion 是 Power BI 性能优化的重要突破，为 Excel 和其他 MDX 客户端提供即时性能提升。通过减少 SE 查询，MDX Fusion 加快查询速度，降低 DirectQuery 模式下的数据源压力。立即将数据集部署到 Power BI，通过 Excel 或 XMLA 端点连接，体验更流畅的报表性能！