Ch1

• 蜘蛛网模型
  • 从主体数据库中抽取部分数据作为一个小的数据库
  • 缺乏可信度：基于不同的数据进行抽取
  • 数据无时基：抽取时间不同
  • 数据算法差异：抽取方式不同，可能会进行再加工
  • 抽取的多层次
  • 外部数据问题
  • 无起始公共源
• 维度建模
  • 以商业用户可理解的方式发布数据
  • 提供高效的查询性能
  • 并不需要满足范式要求
• 多维数据库种类
  • RLOAP
    • 基于关系数据库的OLAP实现
    • 按列存，查询效率更高
  • MOLAP
    • 基于多维数据组织的OLAP实现
    • 将细节数据和聚合后的数据均保存在cube中，以空间换效率
  • HOLAP
    • 基于混合数据组织的OLAP实现
    • 将细节数据保留在关系型数据库的事实表中，但聚合后的数据保存在cube中，聚合需要更多时间，但查询效率比ROLAP高
• 事实表
  • 通常是连续值，不采用文本方式，主要的空间消耗
  • 参照的完整性
    • 事实表具有多个维度表的主键相关联的外键
  • 粒度
    • 事务（最基本的单位）
    • 周期性快照（有聚集的信息）
    • 累计快照
  • 组合键
    • 事实表通常有包含外键集合的主键，主键称为组合键。
• 维度表
  • 一个数值元素是事实属性，还是维度属性？
    • 如果包含多个值并作为计算参与度量，则是事实（产品的价格经常变化，是事实属性）
    • 若对具体值的描述，且表达常量和约束，则为维度属性
  • 多为离散属性
  • 可能会有数据冗余
    • 这样设计的查询更方便
    • 存储开销更小
    • 数据不会经常改变
• 架构
  • DW/BI架构
    • 操作性源系统 -> ETL（专为有意义，可展示的信息） -> 展现区/BI
  • 独立数据集市
    • 以部门为基础来部署，不考虑企业级别的信息共享与集成
    • 短期有利于较低成本实现快速开发，长期由于数据冗余造成浪费和低效
  • Kimball架构
    • ETL -> 企业数据仓库（EDW） -> 展现区（企业数据仓库总线） <- BI应用

Ch2 维度建模技术

• 维度设计过程

  1. 选择业务过程
  2. 声明粒度
     • 用于确定某一事实表中的行表示什么。原子粒度是最低级别的粒度。
  3. 确认维度
     • 如何描述来自业务过程度量事件的数据
     • 描述环境的维度：包含BI应用所需要的用于过滤及分类实时的描述性属性（可以用文字）
  4. 确认事实
     • 用于度量的事实（数字型表示）

• 星型模型

  • 多个维度表围绕事实表，是部署在关系数据库系统上的多维结构
  • 优点
    • 简单易懂
    • 性能优异：避免了小表的连接
    • 适于变化：在事实/维度表中都很容易插入新数据

• 事实表

  • 可加度量：可以按照与事实表关联的任意维度汇总（销售量）

  • 半可加度量：可以对某些维度汇总，但不是所有维度（库存量可以对除时间维度进行汇总）

  • 不可家度量：比率、利润率

• 空值

  • 可以存在空值度量，聚合函数可以对空值进行操作（不要填0）
  • 维度值外键不能存在空值，用代理键（unknown）

• 一致性事实

• 如果某些度量出现在不同的事实表中，定义需要相同

  • 例如身高都以cm为单位

• 事实表类型

  • 事务事实表
    • 一行对应空间或时间上某点的度量事件
  • 周期快照事实表
    • 每行汇总来发生在某一周期内的多个度量时间，粒度是周期性的
    • 例如：某个周期内的库存量
  • 累积快照事实表
    • 每行汇总了发生在过程开始和结束之间步骤内的度量事件，也就是有很多度量值，在不同时间发生的
    • 例如：申请学校的流程、入库流程
  • 无事实事实表
    • 不带度量的事实，可以与累积快照事实表结合， 统计每个时间段发生的事件数（count=1）
    • 例如：某一天发生的学生参加课程的事件
  • 聚合事实表或OLAP多维数据库
    • 对原子粒度事实表上钻操作，提高查询性能
  • 合并事实表
    • 粒度相同的事实可以合并

