数据仓库设计与管理：多维度数据模型解析

在数据仓库的设计过程中，不同类型的事实表和维度表有着各自独特的用途和设计思路。以下将详细介绍几种常见的数据模型及其设计要点。

周期性快照事实表的应用

在某些业务场景下，需要定期捕获收入、成本和利润率等数据，这时周期性快照事实表就派上了用场。例如，订阅销售事实表就采用了这种方式，它以固定的时间间隔记录相关数据。与产品销售事实表不同，订阅销售事实表并不用于记录具体的业务事件，所以不适合设计为事务型事实表。

业务需求中可能会要求根据年度收入和年度盈利能力每天计算订阅者的类别和等级。为了高效完成这一计算，可以将当天的客户订阅数据存储在单独的表中。因为在只包含当天数据的表上进行计算，比在包含数月或数年订阅数据的表上计算要快得多，而且由于当天的订阅数据已经存储在本地的DDS（数据仓库）中，并且年度收入和年度盈利能力已经计算好，所以也比查询源系统更快。

供应商绩效数据集市

供应商绩效数据集市的事实表粒度为每周每个供应产品一行，而不是每周每个供应商一行。这样的设计使得我们可以深入到产品级别进行分析，也可以汇总到供应商级别。该事实表属于周期性快照类型。

它除了日期维度外，还有一个周维度。周维度通过 week_key 引用，代表评估供应商绩效的周；日期维度通过 start_date_key 引用，即供应商开始向Amadeus Entertainment供应该产品的日期。周维度是基于日期维度创建的视图，通过限制特定日期过滤掉与周级别无关的列，并且与日期维度具有相同的键。

供应商绩效数据集市包含四个维度：日期、周、供应商和产品。其中，供应商维度支持SCD类型2，通过有效和过期时间戳以及 is_current 列来体现。供应商维度中的地址列与客户维度中的地址列类似，但多了 contact_name 和 contact_title ，用于表示供应商组织中的联系人。

该数据集市的目的是支持用户分析“供应商绩效”，这是总支出、成本、退货价值、拒收价值、标题和格式可用性、库存短缺、交货时间和及时性等指标的加权平均值。在定义事实表的度量时，需要与业务部门讨论确定评估绩效的时间段，在本示例中，成本是基于过去三个月计算的。

以下是供应商绩效事实表的详细信息：
| Column Name | Data Type | Description | Sample Value |
| — | — | — | — |
| order_quantity | int | 过去三个月从该供应商订购该产品的数量 | 44 |
| unit_cost | money | 过去三个月Amadeus Entertainment为该产品向供应商支付的平均价格 | 2.15 |
| ordered_value | money | 订购数量乘以单位成本 | 94.6 |
| returns_quantity | int | 过去三个月Amadeus Entertainment从客户处收回并随后退回给供应商的该产品数量 | 1 |
| returns_value | money | 退货数量乘以单位成本 | 2.15 |
| rejects_quantity | int | 过去三个月未先供应给客户就退回给供应商的产品数量 | 1 |
| rejects_value | money | 拒收数量乘以单位成本 | 2.15 |
| total_spend | money | 订购价值减去退货价值减去拒收价值 | 90.3 |
| title_availability | decimal (9,2) | 过去三个月Amadeus Entertainment订购产品时，产品标题的可用百分比 | 100% |
| format_availability | decimal (9,2) | 过去三个月订购产品时，产品以所需格式的可用百分比 | 100% |
| stock_outage | decimal (9,2) | 过去三个月该产品库存缺货的天数除以过去三个月的总天数 | 0 |
| average_lead_time | smallint | 过去三个月订单下达日期与产品交付日期之间的平均间隔天数 | 4 |

CRM 数据集市

CRM（客户关系管理）数据集市主要用于满足CRM活动细分的业务需求，即让CRM用户能够根据多种条件选择客户，以便发送CRM活动。这些条件包括通信权限（订阅/未订阅、电子邮件/电话/邮寄等）、地理属性（地址、城市等）、人口统计属性（年龄、性别、职业、收入、爱好等）、兴趣（音乐品味、书籍主题兴趣、喜欢的电影类型等）、购买历史（订单价值、订单日期、商品数量、店铺位置等）、订阅详情（套餐详情、订阅日期、持续时间、店铺位置等）以及购买产品的属性（例如音乐类型、艺术家、电影类型等）。

