Power BI入门教程

题记:这篇文章不仅是Power BI的入门教程,同时相对于Qlik Sense进行了简单比较。

最近把一个Qlik Sense的示例应用手动转成了Power BI的应用,把相关步骤和遇到的问题记录如下,权当作一个入门教程。

1,准备原始数据

由于Qlik Sense的示例应用只有一个单独的qvf文件,那么原始数据只有通过qvf来导出。此qvf中的数据模型如下图所示:

image

Qlik Sense并没有提供数据模型包含数据的完整导出功能,所以只能采用最麻烦和原始的方式,即:创建表格,在表格中添加某个数据表的所有字段,然后再利用可视化对象的导出功能导出数据为xlsx文件。通过这种方式,把所有表都导出。注:但在实际操作当中,未必所有表都导出,因为有些表可以在Power BI中生成(比如日期表DateParts),有些表在Power BI中也用不到(比如月份顺序表Month Sort Order)。

当然,如果你拿得到原始数据的文件或者数据库,那么就可以跳过这一步。

2,加载数据

这步比较简单,在Power BI中新建一个文档,通过“获取数据”-“Excel”来选择需要导入的xlsx文件。

image

选择文件之后,会显示“导航器”对话框。在这里选择需要导入的工作表(可以选择多个)。选择工作表之后,可以直接点“加载”,也可以点“编辑”来打开“查询编辑器”修改ETL脚本(当然在加载之后还是可以重新编辑脚本)。如果直接点“加载”之后,Power BI就会把选择的工作表中的数据加载进来,这个时候就可以在“数据”视图中预览其中的数据,右侧的“字段”边栏也会显示表及其包含的字段:

image

这个时候,你会发现表名称是xlsx文件中默认的Sheet1,修改表名称的最简单方式就是,在表名称上点右键选择“重命名”。另外,有些xlsx文件导入之后,数据可能会出现下面这种没有正常识别列名称的问题,这个时候就需要用到“查询编辑器”中的“提升的标题”(把第一行数据当作标题/列名)的功能:

image

“查询编辑器”是一个强大的UI操作界面,帮你自动生成Power Query的M语言脚本,可以通过“高级编辑器”来查看每个表的Power Query脚本。而Power Query的第一步就是通过“源”和“导航”脚本来实现Extract-Load的步骤。对于Power BI而言,Extract-Load可以实现的能力有:

  • 从各类数据源中提取数据:各类文件、各类数据库、各类Azure的PaaS、各类联机服务和其他数据源(比如R脚本)
  • 对数据源的配置进行设置
  • 直接录入数据

3,转换和塑造数据

在加载数据的过程中或者之后,还可以继续利用“查询编辑器”来对加载的数据进行转换和塑造(即Transform)。所谓转换和塑造就是利用Power Query的M语言脚本来对数据的加载过程,进行额外处理。我大致把这个过程中Power BI能提供的能力整理了一下:

  • 通用
    • 列的管理:选择列、删除列
    • 行的管理:保留行(前后、间隔、重复、错误)、删除行
    • 排序:基于一个列或多个列进行升降序
    • 合并数据:可以是合并数据(两个表提供不同的列),也可以是追加数据(两个表提供不同的行)
  • 转换
    • 表数据管理:对原始数据进行分组、提升第一行作为标题、行列颠倒、首尾行调换、对数据行计数
    • 所有列的处理:重命名列名、数据类型的自动检测和手动修改、替换值、填充单元格(上下两个方向皆可)、透视列(正逆两个方向)、转换为列表(列表转回列)
    • 文本列的处理:拆分(分隔符、字符数)、格式化(大小写、首字母大写、修整Trim、清除非打印字符、添加前后缀)、合并、提取(字符串长度、首子字符串、尾子字符串、选定范围子字符串)、分析(从xml和json字符串中提取出数据)
    • 数值列的处理:聚合运算(求和、最大最小、中值、平均值、标准偏差、值计数、非重复计数)、标准运算(四则、整除、取模、除得百分比、乘得百分比)、科学运算(求绝对值、求幂、求指数、求对数、求阶乘)、三角函数运算、舍入(向上向下、自定义)、特征(奇偶、符号)
    • 时间列的处理:日期的处理、时间的处理、持续时间的处理
    • 结构化列的处理:扩展、聚合
  • 添加计算列
    • 常规:基于公式计算、基于自定义公式计算、基于条件判断计算、添加索引列、复制列
    • 基于文本列添加:格式化后、合并后、提取后、分析后
    • 基于数值列添加:聚合运算后、标准运算后、科学运算后、三角函数运算后、舍入后、提取特征后
    • 基于时间列添加:日期处理后、时间处理后、持续时间处理后