• 维度表

  • 结构：通常比较宽，扁平型非规范表
  • 代理键是唯一主键
    • 不使用自然键（有意义的字符串或日期），会造成多源系统的兼容性问题
    • 改进性能，代理键占空间小
  • 下钻
    • 按照多个字段进行group by，粒度变细
  • 退化维度
    • 除了主键，没有其他维度（订单号）
  • 非规范化扁平维度
    • 简洁、高效，对应于星型模型
  • 多层次维度
    • 不止一个自然层次。例如日期、地理，容易引起数据的不规范（改了上层而没改下层）
  • 空值属性
    • 用Unkonwn等描述性字符串代替，增加一行，可以join
  • 日期维度
    • 主键为整数（代理键），但是有意义的，例如20180923
  • 扮演角色维度
    • 单个维度可以被事实表多次使用
  • 杂项维度
    • 不同的维度混合在一起，最多是笛卡尔积个个数，可以避免蜈蚣模式
  • 雪花维度
    • 多级层级结构，更加规范化，但复杂低效
    • 减少数据量，join变多，效率变低
  • 蜈蚣模型
    • 增加了数据量（外键变多）
  • 支架维度
    • 包含对其他维度的引用
    • 例如：银行账户维度引用开户日期维度
  • 一致性维度
    • 不同的维度表的属性具有相同列名和内容，则维度表具有一致性

• 处理缓慢变化的维度

  • 类型0：保持原样
  • 类型1：重写
    • 原地修改，不能反映历史信息

  • 类型2：增加新行（多行描述统一成员，增加新行的同时，修改原来行的日期、指示器属性）

    1. 行有效的日期/时间戳
    2. 行戒指日期/时间冲
    3. 当前行标识

  • 类型3：增加新属性（不常用）

  • 类型4：增加微型维度
    • 当维度中的一组属性快速变化并划分为微型维度时采用
    • 例如人口统计微型维度：购物的用户信息不断改变，反映购物者当时的消费水平
  • 类型5：微型维度+类型1支架
    • 精确保存历史属性值，按照当前属性值增加报表的历史事实
    • 在客户维度中再增加一个当前人口统计维度
  • 类型6：类型1到类型2属性（加一列和一行）
    • 保存历史和当前维度属性值
    • 增加新行的同时，记录之前的历史部门名称，修改当前部门名称
  • 类型7：双类型1和2
    • 两个都不是微型维度
    • 事实表连接两个产品维度
      • 一个是产品维度，采用类型2增加新行的方式
      • 一个是当前产品维度，即产品维度中当前行标识=current的行

• 处理维度层次关系

  • 可变深度层次：直辖市/省
  • 具有路径字符属性的可变深度层次：每个路径加入有意义的字符串表示深度

Ch3 零售业务

• 日期维度
  • 主键一般为代理键，但具有日期含义yyyymmdd
  • 如何处理当前时间概念？
    • 可以添加某些每天更新的字段：IsCurrentDay
    • 添加滞后属性：0表示今天，-1表示昨天
  • 处理更细的粒度
    • 不将日期和时间放在一个维度
• 促销维度
  1. 为每一种促销手段建立一个维度
     • 促销数据稀疏（只有很少的条件组合能影响产品）
  2. 为所有促销手段建立统一的维度
     • 空外键、空事实
       • 某些东西有促销也有非促销
       • 使用unknown代替，促销维度中也加入unknown，便于连接
• 退化维度（DD）
  • POS Transaction号码，没有外键，没有维度表，但是也是一个维度，可用于购买商品的分组，如相同的 POS Transaction 可以表明这些物品时在同一个交易中购买的
• 对频繁购物者建模
  • 建立频繁购物者维度表，在事实表上增加外键