为了实现这些功能，需要将通信订阅信息存储在单独的事实表中，而将通信权限和通信偏好（即兴趣）添加到客户维度中。购买历史和订阅详情可以从产品销售和订阅销售数据集市中获取，购买产品的属性也可以在产品销售数据集市中找到。

在介绍具体设计之前，先明确一些CRM术语：
- 通信订阅：指客户订阅某种通信服务，如每周时事通讯。
- 通信权限：指客户允许我们或第三方合作伙伴通过电话、电子邮件、邮寄等方式与他们联系。
- 通信偏好：包括通信渠道偏好和通信内容偏好。通信渠道偏好指客户希望通过何种方式（如电话、邮寄、电子邮件或短信）被联系；通信内容偏好指客户希望了解的主题，如流行音乐、喜剧电影或特定的喜爱作者。通信内容偏好也称为兴趣。

将通信订阅信息放在事实表中，将通信权限和通信偏好放在客户维度中。在本案例中，为了简化，假设每个客户只有一个电子邮件地址、一个电话号码和一个邮政地址。但在实际的CRM数据仓库中，客户可能有多个电子邮件/邮政地址和固定电话/手机号码。在这种情况下，需要在订阅事实表中添加 contact_address_key ，并创建一个“联系地址”维度。该维度用于存储哪个电子邮件地址订阅了时事通讯，哪个手机号码订阅了短信服务，哪个邮政地址订阅了促销邮件。这样可以让同一个客户使用不同的电子邮件地址订阅时事通讯。

以下是客户维度中用于纳入通信偏好的附加属性：
| Column Name | Data Type | Description | Sample Value |
| — | — | — | — |
| permission1 | int | 客户允许我们联系时为1，否则为0，默认值为0 | 1 |
| permission2 | int | 客户允许第三方合作伙伴联系时为1，否则为0，默认值为0 | 1 |
| preferred_channel1 | varchar(15) | 首选通信渠道 | E-mail |
| preferred_channel2 | varchar(15) | 次选通信渠道 | Text |
| interest1 | varchar(30) | 第一个兴趣/首选内容 | Pop music |
| interest2 | varchar(30) | 第二个兴趣/首选内容 | Computer audio books |
| interest3 | varchar(30) | 第三个兴趣/首选内容 | New films |

通过通信订阅事实表和客户维度中的附加属性，CRM用户可以根据各种条件选择客户，这个过程称为活动细分。活动是指通过电子邮件、邮寄、RSS或短信等方式定期或临时发送给客户的通信。

除了选择客户，业务需求中还要求让CRM用户能够分析活动结果，具体需要查看以下几个指标：
- 按通信渠道（手机、短信或电子邮件）发送的消息数量。
- 成功交付的消息数量。
- 未能交付的消息数量（包括原因）。

对于电子邮件消息，用户还需要分析打开率、点击率、投诉率、垃圾邮件判定和陷阱点击率。打开率是指客户打开的邮件消息数量除以发送的消息数量；点击率是指电子邮件消息中嵌入的超链接被点击的数量除以发送的电子邮件消息数量；投诉率是指将电子邮件评为垃圾邮件的客户数量除以发送的电子邮件总数；垃圾邮件判定是指电子邮件被ISP或垃圾邮件过滤软件分类为垃圾邮件的概率；陷阱点击率是指发送到ISP创建的虚拟电子邮件地址的电子邮件数量。

为了满足这些需求，需要设计一个活动结果事实表，并引入两个新的维度：活动和交付状态。活动结果事实表的粒度为每个预期活动接收者一行。例如，如果一个活动计划发送给200,000个接收者，那么即使由于某些原因（如10个电子邮件地址无效，20个客户明确表示不希望收到任何营销信息）实际只发送给了199,970个接收者，该表中也会有200,000行记录。

