设计电商销售数据仓库的维度模型

题目

设计电商销售数据仓库的维度模型

信息

• 类型：问答
• 难度：⭐⭐

考点

维度建模,星型/雪花模式设计,缓慢变化维处理,ETL策略

快速回答

设计电商销售数据仓库的核心要点：

• 事实表：销售事实表（包含订单ID、产品ID、客户ID、日期ID、销售额、数量等度量值）
• 维度表：时间维度、产品维度、客户维度、店铺维度
• SCD处理：客户维度采用Type 2缓慢变化维（添加生效/失效日期）
• 优化策略：使用星型模式，分区事实表，建立日期维度索引

1. 核心设计原理

维度建模是数据仓库的核心方法论，通过事实表（存储业务过程度量值）和维度表（存储业务上下文）构建：

• 星型模式：维度表直接关联事实表（推荐）
• 雪花模式：维度表存在层级规范化（增加查询复杂度）

2. 模型设计示例

-- 事实表：销售事实
CREATE TABLE fact_sales (
    sale_id BIGINT,
    date_id INT,          -- 外键关联dim_date
    product_id INT,       -- 外键关联dim_product
    customer_id INT,      -- 外键关联dim_customer
    store_id INT,         -- 外键关联dim_store
    amount DECIMAL(10,2), -- 销售额
    quantity INT,         -- 销售数量
    discount DECIMAL(5,2) -- 折扣
);

-- 维度表：客户维度（SCD Type 2）
CREATE TABLE dim_customer (
    customer_id INT,
    customer_key INT,     -- 代理键（自增ID）
    name VARCHAR(100),
    address VARCHAR(200),
    tier VARCHAR(20),     -- 客户等级
    start_date DATE,      -- 记录生效日期
    end_date DATE,        -- 记录失效日期
    is_current BOOLEAN    -- 是否当前有效
);

3. 最佳实践

• SCD策略选择：
  • Type 1：覆盖历史（适用于不重要的属性变更）
  • Type 2：保留历史（客户地址/等级变更）
  • Type 3：添加新列（极少使用）
• 性能优化：
  • 对事实表按日期分区
  • 在维度表外键上建立位图索引
  • 预聚合高频指标（如每日销售额）
• ETL设计：
  • 增量加载事实表（通过时间戳过滤）
  • 使用MD5校验维度变更

4. 常见错误

• 过度规范化导致雪花模式（增加查询复杂度）
• 忽略SCD处理导致历史数据失真
• 在事实表中存储文本描述（应存维度键）
• 未建立代理键导致业务键变更问题

5. 扩展知识

• 渐变维度（SCD）：处理维度属性随时间变化的策略
• 退化维度：将订单号等直接存入事实表
• 一致性维度：跨主题域共享维度（如统一日期维度）
• 现代演进：Data Vault模型适用于易变需求，但查询更复杂