数据仓库工具箱第三章——零售业务

3.1 维度建模设计的4步过程

3.1.1 选择业务过程

1. 业务过程是由组织完成的微观活动。如：获得订单、开具发票、接受付款、注册学生、处理索赔等。

2. 要将注意力放在业务过程，而不是功能化的部门。

3. 确保一致性的最好方法就是一次发布数据

业务过程包含以下特征：

• 业务过程通常用行为动词表示，表示业务执行的活动。与之相关的维度描述每个业务过程事件关联的描述性环境
• 业务过程通常由某个操作型系统支撑，如账单、购买系统
• 业务过程建立或获取关键性能度量。可能是直接结果，也可能通过过滤器和约束的不同组合，来审查和评估
• 业务过程通常由输入激活，产生输出度量。一系列过程产生一系列事实表

3.1.2 声明粒度

1. 精确定义某个事实表的每一行表示什么。也就是回答 “如何描述事实表中每个行的内容”。

2. 粒度传递的是与事实表度量有关的细节级别，是不可忽视的关键步骤。不应该由事实表主键的传统声明去定义粒度，而是以业务术语表示粒度

典型的粒度声明：

• 客户销售事务上的每个产品扫描到一行中
• 医生开具的票据的列表内容项采用一行表示
• 机场登机口处理的每个登记牌用一行表示
• 仓库中每个材料库存水平的每日快照用一行表示

3.1.3 确定维度

1. 回答 “业务人员如何描述来自业务过程度量事件的数据”，维度集合是用来装饰事实表的，用于区分。

2. 常常用来表示 “谁、什么、何处、何时、为何、如何”。如 日期、产品、客户、设备等。

3.1.4 确定事实

1. 回答“过程的度量是什么”，所有候选事实必须符合第2步的粒度定义，不同粒度的事实不能放在一张表里。

2. 要注意的是：不能只考虑数据来源来建模数据。典型事实是可加性数值，如订货数量或者成本总和。

3.2 销售业务案例研究

假设你在大型食品连锁店总部工作，连锁店由100个分布于5个不同城市的分店组成。每个店都有完整部门：杂货、冷冻食品、日常生活用品等。每个上架产品都有统一编号（SKU），最常用的数据来自顾客购买商品使用的收银机，销售点系统（POS）在收银台扫描产品条形码，计算顾客带走的商品。管理方面主要关注对订单、库存、销售产品的组织工作，目的实现利益最大化。管理决策与价格和促销有关，需要对促销和经营分析。

3.2.1 选择业务过程

将注意力放在最为关键的、最易实现的用户业务过程，包括数据的可用性与质量以及组织的准备工作。

• 因此建模的业务过程是POS零售交易。

• 需要决定对哪种业务开展建模工作。管理层希望更好的理解通过POS系统获得客户购买情况。

3.2.2 声明粒度

设计开发的维度模型应该表示由业务过程获取的最详细的原子信息。查询需要以非常精确的方式对细节进行切分。

这里最细的粒度是POS交易的单个产品，POS系统按照一个购物车中某中产品为单一项而上卷所有销售

为什么要最低的原子粒度处理数据？

• 原子粒度数据有强大的多维性。度量越细，越能获得更多的事实。
• 能够提供灵活分析的能力，适应各种查询请求
• 定义汇总粒度限制了建立更细维度的可能，无法实现下钻细节的需求。

3.2.3 确定维度

详细的粒度说明确定了事实表的主要维度。只要维度自然地承担主维度合并的某个值，且粒度和事实表相符，就可以加到事实表上。

考虑维度是否可以被属性化的POS度量。如日期、产品、门店、促销、收银员、支付方式、POS交易票据数据。

3.2.4 确定事实

事实要与粒度吻合，放入POS交易的单独产品线项。

如销售数量、单价、折扣、扩展的美元销售额（销售数量 x 净单位价格）、扩展的销售折扣额（销售数量 x 单位折扣额）、成本（随时用且不需要记述详细的基于活动的陈本来源）。4类事实——销售数量 、销售可扩展额、销售、成本额——都是完全可加的，不受限制地开展切片切块。

