使用热表和冷表分区优化非常大的 Power BI 数据模型

项目
04/13/2024

本文介绍如何使用热表分区和冷表分区来优化非常大的数据模型。分区提供了一种将表数据划分为离散子集的方法。分区不会在标准 Power BI 数据建模工具中直接公开，但可以通过在 Power BI Desktop 中配置增量刷新策略来利用高级分区方法。增量刷新依赖于分区，如数据集的增量刷新和实时数据中所述。但是，配置热表分区和冷表分区超出了增量刷新策略可以实现的范畴，并假定熟悉典型的表分区方案和基于 XMLA 的工具。

先决条件

由于此分区技术相对复杂，它最适合在以下方面具有经验的高级用户：

了解表分区概念、导入模式分区、 DirectQuery 模式和双模式的工作原理。
了解如何使用基于 XMLA 的工具创建混合表。混合表使用一个或多个导入模式分区和一个 DirectQuery 分区。
了解可用于指定 DataCoverageDefinition的 DAX 函数的要求。这是 DirectQuery 分区的新属性，用于描述混合表的 DirectQuery 分区包含哪些数据，以便 Power BI 引擎可以在适当情况下从查询处理中排除此分区。排除 DirectQuery 分区有助于避免不必要的数据源查询并提高 DAX 查询处理的性能。
了解常规表关系和有限表关系之间的差异。例如，如果要根据相关日期维度表中的值定义事实数据表分区的数据覆盖范围，RELATED 函数非常有用。请记住，facts 表分区是 一个 DirectQuery 分区，与日期表的关系有限，RELATED 函数无法提取值。在此方案中，RELATED 仅当日期维度表为双表时有效。日期表必须处于 DirectQuery 或双模式。它不能是纯导入。

请注意，错误定义 DataCoverageDefinition 可能会导致错误的结果，因为 Power BI 可能会错误地从查询处理中排除 DirectQuery 分区。因此，请确保使用和不使用比较结果， DataCoverageDefinition 以确保它们相加。

何时使用热表分区和冷表分区

下面是一个示例，其中热分区和冷分区可以帮助微调混合表以便进行历史分析。假设你有一个非常大的数据源，这些数据源累积了多年。主要用途是分析过去几年的最新数据。有时，你还希望分析较旧的数据。也许你注意到最近销售额逐年大幅增长。这曾经发生过吗？这是自销售跟踪开始以来的最高销售高峰吗？

如果没有对热分区和冷分区的支持，这种历史分析将要求将所有历史数据以及较新的数据导入到事实数据表中。充其量是资源使用效率低下，因为主要分析甚至不使用任何较旧的历史数据。最坏的情况是，数据量太大，甚至无法完全导入。与导入模式相比，必须将数据模型切换到 DirectQuery 模式并接受性能损失，或者可以生成单独的模型并强制用户在报表之间切换。具有热分区和冷分区的混合表提供了更好的选择。

如何使用热表分区和冷表分区

首先，使用最新数据的热导入模式分区配置销售表，并将较旧的数据保留在冷DirectQuery 分区中，如下图所示，该表针对 AdventureWorks 示例数据模型的 FactInternetSales 表进行了说明。 OrderDateKey 大于或等于 20200101 的任何行都通过热导入模式分区导入到数据模型中。 OrderDateKey 小于 20200101 的行通过冷 DirectQuery 分区进行覆盖。现在，Power BI 可以使用导入模式快速提供主要用例，并且无需导入大量历史数据，这些历史数据只是偶尔分析，因为 DirectQuery 分区已涵盖此内容。

Adventure Works 示例数据模型的 Fact Internet Sales 表的屏幕截图。事实 Internet 销售表打开，其中显示了筛选的行。

如果你有一个 AdventureWorks 示例数据仓库并想要继续执行操作，下面是常规步骤：

创建数据集。使用 Power BI Desktop 创建 AdventureWorks 数据集和报表。在纯 DirectQuery 模式下包括所有表。然后，将表以外的 FactInternetSales 所有表转换为双模式。将 FactInternetSales 表保留为 DirectQuery 模式。
上传数据集。使用托管在 Power BI Premium 上的工作区，并为写入操作启用了 XMLA 终结点。
更新兼容性级别。在 SQL Server Management Studio (SSMS) 中打开包含 AdventureWorks 数据集的工作区。右键单击 AdventureWorks 数据集>>脚本数据库作为“创建”或“替换”，然后选择“新建查询编辑器”窗口。将 compatibilityLevel 属性设置为 1603 (或更高版本) 。选择“ 执行 ”或按 F5。验证操作是否已成功完成。

配置 FactInternetSales 表分区。右键单击 AdventureWorks 数据集>>脚本数据库作为“创建”或“替换”，然后选择“新建查询编辑器”窗口。将整个分区部分替换为以下部分。请确保更新 Sql.Database 行以指向环境中的 AdventureWorksDW 数据库。选择“ 执行 ”或按 F5。验证操作是否已成功完成。

   "partitions": [ 
    { 
      "name": "FactInternetSales-DQ-Partition", 
      "mode": "directQuery", 
      "dataView": "full", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    }, 
    { 
      "name": "FactInternetSales-Import-Partition", 
      "mode": "import", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    } 
  ],

