第一章：维度建模基础

1.1 维度建模的核心目标

两大核心目标：

  1. 以商业用户可理解的方式发布数据

    • 使用业务术语而非技术术语
    • 结构直观，易于理解
    • 与业务流程一致

  2. 提供高效的数据查询性能

    • 最小化表连接数量
    • 优化查询路径
    • 支持快速聚合计算

1.2 设计原则

简单性原则：

简单数据模型 → 易于理解 → 高效查询
  ↓
复杂数据模型 → 难以理解 → 性能低下

关键要点：

  • ✅ 从简单的数据模型开始
  • ✅ 不要求必须满足第三范式（3NF）
  • ❌ 避免过度复杂的模型设计

3NF vs 维度模型对比：

特性3NF（规范化）维度模型
设计目标消除数据冗余查询性能和易用性
表的数量多（高度拆分）少（星型结构）
查询复杂度高（多表JOIN）低（少量JOIN）
数据冗余最小适度允许
适用场景OLTP（事务处理）OLAP（分析处理）

1.3 星型模型 (Star Schema)

定义：维度模型的基本结构，由一个事实表和多个维度表组成，形似星状。

结构图：

        维度表1
           |
维度表2 - 事实表 - 维度表3
           |
        维度表4

组成部分：

1.3.1 事实表 (Fact Table)

特征：

  • 存储业务过程的度量值（数值型数据）
  • 位于星型结构的中心
  • 包含外键连接到维度表
  • 包含可加性的事实（度量值）
  • 行数通常非常大（百万到数十亿行）

示例：销售事实表

销售事实表
├── 日期键 (FK)
├── 产品键 (FK)
├── 门店键 (FK)
├── 促销键 (FK)
├── 销售数量 (度量)
├── 销售额 (度量)
└── 折扣金额 (度量)
1.3.2 维度表 (Dimension Table)

特征：

  • 包含描述性文本信息
  • 提供业务上下文
  • 通常包含 50-100 个属性
  • 行数相对较少
  • 有单一主键

示例：产品维度表

产品维度表
├── 产品键 (PK)
├── 产品名称
├── 品牌
├── 类别
├── 子类别
├── 包装类型
├── 包装尺寸
├── 重量
└── ... (更多属性)

1.4 事实表粒度

粒度定义：事实表中一行所表示的业务含义的详细程度

三种粒度类型：

1.4.1 事务粒度 (Transaction Grain)
  • 最细粒度
  • 每行代表一次业务事件
  • 示例：每行 = 一笔销售交易
1.4.2 周期快照粒度 (Periodic Snapshot Grain)
  • 汇总粒度
  • 每行代表某个时间段的汇总
  • 示例：每行 = 某一天的库存快照
1.4.3 累积快照粒度 (Accumulating Snapshot Grain)
  • 过程粒度
  • 每行代表业务流程的生命周期
  • 示例：每行 = 一个订单从下单到完成的全过程

粒度选择原则：

粒度越细 → 灵活性越高 → 存储空间越大
粒度越粗 → 查询越快 → 灵活性越低

1.5 雪花模型 (Snowflake Schema)

定义：在星型模型基础上，将维度表进一步规范化，形成多层次结构。

结构对比：

星型模型：

产品维度表
├── 产品键
├── 产品名称
├── 品牌名称
├── 类别名称
└── 子类别名称

雪花模型：

产品维度表              品牌维度表
├── 产品键              ├── 品牌键
├── 产品名称     →      ├── 品牌名称
├── 品牌键 (FK)         └── 品牌描述
└── 子类别键 (FK)
        ↓
   子类别维度表          类别维度表
   ├── 子类别键          ├── 类别键
   ├── 子类别名称  →     ├── 类别名称
   └── 类别键 (FK)       └── 类别描述

星型 vs 雪花对比：

特性星型模型雪花模型
维度表规范化否（反规范化）是
数据冗余较多较少
查询性能更好（JOIN少）较差（JOIN多）
维护复杂度低高
存储空间略大略小
推荐度⭐⭐⭐⭐⭐⭐⭐⭐

重要结论：

  • ❌ 雪花模型不推荐用于数据仓库
  • ✅ 维度表的数据量相对较小，规范化节省空间有限
  • ✅ 星型模型的查询性能优势远大于空间节省

1.6 数据集市 (Data Mart)

定义：面向特定业务部门或主题的小型数据仓库。

特点：

  • 从企业级数据仓库中提取特定数据
  • 为特定用户群体服务
  • 提供快速查询和分析
  • 数据量相对较小

常见数据集市类型：

类型主要内容服务对象
销售集市销售额、客户、订单、产品销售部门
财务集市收入、成本、利润、预算财务部门
营销集市活动效果、客户行为、ROI市场部门
人力集市员工、薪资、绩效、考勤人力资源
供应链集市库存、采购、物流、供应商供应链部门

架构关系：

企业数据仓库 (EDW)
    ├── 销售数据集市
    ├── 财务数据集市
    ├── 营销数据集市
    └── 库存数据集市

第二章：维度建模技术

2.1 维度模型设计的四步骤

Kimball 四步骤法：

1. 选择业务过程
   ↓
2. 声明粒度
   ↓
3. 确认维度
   ↓
4. 确认事实
步骤 1：选择业务过程

定义：确定要建模的业务活动

示例：

  • 销售下单
  • 库存盘点
  • 客户投诉
  • 网站访问

关键问题：

  • 这个业务过程产生什么业务事件？
  • 业务用户关心哪些业务过程？
  • 数据源能支持哪些业务过程？
