数据仓库逻辑数据模型实战：中国移动经分系统ETL与维度建模解析

简介：这是一份面向电信行业数据仓库从业者、企业级BI架构师及信息化负责人的逻辑数据模型参考文档，以中国移动经营分析系统为载体，系统讲解数据仓库在大型运营商中的落地方式。文档围绕数据仓库架构、ETL流程、维度建模、数据集市与商业智能、性能优化、数据安全治理及持续集成更新等核心主题展开，重点剖析星型/雪花型模型、客户账户通话记录等实体关系，并给出逻辑模型与物理实现间的设计思路，适合用于理解运营商级数据建模规范与经营分析场景。资源为1个PDF文件，共64页，压缩包大小13.07MB，内容结构完整，可直接用于方案设计、内部培训或课程案例参考。目前已有221人学习/下载，对于构建企业级数据仓库或开展电信行业经营分析项目具有较高参考价值，读者可据此掌握从源系统抽取整合到多维分析展现的完整链条。

1. 一套支撑经营分析的逻辑数据模型，到底拆成了什么

中国移动经营分析系统要回答的从来不是"这个月收入多少"这种单点问题，而是"某用户从语音套餐换成流量套餐后，离网概率是否下降""某地市基站故障对投诉量的影响有多大"这类跨域联合分析。支撑这些分析的不是CRM或计费系统，而是一个面向主题、集成历史数据的专用数据仓库。这份64页的逻辑数据模型说明文档，描述的就是中国移动如何把分散在几十个OLTP源系统中的数据，组织成客户、账户、产品、事件、账务等主题域，再通过维度建模变成可分析的星型结构。它不涉及具体服务器配置，但所有ETL和报表都围绕这套逻辑模型展开。适合正在做电信、金融类数据建模的数仓工程师，也适合想理解大型企业如何落地数据仓库逻辑模型的产品和技术负责人。

2. 从计费流水到分析主题：ETL分层与数据装载

2.1 数据仓库为什么要分层，而不是直接灌入报表

中国移动的源系统包括计费、CRM、客服、网管等几十个OLTP系统。如果让报表直接查源库，高峰期计费系统的压力会被分析查询直接放大。数仓分层的核心意义是把操作型环境与分析型环境剥离开，形成ODS、DWD、DWS、ADS四层结构。ODS层保留源系统原始数据，DWD层清洗为明细事实，DWS层汇总为公共指标，ADS层面向具体分析场景或部门集市。

在逻辑数据模型文档中，分层会映射到不同主题域：客户域、产品域、事件域、账务域、营销域。每个域都有自己的实体定义、属性字典和关系约束。物理实现上普遍使用Oracle、GaussDB或Teradata这类MPP数据库，但逻辑模型与物理模型分离，业务人员看到的仍是统一的数据字典。设计分层时要注意，不是所有源系统数据都需要进入ODS，比如网管系统的性能指标只保留必要的监控数据，否则ODS会变成垃圾堆。

2.2 ETL装载流程：从抽取到加载的完整步骤

我一般把ETL拆成四步：抽取、清洗、转换、加载。抽取阶段从源系统增量获取数据，通常以时间戳或序列号作为增量标识。例如计费系统的通话详单表，以create_time作为增量字段；CRM系统的客户资料表以last_update_time作为增量字段。清洗阶段处理空值、格式错误、编码不一致。比如性别字段源系统有"1/2"和"M/F"两套编码，需要统一映射。转换阶段按逻辑模型映射，把源字段转换为目标实体字段，同时生成维度代理键。加载阶段写入目标表，采用delete+insert或者merge方式。

下面是一段典型的ETL转换SQL示例，把计费详单装载到DWD层通话事实表：