处理数据模型。在 Power BI 门户中，使用 AdventureWorks 数据集打开工作区，并按需刷新数据集，以使用数据加载导入分区。
验证报表是否显示最近数据和历史数据。打开 AdventureWorks 并验证报表是否能够显示 2020 年 1 月 1 日之前和之后的销售交易的结果，如以下屏幕截图所示。

两个不同报表的屏幕截图。一个显示 2020 年的数据，一个显示 2019 年的数据。

定义 DirectQuery 分区的数据覆盖范围

该解决方案可无缝地对最近数据和历史数据工作。但是，默认情况下，Power BI 会查询所有表分区，因为它不知道每个分区涵盖哪些数据。因此，即使对于 DirectQuery 分区未涵盖的年份，Power BI 仍会查询 DirectQuery 分区。销售数据在导入分区中随时可用，DirectQuery 分区不贡献任何行，但这种多余的源查询仍会导致数据源上的明显负载，并导致 DAX 查询处理延迟。若要避免这种多余的源查询，请使用 DataCoverageDefinition。

如以下屏幕截图所示，Power BI 报表仍向数据源发送多个不必要的 2020 SQL 查询，因为每个视觉对象的 DAX 查询都会导致 Power BI 查询 DirectQuery 分区。

DAX 查询的屏幕截图。

通过将 DirectQuery 分区上的属性设置为dataCoverageDefinition以下 TMSL 代码片段中的属性，可以避免这些 SQL 查询。但请记住，在应用或更改数据覆盖率定义后，必须刷新数据集。进程重新计算足以评估数据覆盖率定义。如果忘记此步骤，触摸分区的查询将失败，并显示错误消息“表'[表名称]]中 DQ 分区的 DataCoverageDefinition 尚未计算。它需要重新处理”。

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        }

如前所述， dataCoverageDefinition 属性有助于消除不必要的数据源负载。它还提高了最近数据的分析性能，因为现在 Power BI 可以在适当情况下从 DAX 查询处理中排除 DirectQuery 分区。可以使用简单的 AND、OR 和 NOT 运算符为单个值以及区域定义简单的数据覆盖表达式。还可以使用 RELATED 函数基于维度表中与事实数据表有固定关系的列来定义数据覆盖范围。如果数据覆盖表达式使用维度表中的列，请确保维度表处于双重模式。还可以基于事实数据表本身的列定义数据覆盖范围。请参阅下表，了解受支持的操作，这些操作分为三个组。 

类型	注释	示例
基于值的单个谓词 ()	相等、不相等和 IN 运算符支持维度表和事实数据表	相关 ('Date'[Year]) = 2020 NOT RELATED ('Date'[Year]) = 2020 相关 ('Date'[Year]) in {2020， 2021， 2022} InternetSales'[SalesAmt] = CURRENCY (100.0) NOT InternetSales'[SalesAmt] = CURRENCY (100.0) InternetSales'[SalesAmt] IN {CURRENCY (100.0) ， CURRENCY (200.0) }
基于范围的单个谓词 ()	可以是比较运算符，如 >、 <、= >、 <= 要求维度表处于双模式	相关 ('Date'[Year]) > 2020 相关 ('Date'[Year]) <= 2020
多个谓词	相等、不相等和比较不支持 IN 运算符限制为双模式下的单个维度表	相关 ('日期'[Year]) > 2010 && 相关 ('Date'[Year]) > 2020 RELATED ('Date'[Year]) = 2020 && RELATED ('Date'[Calendar Quarter]) = 1 RELATED ('Date'[Year]) > 2020 && NOT RELATED ('Date'[Calendar Quarter]) = 1 相关 ('Date'[Year]) > 2020 && RELATED ('Date'[Calendar Quarter]) < 3 RELATED ('Date'[Year]) > 2020 && (RELATED ('Date'[Calendar Quarter]) = 1 \|\|相关 ('Date'[日历季度]) = 2)

DataCoverageDefinition使用 DirectQuery 分区上的属性，可以基于导入模式下的热分区和 DirectQuery 模式下的冷分区优化最大的 Power BI 数据模型，从而避免对数据源进行不必要的查询。这种源查询减少有助于在分析热数据时提高报表性能。它还有助于降低数据源上的负载，这样有助于最大程度地扩大数据源的规模。但请记住，使用 dataCoverageDefinition 属性优化数据模型仍然是一种高级方案。请确保仔细验证结果。

注意事项和限制

目前，DataCoverageDefinitionDirectQuery 分区上的属性需要静态值，例如 RELATED ('Date'[Year]) = 2020 或 RELATED ('Date'[Year]) {2020， 2021， 2022}。不支持动态分配，例如 RELATED ('Date'[DateKey]) = TODAY () 。
使用实时数据进行增量刷新不会利用 DataCoverageDefinition 属性。如果将数据覆盖率定义应用于 DirectQuery (实时) 分区，则增量刷新在重新创建分区时会删除数据覆盖率定义。

通过