• Regular Unit Price 常规单价
• Discount Unit Price 折扣单价
• Net Unit Price 净单价，类似于成本，减去利息
• Gross Price 毛价格，包括成本、运费、人工的价格总和，未打折扣前的价格
• Extend Sales Dollar Amount 扩展的美元销售额（销售数量 x 净单位价格）
• Extend Discount Dollar Amount 扩展的销售折扣额（销售数量 x 单位折扣额）
• Gross Profit 销售收入扣除主营业务的直接成本后的利润部分。其中的直接成本不包括企业的管理费用、财务费用、销售费用、税收等。这件衣服的进价是20块钱，你卖了50块钱，你的毛利润就是30块钱。

1. 计算获得的事实

• 总利润额 = 扩展销售总额 - 扩展成本总额，完全可加的
• 推荐将计算获得的事实物理存储在数据库中 ，其计算与ETL过程保持一致性，消除了用户计算错误产生的可能性
• 视图是减少用户错误且节省存储的一种合理方式，但是要保证通过视图访问数据的方式不会有意外，且所有用户在访问数据时都通过视图，且没有用户可以绕过视图直接访问物理表。对于一些不可加度量如百分比，不能被预先计算出来（比较难实现）。
• OLAP多维数据更加适合这样的环境。

2. 不可加事实

• 利润率 = 利润总额 / 扩展销售总额，非可加的事实
• 百分比和比率（利润率）是不可加的。应当将其分子分母分别存储在事实表中，比率可采用BI工具计算事实表的任意分片，计算的是汇总的比率，不是比率的汇总
• 单价也是不可加的，1元卖了1个东西（每个1元），2元买了4个东西（每个0.5元）。总销售额3元，但是不能汇总单价，说单价1.5元。为分析平均价格，在计算总额与总销售数量的比值前，必须增加销售额及销售数量
• 一般来说非可加的事实通常是从其他源系统获得的，除了直接在事实表中应用过滤器，他们都是非典型的

3. 事务事实表

• 原子事务事实表的粒度可在事实环境下被简洁地描述。如每个事务一行或每个事务线一行
• 事实表记录的是一个事务事件，通常比较稀疏
• 事务事实表无法预测，且数量庞大（可能包含十亿行的表）
• 事务事实表趋向成为多维化
• 事务事件返回的度量通常是可加的，只要它们通过数量来扩展，而不是获取单位度量。

估算最大表的情况，也就是估计事实表的行数，每个基本周期内产生多少POS事务行项。假设销售总额40亿，客户票据中平均每项价格为10元，每年大概有4亿事务项。

3.3 维度表设计细节

3.3.1 日期维度

维度模型总是需要详尽的日期表。SQL日期函数不支持范围广泛的日期属性，包括周、财务周期、季节、假日、周末等。预期将这些非标准日历计算放入查询，不如放在日期维度表中，通过查询直接获得

• 每个业务过程都需要获取时间序列的性能度量，日期通常是数据库分区模式优先考虑的（连续的时间间隔数据）。

• 可以提前建立日期维度，因为该维度表的行数少，可预测。

• 日期维度：粒度按天处理的维度表 ； 当天时间维度：time-of-day

• 文本属性的标识，比如日期维度中假日标识只有两个可能的值（类似于下拉式查询过滤列表中的值），标识应该是有意义的假日、非假日，而不是Y/N表示。希望采用有意义的、能自我解析的领域值

• 当前与相对日期的属性，大多数日期维度属性不应该更新，但是IsCurrentDay、IsCurrentMonth等需要更新，IsCurrentDay是指向当天的报表有用。大多数仓库是按天加载数据的，应该涉及到昨天。一些维度属性包括对滞后属性的更新，滞后日期列中值0代表今天，-1表示昨天。滞后属性易成为可计算列而不是物理存储，没有必要存在。

• 将当天时间 time-of-day作为维度或事实，日期和时间最好还是分开来（即使操作型日期可以把日期和时间分开，但是如果改变粒度为每行表表示每天的秒，数据量过大）。

• 如果项汇总日期部分分组来过滤或者上卷，如15分钟间隔、1小时、黄金时间等将被视为完整的维度表。如果不需要，则可以把当天时间按照简单日期/时间事务处理。

• 商业用户对事务的持续时间比较感兴趣，可以把离散的开始和结束时间计算一下

