数据仓库实战（二）数据仓库DIM层建设指南（实战步骤+案例解析）

数据仓库维度层(DIM层)是连接数据仓库ODS原始数据与后续分析层的关键桥梁。

一、DIM层的基本概念与作用

1、什么是维度层（DIM层）

维度层是数据仓库架构中专门用于存储和管理维度数据的层次，位于ODS层之上，为DWD、DWS和ADS层提供标准化的维度信息。维度数据描述业务实体的属性特征，如商品、用户、时间、地理位置等，它们与事实表(如订单、支付)结合，构成完整的业务分析视图。

2、DIM层的核心作用

提供统一维度视图

标准化企业维度定义，解决源系统维度不一致问题数据质量保障
对维度数据进行清洗、去重和一致性校验提升查询效率
预先整合维度信息，减少复杂分析时的表连接操作支持历史追溯
记录维度变化历史，支持不同时间点的历史分析降低冗余度
避免在各层重复维护维度属性信息

3、典型的DIM层表类型

DIM层表主要分为两大类：常规维度表

1）如商品维度表、品牌维度表等，一般采用全量更新模式缓慢变化维度(SCD)

2）记录随时间变化的维度属性，主要有三种类型：

SCD Type 1：直接覆盖更新，不保留历史
SCD Type 2：保留历史版本，通过有效期标记区分
SCD Type 3：保留有限历史，通过额外字段存储

二、DIM层表设计原则与策略

1、表设计原则

用户地址信息是电商系统的重要基础数据，需要支持：

包含维度的所有关键属性，满足分析需求一致性
提供统一标准的维度定义，消除歧义稳定性
维度设计应具备稳定性，避免频繁变更可用性
优化查询效率，支持高并发访问可追溯性
对于关键维度，保留历史变更记录

2、更新策略选择

根据维度变化特性选择合适的更新策略：

适用于变化频率低、数据量小的维度，如商品类目增量更新
适用于持续新增的维度，如新商品拉链表
设计适用于需要保留历史版本的维度，如用户属性变化

三、案例分析：商品维度表实现

1、表结构设计

以我们项目中的商品维度表(dim_sku_full)为例：

CREATE TABLE dim.dim_sku_full
(
    `id`                   VARCHAR(255) COMMENT 'SKU ID，商品唯一标识',
    `k1`                   DATE NOT NULL COMMENT '分区字段，数据日期',
    `price`                DECIMAL(16, 2) COMMENT '商品价格，单位元',
    `sku_name`             STRING COMMENT '商品名称，展示用',
    -- 其他商品属性字段
    `category3_id`         STRING COMMENT '三级分类ID，最细粒度的商品分类',
    `category3_name`       STRING COMMENT '三级分类名称，如"休闲男鞋"',
    `category2_id`         STRING COMMENT '二级分类ID，中间层级商品分类',
    `category2_name`       STRING COMMENT '二级分类名称，如"男鞋"',
    `category1_id`         STRING COMMENT '一级分类ID，顶层商品分类',
    `category1_name`       STRING COMMENT '一级分类名称，如"鞋靴"',
    `tm_id`                STRING COMMENT '品牌ID，品牌唯一标识',
    `tm_name`              STRING COMMENT '品牌名称，如"Nike"、"Adidas"',
    -- 扩展属性字段
    `attr_ids`             ARRAY<int(11)> COMMENT '平台属性ID集合',
    `sale_attr_ids`        ARRAY<int(11)> COMMENT '销售属性ID集合',
    `create_time`          STRING COMMENT '创建时间，商品首次录入时间'
)
    ENGINE=OLAP
UNIQUE KEY(`id`,`k1`) -- 使用商品ID和日期作为联合主键
PARTITION BY RANGE(`k1`) () -- 按日期范围分区
DISTRIBUTED BY HASH(`id`) -- 按商品ID哈希分布

2、设计特点分析

宽表设计：整合了商品所有相关维度信息(SKU基本信息、分类信息、品牌信息)

多级分类：保留一、二、三级分类的ID和名称，便于不同粒度的分析

分区策略：按日期分区，支持历史版本管理和数据生命周期管理

复合主键：通过商品ID和日期组合，确保每天每个商品只有一个状态

四、案例分析：用户维度拉链表实现

1、表结构设计

对于变化较为频繁且需要历史追溯的维度，如用户信息，我们采用了拉链表设计。

