DAX权威指南8：DAX引擎与存储优化

十七、DAX引擎

17.1 DAX 引擎的体系结构

  在数据分析领域，DAX语言扮演着至关重要的角色，它被广泛应用于众多基于表格技术（表模式，Tabular ）的微软产品中，如 Power BI、SSAS（SQL Server Analysis Services） 等。表格模型（ Tabular Model ）可同时使用 DAX 和 MDX 作为查询语言。本节描述了表格模型的更广泛的体系结构，而不仅限于特定的查询语言或产品。

17.1.1 表格模型的双引擎架构

Tabular模型提供DAX和MDX两种查询接口。在处理查询时，使用两个引擎：

• 公式引擎（Formula Engine，FE）：负责解析查询、生成查询执行计划（一系列物理操作步骤）。它具备以下特点：

  1. 语言解析能力：能够解析复杂的 DAX 和 MDX 表达式，处理所有函数的操作请求。
  2. 与存储引擎的交互：当需要从基础表中检索数据时，会将部分请求转发给存储引擎。
  3. 单线程执行：公式引擎是单线程的，按顺序向存储引擎发送请求，一次处理一个查询，无法在不同查询之间重用结果。
  4. 数据缓存的使用：公式引擎始终使用存储引擎返回的数据缓存，或利用其他公式引擎运算符计算的数据结构。数据缓存不压缩，以未压缩格式存储在内存中，可在后续查询中重用。

• 存储引擎（SE）：负责从表格模型检索数据，以响应公式引擎发出的请求。

  1. 独立性与运算符集：存储引擎独立于 DAX 和 MDX 查询语言，具有自己的运算符集。不同的存储引擎类型具有不同的运算符集范围，这会影响查询的性能和优化策略。 例如，VertiPaq 存储引擎的运算符集相对有限，而 SQL 存储引擎的运算符集则非常丰富。
  2. 并行响应能力： 存储引擎具有并行响应能力，可以利用多个核心来处理查询请求。然而，由于公式引擎（FE）是单线程的，它会同步发送请求到存储引擎，这可能会限制存储引擎的并行性。
  3. 数据缓存与格式：存储引擎返回的数据缓存是以未压缩格式存储在内存中的表，无论数据来自哪个存储引擎。数据缓存可以在后续查询中重用，提高查询效率。

     数据缓存：公式引擎的典型运算符包括表之间的联接、具有复杂条件的筛选、聚合和查找。这些运算符通常需要来自数据模型中列的数据。在这些情况下，公式引擎向存储引擎发送一个请求，存储引擎通过返回数据缓存来响应请求。数据缓存是由存储引擎创建并由公式引擎读取的临时存储区域（以未压缩格式存储在内存中的表）。所以DAX 对缓存结果的重用是完全依赖于存储引擎。

  4. 聚合技术： 聚合是存储引擎中用于优化查询性能的重要技术，可在 VertiPaq 和 DirectQuery 中定义。但通常在 VertiPaq 中定义以实现最佳性能。
     • 聚合表可以减少数据量，优化数据结构，从而提高查询效率。
     • 聚合表的存储模式可设置为 Import，以加快查询速度
     • 聚合表对模型具有只读访问权限的用户隐藏，以避免安全问题。

对比项	公式引擎 (FE)	存储引擎 (SE)
核心职责	解析 DAX/MDX，生成执行计划，执行计算逻辑	从底层数据源（内存或外部）检索数据
语言支持	支持 DAX 和 MDX	仅执行 FE 下发的查询（如 xmSQL、SQL 等）
并行能力	单线程（串行执行）	多线程（并行扫描数据）
缓存机制	无缓存，每次查询重新计算	VertiPaq 缓存最近 512 次查询结果
优化重点	减少复杂计算、优化 DAX 表达式	数据压缩、索引设计、查询下压优化
典型瓶颈	复杂迭代计算（如 SUMX、FILTER 嵌套）	大数据扫描、DirectQuery 网络延迟

解耦设计：Tabular模型引擎是分层式架构，FE 处理逻辑，SE 处理数据，两者解耦设计，这带来了几个好处：

• FE/SE可分别进行升级。例如，Power BI 每个月的更新，可能新增了某个 DAX 函数，那么本质就是对 FE 的更新，SE 是没有任何变化的。
• SE可对接不同数据源(SQL/NoSQL等)

17.1.2 存储引擎的三种模式

在定义表的存储引擎时，开发人员有以下三个选项：

• Import（内存模式或 VertiPaq）：数据加载到内存（VertiPaq），适合高频访问的中小数据集。数据刷新时，从数据源复制和重组数据。

• DirectQuery：表的内容在查询时从数据源读取，在数据刷新期间不会存储在内存中，适合大数据或实时性要求高的场景（实时查询）

• Dual：同时支持 Import 和 DirectQuery，常用于桥接表。

17.1.2.1 VertiPaq引擎

  VertiPaq是DAX查询引擎原生的低级别执行单元，它将数据源的数据压缩存储在列式内存数据库中（直接读到内存里）。列式存储对单列数据的读取和操作更加高效，特别适合于数据分析场景中常见的聚合和筛选操作。

• 查询语言与运算符集：

  • 使用内部伪SQL语言xmSQL表示查询（存储引擎查询的文本表示形式），主要目的是方便人类理解公式引擎如何查询VertiPaq
  • VertiPaq提供有限的运算符集，复杂评估需回调公式引擎，由公式引擎来完成更复杂的计算逻辑。

• 多线程与并行性：

  • VertiPaq是多线程的，可在多个内核上扩展，提高查询性能。
  • 单个查询的并行性受限，最多一个线程用于一张表的一个Segment。只有涉及多个Segment时，才能从并行性中获益。

• 缓存机制：

  • VertiPaq有缓存系统，通常保存每个数据库的最后512个内部查询结果。
  • 收到相同xmSQL查询时，直接返回缓存数据，提高效率。
  • 缓存不影响数据安全性。因为行级安全系统仅影响公式引擎行为，在用户受限于查看表中的特定行时，会生成不同的xmSQL查询。

• 扫描性能优势：

  • 存储引擎的扫描操作通常比公式引擎更快，即使只有一个线程可用。
  • 这得益于存储引擎的优化和压缩数据存储，而公式引擎只能使用未压缩的数据缓存。

17.1.2.2 DirectQuery 引擎

  DirectQuery 存储引擎：DirectQuery 存储引擎是一个通用定义，用于描述将数据保存在原始数据源中，而不是将数据复制到内存里。

• 查询方式：当公式引擎以DirectQuery模式向存储引擎发送请求时，会以特定的查询语言向数据源发送查询。比如关系数据库 → 生成SQL，SSAS → 生成DAX/MDX。
• 查询计划：对于DirectQuery模式，公式引擎会利用数据源查询语言中的高级函数，生成不同的查询计划。如 SQL 可处理字符串转换（UPPER 和 LOWER等），而 VertiPaq 引擎无此类函数。
• 存储引擎优化：使用 DirectQuery 时，存储引擎的优化依赖于数据源的优化，例如关系数据库中的索引。优化主要针对 DAX 和 MDX 模式，而非 SQL 模式。更多内容详见《White paper: DirectQuery in Analysis Services 2016》

17.1.2.3 对比与最佳实践

对比项	VertiPaq（内存模式）	DirectQuery（直连模式）
数据存储位置	内存列式存储（压缩数据）	原始数据源（如 SQL Server、Snowflake）
查询语言	xmSQL（内部伪 SQL）	原生查询语言（如 T-SQL、DAX）
数据延迟	依赖刷新周期（非实时）	实时（直接查询源数据）
性能优势	高速扫描（列存+多线程）	避免数据冗余，适合大数据
适用场景	高频分析、复杂计算	实时性要求高、数据量过大
缓存机制	缓存查询结果（最近 512 次）	无缓存，每次查询直达源系统
优化手段	列压缩、字典编码、聚合表	源系统索引优化、分区表
并行能力	多线程（按 Segment 并行）	依赖源系统并行能力

Dual特性	说明
数据同步	VertiPaq 存储副本 + DirectQuery 实时查询
引擎选择	自动路由（优先 VertiPaq，必要时切 DirectQuery）
典型用例	历史数据用内存加速，实时数据直连源库

存储引擎选择：

• VertiPaq：优先用于交互式分析（低延迟）。
• DirectQuery：适合实时性要求或超大数据集。
• Dual：平衡性能与实时性。

17.1.3 数据刷新

  数据刷新是一项关键操作，用于将数据加载到内存中。当 SSAS将源表的内容加载到内存时，我们称之为“处理”该表。这一过程通常发生在 SSAS 的操作过程中，或者在 Excel 和 Power BI 的 Power Pivot 中进行数据刷新时。不同的处理模式对应不同的数据刷新方式：