疑问：如果事实表的日期键是日期类型的列，那么SQL查询不就可以直接约束，利用SQL提供的日期语义按照年、月过滤，从而避免据称非常昂贵的连接操作

1. 如果关系数据库不能有效处理与日期维度表的连续，那就有大问题了。多数数据库优化器能高效地处理多维查询，不用把连接想的那么困难
2. 商业用户不熟悉SQL日期语义，且不支持对工作日、周末、假期、财务周期的过滤
3. 如果按照非标准的日期属性对日期分片，那么日历逻辑应该由维度表解决，而不是代码

3.3.2 产品维度

1. 将重复的低粒度值保存在主维度表中是一种基本的维度建模技术，规范化这些值将其放入不同的表难以实现简单化与高性能的目标。

2. 用于事实计算和维度约束、分组及标记的数据应该保存在两个不同的位置。维度模型应该尽可能保持一致，应用开发应该简单且可预见。

• 产品维度描述仓库中存储的每一个SKU（产品统一编码），几乎都来自于操作型产品主文件

• 扁平化多对一关系，商品层次是属性的主要分组之一（SKU上卷到品牌，品牌上卷到类别，类别上卷到部门），存在多对一的关系，每个SKU的级别都被定义好。

• 书上的例子SKU描述列中大约有300 000个不同值，但是部门属性大约只有50种。所以部门属性大约有6000个重复值，这个是完全可接收的，不需要再分解到另一个规范化的包。

• 具有内嵌含义的属性，维度表中按照自然键概念确定的操作型产品代码通常有内嵌的含义，不同部分表示不同的特征，由多个部分组成的属性可以完整存在维度表中也可以分解成不同组件，如操作型代码第5到9位表示制造商，制造商的名字也应该包含在维度表中

• 作为属性或事实的数字值，有时可能遇到难以判断数字值归入维度属性还是事实。如果预先定义稳定的数字值，用于过滤和分组，应该被当成产品维度属性。如通常价格标准变化缓慢。当同时用于计算计算和分组的时候，应该同时存储，

• 下钻维度属性，合理的产品维度表大概有50个描述属性，维度模型上下钻只不过从维度表中增加了行头指针属性，上卷移除表头，根据属性上卷或下钻（部分属性不是层次的组成部分），也就是可以自定义

3.3.3 商店维度

• 一般商店维度描述零售连锁店的每个门店，没有一个全面完整的商店主文件。

• 多层次维度表，商店维度在本节中主要是地理维度，包含地址（街区、地区、国家）可上卷。一个维度表中表示多个层次不常见，跨多个层次的属性名称和值应该具有唯一性

• 对于Floor Plan类型应该采用10-20个字符描述，具有可理解的含义；Selling Square Footage这种列是数字，由于是商店的一种约束属性，作为商店维度表中比较好。

• 维度表中的日期，首次开店日期和最后改建日期是日期类型的列。如果要按照非标准的日历属性（开店日的财务周期）分组和约束，要连接键复制到日期为表中，需要重新标记和主日期维度的列区分

3.3.4 促销维度

促销维度通常被认为是一种因果维度，描述了认为可能导致产品销售发生改变的因素，包括临时降价、终端通道展示、广告等。

• 各种可能存在的因果条件是高度关联的。在促销维度中为每个发生的促销条件的组合建立一行是有意义的

• 应该仔细权衡包含在促销维度中的促销成本属性，该属性可用于分组和约束，但是该成本没有出现在表示独立产品销售的POS事务事实表中，其粒度不符合，应该在粒度为整个促销的事实表中

• 空外键、空属性和空事实。很多销售事务包含未促销的产品，促销维度要有一行唯一表示是否包含促销田间，避免事实表中出现空的促销键。

• 不要在事实表中使用空值键， 违背了参照完整性的要求，且会给用户带来困惑，无法实现与空值的连接操作

• 当维度属性时空值，建议用Unknown或Not Applicable等特殊语法加以区分

• 事实表中遇见空值，可以用聚合函数处理，用零值替换可能会使聚集计算倾斜

如何判断某个促销是否有效？