-- 从ODS计费详单表装载DWD层通话事实表 INSERT INTO dwd_call_fact ( call_id, -- 通话主键 identity_sk, -- 客户代理键，关联dim_customer acct_sk, -- 账户代理键 call_time, -- 业务时间 call_duration, -- 通话时长（秒） roaming_flag, -- 是否漫游 source_sys_cd, -- 源系统编码 load_time -- 装载时间 ) SELECT t.call_detail_id, -- 源流水号 c.identity_sk, -- 通过身份证号关联维度表取得代理键 a.acct_sk, TO_DATE(t.start_time, 'YYYY-MM-DD HH24:MI:SS'), t.call_duration, CASE WHEN t.roam_city_id != t.home_city_id THEN 1 ELSE 0 END, 'BOSS', -- 源系统编码 SYSDATE FROM ods_call_detail t LEFT JOIN dim_customer c ON t.id_no = c.id_no AND c.current_flag = 'Y' LEFT JOIN dim_account a ON t.bill_no = a.bill_no AND a.current_flag = 'Y' WHERE t.create_time >= '${biz_date}' AND t.create_time < '${biz_date_next}' AND t.load_status = '0'; -- 只装载未处理数据

这段SQL的关键在于通过LEFT JOIN将源系统业务主键映射为维度表代理键。如果源数据没有匹配到客户或账户，identity_sk会得到NULL，后续分析需要决定是丢弃还是归入"未知"维度。${biz_date}是调度参数，由批处理框架传入。call_duration用ROAM标志清洗，把漫游判断逻辑提前到ETL，而不是留给下游报表。load_status是ODS表中的装载标记，跑完更新为'1'，防止重复装载。

2.3 装载频率、历史保留与安全控制

电信数据量随业务增长很快。通常每日凌晨跑昨日增量，日志类数据（如上网日志、信令）使用小时级装载。历史保留策略依赖业务需求：通话详单保留24个月，账务数据保留36个月，客户维表保留全量历史版本。逻辑模型文档中会定义生命周期管理规则，比如按月分区，超过保留期的分区直接drop。注意，drop分区前需要确认下游数据集市没有依赖该分区的物化视图。

数据安全从ETL阶段就要介入。ODS层通常只保留有限字段，敏感字段如身份证号、手机号加密存储。ETL日志中不能打印全量身份证号。访问控制上，不同岗位通过角色只能读取特定主题域。行级权限通过安全标签或视图实现，比如某省分析人员只能看到本省数据。这部分在逻辑模型中不直接体现，但物理建模时要预留prov_id、area_id等区域字段作为权限过滤键。

3. 维度建模与逻辑模型落地：星型/雪花、代理键与粒度控制

3.1 主题域与实体定义

逻辑数据模型里最核心的部分是实体、属性和关系的定义。中国移动经营分析系统的典型主题域包括：

这些实体间的关系用ER图描述。客户与账户是1:N，账户与账单是1:N，客户与产品是M:N。M:N关系在数据仓库中通常拆成事实表或桥接表，避免查询时产生笛卡尔积。设计实体时建议每个域都包含create_time、update_time、source_sys_cd这几个审计字段，方便追溯数据来源。

3.2 星型模型和雪花模型，怎么选

逻辑模型最终要物理化。大多数分析场景选择星型模型，比如通话事实表边上直接挂dim_customer和dim_time。星型查询路径短、易理解，适合OLAP。雪花模型把维度表进一步规范化，比如把客户维度拆成客户表、地址表、区域表，减少冗余但增加关联复杂度。中国移动这类大型数仓，实际常用的是"星型为主、适度雪花化"：公共维度（时间、产品、区域）用雪花，业务专用维度用星型。比如区域维度单独一张表，客户维度只保留area_id外键，这样既避免维度表过度冗余，又不会像全雪花那样让查询关联七八张表。

3.3 代理键策略与缓慢变化维度

逻辑模型中会定义业务主键，但物理事实表建议使用代理键，即自增整数或序列。原因有三个：业务主键可能被复用，比如用户ID注销后重新发放；业务主键是复合结构，关联时性能差；维度属性变化时代理键可以标记历史版本。对于客户地址变化这类缓慢变化维度，常用SCD2策略。下面是用MERGE维护SCD2维度的示例：

MERGE INTO dim_customer c USING ( SELECT 'C10001' AS cust_id, '张三' AS cust_name, '北京' AS addr, DATE '2025-01-10' AS eff_date FROM DUAL ) s ON (c.cust_id = s.cust_id AND c.current_flag = 'Y') WHEN MATCHED THEN UPDATE SET c.exp_date = s.eff_date - 1, c.current_flag = 'N' WHERE c.addr != s.addr; -- 属性变化才失效旧记录 INSERT INTO dim_customer (cust_sk, cust_id, cust_name, addr, eff_date, exp_date, current_flag) VALUES (seq_cust.nextval, s.cust_id, s.cust_name, s.addr, s.eff_date, TO_DATE('9999-12-31','YYYY-MM-DD'), 'Y');