步骤 2：声明粒度

定义：明确事实表中一行所代表的业务含义

粒度示例：

  • ✅ 细粒度：每行 = 一个销售订单中的一个商品行项
  • ⚠️ 中粒度：每行 = 某一天某个门店的销售汇总
  • ❌ 粗粒度：每行 = 某个月某个区域的销售汇总

重要原则：

  • 📌 粒度是维度模型设计中最关键的决策
  • 📌 粒度决定了维度的选择
  • 📌 建议从最细粒度开始设计
步骤 3：确认维度

定义：识别描述业务过程上下文的"谁、什么、哪里、何时、为什么、如何"

维度识别检查清单：

  • 何时 (When)：日期、时间
  • 谁 (Who)：客户、员工、供应商
  • 什么 (What)：产品、服务
  • 哪里 (Where)：地点、门店、仓库
  • 为什么 (Why)：原因、促销
  • 如何 (How)：渠道、方式

示例：销售业务过程

维度列表：
├── 日期维度（何时）
├── 产品维度（什么）
├── 门店维度（哪里）
├── 客户维度（谁）
├── 促销维度（为什么）
└── 支付方式维度（如何）
步骤 4：确认事实

定义：识别业务过程产生的数值型度量

事实类型：

  1. 可加事实 - 可以跨所有维度求和
  2. 半可加事实 - 不能跨时间维度求和（如库存）
  3. 不可加事实 - 不能求和（如比率、百分比）

示例：销售事实

可加事实：
├── 销售数量
├── 销售额
├── 折扣金额
├── 成本
└── 利润

半可加事实：
└── 库存数量（不能跨时间求和）

不可加事实：
├── 单价（比率）
└── 利润率（百分比）

2.2 一致性事实 (Conformed Facts)

定义：在不同事实表中，相同业务含义的事实必须有相同的技术定义和命名。

重要性：

  • 确保跨事实表的可比性
  • 避免数据歧义
  • 支持钻取分析

一致性规则：

场景处理方式
相同业务含义 + 相同计算逻辑✅ 使用相同命名
相同业务含义 + 不同计算逻辑❌ 使用不同命名
不同业务含义❌ 必须使用不同命名

示例：

✅ 正确：

销售事实表.销售额 = 数量 × 单价
退货事实表.销售额 = 数量 × 单价
（定义一致，命名一致）

❌ 错误：

销售事实表.销售额 = 数量 × 单价
预测事实表.销售额 = 基于模型的预测值
（定义不一致，但命名相同 - 会导致混淆）

✅ 正确修正：

销售事实表.实际销售额
预测事实表.预测销售额
（定义不同，命名不同）

2.3 一致性维度 (Conformed Dimensions)

定义：在多个事实表中共享的维度，具有相同的属性和含义。

示例：

日期维度 → 被销售、库存、采购事实表共享
产品维度 → 被销售、库存、退货事实表共享
客户维度 → 被销售、服务、营销事实表共享

实现方式：

  1. 物理一致性：多个事实表使用同一个维度表
  2. 逻辑一致性：不同维度表但属性定义一致

好处：

  • ✅ 支持跨业务过程的钻取
  • ✅ 确保分析的一致性
  • ✅ 简化 ETL 开发
  • ✅ 提高用户信任度

第三章：事实表类型详解

3.1 事务事实表 (Transaction Fact Table)

定义：记录业务事件发生时点的度量，每行对应一次业务事务。

特征：

  • ✅ 粒度最细 - 原子级别
  • ✅ 最常见 - 80% 的事实表是事务型
  • ✅ 可稀疏 - 只有事件发生时才有记录
  • ✅ 最灵活 - 支持任意维度的分析

典型应用场景：

  • 销售交易
  • ATM 取款
  • 网站点击
  • 呼叫中心通话记录

表结构示例：销售事务事实表

CREATE TABLE fact_sales (
    -- 维度外键
    date_key INT NOT NULL,
    product_key INT NOT NULL,
    store_key INT NOT NULL,
    customer_key INT NOT NULL,
    promotion_key INT NOT NULL,
    
    -- 退化维度
    transaction_number VARCHAR(20) NOT NULL,
    
    -- 度量
    quantity_sold DECIMAL(10,2),
    sales_amount DECIMAL(12,2),
    discount_amount DECIMAL(12,2),
    cost_amount DECIMAL(12,2),
    profit_amount DECIMAL(12,2),
    
    -- 时间戳
    transaction_timestamp TIMESTAMP,
    
    PRIMARY KEY (date_key, product_key, store_key, transaction_number)
);

数据示例：

日期键产品键门店键交易号销售数量销售额
2024010110015TXN0012200.00
2024010110025TXN0011150.00
2024010110037TXN0025500.00

优点：

  • 📈 支持任意粒度的汇总
  • 📈 保留完整的历史细节
  • 📈 灵活性最高

缺点：

  • 📉 数据量最大
  • 📉 查询汇总时需要实时计算

3.2 周期快照事实表 (Periodic Snapshot Fact Table)

定义：在预定的时间间隔（如每天、每周、每月）记录度量的累计值。

特征：

  • ✅ 固定周期 - 每个周期一行
  • ✅ 可预测 - 行数 = 周期数 × 维度组合数
  • ✅ 稠密表 - 即使没有活动也会有记录
  • ✅ 汇总性 - 已经预先汇总

典型应用场景：

  • 每日库存快照
  • 每月账户余额
  • 每周业绩汇总
  • 每日网站流量统计

表结构示例：每日库存快照事实表