1. 促销产品的销售是否在促销期间获得大幅增加，也叫提升。和基本销售情况（历史销售情况估算）比较
2. 促销产品在促销前后的销售，与促销期间的销售比较是否有降低，也就是说把常规时候的销售转移到促销期间了
3. 销售侵蚀，促销产品在销售方面很好，但是与他相邻的产品销量显著降低。
4. 促销分类中的所有产品是否都获得了销售方面的净总增益，要考虑促销前、中、后时间段（市场增大）。
5. 促销是否有利可图，考虑销售侵蚀和促销的开销。

是否要将4个不同的因果机制（降价、广告、展示、礼券）区分开，建立不同的维度而不是放在一个维度中？ 内容上没有差别

放在一起的理由：

1. 如果4个因果机制高度管理，合并成一个不会比任一个大很多
2. 合成单一维度可方便浏览，观察相互影响关系，这样的浏览仅展示了可能的促销组合。无法对应起来

划分开的理由：

1. 对业务群体分别考虑不同的机制时，更易于理解
2. 对不同维度的管理更加直接。

3.3.5 事务号码的退化维度

• 零售事实表的每个列表项行都包含POS事务号码，是事务头指针记录的键。如事务日期和商店标识
• 将POS事务号码称为退化维度（以DD标记区分）。退化维度是没有对应维度表的维度键。
• 订单号码、发票号码通常产生空的维度并且表示为事务事实表中的退化维度。

3.4 实际的销售模式

​ 如果单个POS交易可以接受多种支付方式，则支付方式可能不是以声明粒度的单一方式出现。与其将声明的粒度替换为非自然的，例如，每个产品每种支付方式一行，不如将支付方式获取到不同的事实表中，其粒度要么是每个事务一行（各种支付方式选项可出现在不同事实表中），要么是每个事务的每个支付方式一行（需要与每行关联的不同的支付方式维度）

​ 下面是2021年1月期间波士顿地区通过促销快餐分类的周销售总量的分析

3.5 零售模式的扩展能力

商业用户分析购物模式的兴趣，主要是对地理、人口统计学等其他不同特征感兴趣。

那么我想要处理新的频繁购物者信息这么办呢？

1. 建立频繁购物者维度表，并在事实表上加一个外键。
2. 由于无法要求购物者拿着之前的发票，以新的频繁购物者号记录历史销售事实，只能默认购物维度代理键替代，对应先前的频繁购物者购物程序维度行到历史事实表行
3. 在购物的人不一定都是频繁购物者，希望在购物维度中包含一个不确定行，表示是否是频繁者。

维度模型可预见的对称性确保能承受一些变化，包括

• 新维度属性。维度的新文本描述符可作为新列加入；如果新属性特定时间可用，则老的维度行中要插入不可用的类似描述。这里需要注意缓慢变化的维度
• 新维度，事实表上加新维度外键
• 新可度量事实，需要注意粒度，相同粒度的才能被放进来。

3.6 无事实的事实表

​ 前面的模式无法解决：处于促销状态但是尚未销售的产品包括哪些，销售事实表所记录的是卖出的SKU，事实表中不包括由于没有销售行为而SKU为0的行。那么如何解决呢？需要促销范围或事件事实表

• 促销范围事实表的键可以是研究案例中的日期、产品、商品、促销等
• 粒度和销售事实表有区别，为每天每个商店中促销的产品加载一行，无论是否卖出
• 能够获得所包括的键之间的关系，没有度量结果，被称为无事实的事实表

具体步骤

1. 查询促销无事实的事实表，确定给定时间内促销的产品。
2. 然后确定通过POS销售事实表哪些产品卖出去了
3. 取差集，在OLAP多维数据库中比较容易

3.7 维度与事实表键

3.7.1 维度表代理键

数据仓库中维度表与事实表的每个连接应该基于无实际含义的整数代理键（没有业务含义，仅仅用来连接），应该避免使用自然键（有多个部分组成的有业务含义的键）作为维度表的主键，容易逻辑失效