以下是活动结果事实表的详细信息：
| Column Name | Data Type | Description | Sample Value |
| — | — | — | — |
| campaign_key | int | 发送给客户的活动的键 | 1456 |
| customer_key | int | 预期接收该活动的客户的键 | 25433 |
| communication_key | int | 活动所属通信的键，例如该活动是名为“Amadeus音乐每周时事通讯”（日期为2008年2月18日）的通信实例 | 5 |
| channel_key | int | 活动发送的通信渠道的键，例如一个活动可以发送给200,000个客户，其中170,000个通过电子邮件，30,000个通过RSS | 3 |
| send_date_key | int | 活动实际发送日期的键 | 23101 |
| delivery_status_key | int | 交付状态的键，0表示未发送，1表示成功交付，2到N包含活动未能交付给预期接收者的各种原因，如邮箱不可用、邮箱不存在等 | 1 |
| sent | int | 活动实际发送给接收者（从我们的系统发出）时为1，否则为0，未发送的原因包括电子邮件地址验证失败和无客户许可等 | 1 |
| delivered | int | 活动成功交付时为1（例如，如果是电子邮件活动，是DATA命令上的SMTP回复代码250），否则为0，如果失败， delivery_status_key 列将包含原因 | 1 |
| bounced | int | 活动硬退回（SMTP客户端收到永久拒绝消息）时为1，否则为0 | 0 |
| opened | int | 活动电子邮件被打开或查看时为1，否则为0 | 1 |
| clicked_through | int | 客户点击活动电子邮件中的任何链接时为1，否则为0 | 1 |
| complaint | int | 客户点击垃圾邮件按钮（在一些电子邮件提供商如Yahoo、AOL、Hotmail和MSN上可用，我们通过反馈循环获取信息）时为1，否则为0 | 0 |
| spam_verdict | int | 活动内容被ISP或垃圾邮件过滤软件（如Spam Assassin）识别为垃圾邮件时为1，否则为0 | 0 |
| source_system_code | int | 该记录来自的源系统的键 | 2 |
| create_timestamp | datetime | 记录在DDS中创建的时间 | 2007年2月5日 |
| update_timestamp | datetime | 记录最后更新的时间 | 2007年2月5日 |

交付状态维度包含交付事实为0时的失败原因代码。如果交付事实为1，则 delivery_status_key 为1，表示正常。 delivery_status_key 为0表示活动未发送给特定客户。该维度中的 category 列可以包含基于SMTP回复代码的严重程度级别，例如，如果回复代码为550（邮箱不可用），则可以将其分类为严重。

数据层次结构

在维度表中，存在一种称为层次结构的结构，它对于数据的分析和汇总非常重要。层次结构提供了在分析数据时进行向上汇总和向下钻取的路径。

在维度表中，有时一个属性（列）是另一个属性的子集，这意味着该属性的值可以由另一个属性进行分组。可以用于分组的属性被认为比被分组的属性处于更高的级别。例如，一年由四个季度组成，一个季度由三个月组成，在这种情况下，最高级别是年，其次是季度，最底层是月。我们可以将一个事实从较低级别汇总到较高级别，例如，如果知道一个季度中每个月的销售值，就可以知道该季度的销售值。

产品销售事实表的四个维度都有层次结构，日期、店铺、客户和产品维度的层次结构如下所示：

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    A(Year):::process --> B(Quarter):::process
    B --> C(Month):::process
    D(Fiscal Week):::process --> E(Fiscal Period):::process
    F(Week):::process -.->|不直接汇总| C
    G(SQL Date):::process --> F
    H(System Date):::process --> F
    I(Julian Date):::process --> F
    J(Day):::process --> F
    K(Day of the Week):::process --> F
    L(Day Name):::process --> F
    M(Store):::process --> N(Regional Structure):::process
    M --> O(Geographical Structure):::process
    P(Customer):::process --> Q(Geographical Location):::process

要在自己的场景中应用层次结构，可以按照以下步骤进行：
1. 查看维度表中的列（属性），找出是否存在分组或子集关系。
2. 将属性按适当的级别排列，即把较高级别的属性放在较低级别属性的上方。
3. 测试数据，以证明较低级别属性的所有成员都可以由较高级别属性进行分组。
4. 识别层次结构中是否存在多条路径（分支），例如，店铺可以按城市和地区进行分组。
5. 可以使用数据探查工具来完成上述步骤。层次结构是维度设计的一个重要组成部分。