CREATE TABLE fact_inventory_snapshot (
    -- 维度外键
    date_key INT NOT NULL,
    product_key INT NOT NULL,
    warehouse_key INT NOT NULL,
    
    -- 半可加度量（不能跨时间求和）
    quantity_on_hand DECIMAL(10,2),
    quantity_reserved DECIMAL(10,2),
    quantity_available DECIMAL(10,2),
    
    -- 可加度量
    inventory_value DECIMAL(12,2),
    
    -- 不可加度量
    days_of_supply INT,
    turnover_rate DECIMAL(5,2),
    
    PRIMARY KEY (date_key, product_key, warehouse_key)
);

数据示例：

日期产品键仓库键现有库存库存价值周转率
2024-01-0110011500500002.5
2024-01-0210011480480002.6
2024-01-0310011520520002.4

与事务表的对比：

特性事务事实表周期快照事实表
粒度最细（每笔交易）周期汇总
行数最多中等
数据密度稀疏稠密
查询性能需要聚合已预聚合
适用场景详细分析趋势分析

优点：

  • 📈 查询性能好（已预聚合）
  • 📈 便于趋势分析
  • 📈 行数可预测

缺点：

  • 📉 丢失细节信息
  • 📉 灵活性较低
  • 📉 某些度量不可加（如库存）

3.3 累积快照事实表 (Accumulating Snapshot Fact Table)

定义：记录具有明确开始和结束的业务流程，一行代表整个流程的生命周期。

特征：

  • ✅ 流程导向 - 关注业务流程的各个阶段
  • ✅ 多个日期 - 包含多个日期维度（每个里程碑一个）
  • ✅ 可更新 - 随流程推进不断更新
  • ✅ SCD Type 1 - 覆盖更新而非保留历史

典型应用场景：

  • 订单履行流程
  • 制造流程
  • 理赔流程
  • 招聘流程

表结构示例：订单履行累积快照

CREATE TABLE fact_order_fulfillment (
    -- 维度外键
    order_key INT NOT NULL,
    product_key INT NOT NULL,
    customer_key INT NOT NULL,
    
    -- 多个日期维度（流程各阶段）
    order_date_key INT,
    payment_date_key INT,
    shipment_date_key INT,
    delivery_date_key INT,
    
    -- 时间间隔度量（滞后指标）
    payment_lag_days INT,
    shipment_lag_days INT,
    delivery_lag_days INT,
    total_fulfillment_days INT,
    
    -- 其他度量
    order_amount DECIMAL(12,2),
    shipping_cost DECIMAL(12,2),
    
    -- 流程状态
    current_status VARCHAR(20),
    
    PRIMARY KEY (order_key, product_key)
);

生命周期示例：

时间点 1：订单创建

订单键下单日期支付日期发货日期交付日期订单金额状态
100120240101NULLNULLNULL500.00已下单

时间点 2：支付完成

订单键下单日期支付日期发货日期交付日期订单金额状态
10012024010120240102NULLNULL500.00已支付

时间点 3：发货

订单键下单日期支付日期发货日期交付日期订单金额状态
1001202401012024010220240103NULL500.00已发货

时间点 4：交付完成

订单键下单日期支付日期发货日期交付日期订单金额状态
100120240101202401022024010320240105500.00已完成

关键特性：滞后指标

-- 计算各阶段耗时
payment_lag_days = payment_date - order_date
shipment_lag_days = shipment_date - payment_date
delivery_lag_days = delivery_date - shipment_date
total_fulfillment_days = delivery_date - order_date

三种事实表类型对比：

特性事务事实表周期快照累积快照
粒度一次事务一个周期一个流程
时间维度1个1个多个
更新方式只插入只插入插入+更新
行数最多中等最少
典型应用销售、点击库存、余额订单、理赔
分析重点详细活动状态趋势流程效率

3.4 无事实的事实表 (Factless Fact Table)

定义：只记录维度之间的关系，不包含数值型度量的事实表。

两种类型：

类型 1：事件跟踪表

记录事件的发生，即使没有可度量的数字。

示例：学生选课事实表

CREATE TABLE fact_student_enrollment (
    date_key INT NOT NULL,
    student_key INT NOT NULL,
    course_key INT NOT NULL,
    instructor_key INT NOT NULL,
    classroom_key INT NOT NULL,
    
    -- 无数值度量！
    -- 只记录"发生了选课这个事件"
    
    PRIMARY KEY (date_key, student_key, course_key)
);

应用场景：

  • 学生选课记录
  • 员工出勤记录
  • 促销活动覆盖
  • 产品展示事件

分析用途：

-- 虽然没有度量，但可以：
-- 1. 统计选课人数
SELECT course_key, COUNT(*) as student_count
FROM fact_student_enrollment
GROUP BY course_key;

-- 2. 统计某学生选了几门课
SELECT student_key, COUNT(*) as course_count
FROM fact_student_enrollment
GROUP BY student_key;

-- 3. 分析选课模式
SELECT course_key, COUNT(DISTINCT student_key)
FROM fact_student_enrollment
GROUP BY course_key;
类型 2：覆盖表

记录应该发生但可能没发生的事件。

示例：促销覆盖事实表

CREATE TABLE fact_promotion_coverage (
    date_key INT NOT NULL,
    product_key INT NOT NULL,
    store_key INT NOT NULL,
    promotion_key INT NOT NULL,
    
    -- 记录"哪些产品在哪些门店参与了促销"
    -- 即使没有销售也要记录
    
    PRIMARY KEY (date_key, product_key, store_key, promotion_key)
);

分析用途：