• 哪些商品参与促销但未被购买？
  • 构建促销包含事实表，无论是否卖出，每天加一行（count=1）
  • 用该促销包含事实表和零售销售事实表求差集即可（except）
• 事实表的主键
  • 使用组合键，包括表外键的子集和退化维度
  • 使用代理键
    • 多个源系统中不会造成歧义
    • 直接的唯一表示
    • 插入删除的替换更新：避免执行复杂的事务
• 雪花模型
  • 复杂，查询效率低
  • 无法实现位图索引
    • 可以很方便的对多个属性进行查询（使用AND/OR/NOT操作）
    • 每个属性有N位bit，每一位表示在当前record上是否存在该属性
    • 位图索引适合属性不多，record不多的情况
• 蜈蚣模式
  • 包含大量维度信息
  • 导致事实表需要更多的磁盘空间

Ch4 库存

• 半可加事实
  • 在库存快照中，当前数量可以进行汇总，但不能在日期维度可加
• 库存累计快照
  • 事实表定义过程开始、结束以及期间的可区分的里程碑
    • 如接收日期维度、验收日期维度、入库日期维度等
  • 适合处理业务用户开展对工作流或流水线的分析

Ch5 采购（看作业SQL）

• 处理缓慢变化维度
• 列举出不同年份推出的产品的数量

• 请按照年龄范围、收入级别列 举出每年发生的事实数量

• 请按照收入级别列举出所有客户的人数

• 请按照当前部门名称列举出所有产品的改变部门的次数

Ch6 订单管理

• 角色扮演维度
  • 例如日期维度，在同一个事实表中会使用多次，可以保证全局的一致性
  • 如果不使用角色维度，则会导致JOIN的时候造成混乱，因为日期维度都在一张表上，修改生产日期可能会修改成销售日期
• 杂项维度
  • 是多个属性的组合，而不是多个属性集的并集
  • 避免了蜈蚣模式，减少了维度数量，从而减少了存储开销
• 避免表头/明细模式
  • 订单头维度很大，会导致与事实表连接时效率很低，连接后空间很大
  • 此外，维度表不应该与事实表以同样的速率增长，如订单明细事务事实增加的同时订单头维度也会增加一项
  • 将订单头维度放入事实表中
  • 也需要避免拆分为两个事实表，会导致连接后数据量大，性能差的问题，这是两个大表的连接操作
• 多币种
  • 本地货币+通用货币（直接在当前事实表）
  • 使用货币兑换事实
• 飞机票的多维模型？

Ch7 会计

• 维度属性层次

  • 固定层次：好处理

  • 不整齐可变深度层次（直辖市/省）

    • 递归指针

    • 映射桥接表（里面有距离父指针的层次，最高层父节点标识，最低层子节点表示标识）

      • 每个父节点，都有对于其所有的子节点

      • -- 使用 7-11
        Select 子组织键
        From 事实表，组织映射桥接表
        Where 事实表.组织id == 组织映射桥接表.子组织键
        And 组织映射桥接表.父组织 = 9
        

    • 给各个节点用字符进行命名

• 组织结构变迁

  • 将 4\5\6 整体迁移到 9 下面

    -- 将4,5,6与父结点1,2的关联关系删除
    Delete from Org_Map where child_org in (4,5,6) and parent_org not in(4,5,6)
    -- 将4，5，6分别与1，7，9建立父子关系
    Insert into Org_Map (parent_org, child_org) select parent_org, 4 from Org_Map where parent_org in (1,7,9)
    -- 5，6类似4
    

• 案例分析

  • 采用固定层次模式和动态模式设计多维模型

• 教育学部下属的各个单位2018年新购置的各类资产分别有几项？

• 数据学院成立于2016年，分年度列出该学院购置的资产数量

• 计算机系原隶属于信息学院，现隶属于计软学院，需要对原始数据仓库做何调整？

Ch8 客户关系管理

• 客户维度

  • 规模大，属性多，变化快
  • 通常表示的是融合了多个内部和外部源系统的集成数据

• 使用支架维度，如首次购买日期维度

• 将聚集事实作为维度属性提供给用户，跟踪某类客户，如上一年度花费超过一定数额的客户