源系统映射

源系统映射是将维度数据存储与源系统进行映射的过程。完成DDS（数据仓库）设计后，需要将DDS中的每一列映射到源系统，以便在填充这些列时知道从哪里获取数据。同时，还需要确定将源列转换为目标列所需的转换或计算，这有助于理解ETL逻辑在填充DDS表中的每一列时必须执行的功能。

由于ODS（操作数据存储）集成了来自多个源的数据，因此DDS列可能来自源系统中的多个表，甚至多个源系统。这时， source_system_code 列就非常有用，它可以帮助我们了解数据来自哪个系统。

以产品销售数据集市为例，首先需要找出各列的数据来源。在设计DDS时，我们创建了DDS列（事实表度量和维度属性）以满足业务需求，现在需要通过查看源系统表来确定数据的来源。我们将源表及其缩写写在括号中，以便在映射表中使用，然后编写这些源表之间的连接条件，这样在开发ETL时就知道如何编写查询。

以下是产品销售事实表的源表和连接条件：
- 目标表：产品销售事实表
- 源表：WebTower9销售订单头表 [woh] 、WebTower9销售订单明细表 [wod] 、Jade订单头表 [joh] 、Jade订单明细表 [jod] 、Jupiter项目主表 [jim] 、Jupiter货币汇率表 [jcr]
- 连接条件： woh.order_id = wod.order_id ， joh.order_id = jod.order_id ， wod.product_code = jim.product_code ， jod.product_code = jim.product_code

在这个案例中，库存由Jupiter管理，销售交易存储在两个前台系统WebTower9和Jade中，这就是为什么需要将Jupiter库存主表与WebTower9和Jade中的订单明细表进行关联。

然后，在产品销售事实表中添加“源”和“转换”两列，以明确各列的数据来源和转换方式：
| Column Name | Description | Source | Transformation |
| — | — | — | — |
| sales_date_key | 客户购买产品的日期 | woh.order_date , joh.order_date | 根据日期列在日期维度上进行键查找 |
| customer_key | 购买产品的客户 | woh.customer_id , joh.customer_number | 根据客户ID列在客户维度上进行键查找 |
| product_key | 客户购买的产品 | wod.product_code , jod.product_code | 根据产品代码列在产品维度上进行键查找 |
| store_key | 客户购买产品的店铺 | woh.store_number , joh.store_number | 根据店铺编号列在店铺维度上进行键查找 |
| order_id | WebTower9或Jade订单ID | woh.order_id , joh.order_id | 必要时进行修剪并转换为大写字符 |
| line_number | WebTower9或Jade订单行号 | wod.line_no , jod.line_no | 去除前导零 |
| quantity | 客户购买产品的数量 | wod.qty , jod.qty | 无 |
| unit_price | 购买产品的单价（美元） | wod.price , jod.price | 除以该日期的 jcr.rate ，并四舍五入到四位小数 |
| unit_cost | 产品的直接和间接成本（美元） | jim.unit_cost | 除以该日期的 jcr.rate ，并四舍五入到四位小数 |
| sales_value | 数量乘以单价 | 该事实表的数量列乘以单价列 | 四舍五入到四位小数 |
| sales_cost | 数量乘以单位成本 | 该事实表的数量列乘以单位成本列 | 四舍五入到四位小数 |
| margin | 销售价值减去销售成本 | 该事实表的销售价值减去销售成本列 | 四舍五入到四位小数 |
| sales_timestamp | 客户购买产品的时间 | woh.timestamp , joh.timestamp | 转换为 mm/dd/yyyy hh:mm:ss.sss 格式 |
| source_system_code | 该记录来自的源系统 | 1表示WebTower9，2表示Jade，3表示Jupiter | 在元数据数据库的源系统表中进行查找 |
| create_timestamp | 记录在DDS中创建的时间 | 当前系统时间 | 无 |
| update_timestamp | 记录最后更新的时间 | 当前系统时间 | 无 |

