设计数据仓库架构

  • 7 分钟

与所有关系数据库一样，在数据仓库包含的表中，存储了你要分析的数据。 最常见的是，这些表组织在一个针对多维建模进行优化的架构中，其中与称为事实的事件关联的数值度量值可以通过多个维度上的关联实体的属性进行聚合。 例如，与销售订单关联的度量值（比如支付的金额或订购的商品数量）可以按销售发生的日期、客户、商店等的属性进行聚合。

数据仓库中的表

关系数据仓库的一个常见模式是定义包含两种表的架构：维度表和事实数据表。

维度表

维度表描述了业务实体，例如产品、人员、地点和日期。 维度表包含实体属性的列。 例如，客户实体可能有名字、姓氏、电子邮件地址和邮寄地址（可能由街道地址、城市、邮政编码和国家/地区组成）。 除了属性列，维度表还包含一个唯一的键列，用于唯一标识表中的每一行。 事实上，维度表通常包含两个键列：

  • 代理键，它特定于数据仓库，并唯一地标识数据仓库中维度表中的每一行，通常是一个递增的整数。
  • 备用键，它通常是一个自然键或业务键，用于标识实体记录源自的事务源系统中实体的特定实例，例如产品代码或客户 ID。

注意

为什么有两个键？ 有几个合理的理由：

  • 数据仓库可能填充了来自多个源系统的数据，这可能导致业务键重复或不兼容的风险。
  • 简单的数值键通常在联接大量表的查询中表现更好，这是数据仓库中的常见模式。
  • 实体的属性可能会随时间而变化，例如，客户可能会更改他们的地址。 由于数据仓库用于支持历史报告，因此你可能希望在多个时间点保留实体的每个实例的记录；因此，例如，特定客户的销售订单会计入他们下达订单时所居住的城市。 在这种情况下，多个客户记录将具有与客户关联的相同业务键，但客户在不同时间居住的每个离散地址的代理键不同。

例如，客户的维度表可能包含以下数据：

CustomerKey CustomerAltKey 名称 电子邮件 街道 城市 PostalCode CountryRegion
123 I-543 Navin Jones navin1@contoso.com 1 Main St. Seattle 90000 United States
124 R-589 Mary Smith mary2@contoso.com 234 190th Ave Buffalo 50001 United States
125 I-321 Antoine Dubois antoine1@contoso.com 2 Rue Jolie Paris 20098 法国
126 I-543 Navin Jones navin1@contoso.com 24 125th Ave。 纽约 50000 United States
... ... ... ... ... ... ... ...

注意

请注意，该表包含 Navin Jones 的两条记录。 这两条记录使用相同的备用键来识别此人 (I-543)，但每条记录都有不同的代理键。 由此，可以推测客户从西雅图搬到了纽约。 居住在西雅图期间对客户进行的销售与键 123 相关联，而在移居纽约后客户进行的购买则根据记录 126 进行记录。

除了表示业务实体的维度表外，数据仓库通常还包含表示时间的维度表。 此表使数据分析师能够通过时态间隔来聚合数据。 根据需要分析的数据类型，时间维度的最低粒度（称为“颗粒”）可以表示时间（精细到小时、秒、毫秒、纳秒甚至更低的粒度）或日期。

例如，日期级别的粒度的时间维度表可能包含以下数据：

DateKey DateAltKey DayOfWeek DayOfMonth Weekday 月 MonthName 季度 年份
19990101 01-01-1999 6 1 星期五 1 1 月 1 1999
... ... ... ... ... ... ... ... ...
20220101 01-01-2022 7 1 星期六 1 1 月 1 2022
20220102 02-01-2022 1 2 星期日 1 1 月 1 2022
... ... ... ... ... ... ... ... ...
20301231 31-12-2030 3 31 星期二 12 12 月 4 2030

表中的记录所涵盖的时间跨度必须包括相关事实数据表中记录的任何关联事件的最早和最晚时间点。 通常，在这之间的适当粒度处，每个间隔都有一条记录。

事实数据表

事实数据表存储观测数据或事件的详细信息；例如，销售订单、库存余量、汇率或记录的温度。 事实数据表包含可通过维度聚合的数值的列。 除了数值列，事实数据表还包含可引用相关维度表中唯一键的键列。

例如，包含销售订单详细信息的事实数据表可能包含以下数据：

OrderDateKey CustomerKey StoreKey ProductKey OrderNo LineItemNo 数量 单价 税款 ItemTotal
20220101 123 5 701 1001 1 2 2.50 0.50 5.50
20220101 123 5 765 1001 2 1 2.00 0.20 2.20
20220102 125 2 723 1002 1 1 4.99 0.49 5.48
20220103 126 1 823 1003 1 1 7.99 0.80 8.79
... ... ... ... ... ... ... ... ... ...

事实数据表的维度键列确定了其粒度。 例如，销售订单事实数据表包括日期、客户、商店和产品的键。 一个订单可能包括多个产品，因此该粒度代表了特定日期在商店中销售给客户的单个产品的明细项目。

数据仓库架构设计

在商务应用程序中使用的大多数事务数据库中，数据经过规范化以减少重复。 但是，在数据仓库中，维度数据通常是去规范化的，以减少查询数据所需的联接数。

通常，数据仓库组织为一个星型架构，其中事实数据表与维度表直接相关，如以下示例所示：

显示星型架构的关系图。

实体的属性可用于聚合多个分层级别的事实数据表中的度量值，例如，按国家或地区、城市、邮政编码或单个客户查找总销售收入。 每个级别的属性可以存储在同一维度表中。 但是，如果实体具有大量的分层属性级别，或者某些属性可以由多个维度共享（例如，客户和商店都有一个地理地址），则可以将一些规范化应用于维度表并创建雪花型架构，如以下示例所示：

显示雪花型架构的关系图。

在这种情况下，DimProduct 表已进行规范化，可以为产品类别和供应商创建单独的维度表，并添加了 DimGeography 表来表示客户和商店的地理属性。 DimProduct 表中的每一行都包含 DimCategory 和 DimSupplier 表中对应行的键值；DimCustomer 和 DimStore 表中的每一行都包含 DimGeography 表中相应行的键值。