• VertiPaq 模式：引擎会读取数据源的内容，并将其转换为内部的 VertiPaq 数据结构。
• DirectQuery 模式：在这种模式下，表的处理仅需清除内部缓存，而无需从数据源加载数据。

  DAX 可以运行在多个环境中，包括 SSAS表格模式、Azure 分析服务、Power BI 服务（服务器和本地 Power BI 桌面），以及 Microsoft Excel 的 Power Pivot。从技术上说，Power Pivot 和 Power BI 使用的是定制化的 SSAS 表格模式。虽然这些环境在某些方面有所不同，但在本书中，我们不会对它们进行区分。当提到 SSAS 时，读者可以理解为 Power Pivot、Power BI 或 SSAS 的一致部分，除非特别指出差异。

在 VertiPaq 模式下，处理一个表的步骤如下：

1. 读取源数据集，将其转换为 VertiPaq 的列数据结构，并对每列进行编码和压缩。
2. 为每个列创建字典和索引。
3. 创建关系的数据结构。
4. 计算并压缩所有计算列和计算表。

  注意：最后两个步骤并不一定严格按顺序执行。实际上，关系可能依赖于计算列，而计算列也可能依赖于关系（因为它们使用了 RELATED 或 CALCULATE 函数）。因此，SSAS 会创建一个复杂的依赖关系图，以确保这些步骤能够按照正确的顺序执行。

17.2 理解 VertiPaq 存储引擎

  VertiPaq 是一个内存列式数据库。 内存数据库意味着所有数据都存储在 RAM 中，数据访问速度极快，能够快速响应复杂的查询请求，不受磁盘 I/O 的限制。

  与传统的关系型数据库（行存储）不同，VertiPaq 采用列式存储架构，也就是将数据按列组织，而非按行组织，这带来了两个显著的好处：

• 快速列访问：列式存储使得引擎能快速进行单列访问，快速完成聚合计算（无需扫描其他无关列，大大减少了 I/O 操作）。
• 高效压缩：列式数据库在存储上具有天然的压缩优势。由于每一列的数据类型相同，且通常存在大量重复值，所以列式数据库可以通过多种压缩技术，大幅减少存储空间。

17.2.1 列式数据库

为了更好地理解列式数据库的优势，我们先来看看行式存储和列式存储的区别。以如下Product表为例：

1. 行式存储（Row-Oriented Storage）：数据按行组织，每一行的所有列数据存储在一起，例如：

   ID, Name, Color, Unit Price
   1, Camcorder, Red, 112.25
   2, Camera, Red, 97.50
   3, Smartphone, White, 100.00
   4, Console, Black, 112.25
   5, TV, Blue, 1, 240.85
   6, CD, Red, 39.99
   7, Touch screen, Blue, 45.12
   8, PDA, Black, 120.25, 
   9, Keyboard, Black, 120.50
   

2. 列式存储（Column-Oriented Storage）：数据按列组织，每一列的数据单独存储：

   ID,1,2,3,4,5,6,7,8,9
   Name,Camcorder,Camera,Smartphone,Console,TV,CD,Touch screen,PDA,Keyboard
   Color,Red,Red,White,Black,Blue,Red,Blue,Black,Black
   Unit Price,112.25,97.50,100.00,112.25,1240.85,39.99,45.12,120.25,120.50
   

  单列计算（列式存储）：列式存储中，每一列的数据在物理存储上是连续的，所以数据库在处理特定类型的查询时非常高效。例如，当需要计算Unit Price列的总和时，列式数据库可以直接访问该列的数据来执行聚合操作，而无需扫描整个行。

  单列计算（行式存储）：如果是行式存储数据库，读取Unit Price列的第一个值，引擎需要读取（并跳过）ID、Name 和 Color 的第一行，然后不断重复这个步骤。整个过程中，需要扫描整个表、跳过大量无用的值，这都需要大量的时间。

  双列计算：如果是计算红色产品的单价总和，直接扫描单价列无法直接得到结果，因为需要先确定哪些产品是红色的。传统的做法是扫描“Color”列，找到红色产品的索引，再从“Unit Price”列中提取对应单价。但这种方法涉及频繁的内存跳转，性能较差。

  双列计算（优化） ：更好的方式是先完整扫描“Color”列，标记出所有红色产品的索引位置，然后仅对这些索引位置在“Unit Price”列中进行单价求和。这样，每列只被扫描一次，且访问的是连续的内存位置，避免了随机访问，显著提高了性能。

  复杂查询：对于复杂查询，例如求价格高于50美元的蓝色或黑色产品的总和，由于涉及多列条件，难以通过单次列扫描完成。最直观的方法是基于行扫描，即逐行检查是否满足所有条件，但这在列式存储中效率低下，因为会涉及大量随机内存访问。

综上所述，列式存储的优缺点如下：

• 单列访问速度快：它按顺序读取单个内存块，然后计算该内存块上所需的任何聚合。
• 多列处理慢：多列计算时算法会更加复杂，需在不同时间访问不同内存区域。计算所需列越多，生成结果越困难，有时重建行存储来计算表达式会更加易。
• 更容易压缩数据：列存储有更多压缩选项，可减少数据扫描时间。

17.2.2 VertiPaq 压缩

  处理多列数据时，列式存储会花费更多的 CPU 周期来重新排列数据。通常，以增加CPU使用率为代价来减少数据读取量是值得的。对于现代计算机而言，提升CPU速度相对更容易且成本更低，而减少I/O（或内存访问）时间则更具挑战性。本节要介绍的就是VertiPaq 压缩技术。

17.2.2.1 值编码（Value Encoding）

  值编码通过数学变换减少存储整数值所需的位数，只适用于整数列。假设某一列的值范围为 [194, 216]，存储每个值至少需要 8 位。通过从每个值中减去最小值（194），可以将值的范围缩小到 [0, 22]，此时仅需 5 位即可存储。

  实际情况比这更加复杂。VertiPaq 引擎能够识别列值之间的数学关系，并利用这些关系来优化数据存储，从而降低内存占用。在查询时，它会反向应用这些变换以还原原始值，这可能发生在数据聚合之前或之后。这种机制虽然会增加 CPU 的使用，但减少了数据读取次数，总体上提升了性能。

• VertiPaq 将 DAX 的货币数据类型（也称为固定十进制数字）存储为整数值。因此，货币也可以进行值编码，但浮点数不能。

17.2.2.2 哈希编码（Hash Encoding）

  哈希编码（也称为字典编码）通过构建一个包含列中所有不同值的字典，然后将列中的每个值替换为字典的索引，来进行数据压缩，适用于所有非整数类型。

  假设“颜色”列有四个不同的值：“红色”、“蓝色”、“黑色”和“白色”。哈希编码会为每个值分配一个唯一的索引（如 0、1、2、3），并将列中的值替换为这些索引。

哈希编码的优点：

• 无关数据类型：通过哈希编码，所有列的数据均被转换为整数索引，所以源数据类型是什么都不重要，不同数据类型处理之后的扫描速度和存储空间相同。
• 高效存储：存储列值所需的位数取决于存储索引条目所需的最小位数。例如，如果列中有 4 个不同的值，仅需 2 位即可存储索引，这大大减少了内存占用。
• 基数：从上一点可知，确定列大小的主要因素不是数据类型，而是基数（列中不同值的数量）。基数越低，存储单个值所需的位数越少，内存占用越小，计算时扫描速度越快。因此，设计数据模型时，应尽量降低列的基数。

17.2.2.3 游程编码（Run Length Encoding, RLE）

  游程编码通过减少重复值来节省存储空间。例如，假设某一列中有 310 个连续的“Q1”值（第一个季度），RLE 会将其替换为一个“Q1”值和一个计数器 310（表示连续行数）。

  RLE的效率高度依赖于列的重复模式和数据排序顺序。所以对于主键列（所有列值都不同），并不会进行RLE压缩。RLE 还可以与值编码或者哈希编码结合使用，比如使用RLE 处理列的哈希编码版本：

• 哈希编码：首先，我们对“季度”列进行哈希编码，将每个唯一的字符串值映射到一个整数值，最终得到一个字典；
• RLE：哈希编码后的列应用 RLE，用一个值和一个计数来表示这些重复值。

总结：影响 Tabular 模型压缩比的因素，按重要性排序如下：

1. 列的基数：决定了用于存储值的位数。
2. 数据的分布（重复次数）：列值的重复率越高，压缩效果越好
3. 表的行数
4. 列的数据类型：仅影响哈希字典大小

考虑以上所有因素，几乎不可能预测表的压缩率。

• 降低列的基数可以增加数据的重复性，从而提高压缩效率。例如，如果时间数据按秒存储，那么每小时会产生3600个不同的时间值；而如果按小时存储，则每个小时只有1个时间值。这大大减少了不同值的数量，增加了重复值，提升了压缩比。
• 将数据类型从 DateTime 更改为 Integer 甚至 String ，对列大小的影响可以忽略不计。