时间维度不需要进行源系统映射，因为它是在数据仓库内部计算得出的。建议编写一个SQL脚本，以开始日期和结束日期作为输入参数，生成日历列的值。可以通过从开始日期循环到结束日期，并逐行插入包含各种日期属性的记录来实现。在开发、测试和迁移到生产环境时，这种方法可以方便地重复运行脚本。

财政和假日列通常从源系统或电子表格中导入，先使用SSIS将其加载到临时表中，然后通过前面提到的脚本导入到DDS中。需要将这些源数据映射到日期维度，通过比较源系统列和DDS列来实现，目的是编写日期维度填充脚本。

以下是日期维度的源系统映射信息：
| Column Name | Description | Source | Transformation |
| — | — | — | — |
| fiscal_week | 自9月1日起经过的周数 | jfc.week | 无 |
| fiscal_period | 根据财政日历，一个周期由四周或五周组成，通常公司使用的模式为454、544或445，格式为FPn，一个财政年度始终由12个周期组成 | jfc.period | 去除前导零，添加FP前缀 |
| fiscal_quarter | 一个财政季度是三个财政周期，从9月1日开始，格式为FQn | jfc.quarter | 添加FQ前缀 |
| fiscal_year | 财政年度从9月1日开始，格式为FYnnnn | jfc.year | 添加FY前缀 |
| us_holiday | 是美国国家假日时为1，否则为0 | xh.us.date | 查找转换为1和0 |
| uk_holiday | 是英国法定假日时为1，否则为0 | xh.uk.date | 查找转换为1和0 |
| period_end | 是财政周期的最后一天时为1，否则为0 | 根据财政周期列计算 | 按财政周期分组选择最大值 |
| short_day | 线下店铺营业时间比正常营业时间短时为1，否则为0 | jsh.start , jsh.end | 如果 (start - end) < 12 则为1，否则为0 |

对于产品维度，同样需要进行源系统映射。以下是产品维度的源表和连接条件：
- 源表：Jupiter项目主表 [jim] 、Jupiter产品类型表 [jpt] 、Jupiter类别表 [jc] 、Jupiter媒体表 [jm] 、Jupiter货币汇率表 [jcr] 、Jupiter产品状态表 [jps]
- 连接条件： jim.type = jpt.id ， jim.category = jc.id ， jim.media = jm.id ， jim.status = jps.id

以下是产品维度的源系统映射信息：
| Column Name | Description | Source | Transformation |
| — | — | — | — |
| product_key | 产品维度的代理键，唯一、非空，是产品维度的主键 | 标识列，种子为1，增量为1 | 无 |
| product_code | 自然键，产品代码是Jupiter中产品表的标识符和主键，格式为AAA999999 | jim.product_code | 转换为大写 |
| name | 产品名称 | jim.product_name | 无 |
| description | 产品描述 | jim.description | 无 |

通过以上的设计和映射，我们可以构建一个完整的数据仓库，满足业务对数据分析和决策的需求。在实际应用中，还需要不断优化和调整数据模型，以适应业务的变化和发展。

数据仓库设计与管理：多维度数据模型解析

数据仓库设计的综合考量

在构建数据仓库时，除了上述详细介绍的各种数据模型和映射方法，还需要综合考虑多方面因素，以确保数据仓库能够高效、准确地支持业务需求。

首先，数据质量是数据仓库的基石。在进行源系统映射和数据集成的过程中，要对源数据进行严格的质量检查。例如，检查数据的完整性，确保没有缺失值；检查数据的准确性，避免数据错误或不一致。可以通过编写数据验证脚本，在ETL过程中对数据进行实时检查，对于不符合质量要求的数据进行标记或清洗。

其次，性能优化也是至关重要的。随着数据量的不断增长，数据仓库的查询性能可能会受到影响。为了提高查询性能，可以采用以下几种方法：
- 索引优化 ：在事实表和维度表上创建合适的索引，以加快数据的检索速度。例如，在经常用于查询条件的列上创建索引。
- 分区表 ：对于大型事实表，可以采用分区表的方式进行存储，将数据按照时间、地区等维度进行分区，减少查询时需要扫描的数据量。
- 缓存机制 ：对于一些经常使用的查询结果，可以采用缓存机制进行存储，避免重复查询，提高响应速度。

