基于EXCEL的数据建模分析及商业智能

Jason, CMA
2015-4-26

Excel可在此下载：http://1drv.ms/1FV4qOO 本PPT由R presentation自动生成

通过本PPT和附件EXCEL，您将可以:

  • 描述POWERPIVOT（超级数据透视表）及其应用

  • 描述多维度数据分析模型的建立步骤

  • 通过实例，设计将EXCEL表格数据转换为模型、分析报告

功能概览

  • 一个数据源，满足不同层次明细、格式要求，广泛用于市场、销售、财务分析.

  • 将表格数据变为模型数据.

  • 基本DAX公式，应用PowerView进行动态仪表盘报告

什么是POWERPIVOT，用在哪

  • POWERPIVOT是微软免费提供的EXCEL加强附件，用以自我服务的商业智能
  • 一个数据源，不同层次、展示风格动态实现
  • 基于表格型的商业智能（相对于SQL的关系型数据库）
  • 针对非IT人员的商务分析人员
  • 不用编程便可实现
  • 数据行数不受EXCEL表格的限制
  • 包含DAX
  • DAX = Data Analysis Expression数据分析语言
  • 极其类似EXCEL的函数

用在：财务报表趋势、层次分析

alt text

如您有兴趣测试一下，请使用附件:http://1drv.ms/1FV4qOO

用在：销售、费用的分部门、人员、产品、渠道多维度分析

alt text

如您有兴趣测试一下，请使用附件:http://1drv.ms/1FV4qOO

动态报告

alt text

alt text

如您有兴趣测试一下，请使用附件:http://1drv.ms/1FV4qOO

总结

初步接触POWERPIVOT和POWERVIEW

DAX

  • DAX的几个主要概念
  • 从SUM开始理解计算环境
  • 重点掌握CALCULATE及其用于计算环境的转换
  • 介绍此次建模将用到的具体公式

DAX的几个重要概念

  • 数据类型
  • 与EXCEL不同的操作符
  • 计算环境（非常重要）
    • 当前行环境
    • 筛选环境
  • 计算环境与关系的拓展
    • 一对多
    • 多对一
  • 两类计算方式
    • 计算列
    • 计算字段

数据类型

  • 整形
  • 实形
  • 货币
  • 日期
  • 布尔（真/假）
  • 字符串
  • BLOB（Binary Large object 二进制数据块）

与EXCEL不同的操作符

  • 逻辑运算符

    • && — AND 对于EXCEL AND
    • || — OR 对于EXCEL OR
    • ! — NOT 对于EXCEL <>

  • 对字段的引用 ‘Table Name’[Column Name] 如无空格，可不用单引号

计算环境

  • 行环境（当前行环境）和筛选环境
    • 所有公式均受两种环境的影响
    • 筛选环境由“数据透视表”或slice决定
    • 行环境提供当前行，类似EXCEL应用在整列上的公式
  • 计算列和计算字段
    • 计算列 —类似EXCEL应用在整列上的公式
    • 计算字段 — 在外观上类似列加总 alt text

计算列和计算字段的选择

  • 有时任一个均可实现同一目标。如计算列毛利=销售-成本 或计算字段 毛利 = sum(销售)-sum(成本) — 在透视表层面，读者看到的是一样的结果

  • 下面情形最好用计算列

    • 要将计算结果用于筛选或切片器，如：组合了年月日的列
    • 公式必须在当前行下才有意义， 如：数量*单价 你需要在数据透视表下查看的数据
    • 如看毛利率 — 它不是各个毛利率的相加，用计算列就无法在数据透视表上正确显示

  • 在计算列引用计算字段时，由计算列所产生的当前行会被转换为筛选环境

建议：对每个数据表均设一个唯一的行标识符

您是否真正理解了环境？

  • 如果在表FACTRESELLERSALS 用 SUM 建计算列和计算字段，在计算列的每行显示的和与计算字段是否相等？为什么？试一下。

  • 是否可以表FACTRESELLERSALS建这么一个计算字段：[SalesAmount]-[TotalProductCost] 为什么？ 试一下。

计算环境的转换

  • 递归产生当前行 — SUMX 计算列：SUMX(FACTRESELLERSales, factresellersales[SalesAmount]) 结果与SUM一样，因为：
    1. SUMX在第一参数上产生行环境
    2. 在1的环境下，计算第二参数
    3. 将2的结果加总

？如果用计算字段会是什么结果呢？一样的结果？

CALCULATE的环境的转换

  • CALCULATE将会把行环境转换为筛选环境
    • 将计算列SUM(Salesamount)改为Calculate(SUM(salesamount))是不是跟未加总的数一样了，数据透视表显示的金额正确了？为什么？

理解关系

  • 关系可以用类似于EXCEL的VLOOKUP来近似理解
  • 需要了解的是在不同环境下，
    • 当前行环境的关系无法自动拓展, 且不能在“多”便建立联系。为什么？用EXCEL 的VLOOKUP类比一下。
    • 筛选环境下的关系自动拓展。对“一”的筛选自动应用到“多”边

建模练习用到的公式介绍

  • 用于父女关系的公司
    • PATH（女，父）—返回完整父女路径
    • PATHITEM（路径名，第几节点）—返回路径节点
  • 用于查值（非常类似vlookup)
    • LOOKUPVALUE（要返回的值列，在哪列查找，被找的值）
  • 用于计算单一值
    • DISTINCTCOUNT（要计算的列）
  • 将文本或数值变为日期
    • DATE(年，月，日)

建模步骤

  • 将EXCEL表格添加到模型中　
  • 建立表格之间的联系
  • 建立产品的层次关系
  • 标记日期表格
  • 建立日历的层次关系
  • 建员工的层次关系

POWERVIEW的使用

  • 如何进行KPI报告
  • 如何使用POWERVIEW

总结

  • 将普通工作表数据转为模型数据
  • 建立KPI指标
  • 将员工的隶属关系，以层级方式展示
  • 了解DAX函数
  • 可以从无到有地建立模型，编制多维度、动态分析报告

其 他

软件来源及其帮助网站

EXCEL版本及其它说明

  • 本次使用的EXCEL为2013版，2010版也是可以，但界面会有不同
  • 所使用的数据均被转化为EXCEL表格数据，但模型数据可以有许多不同来源，且无须在EXCEL表格上显示
  • 所使用的数据为网上下载的测试数据ADVENTUREWORK，可能不存在任何经济、商业上的逻辑联系
  • 作者不对本文件及附件的各方面的质量做任何保证
  • 如果涉及版权问题，请与 xizhijie_xm@outlook.com 联系，我将立即给予纠正
  • 本PPT由Rstudio的R Presentation 自动生成， 有任何建议，请与xizhijie_xm@outlook.com 联系