-- 分析促销效果：参与促销但未产生销售的情况
SELECT pc.product_key, pc.store_key
FROM fact_promotion_coverage pc
LEFT JOIN fact_sales s 
    ON pc.date_key = s.date_key 
    AND pc.product_key = s.product_key
    AND pc.store_key = s.store_key
WHERE s.sales_amount IS NULL;
-- 找出参与促销但没有销售的产品

无事实表的重要性：

  • 📌 记录"没有发生"的事件（分母）
  • 📌 支持比率和百分比计算
  • 📌 完整的覆盖分析

第四章：维度表设计技术

4.1 维度表的特征

核心特征：

  • 📝 宽表 - 通常包含 50-100 个属性
  • 📝 少行 - 相比事实表行数少得多
  • 📝 文本为主 - 大多数列是描述性文本
  • 📝 单主键 - 通常是代理键（整数）

4.2 代理键 (Surrogate Key)

定义：数据仓库分配的整数主键，与业务键无关。

为什么使用代理键？

业务键的问题代理键的优势
可能变化永不改变
可能是组合键单一整数
可能是字符串整数（JOIN快）
可能为空永不为空
可能很长固定长度

示例对比：

❌ 使用业务键（不推荐）

CREATE TABLE dim_product (
    product_code VARCHAR(20) PRIMARY KEY,  -- 业务键
    product_name VARCHAR(100),
    ...
);

CREATE TABLE fact_sales (
    product_code VARCHAR(20),  -- 字符串JOIN，慢
    ...
);

✅ 使用代理键（推荐）

CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,  -- 代理键（整数）
    product_code VARCHAR(20),     -- 业务键作为属性
    product_name VARCHAR(100),
    ...
);

CREATE TABLE fact_sales (
    product_key INT,  -- 整数JOIN，快
    ...
);

4.3 退化维度 (Degenerate Dimension)

定义：存储在事实表

使用场景：

  • 追溯原始交易
  • 关联相关行项
  • 问题排查

示例查询：

-- 查询某个订单的所有行项
SELECT * 
FROM fact_sales
WHERE invoice_number = 'INV20240101001';

-- 统计每个订单的总金额
SELECT invoice_number, SUM(sales_amount) as order_total
FROM fact_sales
GROUP BY invoice_number;

4.4 维度层次结构 (Dimension Hierarchies)

定义：维度内部的上下级关系，支持钻取分析。

常见层次结构：

地理层次
国家
  └── 省份
       └── 城市
            └── 门店
产品层次
产品类别
  └── 子类别
       └── 品牌
            └── 产品
时间层次
年
  └── 季度
       └── 月
            └── 周
                 └── 日

实现方式：扁平化存储（推荐）

CREATE TABLE dim_geography (
    geography_key INT PRIMARY KEY,
    
    -- 层次结构的所有级别都存储在同一表中
    store_code VARCHAR(20),
    store_name VARCHAR(100),
    city_name VARCHAR(50),
    province_name VARCHAR(50),
    country_name VARCHAR(50),
    
    -- 其他属性
    store_type VARCHAR(20),
    store_size_sqm INT
);

优点：

  • ✅ 查询简单（无需JOIN）
  • ✅ 性能好
  • ✅ 易于理解

⚠️ 避免雪花化： 不要为每个层次创建单独的表！

4.5 缓慢变化维度 (Slowly Changing Dimensions, SCD)

定义：处理维度属性随时间变化的技术。

SCD Type 0：保留原始值

策略：永不改变 应用：原始值、固定参考数据

SCD Type 1：覆盖

策略：直接更新，不保留历史 应用：错误修正、不重要的变化

示例：

-- 客户地址变更（不保留历史）
UPDATE dim_customer
SET address = '新地址'
WHERE customer_key = 123;

优点：简单 缺点：丢失历史，无法回溯分析

SCD Type 2：增加新行（最常用）⭐

策略：插入新行，保留历史记录

表结构：

CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY,      -- 代理键
    customer_id VARCHAR(20),           -- 业务键
    customer_name VARCHAR(100),
    address VARCHAR(200),
    customer_type VARCHAR(20),
    
    -- SCD Type 2 控制字段
    effective_date DATE,               -- 生效日期
    expiration_date DATE,              -- 失效日期
    is_current BOOLEAN,                -- 是否当前版本
    version_number INT                 -- 版本号
);

变化过程示例：

2023-01-01：初始记录

customer_keycustomer_idnameaddresseffective_dateexpiration_dateis_current
1001C001张三北京2023-01-019999-12-31TRUE

2024-06-01：地址变更为上海

customer_keycustomer_idnameaddresseffective_dateexpiration_dateis_current
1001C001张三北京2023-01-012024-05-31FALSE
1002C001张三上海2024-06-019999-12-31TRUE

优点：

  • ✅ 完整保留历史
  • ✅ 支持时点查询
  • ✅ 最准确的历史分析

缺点：

  • 📉 维度表行数增加
  • 📉 ETL 复杂度提高

查询示例：

-- 查询当前版本
SELECT * FROM dim_customer WHERE is_current = TRUE;

-- 查询历史某个时点的状态
SELECT * 
FROM dim_customer
WHERE customer_id = 'C001'
  AND '2024-01-01' BETWEEN effective_date AND expiration_date;
SCD Type 3：增加新列

策略：增加列存储历史值（通常只保留前一个值）

表结构：

CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY,
    customer_id VARCHAR(20),
    customer_name VARCHAR(100),
    
    -- 当前值
    current_address VARCHAR(200),
    current_customer_type VARCHAR(20),
    
    -- 前一个值
    previous_address VARCHAR(200),
    previous_customer_type VARCHAR(20),
    
    -- 变更日期
    last_change_date DATE
);

