数据仓库建模（四）：维度表的设计

半吊子Kyle

已于 2022-10-30 17:03:11 修改

阅读量3.1k

点赞数 1

分类专栏： Hive 文章标签：数据仓库

于 2022-10-23 17:47:21 首次发布

本文链接：https://blog.csdn.net/hell_oword/article/details/123699685

版权

Hive 专栏收录该内容

27 篇文章 8 订阅

订阅专栏

一、维度表的整体结构

1.1 维度表的结构设计

① 包含单一的主键

每个维度表都包含单一的主键列。

② 行的描述环境要和事实表行完全对应

维度表的主键可以作为与之关联的任何事实表的外键，维度表行的描述环境需要和事实表行完全对应。

③ 维度表通常比较宽

维度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性，维度表的属性是查询及BI应用的约束和分组定义的主要目标。

1.2 维度代理键

维度表中会包含一个列，表示唯一的主键。（维度代理键其实就是维度表的主键）

该主键不是操作系统的自然键，由于需要跟踪变化，因此若采用自然键，将需要多个维度行表示。维度的自然键可能由多个源系统建立，这些自然键将出现兼容性的问题，难以管理。

DW/BI系统需要声明对所有维度的主键管理，因此无法采用单一的自然键或附加日期的自然键，可以为每个维度建立无语义的整型主键。这些维度代理键是按顺序分配的简单整数，以值1开始。每当需要新键时，键值自动加1。

注：日期唯独不需要遵守代理键规则，日期维度是高度可预测的且稳定的维度，可以采用更有意义的主键（见1.10）。

自然键即指有业务意义的唯一ID，例如用户ID、身份证号等。代理键则可以简单理解为该表的自增ID值

1.3 自然键、超久键和超自然键

由操作系统建立的自然键受业务规则影响，无法被 DW/BI 系统控制。

例如，如果雇员辞职，然后重新工作，则雇员的编号(自然键)可能会发生变化。数据仓库希望为该雇员创建单一键，这就需要建立新的持久键以确保在此种情况下，雇员编号保持持久性，不会发生变化。

该示例中的主键有时会被称之为超自然键

最好的持久键其实应该独立于原始的业务过程，并以整数1开始进行分配，多个代理键与某一个雇员关联时，若描述发生变化时，持久键不会发生变化。

题外话：这里的自然键在实际业务中其实使用意义不大，它看起来没有实际的价值，在上面的示例中，我们一般会使用用户的身份证编号来作为他的编码，这样就保证了雇员编号持久性。

1.4 下钻与上卷

维度中有不同的层次，每个层次可以有多个级别，这样就可以根据多个维护层次和级别进行分析，可以灵活获取高级别的汇总信息，获取低级别的明细信息。把获取高级别的汇总信息的过程叫上卷，把获取低级别的明细信息的过程叫下钻

假设时间维度有四个级别，分别是年、月、天、小时，现在我们某个级别分析每天的课程访问量，比如按天分析网页的访问量，此时我们可以按小时下钻分析，得出一天内每小时的网页访问量，也可以按月上卷，得到月度的网页访问量

1.5 维度退化

有时，维度表除了主键外没有其他内容。

例如，当某一发票包含多个数据项时，数据项事实行继承了发票的所有描述性维度外键，发票除了外键外无其他项，但发票数量仍然是此数据项的合法维度键（可以看作数字度量也可以看作维度键，角度不同）。

我们把这种数据退化到维度表中，即表明该字段是维度退化字段，也表明该字段没有对应的维度表。

常见的事实表的主键、日期字段、时间戳等都属于退化维度。

在这里插入图片描述

这个概念比较绕，我来仔细讲下：
在订单表中，用户下单对应一个订单事件，那么用户订单表自然就是事实表了，那么订单编号字段呢？不属于事件的度量，订单编号其实是一个维度字段，我们假如将该字段新建一张订单维度表，我们此时需要把该订单编号对应的其它维度数据放在该表（订单维度表）中，此时我们会发现，没有什么字段可以加入订单维度表（维度除了主键没有其它内容），此时我们就需要把该维度（订单编号）退化到事实表中（订单事实表）

1.6 非规范化的扁平维度

一般来说，维度设计者需要抵制由多年来操作型数据库设计所带来的对规范化设计的要求，并将非规范化的多对一固定深度层次引入扁平维度行的不同属性。

非规范化维度能苟实现维度建模的双重目标：简化及速度

1.7 多层次维度

多数维度包含不止一个自然层次。

例如，日历日期维度可以按照财务周期层次从天到周进行划分，也可能存在从天到月再到年的层次。位置密集型维度可能包含多个地理层次。

所有这些情况下，在同一维度中可以存在不同的层次。

1.8 维度属性的标识与状态信息

维度的定义不要太随意，尽量制定相应的规范，把业务指标的定义和维度关联起来。

令人迷惑的缩写、真假标识以及业务指标可以作为维度表中文本字词含义的补充解释。

状态信息码所包含的意义应分解成不同的表示不同描述行维度属性的部分。

1.9 维度表中的空值属性

当给定维度行没有被全部填充时，或者当存在属性没有被应用到所有维度行时，将产生控制维度属性。

上述两种情况下，我们推荐采用描述性字符串代替控制。例如，使用 Unknown 或 Not Apllicable 替换空值。应该避免在维度属性中使用空值，因为不同的数据系统在处理分组和约束时，针对空值的处理方式不一致。

