建模师日志：数据建模实战升级案例解析，销售与费用分析

天Э昕♂淡然

首先今天用于分析的业务模型来自网络，所有的分享和截图已经经过数据所有者同意，并已经脱敏处理。本帖为 建模师日志：数据建模实战升级案例解析 系列的首贴，未来我会积累案例并汇总后供大家学习建模思路。


首先晒的是原始提问，如何结合总代收入来计算区域费用的费比。即我们可以说就是拿表中的办公费用的26来除广州省办的总体收入430，来得到6%费比。其余几个费用大类以此类推。分别算的14%，20%，11%，并且总计52%这样的结果。

于是很多人开始从透视表角度给出芸芸的DAX解决方案。。。（因为是PowerPivot透视表）。


问题初探：
但是一般这种问题，往往是建模本身就有问题，于是我找数据所有者要到了这个计算表，还有对应的数据源文件。

包含数据模型的报告工作簿的查询状况，

原始数据的2个工作簿。
然后，通过打开报告工作簿的查询，还有数据源设置，可以看到，原先的处理方式是直接将两个数据源文件进行了合并（追加合并模式）。
具体合并的查询代码可以见《原始案例需求.xlsx》，相关的素材也在《原始数据》文件夹下。



问题再探：
探寻到上一个阶段，事实上，我们需要对原始数据进一步开刀才能揭示问题了。毕竟这两个表到底啥样子，而且模型中字段茫茫多，多少是有用的，多少是没有意义的，业务上存储的字段大概啥用途。仅从合并的分析原始案例需求工作簿里面很难推进。
同时，我也和提问者沟通上了，会在下面进一步沟通和解析。

上图是来自 《费用明细.xlsx》的一个截图，136个字段，2个数据源已经完全相同的结构，只不过数据行数不一。
难怪提问者会采取文件夹合并的方式来制作分析数据集。
对此，我们作为分析师需要进一步解析。
首先，136个字段太过于冗余，而且肯定存在很多完全没有必要的字段。通过针对2个表进行了数据公式，简单的可以得到以下一个初步的分析结果。

计数的是对应表内存在数据的数量，我用的是CountA公式。
这样即使是销售表的有数据字段多一点，也就29个。一下次从136砍到了29及以下，问题就好很多了。
然后通过和提问者的持续沟通，得到了一个非常重要的信息：
销售表是销售行为的主要记录
费用表是费用成本的主要记录
两个会在诸如省办考核，省份分析等 维度上做同条件的分析。
且，有销售记录，一定有费用成本的发生。但是费用成本不一定有关联的销售记录。
于是，可以从理解上嗅到一个初步的印象：这是2个完全不同的事实数据集。。。把他们纵向追加查询的建模本身就没有业务道理。
只不过有很多维度可能是会公用的。
重点：双事实表，有公用维度！


寻找维度线索：


上面有数据源的2个有效字段列表，而且数据源本身的命名尚且算是规范的。于是我们可以从中入手寻找规律。
把上图的2个列表简单的放到一起，进行透视。我们可以得到下面的一个结果：

那么这两个表共有的字段，会不会有更大的线索在里面呢？？
答案显然是有的：
虽然提问者强调 分析维度和考核维度是2个事实的完全公用部分。
但是通过比对，我们还拿到了更多。具体分析如下：

当然，我们归纳维度不会只是建模师自己想当然，我归纳了4个维度，并且看到费用表有跨年的日期字段，我还额外添加了日期表作为建模常备。
上面还有区域划分等不能明确的，就暂且各自保留在事实表内，不去过多干预。

因此到了这步，这个模型的大概状况是了解的。另外需要提醒的是，从费用表里面提取的考核维度的项目数量和从销售表里面提取的项目数量是不一样的。因此就需要和提问者沟通清楚出来方式。比如只用共用的部分，还是需要把2表不同的项全部合并到一起。细节上还是需要注意的。




分离维度表，优化事实表，数据清洗！！！：

基于上面的分析过程，我们把2个事实表分别放在不同的文件夹，并且未来可以通过文件夹合并的方式或者更换文件夹里面的文件来同数据清洗关联。
现在我们重新创建一个EXCEL工作簿，命名为《优化后抽离数据.xlsx》。

这是 基于2个数据源重新抽离之后的，查询依赖项，为了更好的看到路径，我临时修改了EXCEL的主题。
具体的操作的代码和数据会在附件中的文件可以看到。
于是，通过ETL的过程，我们把2个文件工作簿，我们变成了4个维度表，2个精简后的事实表，还有一个日期表，我是复制进去的。

至此，数据清洗的过程也好了



料理数据模型：
那么有了干净有序的素材，我们的建模就相对轻松。
在《脱敏版销售与费用分析模型.xlsx》中，我们直接读取上一步骤中整理过的数据表，用于数据建模和PowerPivot。

最简单的导入数据清洗，源》导航》更改类型
好的食材，往往就只要简单的料理，就能很好吃了。
结合上面导入的数据，我们很快的可以如下建模：

公用5个维度表的2个事实表，很简单的双子星（星型模型）。
然后，我们也开始解决提问需要的简单计算。

朴实无华，直接插入透视表，开始计算！！

这个截图已经和提问者开始的求助的要求，并和手工模拟的数据已经一致。




小结：
经过改良的模型应该可以适配多数分析的计算，如果需求还能进一步归纳模型，
添加日期表，并标记日期表之后，我们可以很方便的调用时间智能函数用来做时间向的分析和计算
在最终的优化模型中，我构建了一些辅助Key，并做好了视图隐藏的配置，在创建模型透视表的时候可以减轻分析师的干扰压力。
日期表中的列，有些辅助排序的列，已经做了相应的设置。呈现排序更方便


成为一名优秀的数据建模师基本上意味着你能够将你的模型与众多已经经过透彻研究的经典模型进行匹配。因为你的模型和其他经典模型在本质上没什么不同。虽然你的模型通常有一些特别要求，但更多的可能是你的具体问题其实早已经有相应的解决方案。学习如何发现你的数据模型与示例模型之间的相似性虽有难度，但效果非常好。比如，业务中的大部分难题会在你学习数据建模的过程中迎刃而解。                                            《Power  BI 建模权威指南》

本帖涉及文件的附件如下：
链接：https://pan.baidu.com/s/1hhIhmr69h3mQqJDdUE-k3w
提取码：0bz1

肥皂里的天堂

支持老师的分享，收藏学习！

flashevil

宝贵的学习素材，保持关注！

qmf

感谢分享！

ladeng6666

感谢船长的分享，太棒了，点赞，点赞！

凤凰羽衣

收藏分享

Excel培训师

牛，真正来自生产一线的实战型分享，受益匪浅，感谢刘老师

渡渡orz

精彩，向老师学习

夏天2012

必须强烈点赞支持！！！现实中的案例，会让人感同身受，并且充分理解建模的规范和必要！

硅谷少年

非常好的案例。遇到字段很多的表，非常有必要参考此类案例做维度拆分