优点：简单，能比较前后变化 缺点：只能保留有限历史（通常一个版本）

应用场景：需要比较"前后"变化的分析

SCD Type 4：历史表

策略：当前值在主表，历史值在单独的历史表

主表（当前值）：

CREATE TABLE dim_customer_current (
    customer_key INT PRIMARY KEY,
    customer_id VARCHAR(20),
    customer_name VARCHAR(100),
    address VARCHAR(200)
);

历史表：

CREATE TABLE dim_customer_history (
    customer_key INT,
    customer_id VARCHAR(20),
    customer_name VARCHAR(100),
    address VARCHAR(200),
    effective_date DATE,
    expiration_date DATE,
    PRIMARY KEY (customer_key, effective_date)
);
SCD Type 6：混合型（Type 1 + 2 + 3）

策略：结合多种类型的优点

表结构：

CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY,
    customer_id VARCHAR(20),
    customer_name VARCHAR(100),
    
    -- Type 2：历史行
    historical_address VARCHAR(200),
    
    -- Type 3：当前值列
    current_address VARCHAR(200),
    
    -- Type 1：可覆盖的属性
    email VARCHAR(100),
    
    effective_date DATE,
    expiration_date DATE,
    is_current BOOLEAN
);

SCD 类型选择指南：

SCD 类型使用场景推荐度
Type 0永不改变的数据（出生日期）⭐⭐⭐
Type 1错误修正、不重要的变化⭐⭐⭐⭐
Type 2需要完整历史的重要属性⭐⭐⭐⭐⭐
Type 3只需要前后对比⭐⭐⭐
Type 4历史查询频率低⭐⭐
Type 6复杂需求⭐⭐

4.6 角色扮演维度 (Role-Playing Dimensions)

定义：同一个维度表在事实表中扮演不同角色。

最常见：日期维度

CREATE TABLE fact_order (
    order_key INT,
    
    -- 同一个日期维度，三种不同角色
    order_date_key INT,      -- 下单日期
    ship_date_key INT,       -- 发货日期
    delivery_date_key INT,   -- 交付日期
    
    -- 度量
    order_amount DECIMAL(12,2)
);

-- 都指向同一个日期维度表
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,
    full_date DATE,
    year INT,
    quarter INT,
    month INT,
    day_of_week VARCHAR(10)
);

实现方式：

  1. 物理方式：创建视图（推荐）
CREATE VIEW dim_order_date AS SELECT * FROM dim_date;
CREATE VIEW dim_ship_date AS SELECT * FROM dim_date;
CREATE VIEW dim_delivery_date AS SELECT * FROM dim_date;
  1. 逻辑方式：在BI工具中创建别名

其他示例：

  • 发货地址 vs 收货地址（同一地理维度）
  • 发起人 vs 审批人（同一员工维度）
  • 起点 vs 终点（同一地点维度）

4.7 杂项维度 (Junk Dimensions)

定义：将多个低基数的标志和指示器组合到一个维度表中。

问题：事实表中的多个标志字段

-- ❌ 不推荐：事实表中有太多标志字段
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    
    -- 太多标志字段！
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    is_promotion BOOLEAN,
    is_cash_payment BOOLEAN,
    is_member BOOLEAN,
    weather_condition VARCHAR(20),
    
    sales_amount DECIMAL(12,2)
);

解决方案：创建杂项维度

-- ✅ 推荐：杂项维度表
CREATE TABLE dim_transaction_profile (
    profile_key INT PRIMARY KEY,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    is_promotion BOOLEAN,
    is_cash_payment BOOLEAN,
    is_member BOOLEAN,
    weather_condition VARCHAR(20)
);

-- 简化的事实表
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    profile_key INT,  -- 指向杂项维度
    
    sales_amount DECIMAL(12,2)
);

杂项维度的组合： 假设 5 个布尔标志 + 1 个天气（3种可能）

  • 最多组合数：2^5 × 3 = 96 行

优点：

  • ✅ 减少事实表列数
  • ✅ 便于分析标志组合
  • ✅ 提高查询性能

4.8 日期维度设计

日期维度是最重要的维度之一

完整的日期维度表结构：

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,           -- 格式：20240101
    
    -- 基本日期信息
    full_date DATE,
    date_name VARCHAR(20),              -- 2024年1月1日
    
    -- 年相关
    year INT,
    year_name VARCHAR(10),              -- 2024年
    
    -- 季度相关
    quarter INT,
    quarter_name VARCHAR(10),           -- 2024-Q1
    
    -- 月相关
    month INT,
    month_name VARCHAR(20),             -- 2024年1月
    month_abbr VARCHAR(3),              -- Jan
    
    -- 周相关
    week_of_year INT,
    iso_week INT,
    
    -- 日相关
    day_of_month INT,
    day_of_year INT,
    day_of_week INT,                    -- 1=周一
    day_name VARCHAR(10),               -- 星期一
    day_abbr VARCHAR(3),                -- 周一
    
    -- 业务属性
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    holiday_name VARCHAR(50),
    is_working_day BOOLEAN,
    
    -- 财务周期
    fiscal_year INT,
    fiscal_quarter INT,
    fiscal_month INT,
    
    -- 相对日期（用于相对时间分析）
    days_from_today INT,
    weeks_from_today INT,
    
    -- 同期分析
    same_day_last_year_key INT,
    same_day_last_month_key INT
);

数据示例：

