数据仓库建模探讨

1、韩贺奇2013-2-26n数据模型的重要性及其原则n建模的基本方法与过程n数据模型层次n实施方法论2业务理解。确定业务主题域，识别核心业务对象及其关系 3概念模型逻缉模型物理模型层次分明。清晰合理地划分，层次要有充分的存在的理由关系清晰。表间ER关系，层次之间mapping关系结构合理。适当冗余，适度降范式存取方便。ETL过程简单4 ER建模的核心是明确实体、过程、关系（实体之间、实体与过程之间）；实体往往是主数据，而过程往往是交易数据，交易数据围绕主数据展开；刻画实体或过程本身的细节，往往涉及master & detail强依赖关系；在概念模型阶段，建立对象之间的关系时，可以先不必展开对象的

2、细节，以快速形成清晰的ER图维度建模要考虑事实表数据粒度是否合适（比如尽量存储基础指标，指标的分子分母单独存储等），以及缓慢渐变维的处理；特别地，对于基础指标，要考虑唯一定义、唯一存储ER建模 维度建模 概念模型逻缉模型物理模型5逻缉模型转为物理模型有一定挑战性678客户主题域实体关系图91。数据内容。加载上游供方系统提供的增量文件，保留数据原貌，不做改变。具体包括包括当前增量表、当前合格数据表；历史表、脏数据表2。保留时间。对于每日增量文件（含一天多个批次），保留最近7天；对于每月增量文件，保留最近3个月的数据3。数据用途。便于下游应用系统加载数据后，能直接与上游供方系统提供的数据核对，提升

3、下游自助核对数据的能力，减少对数据交换平台人工服务的依赖1。数据内容。包含历史版本的偏源或偏IDS的明细数据，以及适度整合的清单宽表，这些清单宽表的直接用途是方便进一步加工为指标层所需的公共指标，也可以直接提供给下游应用系统做个性化的应用（如按特定口径加工为应用所需的指标，或直接展现为清单报表）2。保留时间。原则上，交易类数据保留最近两年，主数据长期保留（暂定最近10年）3。数据用途。基础层作为数据仓库或数据集市的基础数据；另外，使数据中心具备提供清单数据以及一段时间内存量数据的能力1。数据内容。计算保存下游报表集市需要的共性指标，维度较多、粒度较细2。保留时间。原则上指标数据保留最近两年3。

4、数据用途。指标数据基于中心数据库统一出口，保证数据的一致性4。建设思路。鉴于公共指标层建设的难度，建议从少数关键业绩指标（KPI）开始，由下游报表集市发起，包括指标口径定义以及指标计算，数据中心负责制定准入标准、统一管理，指标计算程序以及数据库表，统一部署到中心数据库 1。数据内容。数据挖掘用宽表，报表用各种粒度指标表。也可以应下游应用方要求，对基础层或指标层的数据做进一步的个性化定制2。保留时间。视业务部门的要求而定3。数据用途。报表展现，OLAP分析，数据挖掘等缓冲层 基础层 指标层应用层10缓冲层一般缓冲交易系统提供的增量数据，对缓冲层的数据需要做必要的清洗以及一定的标准化处理基础层基础

5、层的数据是原子粒度数据，需要适度的整合以及一定的范式化处理，比如客户联系方式需要整合，等。此外，根据需要，考虑对部分表做历史版本应用层原则上，ODS基于基础层对外提供明细数据服务。实践中，可以根据应用方的要求，对基础层的数据做一些跨表整合提供宽表服务，或者适当汇总提供指标数据服务11缓冲层如果EDW的数据来源于ODS，则无须做过多的清洗，除非业务上有数据准入的严格要求。原则上 ，不做代码标准化基础层基础层的数据是原子粒度数据，可以偏向供方规格设计，添加必要的技术字段即可。在基础层，可以做一些原子宽表，便于快速生成指标存入指标层次指标层指标口径唯一定义，指标数据唯一存储。考虑细粒度基础指标，分子

6、分母分开存储应用层EDW本身不做应用，无须该层。原则上，EDW对外提供基础层与指标层的数据，但可以根据应用方的需要，为其加工个性化数据，存放于该应用层（或者叫定制层）12缓冲层除非需要接入来自交易系统的数据，并且数据质量不理想，否则无须建立缓冲层基础层存放来自EDW的原子数据，或者还需较为复杂加工的明细基础指标数据。对于DM，基础层是一个规模比较小的层次指标层EDW提供的基础指标数据如果接近应用，可以存放到指标层；此外，基于基础层自行计算得到的指标数据存放于指标层。指标层遵循“唯一定义、唯一存储”的原则应用层根据应用需要建立数据表，主要目标是使前端程序开发容易，数据展现性能提升。应用层为前端展

7、现服务，数据来源是指标层的进一步汇总或重新组织。在实施层面，如果基础层或指标层的表直接适合应用，可以考虑在应用层建视图无论EDW还是DM，基础层与指标层的结构应当保持相对稳定，是模型设计的重点。基础层注重数据齐全与质量可靠，一般采用ER建模；指标层强调“唯一定义、唯一存储” ，一般采用维度建模。应用层的ER特征较弱，会涉及到维度建模13数据层次缓冲层基础层指标层应用层建模方法-ER建模维度建模维度建模建模过程概念模型-确定原子主题确定分析主题-逻缉模型-表中的元素为基本属性需整理出指标维度矩阵，表中的元素为指标根据前端展现的要求设计，维度建模只是可能用到的方法物理模型-需做较多的降范式处理，增