这段MERGE的逻辑是：先关闭当前有效记录，再插入新版本。eff_date是生效日期，exp_date是失效日期，current_flag='Y'表示当前版本。注意WHERE子句只更新实际发生变化的行，否则每次装载都会生成新版本，维度表会快速膨胀。合并后老版本通过exp_date保留历史，查询时用WHERE current_flag='Y'过滤当前数据。

3.4 事实表的粒度与度量

事实表的粒度是最容易被忽略的设计点。通话事实表的最小粒度是一张通话详单，但报表只需要汇总到日、客户、区域。不建议在底层明细表上频繁GROUP BY，建议把明细事实表和汇总事实表分开建模。逻辑模型文档中会区分基本事实表、过渡事实表和累计快照表。设计时先明确粒度，再定义度量。比如通话时长是可加度量，但ARPU（每用户平均收入）是半可加度量，不能直接按客户数求平均。遇到这种情况，事实表中先保存收入和客户数两个可加度量，ARPU在报表层计算。

4. 数据集市与查询提速：分区、索引、物化视图的取舍

4.1 面向部门的数据集市与逻辑模型的关系

逻辑模型是全局统一的，数据集市是针对特定部门或分析主题的定制视图。中国移动的经分系统为市场部、客服部、网络部提供不同数据集市。这些数据集市通常基于逻辑模型中的部分实体重构，比如营销分析集市只涉及客户、产品、渠道、营销活动事实。物理实现时，数据集市表直接物化出来，而不是动态视图，因为BI工具频繁对底层大表计算代价太高。建数据集市时要注意维度一致性，不同集市对"客户"的定义要相同，否则做跨集市分析时维度对不齐。

4.2 分区策略：按时间分区是基础，组合分区是常态

对于TB级的通话事实表，按月RANGE分区是最基本的要求。但仅按时间分区远不够，电信数仓常见做法是"月分区+客户哈希子分区"或者"月分区+区域列表分区"。组合分区可以让WHERE条件裁剪掉更大范围的数据。比如分析某地市某月用户行为，如果区域不是分区键，扫描会跨全部分区。一个典型的分区建表语句：

CREATE TABLE dwd_call_fact ( call_id NUMBER(16), identity_sk NUMBER(10), call_time DATE, call_duration NUMBER(10), area_id NUMBER(6) ) PARTITION BY RANGE (call_time) SUBPARTITION BY HASH (identity_sk) SUBPARTITIONS 16 ( PARTITION p202501 VALUES LESS THAN (TO_DATE('2025-02-01','YYYY-MM-DD')), PARTITION p202502 VALUES LESS THAN (TO_DATE('2025-03-01','YYYY-MM-DD')) );

call_time做主分区，identity_sk做哈希子分区。查询条件里同时指定了月份和客户ID时，数据库可以同时裁剪掉不相关的月分区和哈希子分区。注意：不要对分区键做函数运算，WHERE TO_CHAR(call_time,'YYYYMM')='202501'会导致分区裁剪失效，只能全分区扫描。

4.3 索引的取舍：位图索引 vs B树索引

OLTP系统中普遍使用B树索引，但在数据仓库里，位图索引对低基数列更有效。比如roaming_flag只有0和1两个值，B树索引扫描会返回大量行，访问代价很高。位图索引则能快速做COUNT和AND/OR组合判断。创建位图索引的语句：

CREATE BITMAP INDEX idx_call_roaming ON dwd_call_fact(roaming_flag);

位图索引的缺点是高并发DML时锁开销很大。所以一般在数据加载完成后、查询时段创建，或者只建在只读分区上。对于高基数列如identity_sk，B树索引仍然有效，但要注意选择率：如果查询返回超过全表5%的行，索引扫描可能比全表扫描更慢。这时候不如用物化视图先聚合。

4.4 物化视图刷新方式

