背景
行业：零售行业
详细信息：由100个分布在5个州的分店组成，每个商店都有完整的部门。包括杂货、冷冻食品、日常生活用品、肉类、农产品、烘烤食品、花卉、保健/美容产品等。每个商品包含被称为产品统一编号(SKU)的60000种不同 的上架产品
管理重点：对订单、库存、销售产品的组织工作
目的：实现利润最大化，利润最终来源于赚取每种商品尽可能多的差价，降低获得产品的开销，提供具有较强竞争力的环境以吸引更多的顾客消费。
第一步：选择业务过程
通过对业务需求以及可用数据源的综合考虑，决定对那种业务展开建模工作。
注意力应放在最为关键的、最易实现的用户业务过程。应注重考虑数据可用性与质量，以及组织的准备工作等。
在本章案例中，管理层希望更好的理解通过POS系统获得的客户购买情况。
因此我们将要建模的业务过程是POS零售交易。
该数据保证商业用户能够分析被销售的产品，他们是在哪几天、在哪个商店、处在何种促销环境中被销售的。
第二步：声明粒度
业务确认之后，就要确定粒度。在维度模型中应该包含哪个级别的细节数据。
粒度必须是最低的原子粒度处理数据。原子粒度具有强大的多维性。
事实度量越详细，就能获得更确定的事实。
原子数据与多维方法能够实现最佳匹配。
原子数据能够提供最佳的分析灵活性，因为他可以被约束并以某种方式上卷。同时可以适应随意的查询要求。
原子信息必须来源于业务过程。
当然，也可以使用汇总粒度表示原子数据的聚集，汇总粒度可以用于DW/BI系统的初步汇总层，但是底层还是要使用原子粒度。
本案例中，最细粒度的数据是每一单交易中的单个产品，如果可以使用这个粒度建模，以后做任意分析肯定比使用汇总粒度–每一单，要来的顺滑。
第三步：确定维度
详细的粒度说明确定了事实表的主要维度。然后只要主维度中的某几个值可以合并成一个额外的额度，就可以将它增加到事实表中。
如果附加的维度与粒度不符，可以考虑取消该维度或重新声明粒度。
在本案例中可以提取以下描述：
日期，产品，门店，促销，收银员，支付方式，POS交易票据数量。
第四步：确定事实
最后是确定应该将哪些事实放到事实表中。
事实必须与粒度吻合：放入POS交易的单独产品线项。
在考虑事实时也要尽可能调整早期的粒度声明和维度选择，这是最后的机会。
| 事实表度量 | 维度表 | 描述 |
|---|---|---|
| Date Key | 日期维度 | |
| Product Key | 产品维度 | |
| Store Key | 商店维度 | |
| Promotion Key | 促销维度 | |
| Cashier Key | 收银员维度 | |
| Payment Method Key | 支付方式维度 | |
| POS Transaction | POS 交易 | |
| Sales Quantity | 销售数量 | |
| Regular Unit Price | 常规价格 | |
| Discount Unit Price | 折扣价格 | |
| Net Unit Price | 净单价 | |
| Extended Discount Dollar Amount | 扩展折扣金额 | |
| Extended Sales Dollar Amount | 扩展销售金额 | |
| Extended Cost Dollar Amount | 扩展成本金额 | |
| Extended Gross Profit Dollar Amount | 扩展毛利润金额 |
可加事实
最突出的可加事实就是利润，也就是收入。可通过扩展销售金额减去扩展成本金额得到纯利润。而且对于所有维度而言，收入都是可加的。
不可加事实
不可加事实就是一些百分比或比率数值，例如利润率。
还有一个不可加事实就是净单价，因为净单价是一个不可以再分解的最小数字，而且相加没有意义。
事务事实
事务性事实表才是最常见的事实类型，一般选择最大的业务组建最大的表。
维度表设计细节
日期维度
日期维度是最特殊的维度，因为他出现在所有的维度模型中。实际上所有业务都需要获得时间序列的性能度量。
即便一天表示一行，20年也不过7300行，所以日期维度是一个很小的维度表。
| 字段 | 描述 | 例子 |
|---|---|---|
| Data Key(PK) | 主键 | 20200526 |
| Data | 时间 | 2020/05/26 |
| Full Date Description | 完整日期描述 | May 26,2020 |
| Day of Week | 今天是周几 | Sunday |
| Day Number in Calendar Month | 是今年的第几个月 | May |
| Day Number in Calendar Year | 几年是多少年 | 2020 |
| Day Number in Fiscal Month | 财务做月度报告的度量 | F05 |
| Day Number in Fiscal Year | 财务做年度报告的度量 | F2020 |
| Last Day in Month Indicator | 月末标识 | |
| Calendar Week Ending Date | 周天标识 | weekday |
| Calendar Week Number in Year | 是今年的第几周 | |
| Calendar Month Name | 月的名称 | |
| Calendar Month Number in Year | 是今年的第几个月 | |
| Calendar Year-Month(YYYY-MM) | 年-月 | YYYY-MM |
| Calendar Quarter | 季度 | Q2 |
| Calendar Year-Quarter | 年-季度 | YYYY-Q2 |
| Calendar Year | 年 | YYYY |
| Fiscal Week | 财务做周报告的度量 | FWeek 1 |
| Fiscal Week Number in Year | 本周是今年的第几周 | 05 |
| Fiscal Month | 财务做月度报告的度量 | |
| Fiscal Month Number in Year | 本月是今年的第几个月 | MM |
| Fiscal Year-Month | 财务年-月 | YYYY-MM |
| Fiscal Quarter | 财务做季度报告的度量Q2 | |
| Fiscal Year-Quarter | 财务年-季度 | YYYY-Q2 |
| Fiscal Half Year | 财务做半年报告的度量 | |
| Fiscal Year | 财务做年度报告的度量 | |
| Holiday Indicator | 假日指示器 | not_holiday/节假日名称 |
| Weekday Indicator | 工作日指示器 | |
| SQL Date Stamp | 时间戳 | |
| IsCurrentDay | 今天 | |
| IsCurrentMonth | 本月 | |
| IsPrior60Days | 最近60天 | |
| IsFiscalMonthEnd | 财务本月 |
整个日期维度基本被分为了日历周期与财务周期。
将日期维度设计的如此细致是为了降低SQL查询的复杂程度，为了应对繁琐同时无法预期的查询操作，预期依赖代码不如直接提供一个详尽的日期维度表。
关于文本属性的标识应该给一个准确的描述，不要使用Y/N,1/0这样的描述，尽可能使用是节假日，不是节假日。
如果日期维度要求极为细致，可以使用分钟做粒度，然后使用缓慢变化维度的type4来解决粒度过于细致的问题。
产品维度
| 字段 | 描述 |
|---|---|
| Product Key(PK) | 产品密钥 |
| SKU Number(NK) | SKU编号 |
| Product Description | 产品描述 |
| Brand Description | 品牌描述 |
| Subcategory Description | 子类描述 |
| Category Description | 类别说明 |
| Department Number | 部门编号 |
| Department Description | 部门描述 |
| Package Type Description | 包装类型描述 |
| Package Size | 包装尺寸 |
| Fat Content | 脂肪含量 |
| Diet Type | 饮食类型 |
| Weight | 重量 |
| Weight Unit of Measure | 重量计量单位 |
| Storage Type | 存储类型 |
| Shelf Life Type | 保质期类型 |
| Shelf Width | 货架宽度 |
| Shelf Height | 货架高度 |
| Shelf Depth | 搁板深度 |
对于SKU描述，最好具有唯一性，但是当表记录多时，很容易出现极端的大量重复值。
但是这种情况是完全可以接受的，将重复的低粒度值保存在主维度表中是一种基本的维度建模技术。规范化这些值将其中放在不同的表将难以实现简单化与高性能的主要目标。
产品维度的大多数属性并不是商品层次的组成部分。但是使用一个离散型属性来约束整个商品层次属性，往往比较有意义。
同时也可以使用具有内嵌含义的属性，例如操作型代码中第一到第四个字符表示一个信息，第五到第九个字符表示另一个信息。
至于数字该分配到维度表还是事实表，就要看这个数字是否有用于计算的需求，如果他的变化极为缓慢，或者说保证不变的话，也可以作为维度表来使用。
有时，数字值即用于计算，也用于过滤/分组。再这样的情况下，可以考虑同时存储，维度表中存储标准指导价格，事实表中存储真实销售价格。
产品维度是大多数维度模型中的常见维度。构建该维度要使用大量的描述性属性。
商店维度
商店分布在各个城市，所以商店就是主要的地理维度。既然是地理维度，肯定有地理的多层次维度。
| 字段 | 描述 |
|---|---|
| Store Key(PK) | 公钥 |
| Store Number(NK) | 商店编号 |
| Store Name | 商店名称 |
| Store Street Address | 商店街道地址 |
| Store City | 商店所在城市 |
| Store County | 商店所在县 |
| Store City-State | 商店所在城市的状态 |
| Store State | 商店状态 |
| Store Zip Code | 商店邮政编码 |
| Store Manager | 商店经理 |
| Store District | 商店所在的区 |
| Store Region | 商店所在区域 |
| Floor Plan Type | 楼层平面类型 |
| Photo Processing Type | 照片处理类型 |
| Financial Service Type | 金融服务类型 |
| Selling Square Footage | 销售面积 |
| Total Square Footage | 总面积 |
| First Open Date | 首次开放日期 |
| Last Remodel Date | 上次改型日期 |
维度表中，Floor Plan类型，Photo Processing 类型以及Financial Service 类型都是短文本描述符，用来描述特定的商品。应该采用10-20个字符来描述他们，来保证使用下拉能够具有可理解的含义。
促销维度
促销维度描述了销售商品的促销条件。促销条件包括临时降价、终端通道展示、报纸广告、礼券等。促销维度通常被认为是一种因果维度，因为它描述了认为可能导致产品销售产生改变的因素。
不管怎么样，大家关注的都是促销是否有效。促销的成功可以基于下面的因素来判断：
- 促销产品的销售是否在促销期间获得大幅提升。提升多少的度量是根据如果不进行促销的话提升了多少。
- 降价是否抵消了促销产生的销售增益，是否是因为降价压制了正常售价的销售
- 是否存在销售侵蚀，即同类产品的销售显著降低了
- 促销分类中所有产品是否都获得了净总增益
- 促销是否有利可图
促销活动还和广告与终端通道展销相关。在促销维度中为每个发生的促销条件的组合建立一行是具有实际意义的。
| 字段 | 描述 |
|---|---|
| Promotion Key(PK) | 主键 |
| Promotion Code | 促销代码 |
| Promotion Name | 促销名称 |
| Price Reduction Type | 降价类型 |
| Promotion Media Type | 促销媒体类型 |
| Ad Type | 广告类型 |
| Display Type | 显示类型 |
| Coupon Type | 优惠券类型 |
| Ad Media Name | 广告媒体名称 |
| Display Provider | 显示提供程序 |
| Promotion Cost | 推广费 |
| Promotion Begin Date | 促销开始日期 |
| Promotion End Date | 促销结束日期 |
当然，最好有一个无促销的行，毕竟我们不希望在事实表中因为促销维度而留下一个空值键。
实际的销售模式
维度表代理键
维度表的唯一主键是代理键而不是操作系统自带的标识符。代理键简单地以按照顺序序列生成的整数表示，比如第一行的代理键为1，第二行就是2。
实际上代理键没有任何业务上的意义，他仅仅用来连接维度表与事实表。
记住，不要去使用包含业务关系的智能多维键，不要去使用多维键，不要去使用多维键。因为查询与数据访问应用不应该有任何与键的依赖关系，这些人为塑造的逻辑很容易失效。
**注意：**维度表与事实表的每个连接应该基于无实际含义的整数代理键，避免使用自然键作为维度表的主键。
代理键的优点：
为数据仓库缓冲操作性系统的变化。代理键在深层的表示是数据部门对DW/BI系统的控制权，保证键值不由生产部门控制。
集成多个源系统。代理键能够确保数据部门从多个操作型元数据集成数据，即使他们缺乏一致性的源键，但是通过交叉引用映射表可以将多个自然键变成一个代理键。
改进性能。代理键是一个很小的整数，来确保能够适应未来预期的粒度变化。
处理空值或未知条件。特定的代理键用来记录不涉及操作型代码的维度条件，比如上面的促销维度代理键。
支持维度属性变化跟踪。伪代理键实际上是把所有自然键粘合在一起加上一个时间戳，但是代理键应该可以通过自然键的轮廓来表达出来。
代理键也需要分配与管理，但并非超过人们的预想。只需要在ETL系统建立并维护交叉参考表，用于以代理键替代每个事实表和维度表行。
维度表自然键
自然键是数据进入数仓前的标识键，一般由业务系统，操作系统等等产生。
自然键通常是维度表的属性。如果自然键来源于多个源，则可以使用“|”来进行分隔，比如“CRM|648321”。
维度表超自然键
自然键在进行各种操作时，肯定会发生损坏或丢失，那么ETL系统需要分配在系统声明周期中保持不变的持久性标识符，就是超自然键。
他是一种简单的整数序列分配方法，类似于自然键，超自然键会作为维度属性来处理，但是不能作为代理键的替换方式。
退化维度代理键
虽然通常不会给退化维度分配代理键，但每个维度都需要评估以确定是否需要。
比如我们控制事务记录的标识符最大只有4字节的大小，如果把他们全部用完了，肯定只能去覆盖之前的记录。
所以在规划时，标识符要尽可能规划的足够大，比如24字节的字母数字序列。
日期维度的智能键
日期维度是一个很特殊的维度，他是可预测的，1月1号的代理键是1,2号就是2，一次类推。
但是与其这样，不如给他一个更有意义的整数，比如YYYYMMDD。
事实表的代理键
事实表的代理键就是一个简单整数，没有任何业务含义。
虽然事实表代理键不可能获得查询性能方面的提升，但是有以下利益：
直接的唯一标识。可以直接用来标识一行。
返回或恢复海量加载。如果想要加载一万行，只需要结束行代理键减去开头行代理键为一万即可。
插入加删除的替换更新。比起真的进行更新，不如插入新的行，删除原行，那么代理键就是用于定位的关键点。
使用事实表代理键作为父/子模式中的父节点。一个事实表包含的行时另外粒度更细的事实表的父指针。因为自然键是混乱且无法预测的，代理键是ETL系统分配的，显然是后者更具有可控性。因此子事实不必遍历父事实表的代理键就可以被分块或分片。
抵制规范化的冲动
有意识的去打破传统建模规则，要易用，要有性能。
具有规范化维度的雪花模式
雪花模式就是维度表也有维度表。
我们地址采用雪花模式：
- 雪花模式构建了一个复杂的模型。
- 大量表的连接会导致查询性能降低
- 连接的复杂性增加了错误策略的可能性
- 不节省磁盘空间
- 对用户的浏览维度能力是有负面影响的，就好比“没有注释的代码”
- 需要很复杂的SQL语句来查询
- 雪花模型无法使用位图索引
支架表
支架表可以节省空间并能确保相同的属性被一致地引用，但他仍然有缺点。
支架表引入了更多的连接，降低了系统的性能。
可以使用支架表，但是出于其他因素考虑，维度模型尽量不要大量使用支架表。
包含大量维度的蜈蚣事实表
包含了太多的维度表，无法实现有效的索引。大量的链接无论对可用性还是性能来说都是一个问题。
很多维度都是可以进行合并的，像这样将同一层次的元素表示为不同维度是建模中的常见错误。
小结
建模四步：选择业务流程–》声明粒度–》确定维度–》确定事实
在这四步中，粒度和维度都可以随时的进行调整，只要保证事实表用起来足够的简单易懂。
在构建维度表时，要使用详细的、健壮的描述性属性。
日期维度是一个通用性很高的维度。
数字如果用于计算则存储在事实表中，如果用来过滤/分组则存储在维度表中。
有些状态维度可以定义一个标准的维度行。
要避免使用雪花维度表与蜈蚣事实表。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