另外，数据安全也是不可忽视的问题。数据仓库中存储着大量的敏感业务数据，需要采取有效的安全措施来保护数据的安全性。例如，设置不同的用户角色和权限，对数据进行访问控制；采用加密技术对敏感数据进行加密存储，防止数据泄露。

数据仓库的维护与监控

数据仓库建成后，还需要进行持续的维护和监控，以确保其正常运行和数据的准确性。

在维护方面，主要包括以下几个方面：
- 数据更新 ：定期更新数据仓库中的数据，确保数据的及时性和准确性。可以根据业务需求设置不同的更新频率，如每天、每周或每月更新一次。
- 表结构调整 ：随着业务的发展，可能需要对数据仓库的表结构进行调整。例如，添加新的列、修改数据类型等。在进行表结构调整时，要注意对现有数据的影响，确保数据的一致性。
- ETL脚本维护 ：定期检查和维护ETL脚本，确保其正常运行。对于出现问题的脚本，要及时进行修复和优化。

在监控方面，需要对数据仓库的运行状态进行实时监控，及时发现和解决问题。可以监控以下几个方面：
- 数据质量监控 ：定期检查数据的质量，如数据的完整性、准确性等。对于不符合质量要求的数据，要及时进行处理。
- 性能监控 ：监控数据仓库的查询性能，如查询响应时间、吞吐量等。对于性能下降的情况，要及时进行分析和优化。
- 系统资源监控 ：监控数据仓库所在服务器的系统资源使用情况，如CPU、内存、磁盘I/O等。对于资源使用过高的情况，要及时进行调整。

数据仓库与业务的协同发展

数据仓库的最终目的是为业务决策提供支持，因此需要与业务进行紧密的协同发展。

一方面，要根据业务需求不断优化数据仓库的设计。业务需求是不断变化的，数据仓库需要及时响应这些变化，提供更符合业务需求的数据和分析功能。例如，当业务推出新的产品或服务时，数据仓库需要能够及时收集和分析相关数据，为业务决策提供支持。

另一方面，要加强数据仓库与业务部门之间的沟通和协作。业务部门是数据的使用者，他们对数据的需求和使用方式有更深入的了解。通过与业务部门的沟通和协作，可以更好地理解业务需求，提供更有价值的数据分析和决策支持。

以下是一个简单的数据仓库维护和监控流程的mermaid流程图：

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    A(数据仓库建成):::process --> B(数据更新):::process
    B --> C(表结构调整):::process
    C --> D(ETL脚本维护):::process
    D --> E(数据质量监控):::process
    E --> F(性能监控):::process
    F --> G(系统资源监控):::process
    G --> H{是否有问题?}:::process
    H -- 是 --> I(问题分析与解决):::process
    I --> B
    H -- 否 --> J(持续运行):::process
    J --> B

总结

数据仓库的设计和管理是一个复杂而系统的工程，涉及到多个方面的知识和技术。通过合理设计数据模型、进行源系统映射、优化性能、保障数据安全以及加强维护和监控等措施，可以构建一个高效、准确、安全的数据仓库，为业务决策提供有力支持。同时，数据仓库需要与业务进行紧密的协同发展，不断适应业务的变化和发展，才能发挥其最大的价值。

在实际应用中，要根据具体的业务需求和数据特点，灵活运用各种方法和技术，不断优化和完善数据仓库的设计和管理。希望本文介绍的内容能够为数据仓库的设计和管理提供一些有益的参考和指导。

要点	说明
数据质量	是数据仓库的基石，需在ETL过程中进行严格检查
性能优化	可采用索引优化、分区表、缓存机制等方法
数据安全	设置用户角色和权限，采用加密技术保护数据
维护与监控	包括数据更新、表结构调整、ETL脚本维护以及数据质量、性能和系统资源监控
与业务协同	根据业务需求优化设计，加强与业务部门的沟通协作