8、加技术字段等处理转换较少处理转换较少14总体上遵循“需求驱动，自顶向下”的方法，数据源调研应当尽早，以大致确定需求是否可以实现。具体过程如下：显然，需求确定以及数据源调研后，应用层与指标层的设计可以并行开展，供数规格的确定与基础层设计紧随其后 1。基于报表需求设计应用层 2。基于报表需求整理指标维度矩阵，设计指标层 3。与上游确定供数规格，设计基础层 4。建立数据模型层次之间的逻缉关系，以验证模型 4。1 建立应用层与报表指标的mapping 4。2 建立指标层与应用层的mapping 4。3 建立基础层与指标层的mapping 4。4 建立供数规格与基础层的mapping应用层设计指标层设计

9、供数规格确定基础层设计输入产出作业需求应用层模型指标层模型供数规格基础层模型etl mappingetl mappingetl mapping供数规格源系统模型etl mapping15l关系实体变成子实体的一个外部引用 （person & address）l父实体与最多N个子实体之间的one-to-max关系，可以在父实体中定义N个属性以吸收子实体（person & address）l父子实体之间是标识关联的关系，则可以消除父实体，将其属性移到子实体中（事实表扁平化，原子宽表）l在反映多对多的关系的关系实体，如果其中某类关系是极其关键或经常访问的，则可以直接在基础实体中直接引用（policy

10、 & agent）16名称含义说明备注anchor_id主键锚点一般使用hashcode值，基于业务主键生成，并是其代理。一组业务主键相同的记录（历史版本），有相同的主键锚点(anchor_id, eff_start)为联合主键，不必为每条记录额外增加单一字段作为物理主键eff_start生效起期源系统供出日期，指哪天的增量，考虑到源系统一天可能供出多个批次，可以将eff_start设计为数字类型，格式为yyyymmdd序号，比如20130221001。对于特殊的批次，比如数据修复，序号可以规定999eff_end生效讫期记录新增时间，eff_end填上99991231001记录修改或删除时，

11、eff_end填上源系统供出的批次号，比如20130308001etl_timestamp记录生成时间指记录insert的时间，精确到秒甚至毫秒create_by创建系统创建该记录的源系统根据upt_by为空这一条件，即可得到记录新增时的版本数据upt_by更新系统记录新增时设为null，修改时需要填写具体的源系统17缓慢渐变维，即维度中的属性可能会随着时间发生改变，比如包含用户住址Address的DimCustomer维度，用户的住址可能会发生改变，进而影响业务统计精度，DimCustomer维度就是缓慢渐变维（SCD），对于SCD，处理方式通常有以下几种：Type 2：通过添加记录来将每一

12、次变化都记录到SCD中，每条记录都有两个字段（如Effective_start和Effective_end）表明该记录的有效期间，并且可以设定一个Active标志位字段，当该字段为True的时候表明这条记录是最新的状态，为False的时候表明该记录是历史记录，其有效期间可以通过Effective_start和Effective_end字段来查Type 1：完全不记录历史变化信息，在ETL将数据载入SCD的时候，对于会产生变化的属性值直接覆盖，比如对于DimCustomer的Address，每次都会将新的地址update到该字段，因此这个SCD实际上总是最新的当前信息，却没能包含历史信息Type

13、 3：通过对会发生变化的字段，添加相应的历史字段，来记录最近的变化而非全部变化。比如DimCustomer中有两个字段Address和Address_Old，第一个字段是用户的当前住址，后一个字段是用户之前一次的住址，显然，更久之前的信息就无法追溯了Type 4：除了一个记录当前信息的维度外，单独建立一个历史信息维度，该维度中需要包含有效期间字段（如Effective_start和Effective_end）Type 5 = 1+2+3：可以看到，对于Type 1/2/3，都是对于SCD中渐变属性的处理方式，而针对一个包含多字段的复杂的SCD，可能需要结合以上三种处理方式。比如对于DimCus

14、tomer中的用户联系方式属性email，如果业务上并不重要，那么这个字段可以采取Type 1的方式，即每次只保留最新的联络方式，覆盖原来的；假如业务中需要分析用户所在地Region，那么很可能需要用到Type 2，记录每一个Region的改变；而对于地址信息Address，可能并不需要追溯很久的变化，那么加一个Address_Old字段来记录上一次的住址就够了18营销员维agt_code营销员代码gender性别join_dt入司日期birh_dt出生日期edu_bkgrnd学历rank_code职级unit_code所属营业组dept_code所属营业部district_code所属营业区

15、etl_timestamp记录生成时间eff_start生效起期eff_end生效讫期营销机构维sales_org_code营销机构代码sales_org_name营销机构名称sales_org_type营销机构类型district_code营业区dept_code营业部etl_timestamp记录生成时间eff_start生效起期eff_end生效讫期行政机构维adm_org_code行政机构代码adm_org_name行政机构名称adm_org_type行政机构类型hq_code总公司branch_code分公司sub_branch_code中支公司etl_timestamp记录生成时间

16、eff_start生效起期eff_end生效讫期重点说明一下营销员维表，该表基于营销员基本信息表（agent），并从营销机构维表中扩充了两个字段dept_code与district_code。通过营销员维表，可以方便地查询到历史上任何一天，营销员属于哪个营业组、营业部、营业区。显然，营销员维表的增量来自于营销员基本信息表与营销机构维表。19个险保单宽表（基本信息）policy_no保单号码class_code险种代码FKrinder_ind主附险标记sales_channel渠道FKsales_agt_code销售代理人销售base_branch_code支公司行政coverage_eff_start保项责任起期coverage_eff_end保项责任讫期issue_dt签发日期lapse_dt失效日期reinstate_dt复效日期surrender_dt退保日期coverage_status保项状态status_reason状态原因etl_timestamp记录生成时间个险保单宽表（扩展信息）gender性别age年龄join_month入司月数ed