例如：如下是一个产品维度表，当某个维度属性没有被完全填充后，就可以使用 Unknown 或 Not Apllicable 替换空值

产品编号	产品名称	产品类型	产品状态
001	abc	示例产品一	Unknow

1.10 日历日期维度

连接到实际事实表的日历日期维度，能够对事实表，按照熟悉的日期、月份、财务周期和日历上的特殊日期进行导航。

不要指望能够用SQL计算各个法定节假日，但可以在日历日期维度上寻找各个节假日，前提是在上一年底，生成一个完整的下一年的日历日期维度表。

日历日期维度通常包含许多描述，例如，周数、月份名称、财务周期、国家假日等属性。为了方便划分，日期维度主键可以更有意义，例如，用一个整数表示YYYYMMDD ，而不是用顺序分配的代理键。

然而，日期维度表需要特定的行表示未知或待定的日期。

若需要更详细的精确度，可以在事实表中增加不同的日期时间戳。日期时间戳不是维度表的外键，但以单独列的形式存在。如果商业用户按照当天时间（time-of-day）属性进行约束货分组，例如，当天时间或其它数字分组，则需要在事实表上新增一个 “当天时间(time-of-day)” 维度外键。

在这里插入图片描述

笔者公司的日历日期维度表，每年都会更新刷数到指定的表中，这样会减少很多的日期计算逻辑。

1.11 扮演角色的维度

单个物理维度可以被事实表多次引用，每个引用连接逻辑上存在角色纬度。

例如，事实表可以有多个日期，每个日期通过外键表示不同的日期维度，原则上每个外键表示不同的日期维度视图，这样引用具有不同的含义。

这些不同的维度视图(唯一的属性列名)被称为角色。

1.12 杂项维度

事务性商业过程通常产生一些列混杂的、低粒度标识和状态信息。

与其为每个标识或属性定义不同的维度，不如建立单独的将不同维度合并到一起的杂项维度。

这些维度通常在一个模式中标记为事务性概要纬度，不需要所有属性可能值的笛卡尔积，但应该只包含实际发生在源数据中的合并指。

杂项维度的特点：属性多但每个属性的值列表很少，并且极少修改

例如：在用户的销售事实表中，可能会存在大量的离散数据（状态信息），如：

verif_ind: 订单状态是否验证
credit_check_flag: 信用状态是否检查
new_customer_ind: 是否首次下单
web_order_flag: 是否为线上订单

杂项维度通常的处理方式：

① 忽略这些标志和指标

如果业务中可以忽略这些杂项纬度，那么可以在 DWS 层忽略这些杂项纬度

② 保留这些标志和指标

一般尽量不要在事实表中存储难以识别的标志位，尤其是当每个标志位还配有一个文字描述字段时。不要在事实表行中存储包含大量字符的描述符，因为每一行都会有文字描述，它们可能会使表快速地膨胀。
③ 将每个标志位放入其自己的维度中

将每个标志位当作普通的标志位处理，即每个标志位都有对应的纬度表，在装载事实表数据前先处理这四个维度表，然后在事实表中引用这些代理键（必要时需要生成新的代理键）

首先，当类似的标志或状态位字段比较多时，需要建立很多的维度表，其次事实表的外键数也会大量增加。
处理这些新增的维度表和外键需要大量修改数据装载脚本，还会增加出错的机会，同时会给ETL的开发、维护、测试过程带来很大的工作量。

因此杂项维度应该与普通维度的处理区分开

经验值：如果外键的数量处于合理的范围中，即不超过20个，则在事实表中增加不同的外键是可以接受的。但是，若外键列表已经很长，则应该避免将更多的外键加入到事实表中。

④ 创建新的维度表

我们可以分离出单独的维度表保存这些标志位字段的数据，它们的数据量很小，并且极少改变。事实表通过维度表的代理键引用这些标志。例如：创建单独的订单维度表，将这些杂项维度放入订单维度表中，通过订单号作为代理键，用于关联事实的杂项维度信息。

缺点：尽管创建了新的维度表，但是事实的每行数据仍热对应一个杂项维度，维度表仍然随着事实表的数量在膨胀。

⑤ 使用杂项维度表

创建杂项维度表，用于放置各种离散的标志或状态数据。

对杂项维度数据量的估算会影响其建模策略，例如，某个简单的杂项维度包含10个二值标识（是或否），则杂项维度表中最多包含1024（2^10）行。杂项维度可提供所有标识的组合（有效降低维度表的数据量），并用于基于这些标识的约束和报表。

事实表与杂项维度之间存在一个单一的、小型的代理键。

如果具有高度非关联的属性，包含更多的数量值，则将它们合并为单一的杂项维度是不合适的。
如果存在5个标识，每个仅包含3个值，则单一杂项维度是这些属性的最佳选择，因为维度最多仅有243（3^5）行。
但是如果5个没有关联的标识，每个具有100个可能值，建议建立不同维度，因为单一杂项维度表最大可能存在1亿（100^5）行。

杂项维度中行的组合确定并已知的前提下，是应该事先为所有组合的完全笛卡尔积建立行，还是建立杂项维度行，只用于保存那些在源系统中出现的组合情况的数据。

杂项维度创建千要看大概有多少可能的组合，最大行数是多少。一般来说，理论上组合的数量较小，比如只有几百行时，可以预装载所有组合的数据，而组合的数量大，那么在数据获取时，当遇到新标志或指标时再建立杂项维度行。当然，如果源数据中用到了全体组合时，那别无选择只能预先装载好全部杂项维度数据。