17.2.3 Reencoding

  SSAS在处理数据时，会通过扫描数据源的行示例以决定对每列数据采用哪种编码算法（值编码或字典编码）。对于非整数类型的列，SSAS 直接采用字典编码。而对于整数类型的列，SSAS 会根据数据的特点来选择编码方式：

• 如果数字是线性增长的，比如像主键那样，那么值编码会更合适；
• 如果数字的范围是固定的，值编码也是一个好的选择；
• 如果数字分布在很广的范围内，且彼此差异很大，那么应该选择字典编码。

  SSAS在选定编码算法并开始压缩数据列后，有时可能会发现最初的选择并不理想。例如，如果最初基于几百万行数据（100–201）选择了值编码，但随后出现了一个极大的异常值（例如 60,000,000），这将导致需要更多的存储空间，从而使得值编码不再适用。在这种情况下，SSAS可以选择重新编码，转而使用字典编码来处理整个列。

  Reencoding是非常耗时的，为了避免频繁的重新编码，开发人员可以通过以下方式优化：

• 提供高质量的样例数据：将具有代表性的高质量数据放在第一组行中，确保SSAS在第一次扫描时读取的数据能够准确反映整列数据的分布特征，减少重新编码的可能性。
• 使用编码提示参数：在Analysis Services 2017及更高版本中，可以为列提供编码提示，帮助SSAS更准确地选择编码方式。

17.2.4 找到最佳排序

  在 VertiPaq 引擎中，"找到最佳排序顺序"指的是在处理数据时，引擎会尝试不同的行排列方式，以最大化游程编码(RLE)的压缩效率，从而减少内存占用并提高查询性能。如果数据排序得当，相同值会连续出现，RLE 压缩效果会非常好。

  SSAS在处理数据时会尝试不同的排序顺序以优化压缩，但在大型表中，这一过程可能会耗费大量时间。为了平衡处理时间和压缩效果，SSAS设置了默认的时间限制（例如每百万行10秒），开发人员可以根据实际情况调整该值。

  SSAS服务器的 ProcessingTimeboxSecPerMRow 参数可以设置默认限制时间，设为 0可以获得最佳压缩效果，但这需要更多的时间。只有当数据模型的规模非常大（几十亿行）才值得这么做。对于小的数据集，这些极端优化措施带来的性能提升有限。

所以在设计模型时，应该 将低基数列(如性别、状态等)放在排序优先级高的位置。假设有一个员工表，包含以下列：

• 性别(基数2)
• 部门(基数10)
• 城市(基数50)
• 员工ID(基数10000)

  好的排序优先级是1. 性别 → 2. 部门 → 3. 城市 → 4. 员工ID，性别、部门等低基数列都能获得很好的压缩。差的排序则是反过来，此时高基数列(员工ID)打乱了低基数列的连续性，压缩效果会差很多。

• 引擎会自动尝试将低基数列放在排序优先级高的位置，但显式地在数据源中排序可以提供更好的起点。
• 对于某些列（如主键列），SSAS会跳过RLE压缩，直接以原始方式存储列（主键列的值通常不会重复）。

  此外，在为表选择最佳排序顺序时，VertiPaq 不会考虑计算列。常规列会首先进行压缩，并确定最佳的排序顺序。计算列的值是在数据处理的最后阶段基于常规列计算生成的，此时常规列的压缩已经完成，计算列的排序顺序直接依赖于常规列的排序顺序。

  假设有一个计算列，其值是布尔类型（True 或 False）。布尔值的压缩效果非常好，因为只需要 1 位就可以存储（True 为 1，False 为 0）。然而，如果常规列的排序顺序导致布尔计算列的值频繁变化（True 和 False 交替出现），那么计算列的压缩效果就会很差。这种情况下，在数据源中预先计算好列的值，然后将这些值作为常规列加载到数据模型中进行统一排序，是更好的选择。

  计算表的压缩效果与常规表相同，但创建计算表需要较大的内存开销。计算表需要足够的内存，才能在压缩之前将整个未压缩表的副本保留在内存中。由于刷新时产生的内存压力，在创建大型计算表之前还请三思。

17.2.5 层次结构与关系

  在表处理结束时，SSAS 构建了两个额外的数据结构：层次结构和关系。层次结构分为属性层次结构和用户层次结构两种类型，主要用于提高 MDX 查询的性能，以及改进 DAX 中的某些搜索操作。由于在 DAX 语言中不存在层次结构的概念，因此层次结构与本书的主题无关。

  关系通过将一个表中的ID映射到另一个表中的行号，来建立表之间的联系，以便在查询时能够快速将筛选器从一个表传递到另一个表，从而提高查询性能。例如，以下“Sales”表和“Product”表通过“ProductKey”列建立关系：

  如果没有关系，将筛选器从"Product"表移动到"Sales"表，VertiPaq需要从"Product"表中检索值，然后在"Sales"表的字典编码中搜索这些值，最后找到对应的行号来应用筛选器，这个过程会很慢。

  为了提高查询性能，VertiPaq将关系存储为ID和行号的配对。这样，给定"Sales"表中的"ProductKey"的ID，VertiPaq可以迅速找到对应的"Product"表中的行。这种关系存储方式在内存中进行，与其他VertiPaq数据结构一样，在处理多表筛选查询时，速度会非常快。

17.2.6 Segment与分区并行

  Segment（段）是VertiPaq在数据处理时划分的逻辑单元，默认每个Segment包含800万行（SSAS）或100万行（Power BI/Power Pivot）。在处理过程中，SSAS逐个读取并压缩每个Segment，同时开始处理下一个Segment。

  可以使用服务配置文件（或 Management Studio 中的服务器属性）中的 DefaultSegmentRowCount 条目在 SSAS 中配置 Segment 的大小。

  查询并行性：查询时，每个Segment可由不同CPU核心并行扫描。较大的Segment可以提高压缩效果，但会增加处理时间；而较小的Segment可以增加查询时的并行性，但过多的小Segment会导致管理任务切换和最终聚合结果的时间增加，反而降低查询性能。

  分区是用户定义的数据逻辑划分，用于优化刷新、查询和存储管理。比如将事实表（如销售、日志数据）按时间分区（最常用）、 按业务键分区（地区、产品类别）等等。分区的作用有：

• 数据增量刷新：只刷新新增或修改的分区，而不是整个表，大幅减少刷新时间。
• 并行处理：不同分区可以由多个CPU核心并行处理，加快数据加载和计算速度。
• 内存管理：可以单独卸载不常用的分区（如旧数据），减少内存占用。

  分区与Segment：Segment的大小不能超过分区大小。对于非分区表，第一个段可能会比默认的段行计数大，VertiPaq 会尝试读取更多行来优化压缩，但一旦表中行数超过这个初始段的大小，就会开始分段处理。对于已经分区的表，每个段的大小不会超过默认值。

  例如，在 SSAS 中，一个包含1000万行的非分区表可能会作为一个单独的段存储，而一个包含2000万行的表可能会被分成三个段(两个包含 800 万行，一个包含 400 万行)。Power BI 和 Power Pivot 对于超过200万行的表也会使用多个段。

  合理的分区策略可以提高查询性能，但过度分区会导致过多的小分区，处理这些小的Segment也就需要更多的资源，反而降低性能。

17.2.7 动态管理视图（DMV）

  SSAS提供了动态管理视图（DMV），用于探索有关数据模型的各种信息，包括模型的压缩方式、各列和表的内存占用情况、表中的Segment数量以及各段中列的位数使用情况。

  DMV的输出虽然使用类似SQL的语法，但并不支持完整的SQL语法。它们的主要作用是提供一种便捷的方法来发现SSAS的状态和收集数据模型的信息。

  DMV可以通过SQL Server Management Studio运行，但更推荐使用DAX Studio或VertiPaq Analyzer工具来简化操作。DAX Studio提供了所有DMV的列表，方便用户使用而无需记忆或查阅资料。VertiPaq Analyzer是一个免费工具，它能展示DMV数据并以有用的报告形式组织这些信息。

VertiPaq 分析器以高效的方式显示有关数据模型的统计信息

DMV主要分为两大类：

• SCHEMA视图：提供有关SSAS元数据的信息，如数据库名称、表和列等，包括数据类型、名称、行数和列中唯一值的统计信息。
• DISCOVER视图：用于收集SSAS引擎的信息或发现数据库中对象的统计信息，如DAX关键字、当前连接数、会话数或跟踪运行数量等。

常用的DMV包括：

• DISCOVER_OBJECT_MEMORY_USAGE：用于查看SSAS实例中所有对象的内存使用情况（不限于当前数据库），以便进行针对性的优化。比如以下查询在DAX Studio 或 SQL Server Management Studio 中运行，结果信息摘录如下：

  SELECT * FROM $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
  

  

    DMV输出的数据是以父子层级结构呈现的表，从实例名称到具体列信息。这种格式直接阅读比较困难，但可以利用Power Pivot数据模型来整理这些数据，使其更易于理。详见此博客。