• 代理键的优点：

  • 为数据仓库缓冲操作系统的变化，不受生产代码的影响，许多组织中历史的操作代码会被重新分配，如不用的手机号。代理键能够区分同一个操作型账号的两个不同的实例，仅依赖操作型代码，可能会遇到键重叠的问题
  • 集成多个源系统，确保从多个操作型源系统中集成数据，建立交叉引用映射表可把多个自然键连接称为一个公共的代理键。
  • 改善性能，代理键是尽可能小的一个整数，能够存更多的数据
  • 处理空值或未知条件，特定的代理键值用于记录不涉及操作型代码的维度条件。如匿名客户
  • 支持维度属性变化跟踪，一种主要处理维度属性变化的技术需要代理键处理单一自然键的多个轮廓。需要避免伪代理键（自然键放到一起+时间戳），有时称为双筒连接，会降低性能和易用性。

• 由操作型源系统分配和使用的自然键，如业务键、产品键、操作键用NK标识

• 持久的超自然键，是能够确定一个标识符用于唯一地和可靠地区分维度实体的属性变化。

• 如果事务控制好在跨多个本地系统或重用时不是唯一的，需要分配代理键，退化维度的代理键

3.7.2 日期维度的智能键

• 日历日期是固定的预先可确定的，可以使用智能键。
• 日期维度的主键可以是一个有意义的整数，格式是YYYYMMDD（这个键不提供给业务用户和BI应用），防止绕过日期维度直接查事实表，对日历属性的过滤和分组发生在维度表。
• YYYMMDD可以用于分区事实表，使分区管理方便。
• 如果优化器包含日期类型的知识（20210301-20210401对应31个值，而不是0401-0301=100），考虑使用日期键。
• 要保留特殊的日期键对应日期未知的情况

3.7.3 事实表的代理键

• 未要求事实表中一定使用代理键，虽然不能获得查询性能方面的改进，但是确实带来利益：
• 直接的唯一标识，单一事实表行可以直接获得，在ETL处理中方便
• 返回或恢复海量加载，这些行带有顺序分配的代理键能知道在哪停止的
• 插入加删除的替换更新，可以替换事实表。首先新行放入数据库，保留替换行的所有业务外键。然后删除原始行，具有返回和恢复能力。不需要全套的事务机制保护。
• 使用事实表代理键作为父/子模式中的父节点，不应该直接把事实表和其他事实表连接

3.8 抵制规范化的冲动

3.8.1 具有规范化的雪花模式

规范化的维度表被称为雪花模式。冗余属性从扁平非规范化维度表中移除，放到同样规范化的维表中。

固定深度层次在维度表中应该被扁平化，牺牲一些空间能改善性能

• 雪花模式是维度建模的合法分支，但是并不推荐
  • 众多的雪花模式表构成了一个复杂的结果，简单化是维度建模的主要目标
  • 多数数据库优化器也要考虑其复杂性，大量表和连接操作导致缓慢的查询操作
  • 雪花模式节省的磁盘空间也不是很多
  • 雪花模式对用户浏览维度的能力有负面影响
  • 做下钻的时候要遍历很多的表，SQL语句复杂
  • 雪花模式不能实现位图索引（能在索引低粒度的列如分类和部门属性时，提高查询或针对问题中的单一列约束的性能）

3.8.2 支架表

1. 为某个事实表范围之内的维度建立附加的支架维度。

2. 好处：能够节省空间并保证相同的属性被一只的引用

3. 缺点：引入更多的连接，降低性能，限制了可解释性

• 支架表日期属性具有描述性的独特的标记用于区分与业务过程有关的其他日期。
• 只有业务希望按照非标准的日历属性（财务周期、假日周期）过滤或分组时，针对主维度属性的日期属性构建支架才有意义。否则要考虑将日期属性作为产品维度中的标准日期类型列判断
• 注意当标准日期维度表按照范围存储时，支架日期将发生错误。

3.8.3 包含大量维度的蜈蚣事实表

• 维度模式中的事实表自然地具有高度规范化和紧凑的特征。
• 为什么会产生蜈蚣事实表呢？在产品层次上频繁分析的元素也当成外键。比如日期维度划分为周维度、月维度、季度维度等。大量的维度通常表示某些维度不是完全独立的，应该合并成一个维度。
• 尽管非规范化维度需要额外的空间，但是事实表仍然是最大的。蜈蚣事实表无法实现对多部分构成的键构建有效的索引。一般来说业务过程可以用不超过20个维度的事实表表示
• 列数据库开发可以减少与蜈蚣事实表有关的查询和存储负载，只有命名列被检索