date_keyfull_dateyearquartermonthday_nameis_weekendis_holiday
202401012024-01-01202411星期一FALSETRUE
202401022024-01-02202411星期二FALSEFALSE

为什么不用日期类型做主键？

  • ❌ JOIN 性能差（日期比较慢于整数）
  • ❌ 不便于处理未知日期
  • ❌ 不同系统日期格式可能不同

特殊日期处理：

-- 未知日期
INSERT INTO dim_date VALUES (0, NULL, '未知', ...);

-- 尚未发生
INSERT INTO dim_date VALUES (99991231, '9999-12-31', '未来', ...);

第五章：高级维度技术

5.1 维度外展 (Dimension Outriggers)

定义：维度表引用另一个维度表（不推荐，但有时必要）。

示例：

-- 主维度表
CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    brand_key INT,  -- 外展到品牌维度
    ...
);

-- 外展维度表
CREATE TABLE dim_brand (
    brand_key INT PRIMARY KEY,
    brand_name VARCHAR(50),
    brand_manager VARCHAR(50),
    brand_category VARCHAR(50)
);

何时使用：

  • 品牌信息需要单独维护
  • 品牌信息更新频繁
  • 多个维度共享品牌信息

⚠️ 注意：会增加查询复杂度，谨慎使用

5.2 桥接表 (Bridge Tables)

定义：处理多对多关系的辅助表。

场景 1：多值属性

问题：一个客户有多个电话号码

-- ❌ 错误做法：用分隔符
customer_phones VARCHAR(200)  -- '1234,5678,9012'

-- ✅ 正确做法：桥接表
CREATE TABLE bridge_customer_phone (
    customer_key INT,
    phone_number VARCHAR(20),
    phone_type VARCHAR(20),
    PRIMARY KEY (customer_key, phone_number)
);

场景 2：分组维度

问题：分析账户组的销售

-- 账户维度
CREATE TABLE dim_account (
    account_key INT PRIMARY KEY,
    account_name VARCHAR(100)
);

-- 账户组维度
CREATE TABLE dim_account_group (
    group_key INT PRIMARY KEY,
    group_name VARCHAR(50)
);

-- 桥接表：一个账户可以属于多个组
CREATE TABLE bridge_account_group (
    account_key INT,
    group_key INT,
    allocation_percentage DECIMAL(5,2),  -- 分配比例
    PRIMARY KEY (account_key, group_key)
);

使用示例：

-- 查询某个账户组的销售额
SELECT g.group_name, SUM(f.sales_amount * b.allocation_percentage / 100)
FROM fact_sales f
JOIN bridge_account_group b ON f.account_key = b.account_key
JOIN dim_account_group g ON b.group_key = g.group_key
WHERE g.group_name = '重点客户组'
GROUP BY g.group_name;

5.3 多货币处理

场景：跨国企业需要处理多种货币

方案 1：在事实表中存储多种货币

CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    
    -- 本地货币
    local_currency_code VARCHAR(3),
    local_sales_amount DECIMAL(12,2),
    
    -- 报告货币（如美元）
    reporting_currency_code VARCHAR(3),
    reporting_sales_amount DECIMAL(12,2),
    
    -- 汇率
    exchange_rate DECIMAL(10,6)
);

方案 2：货币维度

CREATE TABLE dim_currency (
    currency_key INT PRIMARY KEY,
    currency_code VARCHAR(3),
    currency_name VARCHAR(50)
);

CREATE TABLE fact_exchange_rate (
    date_key INT,
    from_currency_key INT,
    to_currency_key INT,
    exchange_rate DECIMAL(10,6),
    PRIMARY KEY (date_key, from_currency_key, to_currency_key)
);

5.4 多时区处理

挑战：跨时区的业务需要统一时间

解决方案：

CREATE TABLE fact_web_events (
    event_key BIGINT PRIMARY KEY,
    
    -- UTC 时间（标准）
    event_timestamp_utc TIMESTAMP,
    utc_date_key INT,
    utc_time_key INT,
    
    -- 本地时间
    event_timestamp_local TIMESTAMP,
    local_date_key INT,
    local_time_key INT,
    
    -- 时区
    timezone_key INT,
    
    ...
);

CREATE TABLE dim_timezone (
    timezone_key INT PRIMARY KEY,
    timezone_name VARCHAR(50),
    utc_offset INT
);

第六章：特殊场景的维度建模

6.1 实时/准实时分区

挑战：部分数据需要实时更新，部分数据是历史稳定数据

解决方案：分区策略

-- 历史分区（只读）
CREATE TABLE fact_sales_history (
    date_key INT,
    product_key INT,
    ...
) PARTITION BY RANGE (date_key);

-- 当前分区（可更新）
CREATE TABLE fact_sales_current (
    date_key INT,
    product_key INT,
    ...
);

-- 视图合并
CREATE VIEW fact_sales AS
SELECT * FROM fact_sales_history
UNION ALL
SELECT * FROM fact_sales_current;

6.2 大维度表处理

问题：客户维度有数亿行怎么办？

解决方案：

1. 微型维度 (Mini-Dimension) 将频繁变化的属性分离出来

-- 客户基本维度（相对稳定）
CREATE TABLE dim_customer_base (
    customer_key INT PRIMARY KEY,
    customer_id VARCHAR(20),
    customer_name VARCHAR(100),
    birth_date DATE,
    gender VARCHAR(10)
);

-- 客户人口统计微型维度（频繁变化）
CREATE TABLE dim_customer_demographics (
    demographics_key INT PRIMARY KEY,
    age_range VARCHAR(20),
    income_level VARCHAR(20),
    credit_score_range VARCHAR(20)
);