• DISCOVER_SESSIONS、DISCOVER_CONNECTIONS和DISCOVER_COMMANDS：这些DMV分别用于查看当前的活动会话、连接以及执行的命令信息，有助于监控SSAS实例的运行状态和性能瓶颈。

• TMSCHEMA_COLUMN_STORAGES和DISCOVER_STORAGE_TABLE_COLUMNS：用于分析列和表中的数据分布情况以及压缩数据所需的内存信息，帮助开发人员了解不同列的压缩效果和内存占用情况，以便进一步优化数据模型的存储结构。

• DISCOVER_CALC_DEPENDENCY：用于分析数据模型中计算对象（如计算列、计算表和度量值）之间的依赖关系，帮助开发人员优化计算逻辑，减少不必要的计算开销，提高查询性能。
  

17.3 了解 VertiPaq 中关系的使用

17.3.1 单列查询

  VertiPaq 是一个列式存储引擎，这种存储方式使得对单列的扫描操作非常高效，因为数据在物理存储上是连续的。以一个简单的查询为例，假设我们只想统计 Sales 表中 Quantity 大于 1 的行数：

EVALUATE
ROW ( "Result", CALCULATE ( COUNTROWS ( Sales ), Sales[Quantity] > 1 ) )

  在这个查询中，VertiPaq 只需要扫描 Sales[Quantity] 这一列，因为该列已经包含了所有行的行号。这种单列扫描的方式非常高效，能够快速返回结果。

17.3.2 跨表查询

  然而，当涉跨表查询时，情况就变得复杂了。例如，我们想统计与 Contoso 品牌产品相关的销售行数：

EVALUATE
ROW ("Result", CALCULATE ( COUNTROWS ( Sales ), 'Product'[Brand] = "Contoso" )
)

  在这个查询中，筛选条件位于 Product 表，而聚合操作需要在 Sales 表上进行。这涉及到跨表的关系处理，不能简单地通过单列扫描来解决。

最简单的办法是逐行遍历 Sales 表，通过与 Product 表的关系，检查每条销售记录对应的产品品牌是否为 Contoso，如果是，就加 1，否则加 0。整个查询可表示为：

EVALUATE
ROW ("Result", SUMX ( Sales, IF ( RELATED ( 'Product'[Brand] ) = "Contoso", 1, 0 ) )
)

这种方法看起来简单，其实复杂性远超预期。考虑 VertiPaq 的列式数据库的特性，引擎需要：

• 扫描Sales表：在 Sales[ProductKey] 上搜索
• 查找Product表：对每行通过关系查找Product[ProductKey] 行号
• 检查Brand值
• 累加结果

这种逐行处理方式会产生大量随机内存访问，性能极差。

17.3.3 VertiPaq 优化算法

VertiPaq采用了更高效的批处理方式：

1. 品牌筛选阶段：
   • 扫描Product[Brand]字典，找到"Contoso"对应的编码
   • 获取包含"Contoso"的Product表的行号列表

2. 键值转换阶段：使用Product和Sales表间的关系，将Product行号转换为对应的Sales[ProductKey] 值
   

3. 最终聚合阶段：由于已经获取了 Sales[ProductKey] 的值列表，VertiPaq 只需扫描 Sales[ProductKey] 列，将这些值转换为行号，然后进行最终的行数计算。

   • 对于COUNTROWS，直接统计行数
   • 对于SUM等计算，还需获取对应列值进行累加

  VertiPaq关系的处理成本主要取决于关系列的基数（唯一值数量）。关系的基数越低，性能越好。当关系的基数超过 100 万个唯一值时，性能可能会显著下降。这是因为高基数关系会导致更多的随机内存访问，从而增加查询成本。优化高基数关系的方法：

1. 预聚合：在不同粒度级别预先聚合数据，减少查询时需处理的行数
2. 减少唯一值：适当降低关系列的基数
3. 合理设计模型：避免不必要的高基数关系

17.4 物化（缓存表）

  在列式数据库如VertiPaq中，物化是指查询执行过程中存储引擎动态生成临时表（称为数据缓存）的关键步骤。当公式引擎向存储引擎发送请求时，存储引擎会返回一个未压缩的临时表，这个表就是数据缓存。无论使用VertiPaq还是DirectQuery存储引擎，都会产生这种数据缓存。

17.4.1物化的两种类型

1. 后期物化(Late Materialization)：
   理想情况下，存储引擎生成的数据缓存行数与查询结果的行数相同，称为后期物化。这种情况下，整个计算在存储引擎中完成，公式引擎无需额外聚合数据，此时性能最优。

   EVALUATE
   SUMMARIZECOLUMNS ('Date'[Calendar Year],"Sales Amount", [Sales Amount]
   )
   -- Calendar Year |  Sales Amount
   -----------------|---------------
   -- CY 2007       | 11,309,946.12
   -- CY 2008       |  9,927,582.99
   -- CY 2009       |  9,353,814.87
   

   此查询每年返回一行，最佳物化是三行（对应三年数据）。

2. 早期物化(Early Materialization)
   当存储引擎生成的数据缓存行数多于查询结果时，称为早期物化。这种情况下， 公式引擎需要执行额外操作（如联接、分组）， 导致查询性能下降

   EVALUATE
   VAR LargeSalesCustomerDates =CALCULATETABLE (SUMMARIZE ( Sales, Sales[CustomerKey], Sales[Order Date] ),Sales[Quantity] > 1)
   VAR Result =ROW ( "CustomerDates", COUNTROWS ( LargeSalesCustomerDates ) )
   RETURN Result
   

   此查询虽然最终返回一行，但中间生成了6,290行的临时表。

  一般来说，涉及单列的操作更容易在存储引擎中完成，容易实现后期物化；而涉及多列组合的操作通常需要早期物化。不过某些情况下是例外，以下查询涉及两表两列相乘，仍可实现后期物化：

DEFINEMEASURE Sales[Sales Amount] =SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[Unit Price] ) )
EVALUATE
ROW ( "Sales Amount", [Sales Amount] )

17.4.2 物化优化

物化是存储引擎在执行查询时动态生成临时数据缓存(Data Cache)的过程。这些临时表需要：

1. 内存分配
2. 数据解压缩(对VertiPaq而言)
3. 在公式引擎和存储引擎间传输

所以性能成本排序是：

1. 最优：完全在存储引擎处理(无物化)
2. 次优：后期物化(结果级物化)
3. 较差：早期物化(中间结果物化)
4. 最差：多次物化(多阶段中间结果)

在复杂的查询中，几乎不可能获得最佳的后期物化，只能尽量减少物化。这包括：

1. 减少中间结果集：尽量降低早期物化产生的临时表行数。这包括：
   • 使用DISTINCT
   • 只选择必要列
   • 尽早应用筛选器
2. 减少物化的次数：合并计算步骤避免中间结果。比如使用迭代函数(如SUMX)替代多步计算、避免嵌套的VAR变量
3. 利用存储引擎计算：将尽可能多的工作推送给存储引擎处理。

17.5 聚合表

17.5.1 基本定义

  聚合表是原始数据表的预分组版本，其核心目的是通过预聚合数据来减少数据量，从而加快查询速度。比如以下原始Sales表包含每一笔交易明细：

通过按日期聚合，可以创建一个新的表“销售日期聚合表”：

  在这个聚合表中，每列都是原始表的“分组”或聚合。如果存储引擎的请求只需要聚合表中存在的列，那么引擎将使用聚合表而不是原始表。

  聚合表的映射规则：每个聚合列必须明确定义其与原始表的关系。对于不是“分组”类型的每个列，都需要指定聚合类型。可用的聚合类型包括表的计数、最小值、最大值、总和和计数行。要注意的是，聚合表只能映射原始表中的列，无法在计算列上指定聚合。

Date: GroupBy Sales[Date]
Quantity: Sum Sales[Quantity]
Amount: Sum Sales[Amount]

17.5.2 最佳实践

  聚合表专为优化存储引擎查询性能而设计，而非用于改善复杂DAX计算的执行效率。可以为同一数据集创建多个聚合表，可设置不同优先级。

  适用场景：DirectQuery模式下，适用于中小型表的性能优化；VertiPaq模式下，仅建议用于十亿级行数的超大表，以实现显著的性能提升。也可采用混合存储方案，一个典型的做法是将聚合表存储在 VertiPaq 中，以提高通过 DirectQuery 访问的大型表的性能。

  注意事项：建模者需确保聚合结果与原始表查询一致。此外，聚合和原始表的存储引擎选择可能受到所用产品版本和许可证的限制。由于聚合表需要额外的定义和维护工作，所以应该只在必要时使用。

17.6 硬件

VertiPaq引擎的性能表现高度依赖于硬件配置，但盲目堆砌高端硬件并不能自动获得最佳性能。如果可以选择硬件配置，应按照以下优先级顺序进行选择：