• 分段属性，将客户分类，如按照其购买行为

• 多值维度的桥接表

  • 借款申请事实 - 申请公布维度 - 申请公布桥接表 - 公布项维度
  • 客户维度- 联系组维度 - 联系组桥接表 - 联系维度

• 连续行为的步骤维度

  • 通过记录各个事实所属的步骤，来分析到底在哪个步骤会被终止

• 时间分为事实表

  • 在2013年处于欺诈警告的客户

    • begin_eff_datetime <= 12/31/2013 and end_eff_datetime >= 1/1/2013
      

  • 计算每个客户在2013年处于欺诈警告的天数

    • sum(least(12/31/2013,end_eff_datetime) - greatest(1/1/2013,begin_eff_datetimie))
      group by customer_name
      

• 找出还剩下1个步骤没有完成，且这种事情发生了3次以上的客户

• 找出申请项目最多的100个客户

• 研究对比分组001和002的销售情况变化

• 如果一个客户可以属于多个分组的话，如何进行查询。

Ch9 人力资源管理

• 递归式雇员层次

  • 方法一：在事实表中雇员维度扮演雇员和经理双重角色

  • 方法二：经理维度作为雇员维度的一个支架表

    • 经理维度 - 雇员维度 - 雇员离职事实
    • 雇员职位变迁时，自然键ID不会变，Key会改变
    • 经理本身既是雇员又是经理

    -- 求老板手下的员工总数
    Select Count(*)-1
    From 雇员维度
    Where current row indicator = true
    

  • 方法三：管理层次桥接表，适用于需要记录直接和间接管理的情况，且不知道管理具体有多少层次

    • 经理维度 - 管理层次桥接表 - 雇员离职事实

    Select Count(*)-1
    From 管理层次桥接表，雇员维度
    Where Manager Key = 001
    And 雇员维度.current row indicator = ‘true’
    And join sentence
    

    • 相比方法二，桥接表产生的数据量更大，每个人与自己的所有上级都有一条记录，如1boss+10经理+100员工=1+20+300=321，自己和自己之间也有一条记录，距离层级为0

• 多技能关键词

  • 雇员维度 - 雇员技能分组桥接表 - 技能维度

Ch10 金融服务

• 账户可以有一个或多个客户
  • 月账户快照事实 - 账户维度 - 账户/客户桥接表（weighting factor） - 客户维度
• 使用多微型维度
  • 客户维度/客户人口统计维度/客户风险概要维度 - 事实表
  • 能减少存储开销
  • 表示事实发生的状态，不会改变
• 在桥接表中增加微型维度
  • 事实表 - 账户维度 - 账户/客户维度桥接表 - 客户维度/人口统计维度
• 查询在1月份共有多少位年龄层次在21-30之间的用户参与了事实

• 查询在1月份由年龄层次在21-30之间的客户参加发起的账户数量
  • 只要该账户在一个客户属于这个年龄层就算

• 动态值范围事实
  • 不在维度表中预先定义范围，SQL无法泛化Group by子句，增加范围定义表，其中动态划定范围lower value & upper value，将band group key加到事实表中
  • 查询余额范围，及其对应的账户数量和总余额

Ch11 电信

• 可以修改模型的方面
  1. 账单维度表示为退化维度
  2. 粒度不应该是每个话单每个月一行，而是话单上每个服务列表项一行
  3. 添加日期维度外键，尽量不要使用具体日期
  4. 没必要使用雪花模式，进行合并
  5. 将比率规划类型代码当成文本放在事实表中不合适（不使用雪花模型）
  6. 在维度上没有描述性信息（不能是缩写）
  7. 维度表的主键最好是代理键
  8. 确定数据的一致性，从而保证数据集成的正确性和健壮性

Ch12 交通运输

• 以区段为基本粒度的建模方式
  • 对比不同乘客级别每年的平均获得的里程数

• ​ 分析2018年虹桥机场每个月的航班数量

