在 Excel 中，可以创建包含数百万行的数据模型，然后对这些模型执行强大的数据分析。 使用或不使用 Power Pivot 加载项均可创建数据模型，以在相同工作簿中支持任意数量的数据透视表、图表和 Power View 可视化效果。
虽然您可以轻松地在 Excel 中构建大量数据模型，但是也有不建议您这样做的理由。 首先，包含大量表格和列的大型模型对于大多数分析而言过于繁琐，会产生难以使用的字段列表。 其次，大型模型会占用宝贵内存，从而对共享相同系统资源的其他应用程序和报告产生负面影响。 最后，在 Microsoft 365 中，SharePoint Online 和 Excel Web App 将 Excel 文件的大小限制为 10 MB。 包含数百万行的工作簿数据模型很容易就达到 10 MB。 请参阅数据模型规范和限制。
在本文中，您将了解如何构建更易于使用且占用较少内存的结构紧密的模型。 花些时间了解高效模型设计的最佳做法，无论是在 Excel 中查看模型、 Microsoft 365 SharePoint Online、 Office Online 服务器还是在 SharePoint 中查看模型，都将为你创建和使用的任何模型提供回报。
还请考虑运行工作簿大小优化程序。 它可分析您的 Excel 工作簿，并在可能时进一步压缩工作簿。 下载 工作簿大小优化器。
本文内容
压缩比和内存内分析引擎
Excel 中的数据模型使用内存内分析引擎在内存中存储数据。 该引擎采用强大的压缩技术来降低存储要求，收缩结果集直到达到原始大小的一小部分。
平均而言，您可以期望将数据模型缩小 7 到 10 倍，但是与原始大小包含相同数据。 例如，如果您从 SQL Server 数据库导入 7 MB 的数据，则 Excel 中的数据模型很可能是 1 MB 或更小。 实际上实现的压缩程度主要取决于每个列中的唯一值数目。 唯一值越多，存储它们所需的内存越多。
为什么讨论压缩和唯一值？ 由于构建可最小化内存占用的有效模型就是压缩最大化，实现该目的的最简单方法是清除您不真正需要的任何列，尤其是这些列包含大量唯一值时。
: 各列的存储要求可能有很大差别。 在某些情况下，最好是使用多列而包含较少唯一值，而不是使用一列而包含大量唯一值。 日期时间优化部分将更详细地介绍此技术。
不存在的列最能有效减少内存使用量
内存使用效率最高的列是指您从未导入的列。 如果您要构建一个高效模型，请查看每个列并确定它是否会参与到您要执行的分析中。 如果它不参与或您不确定，请不要导入它。 您可以在以后根据需要添加新列。
应始终排除的列的两个示例
第一个示例与来自数据仓库的数据相关。 在数据仓库中，通常可找到在仓库中加载和刷新数据的 ETL 进程的项目。 “创建日期”、“更新日期”和“ETL 运行”等列是在加载数据时创建的。 所有这些列在模型中都不需要，在您导入数据时应不要选中。
第二个示例涉及在导入事实数据表时忽略主键列。
包括事实数据表的许多表格具有主键。 对于大多数表（例如，包含客户、员工或销售数据的表格），您需要表格的主键，以便可以使用它在模型中创建关系。
事实数据表是不同的。 在事实数据表中，主键用于唯一标识每一行。 出于规范化目的很有必要，但是在数据模型中很少使用，在数据模型中，您只需要用于分析或建立表关系的列。 因此，当从事实数据表导入时，请不要包括其主键。 事实数据表中的主键不但会占用模型中的巨量空间，并且并无用处，因为它们不能用于创建关系。
: 在数据仓库和多维数据库中，包含大部分为数值数据的大型表格通常称为“事实数据表”。 事实数据表通常包括业务绩效或交易数据，例如与组织单位、产品、细分市场、地理区域对应并聚合的销售和成本数据点。 事实数据表中包含业务数据或者可用于交叉引用其他表中存储的数据的所有列应包括在模型中，以支持数据分析。 要排除的列是事实数据表的主键列，它包含仅在事实数据表中存在的唯一值。 由于事实数据表巨大，一些模型效率很高是因为从事实数据表中排除了部分行或列。
如何排除不必要的列
高效模型仅包含在工作簿中实际需要的那些列。 如果要控制模型中包括哪些列，您必须使用 Power Pivot 加载项中的表导入向导来导入数据，不要使用 Excel 中的“导入数据”对话框。
当您启动表导入向导时，请选择要导入的表。
对于每个表，您可以单击“预览并筛选”按钮并选择确实需要的表部分。 建议您首先取消选中所有列，并在考虑是否需要这些列进行分析之后继续检查所需列。
如何仅筛选必要行？
企业数据库和数据仓库中的很多表包含长时间以来累积的历史数据。 此外，您可能会发现，您感兴趣的表格包含特定分析并不需要的业务领域的信息。
使用表导入向导，您可以筛选掉历史或不相关的数据，从而节省许多模型空间。 下图中使用了一个日期筛选来仅检索包含当前年份的数据的行，排除了不需要的历史数据。
如果我们需要列该怎么办呢？是否仍然可以减少空间使用？
您可以采用其他几种方法来使某列成为更好的压缩候选列。 请记住，影响压缩的列的唯一特征是唯一值的数目。 在此部分中，您将了解如何修改一些列以减少唯一值的数目。
修改日期时间列
在许多情况下，日期时间列会占用大量空间。 幸运的是，有多种方法可降低此数据类型的存储要求。 方法因您使用列的方式以及构建 SQL 查询的舒适级别而有所不同。
日期时间列包括日期部分和时间。 当您考虑是否需要某列时，针对日期时间列，请三思：
-
是否需要时间部分？
-
时间部分是否需要显示小时？ 分钟？ 秒？ 毫秒？
-
是否因为想计算多个日期时间列之间的差值，或者只想按年、月、季度等汇总数据而拥有多个日期时间列？
您关于上述问题的答案将确定您用于处理日期时间列的选项。
所有这些解决方案都需要修改 SQL 查询。 为更轻松地修改查询，您应在每个表中至少筛选出一列。 通过筛选出列，您可以将查询结构从缩写格式 (SELECT *) 更改为包括完全限定的列名称的 SELECT 语句，后者更易于修改。
我们来看看为您创建的查询。 在“表格属性”对话框中，您可以切换到查询编辑器并查看每个表的当前 SQL 查询。
从“表格属性”，选择“查询编辑器”。
查询编辑器显示用于填充表的 SQL 查询。 如果您在导入期间筛选出任何列，则您的查询包含完全限定的列名称：
|
相反，如果您全面导入某个表，而不取消选中任何列或应用任何筛选器，则您将看到查询“Select * from”，它将很难修改：
|
修改 SQL 查询
了解了如何查找查询后，您可以对其进行修改以进一步减小模型大小。
-
对于包含货币或十进制数据的列，如果您并不需要小数位数，请使用此语法去除小数位数：
“SELECT ROUND ([Decimal_column_name]，0) ... .”
如果您需要美分而不需要美分的小数部分，请将 0 替换为 2。 如果您使用负数，则可以舍入到单位、十、百等。
-
如果您有名为 dbo.Bigtable.[Date Time] 的日期时间列，但不需要时间部分，请使用语法去除时间：
“SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) ”
-
如果您有名为 dbo.Bigtable.[Date Time] 的日期时间列，并且需要日期和时间部分，请在 SQL 查询中使用多列，而不是使用单个日期时间列：
“SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],
datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],
datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],
datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],
datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”
根据需要使用任意数量的列来将各个部分存储在单独的列中。
-
如果您需要小时和分钟，并且希望一起放在一个时间列中，则可以使用语法：
Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]
-
如果您有两个日期时间列（例如 [Start Time] 和 [End Time]），并且您真正需要的是它们之间的时间差异（以秒为单位），称为 [Duration] 列，请从列表中删除这两列，并添加：
“datediff(ss,[Start Date],[End Date] as [Duration]”
如果您使用关键字 ms 而不是 ss，则将以毫秒为单位获得持续时间
使用 DAX 计算度量值而不是列
如果您之前使用过 DAX 表达式语言，则可能已经知道，计算列用于基于模型中的一些其他列派生新列，而计算度量值在模型中定义一次，但是仅在数据透视表或其他报表中使用时进行求值。
一个节省内存的方法是将常规或计算列替换为计算度量值。 经典示例是“单价”、“数量”和“总计”。 如果您拥有所有这三列，则可以通过仅维护其中两列而使用 DAX 计算第三列来节省空间。
应保留哪两列？
在上面的示例中，请保留“数量”和“单价”。 这两列中的值比“总计”要少。 若要计算总计，请添加计算度量值，如下例：
“TotalSales:=sumx(‘Sales Table’,’Sales Table’[单价]*’Sales Table’[数量])”
计算列类似于常规列，都会占用模型空间。 相反，计算度量值是动态进行计算，不占用空间。
结论
在本文中，我们讨论了可帮助您构建内存更加高效的模型的几种方法。 降低数据模型的文件大小和内存要求的方法就是减少行和列的数目以及每列中显示的唯一值的数目。 下面是我们介绍的一些方法：
-
删除列毫无疑问是节省空间的最佳方式。 决定您真正需要哪些列。
-
有时，您可以删除列并将其替换为表中的计算度量值。
-
您可能并不需要一个表中的所有行。 您可以在表导入向导中筛选出行。
-
通常，将单列拆分为多个不同部分是减少列中的唯一值数目的好方法。 每个部分将具有数量较小的唯一值，并且合计总量小于原始统一列。
-
在许多情况下，您还需要不同部分用作报表中的切片器。 如果需要，您可以从部分（例如小时、分钟和秒）创建层次结构。
-
许多情况下，列包含的信息比您实际所需的要多。 例如，假设某列存储小数，但已应用格式来隐藏所有小数。 四舍五入可帮助有效地减少数字列的大小。
由于您已尽可能减小了工作簿的大小，所以还请考虑运行工作簿大小优化程序。 它可分析您的 Excel 工作簿，并在可能时进一步压缩工作簿。 下载 工作簿大小优化器。