1. CPU 型号和频率：单核性能越高越好，比如频率3 GHz 或以上。
   另外L2 和 L3 缓存越大越好，尤其是在处理大型表和高基数列时，更大的缓存可以显著提升性能。下图展示了存储在不同位置的数据的典型访问时间，这清楚地说明了 CPU 和缓存对 VertiPaq 性能的重要性：
   

2. 内存速度：越快越好。内存速度直接影响 VertiPaq 的性能，至少选择 1,833 MHz 以上的 RAM。

3. 核心数：越高越好，但快速的核心比大量慢速核心更有价值。VertiPaq 仅在表具有多个 Segment 时才会在多个线程上拆分执行。因此，核心数量的增加只有在处理非常大的表时才会显著提升性能。

4. 内存大小：应确保系统有足够的内存以避免分页。

查询性能几乎不受磁盘I/O影响，只有在内存不足导致系统分页时，磁盘 I/O 才会成为瓶颈。

在为 SSAS Tabular 选择硬件之前，务必先进行性能检测。以下测试使用 DAX Studio 或 SQL Server Management Studio 运行特定的查询，比较了不同 CPU 的性能。

DEFINEVAR t1 = SELECTCOLUMNS (CALENDAR (1, 10000), "x", [Date])VAR t2 = SELECTCOLUMNS (CALENDAR (1, 10000), "y", [Date])VAR c = CROSSJOIN (t1, t2)VAR result = COUNTROWS (c)
EVALUATE ROW ("x", result)

  以上查询在英特尔 i7-4770K 3.5 GHz（桌面工作站）上运行需要 9.5 秒，而在英特尔 i7-6500U 2.5 GHz（笔记本）上运行则需要 14.4 秒。这是因为企业级服务器（尤其虚拟机）虽然配置高，但单核性能往往不如普通电脑，而VertiPaq引擎主要依赖单线程计算。

十八、优化 VertiPaq

  本章将从实际应用角度出发，介绍如何通过节省内存来优化数据模型性能。优化的主要目标是通过减少列的基数、优化压缩、加快迭代和筛选，从而提升数据模型的效率。优化之前，必须学会评估每个设计选择的利弊。因为只有通过评估其对内存的影响，才能判断优化决策是否真正有价值。

  在学习优化技术时，不要一开始就追求最优实践，而是要了解多种优化方法，并认识到并非所有技术都适用于每个数据模型。在应用这些技术之前，必须完整测试每个数据模型，以确保优化效果符合预期。

18.1 收集数据模型的信息

优化数据模型的第一步是全面了解数据库中各个对象的成本信息，这包括：

1. 表级别信息：行数和内存大小
2. 列级别信息：唯一值数量（基数）、字典大小和数据大小（所有段的总大小）
3. 层次结构信息：层次结构规模
4. 关系信息：关系结构规模

其中，列基数是最关键的指标之一，因为它直接影响对象的大小和性能。

18.1.1 VertiPaq Analyzer Table报告指标

VertiPaq Analyzer是一款强大的工具，能够简化从动态管理视图(DMV)收集数据的过程，并以直观的方式呈现分析结果。下图展示了VertiPaq Analyzer对Contoso数据模型的分析结果：

通过向下钻取表名，可以查看每列的详细信息：

指标	描述	指标	描述
Cardinality	对象基数（表的行数或列的唯一值数量）	Table Size	表的总大小（字节） ，是Columns Total Size User Hierarchies Size 和 Relationship Size 的总和。
Rows	表中的行数（在列报告中显示）	Columns Total Size	列的总大小（字节） ，是 Data Size Dictionary Size 和 Columns Hierarchies Size 的总和。
Data Size	段和分区中所有压缩数据的大小	Dictionary Size	字典结构的大小（主要与hash编码列相关）
Columns Hierarchies Size	列自动生成的属性层次结构大小 DAX使用它们来优化筛选器和排序	Encoding	列编码类型（hash或值）
User Hierarchies Size	用户定义层次结构的大小	Relationship Size	表之间关系的字节数，取决于关系中涉及的列的基数
Table Size %	Columns Total Size与Table Size之比	Database Size %	Table Size与Database Size之比
Segments	段数。表的所有列都有相同数量的段。	Partitions	分区数。表的所有列都有相同数量的分区。

• Data Size：段和分区中所有压缩数据的大小（以字节为单位）。它不包括字典和列层次结构，这个数字取决于列的压缩，而压缩又取决于唯一值的数量和表中数据的分布。
• Dictionary Size：字典结构的字节大小。此数字仅与具有 hash 编码的列相关; 对于具有值编码的列，它是一个小的固定数字。字典大小取决于列中唯一值的数量以及文本列中字符串的平均长度。
• User Hierarchies Size：用户定义层次结构的字节数。它在表级别计算，其大小取决于层次结构中列的唯一值数量和字符串的平均长度。

   Columns Hierarchies Size：属性层次结构的大小，取决于列中唯一值数量和字符串的平均长度，类似于字典大小。它适用于值和 hash 编码的列。如果列仅用于聚合，而不作为筛选器或分组条件，可以禁用属性层次结构以优化性能，但这需要高级设置。具体禁用设置详见《Column.IsAvailableInMDX Property》和《New memory options for Analysis Services》

  VertiPaq 通常选择更耗内存的编码方式来存储列，但开发人员可根据需要（比如提高动态聚合的速度）选择特定的编码类型，尽管这可能带来较高的成本。这种优化只在数十亿行的大表中较为明显。（更多信息，详见《Encoding hints》）。

18.1.2 模型优化

  通过分析VertiPaq Analyzer 的报告，可以识别出对报表无用且内存消耗大的列。比如SalesQuotaKey 列在任何报告中都不使用，可以删除它以节省内存和刷新时间。

  另外，上一节展示的是Table报告，还可以查看TableColumn报告。该报告按Columns Total Size降序显示所有列的详细信息，有助于识别需优化的列。另外报告还并排显示Rows和Cardinality，以便识别表中唯一列。

  报告中，OnlineSales表的OnlineSalesKey和SalesOrderNumber列，很少用于聚合报表，却分别占用数据模型10%的大小，删除它们可节省20%的数据库空间。

  VertiPaq Analyzer 的Relationships报告（见下图）可帮助识别数据模型中高成本的关系。在 VertiPaq 中，基数超过100万的关系会显著增加存储引擎成本。一般而言，当关系基数超过10万时，就需关注其性能影响。虽然当前这种关系可能不会对性能产生显著的负面影响（在数百毫秒的级别上是可以测量的），但它的存在可能会在未来的数据库扩展和复杂查询中逐渐暴露出问题，影响整体性能。

18.1.3 DAX Studio 报告

DAX Studio 也能够分析数据模型并报告内存使用情况。指标功能可以在 DAX Studio 功能区的 Advanced （高级 ） 选项卡上找到，一共有三个按钮：

• Import Metrics：这将打开以前保存的 .vpax 文件
• Export Metrics ：这将创建一个 .vpax 文件
• View Metrics：这将分析当前模型并生成报表

Table Metrics 报告如下，更多内容详见DAX Studio文档《Model Metrics》

18.2 反规范化：优化性能与平衡可用性

18.2.1 反规范化的概念与优势

  反规范化是一种数据建模技术，其核心思想是将原本分散在多个表中的数据合并到一个或少数几个表中，减少表之间的关系数量。在关系数据库中，规范化是常见的建模方式，通过将数据分解到多个表中，减少数据冗余并提高数据一致性。然而，在 DAX 数据模型中，这种传统的规范化方法并不总是最优的。

  DAX 数据模型基于 VertiPaq 引擎，该引擎采用基于字典的压缩技术，对重复数据的处理方式与传统关系数据库截然不同。这意味着在 DAX 模型中，反规范化可以显著减少内存消耗，并提升查询性能。

1. 减少内存消耗

  在关系数据库中，为了避免在主表中存储大量重复的长字符串数据，通常会将这些数据存储在单独的表中，并通过外键关联。例如，在一个包含支付类型描述的模型中，Transactions 表仅存储 Payment Code，而将 Payment Description 存储在单独的 Payment Type 表中。

  然而，在 DAX 数据模型中，这种设计并不理想。VertiPaq 引擎会为每列创建一个字典，自动处理重复数据，因此 Transactions 表中的重复描述并不会消耗额外的内存。通过反规范化，将 Payment Description 直接存储在 Transactions 表中，可以消除 Payment Type 表以及 Transactions 表与 Payment Type 表之间的关系，从而减少内存消耗。

2. 提升查询性能

  反规范化到事实表还可以显著提升查询性能。在 DAX 模型中，筛选同一个表中的列通常比通过关系筛选另一个表中的列更高效。例如，在一个包含 Sales、Product、Customer 和 Date 表的星型模式中，如果按性别筛选客户，筛选器需要通过关系从 Customer 表传递到 Sales 表。

  如果 Customer 表中有大量唯一值（如数百万个客户），筛选器生成的键值列表可能会非常庞大，从而影响查询性能。通过将 Gender 列反规范化到 Sales 表中，可以避免这种性能问题，因为筛选器可以直接作用于 Sales 表中的 Gender 列，而无需通过关系传递。