数据集市常用的物化视图是预先计算好的汇总表。比如每日需要计算各省、各月收入，可以定义物化视图，ETL完成后统一刷新。语法示例：

CREATE MATERIALIZED VIEW mv_province_rev REFRESH FAST ON DEMAND AS SELECT prov_id, TRUNC(bill_date,'MM') AS month, SUM(amount) AS revenue FROM dwd_bill_fact GROUP BY prov_id, TRUNC(bill_date,'MM'); -- 每日ETL后执行 EXEC DBMS_MVIEW.REFRESH('mv_province_rev', 'F');

REFRESH FAST依赖物化视图日志，所以基表上需要创建LOG结构。如果底层表每天增量很小，FAST能明显缩短刷新时间；但如果负载包含大量DELETE和UPDATE，维护日志的成本可能超过全量重算。我一般先在测试环境对比FAST和COMPLETE的耗时，增量占全量5%以内才选FAST。刷新时要注意事务隔离，避免报表看到半个刷新周期的数据。

4.5 并行查询、资源控制与数据脱敏

分析查询经常遇到长任务。在MPP或Oracle RAC环境下，可以用SELECT /*+ PARALLEL(8) */ ...提示并行度。但并行度不是越高越好，高并发查询会抢占ETL时段的CPU和IO。建议通过资源组限制：报表用户组并发8，离线跑批走批处理队列。逻辑模型文档中会有意识地设计prov_id、area_id等字段，这些字段既是业务属性，也是脱敏和行级权限的过滤键。比如广东的分析人员登录后，通过安全视图自动把prov_id限制为'GD'，对未授权人员，手机号在查询层直接脱敏为138****1234。

5. 增量更新与模型验证：从逻辑模型落地到物理建模

5.1 增量捕获方式与装载窗口的控制

增量加载方式决定了数仓时效性。常见三种：时间戳增量、日志增量（Oracle CDC、Binlog等）、全量对比。时间戳最简单，但源表如果没有最后更新时间字段，就需要额外维护快照表。日志增量能捕获删除和更新，但需要部署同步组件。经营分析系统里，账务数据用时间戳+状态位做增量，因为账单生成后很少变更；客户资料用全量快照+SCD2维护，因为客户数据量相对小，全量对比成本可控。

5.2 刷新失败与断点续跑

批处理最怕跑到一半失败。一个可靠的做法是：目标表先写入临时表，验证完整后再切换分区。比如按天分区，先装载到tmp_dwd_call_fact，再用EXCHANGE PARTITION交换到正式表。伪代码：

-- 创建临时表并装载 CREATE TABLE tmp_dwd_call_fact AS SELECT * FROM dwd_call_fact WHERE 1=0; INSERT INTO tmp_dwd_call_fact SELECT ... FROM ods_call_detail WHERE load_time >= ...; -- 交换分区 ALTER TABLE dwd_call_fact EXCHANGE PARTITION p202502 WITH TABLE tmp_dwd_call_fact;

交换时临时表和正式表结构必须完全一致，包括分区键约束。交换成功后临时表变成旧数据，直接DROP即可。如果ETL中途失败，正式表仍是上一版本，不影响白天报表。

5.3 逻辑模型验证技巧：用一条SQL检查维度外键完整性

模型设计完成后，验证比建表更重要。一个容易踩的坑是：事实表的代理键在维度表中不存在对应记录。可以用ANTI JOIN快速检查：

SELECT 'call_fact' AS fact_table, count(*) AS orphan_rows FROM dwd_call_fact f LEFT JOIN dim_customer c ON f.identity_sk = c.identity_sk WHERE c.identity_sk IS NULL UNION ALL SELECT 'call_fact_to_time', count(*) FROM dwd_call_fact f LEFT JOIN dim_time t ON f.call_time_sk = t.time_sk WHERE t.time_sk IS NULL;

正常返回结果应为0。如果出现orphan_rows大于0，通常意味着源系统字段变更或ETL映射规则失效。把这句验证SQL挂到每日调度末尾，一旦告警立即检查源表元数据，比事后发现报表数据对不上要快得多。这是我在实际项目中做逻辑模型验收的最后一道保险。

本文还有配套的精品资源，点击获取