-- 事实表同时引用两个维度
CREATE TABLE fact_sales (
    customer_key INT,
    demographics_key INT,
    ...
);

2. 分片维度 (Shrunken Dimension) 创建简化版本的维度

-- 完整产品维度（数百万行）
CREATE TABLE dim_product_full (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    brand VARCHAR(50),
    category VARCHAR(50),
    ... -- 100个属性
);

-- 简化产品维度（只保留常用属性）
CREATE TABLE dim_product_shrunken (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    brand VARCHAR(50),
    category VARCHAR(50)
    -- 只有最常用的10个属性
);

6.3 稀疏维度处理

问题：某些维度组合很少出现，导致大量空值

解决方案：条件维度

-- 不好的设计
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    promotion_key INT,  -- 大部分时间为空
    ...
);

-- 改进：只在有促销时才关联促销维度
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    promotion_key INT DEFAULT 0,  -- 0表示无促销
    ...
);

-- 促销维度包含"无促销"记录
INSERT INTO dim_promotion VALUES (0, '无促销', ...);

6.4 层次结构的处理

问题：组织架构等层次结构可能很深

解决方案 1：扁平化（推荐）

CREATE TABLE dim_organization (
    org_key INT PRIMARY KEY,
    
    -- 各层级都存储在一行中
    employee_name VARCHAR(100),
    level1_manager VARCHAR(100),
    level2_manager VARCHAR(100),
    level3_manager VARCHAR(100),
    level4_manager VARCHAR(100),
    department VARCHAR(50),
    division VARCHAR(50),
    company VARCHAR(50)
);

解决方案 2：路径枚举

CREATE TABLE dim_organization_hierarchy (
    org_key INT PRIMARY KEY,
    employee_name VARCHAR(100),
    full_path VARCHAR(500),  -- 如：'/公司/事业部/部门/小组/员工'
    level INT,
    parent_key INT
);

附录：维度建模最佳实践总结

✅ 设计原则

  1. 从业务出发 - 理解业务流程，不是技术优先
  2. 保持简单 - 星型模型优于雪花模型
  3. 原子粒度 - 从最细粒度开始设计
  4. 一致性 - 一致性维度和事实贯穿整个企业
  5. 用户友好 - 使用业务术语，易于理解

✅ 事实表设计检查清单

  • 粒度是否明确？
  • 是否选择了正确的事实表类型？（事务/周期快照/累积快照）
  • 度量是否与粒度一致？
  • 是否包含所有相关维度外键？
  • 退化维度是否合理？
  • 事实是否可加/半可加/不可加？

✅ 维度表设计检查清单

  • 是否使用代理键作为主键？
  • 是否包含足够的描述性属性？
  • 是否考虑了缓慢变化维度策略？
  • 层次结构是否扁平化存储？
  • 是否避免了不必要的雪花化？
  • 是否有未知/不适用的默认行？

✅ 常见错误
错误正确做法
❌ 在事实表中存储文本描述✅ 移到维度表
❌ 过度规范化（雪花化）✅ 使用星型模型
❌ 使用业务键做主键✅ 使用代理键
❌ 不处理缓慢变化维度✅ 选择合适的SCD类型
❌ 粒度不明确✅ 明确声明粒度
❌ 事实和维度混淆✅ 区分度量和属性

📊 事实表类型选择指南

决策树：

业务场景是什么？
├── 记录每次交易/事件
│   └── → 事务事实表
├── 需要定期快照（库存、余额）
│   └── → 周期快照事实表
├── 跟踪业务流程（订单、理赔）
│   └── → 累积快照事实表
└── 只记录事件发生（无度量）
    └── → 无事实事实表

📊 SCD 类型选择指南

决策树：

属性会变化吗？
├── 否
│   └── → SCD Type 0
└── 是
    ├── 需要保留完整历史吗？
    │   ├── 是 → SCD Type 2 (最常用)
    │   └── 否
    │       ├── 只需要前后对比？
    │       │   └── → SCD Type 3
    │       └── 不需要历史
    │           └── → SCD Type 1
    └── 历史查询很少？
        └── → SCD Type 4

🎯 设计模式速查

场景推荐方案章节
基本维度建模星型模型1.3
记录每次交易事务事实表3.1
定期快照周期快照事实表3.2
业务流程跟踪累积快照事实表3.3
无数值度量无事实事实表3.4
保留完整历史SCD Type 24.5
一维多角色角色扮演维度4.6
多个标志字段杂项维度4.7
多对多关系桥接表5.2
大维度表微型维度/分片维度6.2

实战案例

案例 1：电商销售数据仓库

业务过程：在线销售

粒度：每个订单行项

维度：

  • 日期维度（下单日期）
  • 产品维度
  • 客户维度
  • 促销维度
  • 支付方式维度

事实：

  • 销售数量
  • 销售金额
  • 折扣金额
  • 成本
  • 利润

模型设计：

sql

-- 事实表
CREATE TABLE fact_online_sales (
    date_key INT,
    product_key INT,
    customer_key INT,
    promotion_key INT,
    payment_method_key INT,
    
    order_number VARCHAR(20),  -- 退化维度
    line_number INT,           -- 退化维度
    
    quantity DECIMAL(10,2),
    sales_amount DECIMAL(12,2),
    discount_amount DECIMAL(12,2),
    cost_amount DECIMAL(12,2),
    profit_amount DECIMAL(12,2),
    
    PRIMARY KEY (order_number, line_number)
);

案例 2：银行账户快照