18.2.2 优选星型模型

  反规范化有助于减少关系内存消耗，但也不能完全反规范化成一个表，因为多列反规范化会导致内存消耗增加（相同的描述数据会在多个行中重复存储）。一般来说，星型模式通常更优，因为它在资源消耗和性能之间提供了更好的平衡。

  反规范化：在星型模式中，每个业务实体（如 Customer 表和 Product表）的表应反规范化与该实体相关的所有属性。例如，Product 表应包含 Category、Subcategory、Model 和 Color 等属性，只要关系的基数不是太大（100万是大型基数的阈值），这种模型就能很好地运作。

18.2.3 创建维度表处理低基数属性

  如果多列需要优化，除了反规范化，也可以创建一个新表，包含用户经常查询的列。比如创建一个Customer Info表，存储Gender、Occupation和Education列（基数分别是2、5、5）。这样新表的基数只有50，可显著加快查询速度，因为筛选器应用于 Sales 表时，键值列表更短。

1. 创建主键列：在将数据导入 Sales 表之前，应将 CustomerInfoKey 列添加到 Sales 表中，使其成为本地列，因为本地列比计算列压缩效果更好。也可以使用DAX表达式创建计算列：

   Sales[CustomerInfoKey] =
   LOOKUPVALUE ('Customer Info'[CustomerInfoKey],'Customer Info'[Gender], RELATED ( Customer[Gender] ),'Customer Info'[Occupation], RELATED ( Customer[Occupation] ),'Customer Info'[Education], RELATED ( Customer[Education] )
   )
   

2. 隐藏重复属性：这种设计会导致用户看到同一实体的两组属性（Customer 和 Customer Info 表），容易混淆。因此，只有在必要时才采用这种优化方式。一旦使用，从用户体验角度，Customer Info 表中的反规范化列应在 Customer 表中隐藏，以避免混淆。

3. 使用非活动关系：隐藏后，无法在不查看 Sales 表的情况下创建包含特定 Occupation 的客户报告。因此，需要创建一个非活动关系，当需要特定计算时，使用度量值激活此非活动关系。

   

   此时，可以基于非活动关系进行计算：

   Sales Amount :=
   IF (ISCROSSFILTERED ( Customer[CustomerKey] ),CALCULATE ([Sales Internal],USERELATIONSHIP ( Customer[CustomerInfoKey], 'Customer Info'[CustomerInfoKey] ),CROSSFILTER ( Sales[CustomerInfoKey], 'Customer Info'[CustomerInfoKey], NONE )),[Sales Internal]
   )
   

     该度量值确保在 Customer 表有筛选时，激活 Customer 和 Customer Info 之间的关系，同时禁用 Customer Info 和 Sales 之间的其他关系。虽然 CROSSFILTER 函数在某些情况下可能不必要，但保留它有助于明确禁用筛选器传导的意图。

18.2.4 高基数关系反规范化

  高基数反规范化常见于两个大表之间的关系，如 Sales Header 和 Sales Detail 表。这种关系在DAX查询中特别危险，因为存在大量唯一值，筛选器传导会导致性能问题。所以应该避免两个大表之间的直接关系，改用双星型模式设计，使两个事实表都直接连接维度表。

  比如当按 Customer[Gender] 对 Sales Detail 的 Quantity 列进行分组查询时，筛选器会通过 SalesOrderNumber 列从 Sales Header 传导到 Sales Detail，键值列表巨大，影响性能。较好的设计是反规范化存储在 Sales Details 和 Sales Header 中的所有关系，形成两个共享相同维度的星型模式，避免两个表的关系传递。

  反规范化是 DAX 数据模型中一种重要的优化技术。通过合理地应用反规范化，可以显著减少内存消耗，提升查询性能，同时保持数据模型的可用性。在实际应用中，应根据数据模型的具体情况，灵活选择反规范化的方法和程度，以实现最佳的性能和可用性平衡。

18.3 列基数

  列的基数是指列中包含的唯一值的数量，它直接影响列的大小和压缩率。而列越小，VertiPaq 扫描性能越好。此外，许多 DAX 操作（如迭代和筛选器）的执行时间也与列的基数直接相关。所以，列的基数比表的行数更重要，开发时应识别列的基数和类型，并进行针对优化。

列类型	优化建议	注意事项
关系键（Key of a relationship）	基数由相关表决定，通常无法改变	可考虑反规范化技术
数值度量（Numeric value aggregated in a measure）	对于表示货币交易数量或金额等数值，不要更改精度	非关键度量可适当降低精度
文本描述（Low cardinality text description）	低基数文本描述列，移动到单独的表不会优化性能，因为字典大小不变	如果需要，就保留在原表中。
文本注释（High cardinality text notes）	高基数的文本注释列如果大部分是空值，对性能和存储的影响较小	问题不大
图片数据（Pictures）	建议存储动态加载图像的 URL 以节省内存	Power BI不支持直接存储图片
交易ID（Transaction ID）	在大表中基数高，若 DAX 查询中不必要则删除	若用于钻取操作，可将数字/字符串拆分为多个部分，每部分基数较小。
日期时间（Date and time）	拆分为日期和时间部分
审计列（Audit columns）	除非需要钻取，否则不应导入 VertiPaq 存储模型中	必要时拆分时间戳

  关系数据库中的表通常有用于审计目的的标准列，例如时间标记和上次更新的用户。除非需要钻取，否则不应该将这些列导入 VertiPaq 存储的模型中。在这种情况下，可以参考前面日期和时间的处理规则分割时间标记。

18.4 计算列

18.4.1 计算列的特点

  计算列是基于 DAX 表达式逐行计算的结果，并且这些结果会在表刷新时被存储起来。计算列设计初衷是为了优化查询的执行时间。其特点为：

• 预计算与存储：计算列在表刷新时逐行计算 DAX 表达式的结果，并将这些结果存储在表中。
• 逐行计算：计算列的值是逐行计算的，依赖于表中的其他列或相关表的值。
• 单线程处理：计算列的计算是单线程操作，无法并行处理，因此在大型表中可能会成为性能瓶颈。
• 内存占用：计算列的结果会占用内存，其压缩效率通常不如本地列。

  每次表刷新时，计算列都会重新计算，无论数据是否发生变化：计算列会降低表的刷新速度，尤其是在增量刷新时，因为每次刷新都会重新计算整个表中的所有计算列，而不仅仅是受影响的部分。即使计算列仅依赖于同一表的其他列，它也会对整个表重新计算。如果计算列依赖其他表的内容，刷新时还需要重新计算这些相关列以保持数据模型的一致性。由于计算列的计算是单线程的，逐行进行，且一次只能计算一个列，因此在大型表中创建多个计算列会导致显著的处理瓶颈，显著增加刷新时间。

基于以上特点，使用计算列还是度量值需要权衡，最好是进行测试。例如一个简单计算列：

Sales[Amount] = Sales[Quantity] * Sales[Price]

使用一个简单的度量值，也可计算 Amount 列的求和：

TotalAmountCC := SUM ( Sales[Amount] )

另一种是通过迭代整个表格动态实现计算列的度量值：

TotalAmountM := SUMX ( Sales, Sales[Quantity] * Sales[Price] )

  如果 Quantity 列和Price 列基数分别是100和1000，那么生成的 Amount 列的基数应在 1 到 100,000 之间，这取决于列中的实际值及其中表行的分布 。如果最终计算列的唯一值数量显著增加（大一两个数量级），其压缩效率会非常差。至于计算列还是哪种度量的查询性能哪个更好，需要实际测量来确定。

  扫描单个 Sales[Amount] 列的成本是否小于扫描两个原始 Sales[Quantity] 和 Sales[Price] 列的成本，也需要实际测试才知道。

  在数量比较小的表中，两种方式查询性能非常接近，但计算列占用内存，因此不推荐。大多数情况下，可以使用 SUMX 和 AVERAGEX 等迭代器函数表达式替换用于计算聚合值的计算列。比如TotalAmountM和TotalAmountCC就是通过简单聚合，实现了Sales[Amount]计算列同样的功能。

18.4.2 计算列的适用场景

  计算列的主要优势在于它可以预处理计算结果，优化查询性能，但也会占用内存和刷新时的计算时间，因此并不总是最佳选择。尽管如此，计算列在许多场景中还是很有用的。

1. 分组或筛选数据：如果计算列返回的值用于分组或筛选数据，并且这些值除了在数据导入数据模型之前创建，无法通过其他方式创建（例如，需要基于复杂的逻辑或多个字段的组合），那么计算列可以显著提升性能。例如，将产品的价格分为低、中、高三类：

   Sales[PriceCategory] =
   IF ( Sales[Price] < 50, "Low",IF ( Sales[Price] < 100, "Medium", "High" ) )
   

   这种分类值通常是一个字符串，当用户将其作为筛选器时，计算列可以快速提供结果。