CREATE TABLE dim.dim_user_zip
(
    `id`           VARCHAR(64) COMMENT '用户ID，用户唯一标识',
    `k1`           DATE NOT NULL COMMENT '分区字段，数据日期',
    `login_name`   STRING COMMENT '用户登录名，账号名称',
    `nick_name`    STRING COMMENT '用户昵称，用户自定义展示名',
    `name`         STRING COMMENT '用户真实姓名，已加密',
    -- 其他用户属性字段
    `start_date`   STRING COMMENT '开始日期，当前版本生效开始日期',
    `end_date`     STRING COMMENT '结束日期，当前版本失效日期，9999-12-31表示当前有效版本'
)

2、拉链表处理逻辑

拉链表的核心在于ETL处理逻辑，需要处理两种情况：初始化和增量更新。

2.1、初始化逻辑

-- 用户维度拉链表初始化插入（设置历史起点）
insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
select
    id,                       -- 用户ID
    k1,                       -- 分区日期
    login_name,               -- 用户登录名
    -- 其他字段
    md5(name),                -- 用户真实姓名（MD5加密处理）
    md5(phone_num),           -- 手机号码（MD5加密处理）
    md5(email),               -- 电子邮箱（MD5加密处理）
    -- 其他字段
    '2020-06-14' start_date,  -- 拉链起始日期（业务起始日期）
    '9999-12-31' end_date     -- 拉链结束日期（当前有效版本标记）
from ods.ods_user_info_full;

2.2、增量更新逻辑

拉链表的增量更新是其核心价值所在，我们来看实际项目中的逻辑代码：

insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
with
    tmp as
    (
        select
            -- 当前拉链表最新有效数据
            old.id old_id,                    -- 原用户ID
            -- 其他old字段
            old.end_date old_end_date,        -- 原版本结束日期

            -- 新增数据(当日变化)
            new.id new_id,                    -- 新用户ID
            -- 其他new字段
            new.end_date new_end_date         -- 新版本结束日期
        from
            -- 查询当前拉链表中最新有效记录
            (
                select * from dim.dim_user_zip
                where end_date = '9999-12-31'  -- 筛选当前有效版本
            ) old
        -- 使用FULL OUTER JOIN合并新旧数据
        full outer join
            -- 当日增量数据处理
            (
                select
                    cast(t1.id as VARCHAR(64)) as id,  -- 转换用户ID类型
                    -- 其他字段处理
                    md5(t1.name) as name,            -- 敏感信息加密
                    -- 其他字段
                    '2024-06-15' as start_date,       -- 新版本开始日期
                    '9999-12-31' as end_date          -- 新版本结束日期
                from
                    (-- 取每个用户最新记录
                        select id, k1, ... , row_number() over (partition by id order by create_time desc) rn
                        from ods.ods_user_info_full
                    ) t1
                where rn=1  -- 只取每个用户的最新记录
            ) new
        on old.id=new.id  -- 按用户ID关联
    )

-- 查询1：处理有变更的用户数据，生成新版本记录
select
    if(new_id is not null, new_id, old_id),         -- 用户ID
    -- 其他字段选择逻辑
    if(new_id is not null, new_start_date, old_start_date), -- 版本开始日期
    if(new_id is not null, new_end_date, old_end_date)      -- 版本结束日期
from tmp
where k2 is not NULL  -- 只处理有新分区数据的记录

union all

-- 查询2：处理原记录的历史版本化，更新结束日期
select
    old_id,                       -- 用户ID
    -- 其他原字段
    old_start_date,               -- 版本开始日期
    cast(date_add(date('${pdate}'), -1) as string) old_end_date  -- 更新版本结束日期为当前日期前一天
from tmp
where k1 is not NULL    -- 有原分区数据
  and old_id is not null  -- 有原用户ID
  and new_id is not null; -- 同时有新用户ID，说明是变更记录

3、拉链表更新原理解析

拉链表的核心在于ETL拉链表更新的核心原理在于：保留当前记录

将原有记录的结束日期修改为变更前一天插入新版本
创建新记录，开始日期为变更当天，结束日期为'9999-12-31'新增用户处理
对于新增的用户，直接插入记录，无需处理历史版本使用FULL JOIN
确保既能处理变更用户，也能处理新增用户

DIM层作为数据仓库的关键组成部分，其设计质量直接影响整个数据仓库的可用性和分析效率。通过正确选择维度表类型、设计合理的更新策略，以及实施有效的优化措施，可以构建出高质量的维度层，为上层分析提供坚实基础。