何为复杂

数据量：一百万行以上，上限通常不超过五千万行。

查询数量：查询指需要连接的数据源的个数，数据源通常分为本地数据文件或数据库表，一个复杂项目的连接数通常在十几个到几十个不等。

自定义度量：几十个甚至上百个。

管理期望、了解局限

PowerPivot 虽然具备一定的数据存储能力，但是作为 Excel 的一个内置插件，其性能终究不能与 Power BI Desktop、SQL Server 相比，对此要有清醒的认识。选择 PowerPivot，看重的是 Excel 交付的便利性，与透视表和切片器配合的交互能力，以及基于 Excel 生成可视化图表的灵活性。

开始前准备

硬件篇

硬件配置在之前几篇文章里提到过，有必要再重复一次，对提升 PowerPiovt 性能起到帮助几项配置是：CPU 主频、CPU 核心数量、内存大小、内存的主频。对服务器来讲，这些配置上不封顶，对 Excel 而言，过高的配置会性能过剩。

硬件番外篇

番外篇是我在这次开发中的体会，除了提升“大脑”的配置，显示设备也很重要。两个以上的显示器可以显著减少前后台切换的时间，提升工作效率。对于一些复杂的公式，需要看着模型结构来编写，目前系统不支持数据视图和关系视图同时显示，这个时候可以用截图工具把模型关系视图保存下来单独投到一块屏幕上，实现同步对照，非常方便。最好是支持竖屏的显示器，可以显示更多代码行

(渣画质恰到好处的起到了防止数据外露的效果…..)

软件

开发一个复杂模型对软件和系统的某些要求可以说是刚性的，没有太多回旋余地，

64 位操作系统
64 位 Office
Excel 2016 或者 O365（定期更新）

可能还有很多人在用 Excel 2013，这个版本的 PowerPivot 稳定性要好过早期的 2016,但随着版本迭代，这个优势已经不存在了。而 2013 版的 PowerPivot 由于使用的 DAX 引擎版本较低，不支持很多新的高级函数，比如集合函数，不能直接在表和表之间做交并运算。

压力测试

配置好了软硬件，同时也基于少量测试数据搭建好了模型结构之后，接下来有必要对模型的数据承载能力做一个测试，评估这套模型未来是否能满足项目数据量不断增长的要求，以及响应时间是否在可接受的范围。我的做法是用先软件生成几百万模拟数据来做测试。

管理查询语句

养成好的代码习惯可以节省后面维护和交接的时间，包括

查询分类
规范命名
Queryfolding
Buffer 函数

受 PowerQuery 性能限制，数据清洗过程的操作不宜过多、过于复杂，否则可能带来加载时间的显著增加。如果不得不进行复杂的清洗，对于连接数据库的查询，尽量使 PowerQuery 把处理语句发送到数据库，由数据库引擎执行操作，此过程称为“Query Folding”。对于连接本地文件的复杂查询，使用 Buffer 函数将处理步骤缓存起来，也可以节省处理时间。

管理自定义度量

PowerPivot 没有专门的度量管理工具，所有自定义度量以按生成顺序显示在一个 list 里，当多到一定数量的时候，查找起来并不方便。
通常我们习惯把度量分散保存于模型的某些表，在透视表中调用这些度量的时候它们会和已存在列一起展示，形成干扰。建议预先将自定义度量分成若干类，每一类新建一个空的虚拟表导入模型，将列隐藏，这样你就得到了一个专门存放度量的虚拟表，排除了其他干扰。

需要注意的是，经过我的测试，如果虚拟表只有一列，最多能存放 100 个左右的自定义度量。

管理 DAX 代码

目前 PowerPivot 和 PBI Desktop 都还没有内置的 DAX 代码管理工具，毕竟不是企业级应用，一般不涉及大量代码，但是在一个复杂的模型中，代码量会相当可观，如果不做格式化和注释，后期根本无法维护。可以想象，如果下面这个公式不做格式化，根本无法阅读：

DAX Studio

即使经过了换行处理，由于 PowerPivot 不能给公式添加注释，后期维护的时候重新理解也是要费一番功夫的（在 PBI Desktop 的公式编辑栏里，支持//注释）。为了解决格式化和注释的问题，同时出于代码管理的便利，需要用到 DAX Studio,这是一个轻量但功能相当齐全 DAX 编辑器，可以连接 PowerPivot，PBI Desktop 和 SQL Server 中的表格模型。
这么做的好处是，方便格式化公式、添加注释和快速搜索到特定度量，但要记得每次对公式的修改都要同步到 DAX Studio 中。DAX Studio 还支持公式调优，在某些情况下会非常有用。

文档化工具 PP Utilities

PP Utilities 是一个 Excel 插件，支持将 PowerPivot 模型的数据文档化，主要功能有

导出所有自定义度量和计算列
导出模型里每一列的使用情况，标注未使用的列。
导出模型的内存占用数据

特别提醒

随着模型复杂度的上升，每次修改公式后,Excel 的响应时间也会增加，在系统响应的这段时间里，切忌在 Excel 内进行任何操作，否则可能有很大几率带来假死或其他意外关闭的情况。Excel 每次对修改公式的响应可以大致分为两个阶段：后台刷新和前台响应，每种阶段都可以在右下角看到提示，耐心等待响应结束，在进行接下来的操作。