2. 预计算复杂公式：某些复杂的计算公式在每次查询时都需要重新计算，但如果将这些结果存储为计算列，就可以避免重复计算，从而提高查询性能。比如以下 DAX 度量值：

   AverageOrder :=
   AVERAGEX ('Sales Header',CALCULATE (SUMX ('Sales Detail','Sales Detail'[Quantity] * 'Sales Detail'[Unit Price]),ALLEXCEPT ('Sales Detail','Sales Header'))
   )
   

   Sales Header和Sales Detail都是大表并建立了联系，这种情况下，循环中的上下文转换将占有大量内存。如果将值存储在计算列中，可以会大大缩短执行时间。

   'Sales Header'[Amount] =
   CALCULATE (SUMX ( 'Sales Detail', 'Sales Detail'[Quantity] * 'Sales Detail'[Unit Price] )
   )
   AverageOrder := AVERAGEX ( 'Sales Header', 'Sales Header'[Amount] )
   

3. 优化复杂筛选器：当需要对高基数列进行复杂筛选时，可以使用存储逻辑表达式结果的计算列进行优化。例如以下度量值：

   ExpensiveTransactions :=
   COUNTROWS (FILTER (Sales,VAR UnitPrice =IF (Sales[Unit Discount] > 0,RELATED ( 'Product'[Unit Price] ),Sales[Net Price])VAR IsLargeTransaction = UnitPrice * Sales[Quantity] > 100VAR IsLargePrice = UnitPrice > 70VAR IsExpensive = IsLargeTransaction || IsLargePriceRETURNIsExpensive)
   )
   

     Sales 表中有数百万行，筛选器迭代会消耗大量内存。此时可以将不依赖于现有筛选上下文的逻辑表达式结果存储为计算列，从而避免这种内存消耗。然后通过CALCULATE语句直接对该计算列应用筛选，提升性能。唯一要考虑的是，是否值得为计算列花费更长的处理时间，因此必须在做出最终决定之前测量处理时间。

   Sales[IsExpensive] =
   VAR UnitPrice =IF (Sales[Unit Discount] > 0,RELATED ( 'Product'[Unit Price] ),Sales[Net Price])
   VAR IsLargeTransaction = UnitPrice * Sales[Quantity] > 100
   VAR IsLargePrice = UnitPrice > 70
   VAR IsExpensive = IsLargeTransaction|| IsLargePrice
   RETURNIsExpensive
   ExpensiveTransactions :=
   CALCULATE ( COUNTROWS ( Sales ), Sales[IsExpensive] = TRUE )
   

18.5 常规列

18.5.1 选择要存储的常规列

  同计算列一样，在构建数据模型时，应该从原始数据源导入哪些列到数据模型中，也需要综合考虑内存占用和查询性能。一般原则是 尽量最小化导入到表中的列的基数，不导入基数高且与分析无关的列。具体来说，每一种类型的列都需要特殊考虑。

列类型	描述
主键或备用键（Primary or alternate keys）	表中每一行的唯一值
定性属性（Qualitative attributes）	可以是文本或数字（如名字、颜色、城市、国家） ，用于分组和/或筛选
定量属性（Quantitative attributes）	如价格、金额、数量，可用于筛选或作为计算参数
描述性属性（Descriptive attributes）	提供关于行信息的文本（如说明、注释） ，不会用于筛选或聚合
技术属性（Technical attributes）	因技术原因记录的信息，通常没有商业价值（如上次更新的用户名、时间标记、复制的GUID等）

1. 主键或备用键：如果该列用于表之间的关系映射，那么它必须被存储。如果是无用的唯一标识符，比如Sales表的序号，基数太高且不会用于分析，那么不应该导入。
2. 定性属性：
   • 低基数列优先：低基数的定性属性（如产品类别）通常具有良好的压缩效果，对分析非常有用，应优先存储。
   • 高基数列需谨慎评估：如果某个定性属性的基数很高（如生产批号），则需要仔细评估其在查询中的使用频率。。如果该列在某些查询中被频繁使用作为筛选器，那么导入它的高成本是合理的。
3. 定量属性：数量属性（如Quantity、Price）通常需要导入以进行计算，对于由其他列计算得到的数量属性（如Amount = Quantity * Price），则需要权衡其存储成本和查询性能。
   • 对于小到中等规模的表（使用 VertiPaq）：推荐只存储 Quantity 和 Price（Amount列基数太大，压缩效果差），动态计算 Amount，以节省内存。
   • 对于非常大的表（数十亿行，如Analysis Services Tabular）：动态计算可能会导致查询性能下降。推荐存储 Amount 列，以优化查询性能并支持未来的聚合操作。
   • 对于 DirectQuery 模式：推荐存储 Amount 列，以提高查询性能。

   DirectQuery 模式直接从数据源查询，而非将数据存储在内存中。动态计算会导致额外查询开销，因为每次查询时都需要从数据源重新计算 Amount。

4. 描述性属性：
   • 描述性属性通常具有较高的存储成本，因为它们基数会很高，字典会很大。如果这些列很少用于分组或筛选，则可以考虑不将其存储在内存中。
   • 如果其中空白值多，唯一值少，则其字典较小，存储成本可接受
   • 如果要用于提供详细信息（如钻取操作），考虑仅在复合数据模型中通过 DirectQuery 访问它们。
5. 技术属性：
   • 对于VertiPaq模式，技术属性通常没有商业价值，不需要存储，除非有特定的审计或取证需求。
   • 对于复合模式，技术属性可以通过 DirectQuery 访问，也无需存储在内存中。

18.5.2 常规列存储优化

  列优化的最佳方式是不导入模型，在上一节中，我们讲述了根据表中列的类型做出不同决策。或者是在复合数据模型中，在数据源中保留列，然后通过 DirectQuery 访问它。不过，一旦决定把列作为数据模型一部分，还是可以通过一些方式进行内存优化。

18.5.2.1 使用列分割优化

  这种方法的核心思想是将一个列拆分成多个部分，从而减少每个部分的基数，进而降低内存占用。列分割不能使用计算列的方式，因为这会将原始列存储在内存中。我们可以通过 SQL 或其他数据转换工具（如 Power Query）来实现列分割。

1. 字符串列的分割：假设有一个长度为 10 个字符的 TransactionID 列，我们可以将其分成两部分，每部分 5 个字符（TransactionID_High 和TransactionID_Low ）。

   SELECTLEFT ( TransactionID, 5 ) AS TransactionID_High,SUBSTRING ( TransactionID, 6, LEN ( TransactionID ) - 5 ) AS TransactionID_Low,... 
   

     在某些客户端工具（如 Power BI）中，提供了“详细信息行”功能，允许用户在钻取操作中查看原始数据。通过列分割，我们可以隐藏拆分后的列，仅展示原始列的值。这样，用户在查看详细信息时，不会意识到数据已经被拆分，从而保持用户体验的一致性。

2. 整数列的分割：对于整数列，例如一个范围在 0 到 1 亿之间的 TransactionID 列，可以通过除法和模数运算将其拆分成两个部分。

   SELECTTransactionID / 10000 AS TransactionID_High,TransactionID % 10000 AS TransactionID_Low,...
   

3. 小数列的分割：对于小数列，例如 UnitPrice 列，可以将其拆分成整数部分和小数部分，管这可能不会产生一个均匀分布。

   SELECTFLOOR ( UnitPrice ) AS UnitPrice_Integer,UnitPrice - FLOOR ( UnitPrice ) AS UnitPrice_Decimal,...
   

   后续计算时，可以通过以下公式恢复原始值：

   OriginalValue = IntegerPart + DecimalPart
   

  列分割优化可以减少内存占用，并在某些情况下优化度量值的聚合计算。但是，列分割可能会增加聚合操作的计算时间（因为拆成多个列了，所以计算时需要扫描多个列），从而抵消内存节省带来的性能提升。为了验证列分割优化是否有效，需要通过实际测试来比较优化前后的性能指标。如果内存占用是主要瓶颈，且通过列分割可以显著减少内存占用，那么可以考虑使用列分割优化。

18.5.2.2 优化高基数列

  高基数列（即具有大量唯一值的列）通常会占用大量内存。这是因为高基数列的字典、层次结构和编码通常较大，导致压缩效率较低。

1. 禁用属性层次结构

  在多维数据模型中，每个列都可以被视为一个属性，而属性层次结构则是这些属性值的集合。例如，如果有一个City 列，那么这个列的所有唯一值（如“北京”、“上海”、“广州”等）就构成了一个属性层次结构。属性层次结构主要用于提供筛选和分组功能，以及多维分析。

• 筛选操作：用户可以选择“城市 = 北京”来筛选出所有北京的销售记录。
• 分组操作：用户可以按“城市”对销售数据进行分组，以计算每个城市的总销售额。

  如果一个列不是用于筛选或分组数据，被度量值和钻取结果使用，那么属性层次结构就没有必要，可以将其禁用（列的 Available In MDX 属性设置为 FALSE）。