• 构建基于起降操作的多维模型
  • 航班号和起降结合在一起，用于分析其出发和降落的机场，转机的机场不需要太过关注
  • 引旅行始发地和目的地的机场角色扮演维度

• 构建基于旅行的多维模型
  • 事实应该包含旅行总基准票价、总税费等聚集度量
  • 旅行区段数，在需要将区段作为上卷报表时才考虑

• 船运模式
  • 港口维度角色扮演，表明中转情况
  • 分析素有从最初港口装货并在最终港口卸货的商品数量

• 分析跨洋/ 非跨洋飞行的线路最近一年来的变化趋势

• 分析国内航线或者国际航线中，分别是哪两个城市之间的航线最为密集

• 多时区的日期和时间，包括本地时间和GMT时间，角色视图
  • 比较当前时间上午/下午起飞的航班的起降次数

• 列举出每天最晚飞起的航班和起飞时间

• 分析最近10 年来参加校园访问活动的同学中最终会入学的比率走势

• 分析最近 10 年来最终入学的同学中，有过初始问询，但是并未访问过校园的同学的比率走势

• 多值诊断
  • 桥接表使用两个外键组合作为主键，为诊断分组建立一个维度主键更符合认知，外键必然是指向另一个表的主键是
  • 列出拥有大于等于3个诊断记录的报销单据

• 当检查项数据稀疏时，采用测试度量类型维度来制定某个测试项
  • 列举出所有病人在Type1和Type2上做检查的总次数

• 行列翻转

Ch13 保单

• 如何利用类型 2 设计缓慢变化维度？
  • 每次保留之前的记录，新增当前记录，更新日期和状态
• 如果去除退化维度，会发生什么情况？
  • 用于关联多条记录
• 一个用户可能关联多个行业，多值维度怎么设计 ？
  • 使用桥接表
• 异构的超类和子类产品
  • 每个子类快照事实是超类事实表某一段的拷贝，仅包含属于特定业务线的保险项目或保险项的键
  • 使用超类是为了方便开展分析工作，而不需要同时访问两个大型事实表
• 保费周期快照
  • 汇总2018年2月和3月销售的保费和赚到的保费

• 列举出销售保费小于赚到保费的所有月份

• 多驾驶员的桥接表
  • 列举出不同投保驾驶员数量的保单数量

习题

• PPT 5
• PPT 8
• 期中考试试卷

SQL

• with 语句的有效期为该查询语句，create view的结果在查询后仍然存在

  with table_name[columns] as
  (select * from another_table where xxxx)
  
  create view view_name as
  (select column_name(s) from table_name when condition)
  

• 差集 Except

  -- 查询未促销的商品
      Select xxxxx
      From 零售销售事实
      Where xxxxxx
  Except
      Select xxxxx
      From 促销包含事实
      Where xxxxxx
  

• 组合多个属性列 Union

  • 每张表获取各自的内容然后进行 Union
  • 一般用于where语句中有一个字段不同的情况

• 对于缓慢变化维度中的微型维度

  • 若按照收入级别列举出所有客户的人数，这是无法写SQL语句的，因为一个客户的收入级别可能会改变

• 使用 Count(Distinct()) 来消除冗余

• 时间范围

  -- 在2013年曾处于欺诈警告
  where begin_eff_time <= 12/31/2013:23:59:59
  And end_eff_time >= 1/1/2013:0:0:0
  -- 2013年一整年都处于欺诈警告
  where begin_eff_time <= 1/1/2013:0:0:0 
  And end_eff_time >= 12/31/2013:23:59:59
  

• Group by

  • 条件语句使用 Having
  • 在 select 中选择多内容一定要在 group by 中出现，否则只能使用聚集函数

• least\greatest 求最小值最大值，范围查找时可能会用到

• 累积快照事实表中的count=1表明每个阶段的人数，因此使用sum而不是count进行查询

• Case 语句使用

  Case case_value
      When when_value Then statement_list
      [When when_value Then statement_list] ...
      [Ekse statement_list]
  End 
  
  -- 结合sum使用
  sum(case sex when 'F' then 1 else 0 end) female