从数据到信息
从信息到洞察

如何用PowerPivot开发复杂的数据模型

何为复杂

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

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

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

 

管理期望、了解局限

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

 

开始前准备

 

硬件篇

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

硬件番外篇

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

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

软件

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

  • 64位操作系统
  • 64位Office
  • Excel 2016或者O365(定期更新)

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

 

压力测试

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

 

管理查询语句

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

  • 查询分类
  • 规范命名
  • Queryfolding
  • Buffer函数使用PowerPivot开发一个复杂的数据模型有哪些注意事项

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

 

管理自定义度量

PowerPivot没有专门的度量管理工具,所有自定义度量以按生成顺序显示在一个list里,当多到一定数量的时候,查找起来并不方便。
使用PowerPivot开发一个复杂的数据模型有哪些注意事项通常我们习惯把度量分散保存于模型的某些表,在透视表中调用这些度量的时候它们会和已存在列一起展示,形成干扰。建议预先将自定义度量分成若干类,每一类新建一个空的虚拟表导入模型,将列隐藏,这样你就得到了一个专门存放度量的虚拟表,排除了其他干扰。
使用PowerPivot开发一个复杂的数据模型有哪些注意事项
需要注意的是,经过我的测试,如果虚拟表只有一列,最多能存放100个左右的自定义度量

 

管理DAX代码

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

 

DAX Studio

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

 

文档化工具 PP Utilities

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

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

使用PowerPivot开发一个复杂的数据模型有哪些注意事项

 

特别提醒

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

 

总结

以上就是我在建模过程中的试错经验,也欢迎各位在留言里分享自己在建模时遇到的一些经验教训。

上面的这些坑,只要你有足够的耐心和责任心,并且善于总结,其实不难克服。而另一个摆在面前的更大的困难是,如何在结构错综的模型中抽丝剥茧,定义逻辑刁钻的度量,DAX是一门很强大的分析语言,可以对不同粒度的数据施加不同的影响,但复杂的表间关系会带来很多意向不到副作用,这个话题我们留待下次分享。

原创内容 转载请联系作者:PowerBI极客 » 如何用PowerPivot开发复杂的数据模型

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址