1、NCR 建模规范NCR(中国)有限公司数据仓库事业部杨荣华Sr.Consultant2005.11 2 物理模型建模规范 逻辑模型建模方法论提纲 应用模型建模方法 3逻辑数据模型设计步骤Step 1:业务探索Step 2:信息探索Step 3:定义实体Step 4:定义关系Step 5:定义非键属性Step 6:确认模型 4Step 1:业务探索(Business Discovery)主要任务：主要任务：分析源数据系统 决定细节信息需求 决定功能需求 定义初步接受标准 交付项目：交付项目：业务需求说明书 BRL(Business Requirement List)项目前期准备业务探索信息探索逻
2、辑数据模型 5Step 2:信息探索(Information Discovery)主要任务：主要任务：对候选源数据系统编辑相关信息 数据源分析，对源资料的质量评价 估计候选资料来源的技术特性 候选源数据纪录系统的开发建议 交付项目：交付项目：功能需求列表FRL(Functional Requirement List)源系统数据质量分析 项目前期准备业务探索信息探索逻辑数据模型 6Step 3:定义实体主要步骤：主要步骤：1.制定初始的实体池（不加区分的实体集合）2.定义每个实体的属性 3.结合cLDM，将实体归并到不同主题域4.删除超出项目范围的实体 5.使用ER图制定最终版本的交付材料 利用
3、工具：利用工具：ERWin项目前期准备业务探索信息探索逻辑数据模型ABCDEF FG GH HI IJ JK K重要客户重要客户集团客户集团客户高价值客户高价值客户LM普通用户普通用户所有用户所有用户会员卡客户会员卡客户 7Step 4:定义关系主要任务：主要任务：根据业务逻辑建立实体间的关系 确保数据的完整性关系类别：关系类别：识别（型）关系（identifying relationship）非识别（型）关系(non-identifying relationship)子类关系（Subtype relationship）多对多关系（Many-to-many relationship）嵌套关系（
4、Recursive relationship）项目前期准备业务探索信息探索逻辑数据模型 8Step 5:定义键属性主要任务：主要任务：定义标示一个实体的唯一主键定义外键，区分识别型关系和非识别型关系 遵循3NF范式 项目前期准备业务探索信息探索逻辑数据模型3NF:The keyThe whole key Nothing but the key 9Step 6:确认模型主要任务：主要任务：模型中的任何变更都会带来连锁反应，因此需要非常认真的回顾与评审：实体的变更经常影响关系的定义和属性的位置摆放关系的变更经常影响属性的位置摆放属性的位置的变更可能影响其他属性的摆放通过回答以下问题，持续地对模型的
5、范围进行验证：这一模型组件的含义、与业务的关系是什么？这一模型组件驱动的业务需求是什么？对模型是否已经满足所有业务需求、业务问题及限制条件等，进行验证交付项目：交付项目：逻辑数据模型（Erwin格式）项目前期准备业务探索信息探索逻辑数据模型 10 物理模型建模规范 逻辑模型建模方法论提纲 应用模型建模方法 11主要任务：主要任务：转换逻辑数据模型（转换逻辑数据模型（LDM）为物理数据模型为物理数据模型 定义主索引、次索引定义主索引、次索引 非正规化处理（非正规化处理（demoralizations）数据库建立数据库建立 设计优化 数据库功能测试使用工具：使用工具：ERWin交付项目：交付项目：
6、物理数据模型（物理数据模型（PDM）物理数据库设计 12LDM vs.PDM 逻辑数据模型和物理数据模型可能不一样 逻辑数据模型完全从逻辑意义上，公正的表示业务数据 物理数据模型是在物理平台上的某次具体实施，从LDM中来，但又不完全相同 物理数据模型建设需要综合考虑业务规则，平台本身，数据源等因素 13*被被业业界广泛接受界广泛接受LDM vs.PDM(Cont.)逻辑数据模型逻辑数据模型 *实体 属性 1个实例 数据结构关于：业务元素如何和其他的发生关联 业务逻辑 由业务规则驱动 第三范式 灵活和稳定 独立于 -物理环境 -工具 -应用 -如何获得数据以及如何输出 关系型数据库的理论物理数据
7、模型物理数据模型 表 列-rows 如何获取数据以及如何输出 索引 资源的限制 Denormalization 数据集市 性能 硬件、网络 工具 应用 依赖于某种DBMS 14总体规范在ERwin模型图中，实体（表）不能重叠，实体（表）之间的关系线尽量避免交叉；逻辑模型和物理模型体现在同一ERwin文件中，通过Logical only或Physical Only来表现实体和表、属性和字段的差异；物理模型必须和库中表结构定义完全一致。这就要求对库中表的任何更改，必须同步修改模型；15数据库对象命名规范 其中，为前缀名，表示数据库对象类型；为表隶属主题域名（三位字符缩写）；为主体名，应该能够清楚地
8、说明对象的含义;是后缀名，在该对象需分表存放或以历史表方式 出现时使用。Prefix_Subject_Body_Suffix（注：如无特殊原因全部为大写字符和数字组成）（注：如无特殊原因全部为大写字符和数字组成）16数据库对象命名规范(Cont.)前缀前缀数据库对象数据库对象TB表(Table)VW视图(View)MC宏(Macro)TR触发器(Trigger)SP存储过程(Store Procedure)TT临时表(Temporary Table)VT可变临时表（Volatile Table）部分一般用两位字符表示数据库对象:17数据库对象命名规范(Cont.)部分命名遵循模型中主题域的划分
9、原则，采用三位字符表示:主题域主题域主题域缩写主题域缩写 主题域中文描述主题域中文描述PARTYPAR参与人主题OFFEROFR产品和服务主题MARKETINGMKT市场营销主题EVENTEVT事件主题FINANCEFIN帐务主题LOCATIONLOC地域主题NETWORKNET网络主题FINACE MANAGEMENTFMG财务管理主题CODE/REFERRENCECDE代码表 18数据库对象命名规范(Cont.)部分命名规则:只能由字母、数字和“_”(下划线)构成；主体名应采用能够准确反映其中文含义的英文单词或英文单词 缩写构成，避免出现英文单词和汉语拼音混用的局面；主体名应尽量与源系统中
10、的命名相一致，除非其命名违反上两条规则 19数据库对象命名规范(Cont.)部分命名规则:对于数据库中的表，如因数据量庞大，需按时间分表存放时，需遵循此规则加上后缀。需按日分开存放的表，后缀为 YYMMDD，需按月分开存放的表，后缀为YYYYMM数据库中的历史表，其后缀用HIS 20字段命名规范 字段名由字母，数字和下划线组合而成；字段名应采用能够准确反映其中文含义的英文单词或英文单词缩写构成，若是完整英文单词，采用首字母大写；若是单词缩写，全部使用大写字母；在数据库多个实体中具有相同含义的字段名命名应保持一致；字段名不要使用系统的保留字，长度不可以超过30个字符 21数据类型选取原则 字符类
11、型字段可采用CHAR或VARCHAR类型，具体选择CHAR还是VARCHAR视该字段取值是否定长字符。定长数据采用CHAR类型，非定长数据需要考虑：1.数据的平均占用长度（本工作需要将数据导入数据仓库后才能确定）2.VARCHAR数据类型需要额外的两个字节来存放数据起始位3.CHAR类型可做多值压缩，而VARCHAR数据类型不可以 以确定选择CHAR还是VARCHAR数据类型；22数据类型选取原则 整数根据实际字段取值大小，选取byteint,smallint和integer三种表示方法之一；浮点型字段根据源系统数据类型定义中小数点后精确位数，采用Decimal(x,y)数据类型，其中y为小数
12、点精确位数；日期一律采用YYYYMMDD格式，取Date类型；时间一律采用HHMMSS格式，取integer format 999999格式如无特殊需求，在数据仓库模型设计中将源系统中同时包含日期和时间的日期类型（Date，YYYYMMDD HH24MISS）字段尽量分割成日期和时间两个字段表示；23数据类型选取原则(Cont.)根据数据实际情况，尽可能选择占用空间小且足以存放该数据的数据类型 数据类型数据类型数据大小数据大小占用字节数占用字节数(byte)(byte)BYTEINT-128 to+1271SMALLINT-32,768 to +32,7672INTEGER-2,147,483
13、,648 to +2,147,483,6474DATE(YYYY-1900)*10000+(MM*100)+DD4DECIMAL(n,m)n=1 18m=0-n1 to 2 1 byte3 to 4 2 bytes5 to 9 4 bytes10 to 18 8 bytesCHAR(n)n=1-64000nVARCHAR(n)n=1-64000 24索引选取原则 PI选择原则：作为PI的字段要尽可能多的被访问（Access）（作为Where条件或者关联条件）；组成PI字段取值尽量接近唯一，这样才能保证数据分布（Distribution）均匀；组成PI的字段尽可能稳定（Volatile），不被更
14、新。通常：PK字段可作为UPI或PK字段中部分作为NUPI；单字段做PK的表可用该字段做UPI；多字段做PK的表可从中选择唯一性较强且经常被访问的字段做NUP 物理模型设计阶段定义的PI，在实际数据生成或加载到数据仓库 后，发现并不适合。此时，需要对PI字段重新选定。次索引（Secondary Index）通常在物理模型设计阶段暂不考虑，除非在设计阶段能预见数据访问时会存在性能问题，需通过建立SI来优化。25重要物理属性-主索引 主索引对于索引我们规定不为其命名（因为可变临时表不允许带有名称的索引），在Erwin默认情况下，会对其生成一个系统名称，我们需要修改默认设置。对于PK字段作为UPI的
15、表，直接选中PRIMARY选择框。然后Rename 该索引的名称为空格对于PK字段不适宜做PI的表，需新建一索引，指定其组成字段，并设置其为非唯一主索引。同样，在新建索引的时候，Key Group和Index的名称都修改为空格。26重要物理属性-分区主索引在Teradata V2R5及以上版本中，支持分区主索引。但在ERwin中没有地方能直接设置Partition字段，只能通过Post Scripts在DDL一级实现。27重要物理属性-多值压缩多值压缩在初期物理模型设计阶段是无法确定全部取值的，只有等数据加载到数据仓库后，再根据每个字段的取值，去修正压缩取值列表。28重要物理属性-非空及区分大
16、小写 在数据仓库需要比对源系统提供的前后两个抽取周期的信息变化（使用Not In全字段比对），故对各字段要求非空。源系统提供的一些代码字符，区分大小写。如“A”和“a”表示含义不一致。所以在数据仓库的物理模型设计时，建议所有的字符字段区分大小写。29 物理模型建模规范 逻辑模型建模方法论提纲 应用模型建模方法 30应用模型表名命名规范应用模型是基础数据模型上的汇总数据集合，是为具体的应用方向和应用主题的数据展现而设计的，因此其命名编码中应着重体现这一方面的含义，具体编码规则如下：Prefix_DType_OType_OSuffix_Num（注：如无特殊原因全部为大写字符和数字组成）（注：如无特
17、殊原因全部为大写字符和数字组成）l实体前缀，标识数据库对象的类型，定义如前述；l实体数据展现类型，标识该实体属于何种应用功能类型，以三个字符表示数据展现类型数据展现类型类型名称类型名称CUB多维分析KPI关键业务指标FCT事实表MID中间表DMN数据挖掘与专题分析RPT统计、分析报表 31应用模型表名命名规范实体分析主题主类型，标识该实体内容涉及的业务主题的主要类型，例如VOC表示语音业务分析实体分析主题辅类型，标识该实体内容涉及的业务主题的辅助类型，以三到四个字符表示，定义如实体编号，以两位数字表示的同实体名称下的序列号，区分实体之用主题类型主题类型类型名称类型名称CUS客户分析VOC语音业
18、务分析ADD新业务与数据业务分析REV收入分析MKT市场竞争分析CAM营销管理分析SRV服务质量分析BIG大客户分析NMS网管分析STL结算分析FIN财务分析Prefix_DType_OType_OSuffix_Num（注：如无特殊原因全部为大写字符和数字组成）（注：如无特殊原因全部为大写字符和数字组成）32应用模型字段名命名规范字段名由字母，数字和下划线组合而成；字段名应采用能够准确反映其中文含义的英文单词或英文单词缩写构成，若是完整英文单词，采用首字母大写；若是单词缩写，全部使用大写字母；在数据库多个实体中具有相同含义的字段名命名应保持一致；字段名不要使用系统的保留字，长度不可以超过30个
19、字符尽可能延用基础数据模型中已有的字段名称尽可能延用基础数据模型中已有的字段名称 33应用模型索引选取原则对于事实表，我们建议考虑采用所有的维度字段作为UPI或者在表倾斜率较大的时候添加Identity Column 作为NUPI。其写法如下CREATE TABLE FCT_NBUSN_WAP_CSD_MON(Col_ID INTEGER GENERATED ALWAYS AS IDENTITY,CAL_Month INTEGER NOT NULL TITLE 统计月份,City_ID CHAR(6)NOT NULL TITLE 地市标识,CUST_Brand_Code CHAR(4)NOT
20、NULL TITLE 客户品牌代码,Consume_Level_Code CHAR(2)NOT NULL TITLE 消费层次代码,Gender_Code CHAR(1)NOT NULL TITLE 性别代码 CASESPECIFIC,Age_Level_Code CHAR(2)NOT NULL TITLE 年龄层次代码 CASESPECIFIC,Use_CUST_QUAN DECIMAL(14)NOT NULL TITLE 使用客户数,Comm_NUM DECIMAL(14)TITLE 通信次数,Comm_DUR DECIMAL(14)TITLE 通信时长,Comm_Fee DECIMAL(14,2)TITLE 通话费用)PRIMARY INDEX(Col_ID );其他原则参考物理模型索引选取原则 34
