在原来的系列中,我们都是集中在Power Excel。但是考虑到内容越来越多,所以我们决定把它分开:将原来的系列集中在Excel的使用方面,而将跟超级透视表和Power BI相关的这种应用单开一个系列:DAX,的主要关注DAX函数Power BI和 Power Pivot,主要介绍DAX函数编程当中的一些技巧,一些很重要的方法以及要注意的问题。我们也会讲超级透视表,也会讲Power BI的一些使用技巧,包括可视化的展示等等。当然在Power BI当中还包含Power Query的内容,不包含在这个系列里面。我们会为Power Query单独做一个系列,同时前面介绍的自动化,也会单独做一个系列,主要讲office脚本和 Power Automate。听上去这个计划有点庞大,能做成什么样也不知道,尽力而为吧。 数据模型 数据模型是超级透视表和Power BI的基础。我们以后会花大量的篇幅去介绍的DAX,其实也是附属于数据模型之上的。它是用来对数据模型当中的数据进行计算的这样一个函数体系,或者编程语言。在Power BI中,数据模型实际上可以理解为对实际业务的一种抽象。这种抽象是在数据层面的。也就是我们将业务抽象成了一份一份的数据。比如考察我们公司的销售业务,它都有什么样的数据呢?当然就是具体的销售记录:什么时间,卖了一个什么样的产品,卖给谁,数量有多少,...。但是仅仅只有销售的数据是不够的,我们要完整的看清我们的业务的话,还需要其他的数据,比如产品的相关数据,你卖的产品它是由哪个厂商生产的,单价是多少,进货成本是多少,制造成本是多少,这个产品属于什么样的大类或者小类,...。还有数据,比如你的销售渠道有哪些,在整个的销售周期内,你做了什么样的市场促销活动,它库存状况如何,...。当然还有在所有的业务当中。几乎都会存在的数据,但是往往被我们所忽略:那就是时间数据,你的销售是什么时间发生的。这也是一个很重要的数据通过这样的一个并不太复杂的分析。我们就可以将销售业务抽象为这样的一些数据,这就是数据模型的概念:数据模型就是对实际业务在数据层面进行抽象的结果。抽象出来的这些数据是以表的形式存在于数据模型当中的。从另一个角度理解数据模型,就是:一个数据模型是一张或者多张表的集合;而且如果数据模型是由多张表构成的话,那么在表之间需要创建关系。就像下图所示而数据模型中的表和我们在Excel中熟悉的表,几乎是一样的:
数据模型架构
大家学习 Power BI或超级透视表时,经常会看到这样的两个名词:它们代表着我们数据模型的两种架构,即模型中各个表之间通过关系构成了一种什么形式的结构:大家不需要深入的去理解这里面的一些理论上的背景。有一个基本的了解就可以了。看到这样的文章和资料的时候,可以知道在说什么就行了。所谓星星架构就是有一个表在中间,其余的表在周围。而周围的表相互之间并没有关系,这些表只跟中间的表创建关系。而雪花架构就复杂一些。在外围的表上它可以往外扩展。这个结构可以无限的往向外扩展,每一个表都可以有自己所属的子表。但是实际上,右边的雪花架构的中间部分完全可以看作是一个星形架构。从图上看就是一个星型架构,其中sales表位于中心的位置,而渠道,产品市场活动,库存,日期等等都是周周的表,它们相互之间并没有关系,而每一个表都跟中间的销售表发生关系。但是这个只是模型中最核心的部分,实际上,把完整模型拿出来的话,你会发现这是一个雪花架构的模型: 中间的是销售表,外围是产品,渠道等表。产品表又有自己的子表:小类。小类也有它的子表:大类。而库存这个表也有子表:地理位置,记录这一个仓库位于什么样的城市。改变架构 如果我们要将一个雪花架构的模型变成星型架构,应该怎么做?很简单,只要将地理位置这一个表取消,将它的值放到库存表之后作为一列,每一个仓库它所在的地理位置,地理位置表就消失了。同样,将产品的大类放到小类表中作为它的一列,大类表也可以消失。而将小类表的所有列,作为产品表的几列,小类表也可以消失。产品表的这些列可以作为销售明细表当中的列出现,其余的表格中的列也都可以放在销售明细表中,我们就可以得到一张大表。整个的数据模型就只有这一张表构成。这也是允许的。当然在实际工作里面,我们一般不会这么处理。即使你面对的数据,比如在Excel里我们做了一张大表,记录着所有的这些内容,我们也会想办法将整个的表拆分成销售,渠道,,产品,日期,市场活动,库存等表。这个拆分或者合并的过程,一般是通过Power Query来完成的。事实表和维度表
数据模型当中的表被分为两种:事实表(Fact)和维度表(Dimension)事实表是在模型当中位于中间的表,记录明细数据的。在整个的数据模型当中只有事实表记录明细数据,别的表并不记录这些数据。我们前面说数据模型是对实际业务的抽象,实际业务的发生的具体的数值,就是事实,比如销售了多少,销售了多少次等等,就是事实表的内容。而位于模型外周的那些表,就叫做维度表:产品维度,地理位置维度,组织结构维度,日期维度,库存的维度,...。在雪花模型当中它们还可以往外扩展,往外扩展的仍然是维度表。记录了销售的明细,在某一天发生了一次销售,销售的单位成本是多少,单价多少,数量多少,当然还有其他的一些我们需要统计的数据。除了这些表示真正的事实的数据之外,在表当中还有其他的列,比如产品类product key,它关联到产品维度表。这些列在汇总统计的时候,是起分类的作用的,比如要统计不同类别的产品的销售情况,就需要通过product key关联到产品表中。 在设计数据模型时,经常会面临着对一个表格设计的两难的选择第一种选择,就是将产品相关信息单独放在一个产品维度表中,在事实表中使用Product Key与之进行关联。第二种选择,就是将产品相关信息放在事实表中作为列出现。一般来说,我们推荐第一种做法,这样的设计叫做规范化的设计。但是,在有些情况下,比如维度很简单,或者有特殊统计需求,我们也会采用非规范化的设计。角色扮演维度 在这个模型中,事实表是销售明细,关联了日期维度表。日期表中提供了一个键值:DateKey。 但是事实表中却有三个日期:过期日,订单日,发货日。在统计分析时,这三个日期代表了三个不同的维度,这样就需要在事实表和维度表之间创建三个关系。每个关系扮演不同的角色。这三个关系中,只能有一个是活动的关系,在进行关联分析时,系统使用的就是这个活动关系。比如,如果OrderDateKey - DateKey这个关系是活动的,那么通过日期进行分类汇总时,就是在根据订单日期进行分类汇总。 如果希望使用另外的关系,可以将其变成活动关系。也可以在写DAX计算时,通过函数指定使用的关系。在关系图上,活动的关系用实线表示,非活动的关系用虚线表示。当然,在这种情况下,也可以不创建多个关系,而是使用多个维度表:在这个模型中,我们创建了3个日期表,事实表与每个日期表之间创建一个关系就够了。这样就可以根据不同角色的日期进行分析,相对来说更加直观。 创建模型时,可以通过Power Query创建三个相同的日期表,也可以通过DAX中的计算表来实现。(在Excel中的数据模型中,只能使用Power Query来实现)杂项维度
所谓杂项维度,就是有一些维度,它本身的取值范围特别小,只有这么两三行单独作为一个维度表来说,太琐碎了。这个时候我们就将这些个维度表合二为一,比如,按照初始设计,模型中应该有两个维度表:订单状态表和发货状态表,但是它们取值少,我们就可以合成一个订单发货状态维度表。 实际上,后者是前两个表的交叉,或者叫笛卡尔积。其实就是列举两个表的所有组合。这样就由原来的三行,和两行的两个表,变成了一个六行的维度表。退化维度
考虑一下我们在分析数据时使用维度的场景:往往是在进行展示的时候,使用用来做筛选的,或者是使用维度来进行分类汇总。例如在超级透视表当中,可以在行上放一个筛选的字段,这个字段其实就是维度。比如筛选不同的产品,或不同类别的产品。这些筛选都是针对维度表进行筛选的,事实表只提供将要汇总的那些数值,比如卖了多少产品,卖了多少种产品,总价是多少,或者利润是多少。还有一种很特殊的筛选场景:比如在销售明细表当中对订单号进行筛选,就看这几个订单号的汇总,这种情形也是很常见的。那么我们是不是有必要单独做一个维度表去维护订单号的记录呢?这样做,维度表中势必就有一个订单号列,而这个订单号列和事实表的订单号列要建立一个关联。因为这一列的值,其实和事实表中对应列的值是一模一样的。维度表上也并不提供更多的信息。如果真正有这种情形。不如直接将它放到事实表上就好。这里的订单号仅仅是一个例子,实际当中还有其他的情形也是类似的。一旦你发现是这种情况,也就是说维度表本身,并不提供更多的额外的信息,那么你就不如直接把它放到事实表上。
加入E学会,永久免费学习更多Excel应用技巧 http://www./portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI Power Excel 知识库 按照以下方式进入知识库学习Excel函数 底部菜单:知识库->Excel函数自定义函数 底部菜单:知识库->自定义函数 Excel如何做 底部菜单:知识库->Excel如何做面授培训 底部菜单:培训学习->面授培训 Excel企业应用 底部菜单:企业应用 也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
|