2. 分割优化：如果不能禁用属性层次结构，或者压缩对内存优化来说还不够，那么可以对高基数列进行分割优化。例如，如果 Unit Price 列是一个高基数列，可以通过列分割优化将其拆分成两个部分，然后在度量值中调整计算：

   Sum of Amount :=
   SUMX ( Sales, Sales[Quantity] * ( Sales[UnitPrice_Integer] + Sales[UnitPrice_Decimal] ))
   

   这种方法可以减少内存占用，但计算将更消耗内存，因此需要准确地评估是否应该使用。

18.5.2.3 优化钻取属性

如果一个列仅用于钻取数据，可以通过两种方式进行优化：列分割优化和通过 DirectQuery 访问列。

• 列分割：将一个列拆分成两个部分，然后在钻取操作中显示原始列，隐藏两个拆分列的存在。这种方法可以显著减少内存占用，但需要注意的是，不可将原始值作为筛选器或分组列。

• 通过 DirectQuery 访问列：对于那些不需要频繁聚合计算的列，比如用于钻取的详细信息列，可以通过 DirectQuery 直接从数据源（如数据库）请求数据，而不是将所有数据加载到内存中。

  需要进行聚合计算的列（如用于生成报表的度量值），应该被加载到 VertiPaq 引擎管理的内存中，以便快速进行计算和分析。通过数据分层处理（部分在内存中，部分在数据源中），可以在内存使用和查询性能之间取得一种平衡。

18.5.2.4 日期时间列：平衡精度与性能

  日期时间列是几乎所有数据模型的核心组成部分，但处理不当会成为性能瓶颈。核心原则是将原始Datetime列拆分为独立的Date和Time列，且直接在数据源（如SQL Server）中操作，而不是通过计算列实现。

  例如，从SQL Server的表中读取Transaction Execution列时，可在T-SQL查询中将其拆分为TransactionDate和TransactionTime两列。

...
CAST ( TransactionExecution AS DATE ) AS TransactionDate,
CAST ( TransactionExecution AS TIME ) AS TransactionTime,
...

这样做的好处是：

• 避免Datetime列的基数和字典大小持续增长（即使是10年，日期列基数也不超过3700）。
• 模型需要一个日期列来与日期表进行匹配，且时间智能函数需要完整的每年日历表。

  优化Time列时，建议创建一个Time表，每个时间点为单独一行。时间应四舍五入到Time表的粒度，如小时、分钟或15分钟间隔。下表显示了不同精度级别下的不同基数：

Precision	Cardinality	Precision	Cardinality
Hour	24	15 Minutes	96
5 Minutes	288	Minute	1,440
Second	86,400	Millisecond	86,400,000

  大多数情况下，精度的选择范围在小时和分钟之间。分钟看起来基数较低，但列的压缩依赖于连续行的重复值。因此，将精度从 1 分钟提高到 15 分钟对大型表的压缩有很大的影响。具体采用哪种精度取决于分析需求。下面是一个 T-SQL 代码示例，它将时间截取到不同的精度级别：

1. 截断处理

-- 截取到秒
DATEADD (
MILLISECOND,
- DATEPART ( MILLISECOND, CAST ( TransactionExecution AS TIME(3) ) ),
CAST ( TransactionExecution AS TIME(3) )
)-- 截取分钟
DATEADD (
SECOND,
- DATEPART (SECOND, CAST ( TransactionExecution AS TIME(0) ) ),
CAST ( TransactionExecution AS TIME(0) )
)-- 截取5分钟
-- 将5更改为15以截断为15分钟，改为60以截断为小时
CAST (DATEADD (MINUTE,( DATEDIFF (MINUTE,0,DATEADD (SECOND,- DATEPART ( SECOND, CAST ( TransactionExecution AS TIME(0) ) ),CAST ( TransactionExecution AS TIME(0) ))) / 5 ) * 5,0) AS TIME(0)
)

2. 四舍五入

-- 舍入到秒
CAST ( TransactionExecution AS TIME(0) )
-- 舍入到分钟
CAST ( DATEADD (MINUTE,DATEDIFF (MINUTE,0,DATEADD ( SECOND, 30, CAST ( TransactionExecution AS TIME(0) ) )),0
) AS TIME ( 0 ) )
-- 舍入到5分钟
--将5更改为15以舍入到15分钟，改为60以舍入到小时
CAST ( DATEADD (MINUTE,( DATEDIFF (MINUTE,0,DATEADD ( SECOND, 5 * 30, CAST ( TransactionExecution AS TIME(0) ) )) / 5 ) * 5,0
) AS TIME ( 0 ) ) 

注意事项：

1. 在导入数据时，可以在Power Query中进行转换，但对数百万行的表来说，在原始数据源中转换性能更佳。
2. 当表中每天新增数百万行时，这些细节对内存和性能影响显著。
3. 不要过度优化那些不需要高压缩级别的数据模型，因为降低精度意味着丢失一些可用于深入分析的信息。

18.6 VertiPaq聚合

18.6.1 聚合的概念和使用规则

  聚合功能最初在2018年末作为Power BI的一项新功能被引入，其核心目的是减少存储引擎请求的成本。通过预计算聚合数据（如按月、按产品汇总的销售额），避免每次查询都扫描明细表（如单笔交易），从而减少对 DirectQuery 数据源的请求或减轻 VertiPaq 引擎的计算压力。

  聚合可在 VertiPaq 和 DirectQuery 中定义，但建议优先使用 VertiPaq 聚合（性能更优）。计算引擎会根据查询的“尺度”（如按年、品牌、国家等），自动选择最匹配的聚合表。如果有多个聚合匹配时，会选择优先级最高的。

以包含Sales、Product、Date和Store表的数据模型为例，Sales 表存储了每个交易的详细信息，比如产品、客户和日期。可以为Sales表创建多个聚合，每个聚合都有一个优先级：

1. 按Product和Date聚合，优先级为50
2. 按Store和Date聚合，优先级为20。

• 当查询按产品品牌和年份划分的总销售额时，会使用聚合1，因为其尺度与查询请求匹配。按月或日向下钻取，还是使用此聚合。
• 如果是查询按商店国家/地区和年份划分的销售额，则应该使用聚合2。
• 如果查询按商店国家/地区和产品品牌划分的销售额，由于没有现存的聚合与之完全匹配，只能使用包含所有详细信息的Sales表。
• 当多个聚合与请求兼容时，系统选择优先级最高的聚合，不管是哪种存储模式。

如果 DirectQuery 聚合的优先级高于 VertiPaq 聚合，且二者都与查询匹配时，则引擎将选择 DirectQuery 聚合。因此，开发人员需要定义好优先规则。

18.6.2 设计聚合

聚合能否匹配存储引擎请求取决于以下几个条件：

1. 关系尺度：存储引擎请求所涉及关系的尺度必须与聚合的尺度一致。
2. GroupBy列：聚合中定义为GroupBy的列，必须与请求中的列匹配。

     在聚合中，GroupBy是指在聚合表中用于分组的列。这些列通常是维度列，例如产品、日期、客户等。当存储引擎请求时，如果请求中的分组列与聚合中定义的GroupBy列完全一致，那么这个聚合才可能被匹配。

3. 聚合操作类型：聚合表中的聚合操作（如SUM、COUNT、AVG等）必须与请求中的聚合操作一致。
4. 明细表计数汇总：聚合必须能够处理明细表的计数汇总需求。

   如果查询请求是“按产品和日期分组，计算总销售额和销售数量”，并且还需要知道每个分组的交易次数，那么这个请求中的计数需求（COUNT(Sales)）与聚合表中的计数汇总一致，才能匹配上。

SUM聚合适用于单列的简单求和，对于复杂表达式，则无法直接使用SUM聚合。

Sales Amount := SUM ( Sales[Amount] )   						--可以使用 SUM 聚合
Total Cost := SUM ( Sales[Cost] )       						--可以使用 SUM 聚合
Sales Amount := SUMX(Sales, Sales[Quantity] * Sales[Price])		--不能使用 SUM 聚合
Margin1 := [Sales Amount] - [Total Cost]     					--可以使用 SUM 聚合
Margin2 := SUM ( Sales[Amount] ) - SUM ( Sales[Cost] ) 			--可以使用 SUM 聚合
Margin3 := SUMX ( Sales, Sales[Amount] - Sales[Cost] ) 			-- CANNOT 使用 SUM 聚合

  若需计算差值（如利润 = 收入 - 成本），应分别聚合收入与成本再相减（Margin1/2），而非逐行计算（Margin3）。但如果定义了一个聚合，其中包含Sales[Amount]和Sales[Cost]列的GroupBy摘要，以及Sales表的COUNT聚合，那么这个聚合可以匹配Margin3的请求。