从上面整理的内容来看,Power BI由于沿用了SQL Server和Excel中已经存在的Power Query,所以它的ETL功能还是非常强大的,并且几乎不用你手动编写ETL脚本即可完成复杂的ETL工作。

想对Power Query的功能有详细了解的,建议查看Excel的文档:https://support.office.com/zh-cn/article/%E8%8E%B7%E5%8F%96%E5%92%8C%E8%BD%AC%E6%8D%A2%E5%9C%A8-Excel-2016-%E4%B8%AD-881c63c6-37c5-4ca2-b616-59e18d75b4de?ui=zh-CN&rs=zh-CN&ad=CN

4,建模数据

在完成数据的ETL之后,需要的步骤就是对数据进行建模。一般而言,在导入数据之后,Power BI会根据字段的名称自动推断出表之间的关系的。比如下图就是导入示例数据之后自动构建的关系图:

image

建模的工作和ETL的工作是两个不同的步骤,虽然有些功能达到的效果是一样,但是背后实现的机理是不一样的。最明显的一个地方就是数据类型的修改,在查询编辑器中对数据类型进行修改会替换或产生新的ETL脚本,而在数据视图中修改数据类型不会影响ETL脚本。

Power BI支持的建模能力整理如下:

  • 管理关系:可以通过关系视图来可视化的拖动连线,也可以通过“管理关系”对话框浏览、新建、自动检测、编辑和删除关系 。
  • 关系:关系支持1:1、1:N、N:1,关系筛选支持单向和双向 。
  • 公式计算:添加基于DAX表达式计算得到的度量值(度量值默认放到当前选中的表当中,也可以归到其他表中),添加基于DAX表达式计算得到的计算列,添加基于DAX表达式计算得到的表 。
  • 排序:选择排序的列 。
  • 格式设置:可以设置列的数据类型(小数、定点十进制数、整数、日期/时间、日期、时间、文本、布尔值、二进制),显示格式(各种货币格式、时间格式、百分比、小数位数、科学计数等) 。
  • 属性设置:所属表(可以为度量值设置归属的表),数据分类(设置值的显示特征,比如地址、Url、条码,可以方便让可视化控件更好的处理内容),默认聚合方式(要不要求和等) 。
  • 安全性:基于DAX表达式来设定什么角色可以查看什么数据,非常灵活的行级访问控制机制 。
  • 分组:这是什么鬼,我也还没有搞懂。 有知道的朋友希望不吝赐教。

在建模的过程中,尤其和公式计算相关的东西都涉及到数据分析表达式(Data Analysis Expressions,DAX)的使用,详细的说明可以参考MSDN官方文档:https://msdn.microsoft.com/library/gg413422.aspx

(updated 2016.12.20)更完整的文档在这里:https://msdn.microsoft.com/en-us/library/mt244090.aspx

对于我的这个示例,我实际用到了如下几种建模能力:

  • 日期数据的格式改变:把长格式的日期字符串修改为短的 。
  • 各种度量值的建立:我的度量值主要涉及一些求和、求平均、计数、变化率等。为了更好的管理度量值,我特意新建了一个名为DetialsMeaured的表,公式为:DetailsMesured = ALL(Details[EpisodeID]),然后把建立的各种度量值归到这个表当中 。
  • 百分比值的格式化:求变化率的度量值,可以把显示格式设置为百分比,那么在内置可视化控件中就直接显示为百分比,无需额外设置或者乘100(我使用了一个第三方控件,其无法识别百分比格式,只能在度量值上乘100) 。
  • 建立层级结构:为了支持数据的下钻显示,那么需要建立数据的层次结构,比如财年包含月份。要建立层次结构很简单,直接把一个字段拖动到另外一个字段下面Power BI就会自动创建一个新的层次结构列(包含了你刚刚操作的两个字段),接着可以继续拖入其他列到这个层次结构列下面,还可以拖动来进行排序。
  • 建立日期表:很多分析都是和时间相关的,那么就需要有一张独立的日期表来为维度提供数据(包括年、财年、季度、月、日、天等)。原来的qvf中也存在这一个日期表,也是依靠脚本生成的,对于Power BI而言同样也可以通过脚本来生成一个日期表。生成日期表的脚本如下:
DateKey = ADDCOLUMNS(
CALENDAR(FIRSTDATE(Details[EpisodeAdmissionDate]),LASTDATE(Details[EpisodeAdmissionDate])),
"DateAsInt",FORMAT([Date],"YYYYMMDD"),
"Year",YEAR([Date]),
"Quarter",VALUE(FORMAT([Date],"Q")),
"YearQuarter",FORMAT ( [Date],"YYYY" ) & "/Q" & FORMAT ( [Date],"Q" ),
"Month",MONTH([Date]),
"MonthName",FORMAT([Date],"mmm"),
"Day",DAY([Date]),
"WeekNum",WEEKNUM([Date]),
"WeekDay",WEEKDAY([Date]),
"WeekDayName",FORMAT([Date],"ddd"),
"Fiscal Year",IF(MONTH([Date])>3,YEAR([Date]) +1,YEAR([Date])),
"Fiscal Year Name",IF(MONTH([Date])>3,YEAR([Date]) & "-" & (YEAR([Date]) + 1),(YEAR([Date]) - 1) & "-" & YEAR([Date]))
)

把日期表添加到模型中后,就可以手动把日期表的Date字段和Details表中的EpisodeAdmissionDate字段建立其关系。最终的模型图如下:

image

日期表创建的更多详细介绍,可以参考这个博客文章:http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

5,可视化显示数据

通过可视化控件来显示数据没有太多可以说的。Power BI的官方文档已经写的比较好了,见:https://powerbi.microsoft.com/zh-cn/documentation/powerbi-desktop-report-view/

无非就是把一个可视化控件拖到报表页面上,然后把所需的维度和度量拖到可视化控件中的数据标签页中的“轴”/“图例”或者“值”下面,如下图所示:

image

数据建模的时候提到过下钻显示。下钻显示有两种方式:一种使用层级结构列,一种不使用。

简单说来,对于使用层级结构的方式,就是先建立一个层级结构列,然后把这个列拖到“轴”下面,可视化控件就会在上面的操作栏显示用于下钻数据的特定按钮。如下图所示:

image

对于第二种下钻显示方式,我没有采用,详细的说明可以见:https://powerbi.microsoft.com/zh-cn/documentation/powerbi-service-drill-down-in-a-visualization/

为可视化控件准备好数据之后,还可以通过“格式”设置标签页(即刷子状图标)来设置一些显示格式,比如标题,字体,颜色什么的。

当数据添加到可视化控件之后,这些字段还会自动添加到视觉级别筛选器,除此之外,你还可以额外把字段添加到页面级筛选器和报告级筛选器中。这些筛选器的作用范围望文生义应该可以理解。

不过需要注意的是,筛选器在公开发布到Web后并不可见,所以你还可以单独在报表页面上添加所需的切片器。切片器的数据设置方式和其他可视化控件类似。

6,和Qlik Sense的比较

粗略的比较下来(个人观点):

  1. ETL能力由于背靠SQL Server分析服务的相关技术,所以可以做到和Qlik相当,尤其几乎可以不用手写脚本。
  2. 建模能力和Qlik相当,只是操作体验上会差一些。
  3. 可视化能力比Qlik就差很多,不管是内置的可视化控件还是第三方扩展的控件都是如此。
  4. 前端操作(也即最终用户使用BI应用)的体验也比Qlik差。
  5. 扩展和集成能力也由于Power BI的定位决定了没有Qlik灵活。
  6. 最大的优势是入门的许可费用低廉(如果愿意使用SaaS的话)。
  7. 一大特色是具备人工智能辅助的快速见解能力。

Power BI在可视化能力方面确实需要进一步加强,比如我就遇到如下几个问题:

  • 排序只能基于当前使用的维度,不能自定义排序
  • 堆积面积图图例不能下钻
  • 没有竖条仪表图
  • 饼图不能合并为Other
  • 表格不支持下钻
  • (updated 2016.12.20)货币格式的数值不能显示负数

最后不得不吐槽下,Power BI的文档用机器翻译就算了,感觉Power BI Desktop的中文版也是机器翻译的。还不如我来翻译算了(作为MVP可以免费做贡献)。

posted @ 2016-12-14 16:41 朱永光 阅读(...) 评论(...) 编辑 收藏