简介这是一份面向电信行业数据仓库从业者、企业级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两套编码需要统一映射。转换阶段按逻辑模型映射把源字段转换为目标实体字段同时生成维度代理键。加载阶段写入目标表采用deleteinsert或者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 主题域与实体定义逻辑数据模型里最核心的部分是实体、属性和关系的定义。中国移动经营分析系统的典型主题域包括主题域主要实体说明客户域customer, identity, contact客户基本信息、证件、联系方式产品域product, offer, product_offer套餐、业务产品与营销活动事件域call_event, data_event, complaint通话、上网、投诉等行为事实账务域account, bill, payment账户、账单与缴费记录资源域cell, base_station, board基站、小区、板卡等网络资源这些实体间的关系用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_flagY表示当前版本。注意WHERE子句只更新实际发生变化的行否则每次装载都会生成新版本维度表会快速膨胀。合并后老版本通过exp_date保留历史查询时用WHERE current_flagY过滤当前数据。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_skB树索引仍然有效但要注意选择率如果查询返回超过全表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 10; 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挂到每日调度末尾一旦告警立即检查源表元数据比事后发现报表数据对不上要快得多。这是我在实际项目中做逻辑模型验收的最后一道保险。本文还有配套的精品资源点击获取 数据仓库逻辑数据模型实战：中国移动经分系统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两套编码需要统一映射。转换阶段按逻辑模型映射把源字段转换为目标实体字段同时生成维度代理键。加载阶段写入目标表采用deleteinsert或者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 主题域与实体定义逻辑数据模型里最核心的部分是实体、属性和关系的定义。中国移动经营分析系统的典型主题域包括主题域主要实体说明客户域customer, identity, contact客户基本信息、证件、联系方式产品域product, offer, product_offer套餐、业务产品与营销活动事件域call_event, data_event, complaint通话、上网、投诉等行为事实账务域account, bill, payment账户、账单与缴费记录资源域cell, base_station, board基站、小区、板卡等网络资源这些实体间的关系用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_flagY表示当前版本。注意WHERE子句只更新实际发生变化的行否则每次装载都会生成新版本维度表会快速膨胀。合并后老版本通过exp_date保留历史查询时用WHERE current_flagY过滤当前数据。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_skB树索引仍然有效但要注意选择率如果查询返回超过全表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 10; 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挂到每日调度末尾一旦告警立即检查源表元数据比事后发现报表数据对不上要快得多。这是我在实际项目中做逻辑模型验收的最后一道保险。本文还有配套的精品资源点击获取