业务过程：账户状态跟踪

粒度：每天每个账户的状态

事实表类型：周期快照

模型设计：

sql

CREATE TABLE fact_account_snapshot (
    date_key INT,
    account_key INT,
    customer_key INT,
    branch_key INT,
    
    -- 半可加事实
    opening_balance DECIMAL(15,2),
    closing_balance DECIMAL(15,2),
    
    -- 可加事实
    total_deposits DECIMAL(12,2),
    total_withdrawals DECIMAL(12,2),
    service_charges DECIMAL(10,2),
    interest_earned DECIMAL(10,2),
    
    -- 不可加事实
    average_daily_balance DECIMAL(15,2),
    
    PRIMARY KEY (date_key, account_key)
);

sql

CREATE TABLE dim_organization_hierarchy (
    org_key INT PRIMARY KEY,
    employee_name VARCHAR(100),
    full_path VARCHAR(500),  -- 如：'/公司/事业部/部门/小组/员工'
    level INT,
    parent_key INT
);

附录：维度建模最佳实践总结

✅ 设计原则

  1. 从业务出发 - 理解业务流程，不是技术优先
  2. 保持简单 - 星型模型优于雪花模型
  3. 原子粒度 - 从最细粒度开始设计
  4. 一致性 - 一致性维度和事实贯穿整个企业
  5. 用户友好 - 使用业务术语，易于理解

✅ 事实表设计检查清单

  • 粒度是否明确？
  • 是否选择了正确的事实表类型？（事务/周期快照/累积快照）
  • 度量是否与粒度一致？
  • 是否包含所有相关维度外键？
  • 退化维度是否合理？
  • 事实是否可加/半可加/不可加？

✅ 维度表设计检查清单

  • 是否使用代理键作为主键？
  • 是否包含足够的描述性属性？
  • 是否考虑了缓慢变化维度策略？
  • 层次结构是否扁平化存储？
  • 是否避免了不必要的雪花化？
  • 是否有未知/不适用的默认行？

✅ 常见错误

错误正确做法
❌ 在事实表中存储文本描述✅ 移到维度表
❌ 过度规范化（雪花化）✅ 使用星型模型
❌ 使用业务键做主键✅ 使用代理键
❌ 不处理缓慢变化维度✅ 选择合适的SCD类型
❌ 粒度不明确✅ 明确声明粒度
❌ 事实和维度混淆✅ 区分度量和属性

📊 事实表类型选择指南

决策树：

业务场景是什么？
├── 记录每次交易/事件
│   └── → 事务事实表
├── 需要定期快照（库存、余额）
│   └── → 周期快照事实表
├── 跟踪业务流程（订单、理赔）
│   └── → 累积快照事实表
└── 只记录事件发生（无度量）
    └── → 无事实事实表

📊 SCD 类型选择指南

决策树：

属性会变化吗？
├── 否
│   └── → SCD Type 0
└── 是
    ├── 需要保留完整历史吗？
    │   ├── 是 → SCD Type 2 (最常用)
    │   └── 否
    │       ├── 只需要前后对比？
    │       │   └── → SCD Type 3
    │       └── 不需要历史
    │           └── → SCD Type 1
    └── 历史查询很少？
        └── → SCD Type 4

🎯 设计模式速查

场景推荐方案章节
基本维度建模星型模型1.3
记录每次交易事务事实表3.1
定期快照周期快照事实表3.2
业务流程跟踪累积快照事实表3.3
无数值度量无事实事实表3.4
保留完整历史SCD Type 24.5
一维多角色角色扮演维度4.6
多个标志字段杂项维度4.7
多对多关系桥接表5.2
大维度表微型维度/分片维度6.2
# 人工智能 # 数据仓库 # 学习 # 大数据 # 个人开发 # apache
Logo
亚马逊云科技技术品牌专区

更多推荐

open-code-review Codex 插件教程：对话式调用评审技能指南

本文是一份面向新手的 **open-code-review Codex 插件教程**，教你在 Codex 中通过对话式方式调用 open-code-review 的 AI 代码评审技能。open-code-review 是阿里巴巴开源的 AI 代码评审工具，采用"确定性流水线 + LLM Agent"混合架构，内置多语言评审规则（NPE、线程安全、XSS、SQL 注入等），可在 Codex 里用一

avatar 亚马逊云科技技术品牌专区

OBS Studio 完整指南：免费开源录屏与直播软件从零上手

想做一场像模像样的直播，或者把屏幕内容录成教程视频，但一想到商用软件的订阅费就有些犹豫？其实你不需要花一分钱。OBS Studio 是一款免费开源的直播与录屏软件，支持 Windows、macOS 和 Linux，采集、混音、编码、推流、录制，一条流水线全包了。## 先搞清楚 OBS Studio 是什么OBS Studio 是一个"场景化"的采集合成工具：你把摄像头、窗口、浏览器、图片、

avatar 亚马逊云科技技术品牌专区

如何用MediaCrawler爬取小红书抖音数据？7大平台爬虫从安装到实战完整指南

MediaCrawler 是一个支持小红书、抖音、快手、B站、微博、百度贴吧、知乎的开源自媒体数据采集工具。它能按关键词、按帖子、按创作者主页三种方式抓取笔记/视频内容、评论甚至二级评论，并把结果存成 JSON、CSV、Excel 或数据库。适合做市场调研、竞品监控、内容选题的运营人员，以及想要现成爬虫框架而不想从零写 JS 逆向的开发者。## 从一次"手动抄数据"说起想象这个场景：你负责

avatar 亚马逊云科技技术品牌专区