数据仓库规范

数据仓库规范一．数据仓库层次结构规范1.1基本分层结构系统的信息模型从存储的内容方面可以分为，STAGE接口信息模型、ODS/DWD信息模型，MID信息模型、DM信息模型、元数据信息模型。在各个信息模型中存储的内容如下描述：1)SRC接口层信息模型：提供业务系统数据文件的临时存储，数据稽核，数据质量保证，屏蔽对业务系统的干扰，对于主动数据采集方式，以文件的方式描述系统与各个专业子系统之间数据接口的内容、格式等信息。与该模型对应的数据是各个专业系统按照该模型的定义传送来的数据文件。STAGE是生产系统数据源的直接拷贝，由ETL过程对数据源进行直接抽取，在格式和数据定义上不作任何改变。与生产系统数据的唯一不同是，STAGE层数据具有时间戳。STAGE层存在的意义在于两点：（1）对数据源作统一的一次性获取，数据仓库中其他部分都依赖于STAGE层的数据，不再重复进行抽取，也不在生产系统上作运算，减小生产系统的压力；（2）在生产系统数据已经刷新的情况下，保存一定量的生产系统的历史数据，以便在二次抽取过程中运算出错的情况下可以进行回溯。2)ODS/DWD层（对应原模型的ODS和DW层）信息模型：简称DWD层是数据仓库的细节数据层，是对STAGE层数据进行沉淀，减少了抽取的复杂性，同时ODS/DWD的信息模型组织主要遵循企业业务事务处理的形式，将各个专业数据进行集中。为企业进行经营数据的分析，系统将数据按分析的主题的形式存放，跟STAGE层的粒度一致，属于分析的公共资源。3)MID信息模型：轻度综合层是新模型增加的数据仓库中DWD层和DM层之间的一个过渡层次，是对DWD层的生产数据进行轻度综合和汇总统计。轻度综合层与DWD的主要区别在于二者的应用领域不同，DWD的数据来源于生产型系统，并为满足一些不可预见的需求而进行沉淀；轻度综合层则面向分析型应用进行细粒度的统计和沉淀。4)DM信息模型：为专题经营分析服务，系统将数据按分析的专题组织成多维库表的形式存放，属于分析目标范畴的数据组织与汇总，属于分析的专有资源。其信息主要来源于DWD和MID层汇总，反映实时的经营状况，时间维度为天。而历史经营状况的分析，时间维度一般为月，同时也具有季度、年这样的维度。5)MDW元数据信息模型：描述数据及其环境的数据，即是对数据资源的描述,是信息共享和交换的基础和前提，用于描述数据集的内容、质量、表示方式、空间参考、管理方式以及数据集的其他特征。一般来说,它有两方面的用途。首先,元数据能提供基于用户的信息,如记录数据项的业务描述信息的元数据能帮助用户使用数据。其次,元数据能支持系统对数据的管理和维护,元数据机制主要支持以下五类系统管理功能:（１）描述哪些数据在数据仓库中;（２）定义要进入数据仓库中的数据和从数据仓库中产生的数据;（３）记录根据业务事件发生而随之进行的数据抽取工作时间安排;（４）记录并检测系统数据一致性的要求和执行情况;（５）衡量数据质量。1.2各层物理表前缀在构建数据仓库时，至少应该具备以下物理几层：联通数据模型规范要求的层次数据中心物理模型层次名称物理表前缀维度数据层DIM_数据集市层(DM)展示层DM_主题域DW_F_轻度汇总层(MID)轻度汇总层DW_M_细节数据层（ODS/DWD）细节数据层DW_V_接口层（STAGE）接口层SRC_1.3数据库对象命名规范所有数据库对象名称均使用26个大写英文字母、下划线或数字来命名，并不得以下划线开头。1.3.1用户用户名和表空间的名称应该采用同系统应用相似的英文字符或字符缩写，表空间所对应的一个或多个物理文件名也应有相关性。用户创建的数据索引最好和数据文件分开存放在不同表空间，以减少数据争用和提高响应速度。以上从逻辑上分出的各数据层应当在数据库中用户实现其分离，规定如下：数据层名称用户物理表备注接口层SRCSRC沉淀数据层DWDW_V,DW_M,DW_F数据集市层DMDM_元数据层MDWMDW_目前未设计维度数据层DIMDIM_1.3.2数据库表空间数据库表空间命名，原则上以数据仓库的基本分层结构为准，以TBS_作前缀，为避免单个表空间数据量过大，带来管理上的不便或者引起I/O瓶颈，对于STAG和ODS/DWD数据量比较大的层，可采用多个表空间存储数据，单表空间容量不要太大，以便于业务划分和存储管理为原则，建议单表空间容量控制在800G之内，表空间数据文件建议值为4G。数据表空间数据仓库分层结构用户表空间名数据文件/裸设备STAGSRCTBS_STAG001……TBS_STAGXXX2G=单设备文件=8G,建议值：4GDWDDWTBS_DWV001存放以dw_v开头的表……TBS_DWVXXXINDX_TBS_DMV存放以dw_v开头的表的索引MIDMIDTBS_MID同上DMDMTBS_DM同上INDX_TBS_DMDM索引DIMDIMTBS_DIM同上表名长度不能超过28个字符，表名中含有单词全部采用单数形式，单词选择能够概括表内容的一个或多个英文单词，多个单词间使用下划线分割,单词如果过长可以使用缩写形式。命名规则如下：表的类型前缀层次说明示例维度表DIM_DIMDIM_+主键客户视图DW_V_USER_DW_M_USERDW三个视图产品视图DW_V_PROD_DW_M_PROD_DW市场营销视图DW_V_MART_DW_M_MART_DW发展域DW_F_DEV_DW8个主题域根据业务具体分为：移动业务DW_F_DEV_M_智能网DW_F_DEV_I_数固DW_F_DEV_D_收入域DW_F_INCO_DW同上使用域DW_F_USE_DW同上增值域DW_F_INC_DW成本域DW_F_COST_DW资源域DW_F_RES_DW竞争域DW_F_COMP_DW服务域DW_F_SERV_DW1.3.4数据库分区表规范对于海量数据表要考虑设计为分区表。1.三户日资料保存一个月，按日期主分区按地区子分区，2.主分区命名为：P两位日期编码(如P01)，3.子分区命名为：P两位日期编码_SP地区编码(如P01_SP188),并且必须加上月份字段否则没法区分是那月的资料。2.三户月资料按帐期主分区按地区子分区，主分区命名为：P帐期编码(如P200701)，子分区命名为：P帐期编码_SP地区编码(如P200701_SP188)。3.视图级日数据表按帐期和地区主分区按日期子分区，主分区命名为：P帐期编码_地区编码(如P200701_188)，子分区命名为：P帐期编码_地区编码_SP两位日期编码(如P200701_188_SP01)。4.视图级月数据表按帐期主分区按地区子分区，主分区命名为：P帐期编码(如P200701)，子分区命名为：P帐期编码_SP地区编码(如P200701_SP188)。5.主体域级数据按帐期主分区按日期子分区，主分区格式为：P帐期(如P200701)，子分区格式为：P帐期_SP两位日期编码(如P200701_SP01)。老杨让把主题域建表分区规范改为：主体域级数据按帐期和地区主分区按日期子分区，主分区命名为：P帐期编码_地区编码(如P200701_188)，子分区命名为：P帐期编码_地区编码_SP两位日期编码(如P200701_188_SP01)1.3.5数据库表索引命名以IDX+表名+一位流水号.例：IDX_ODS_BUSI_USER_1;如果表名过长可以使用缩写形式1.3.6数据库表键值主键命名以PK+表名+一位流水号(1~9).例:PK_DEPT_1;如果表名过长可以使用缩写形式外键命名以FK+表名+一位流水号(1~9).例:FK_DEPT_1;如果表名过长可以使用缩写形式1.3.7数据库字段命名规范数据库字段名中含有单词选择能够概括表内容的一个或多个英文单词，多个单词间使用下划线分割,单词如果过长可以使用缩写形式。一些基本字段名示例：用户idUSER_NO用户数USER_COUNTS话单数CDR_NUM通话时长CALL_DURATION计费次数MOBILE_TIMES每个字段必须有注释，并且在生成SQL脚本时一并生成，创建表时必须创建注释。保持字段名和类型的一致性，同一字段名在不同表中必需保持同一数据类型。数据类型长度在定义时应稍大于目前标准的长度，用空间来换取将来变更带来的不便。1.3.8数据库存储过程规范（1）存储过程命名规则：P_目标表。（2）存储过程要求有注释，注释内容为：列出创建人，创建用途，创建时间。（3）存储过程日志规范：每一存储过程均应记录执行存储过程的日志信息。必须调用专用写日志的存储过程，同时有exception时的处理机制。（4）存储过程修改规范修改时应注释清楚修改人，修改日期，修改原因和修改内容。1.3.9数据库函数命名规范函数命名规则F_功能，比如F_TRAN_AREA。1.3.10据库触发器的命名规范触发器以TR作为前缀，触发器名为相应的表的别名加上后缀，INSERT触发器加‘_INSERT’，Delete触发器加‘_DELETE’，Update触发器加‘_UPDATE’，如：TR_CUST_INSERT。1.3.11序列命名规范序列以S作为前缀，序列命名规则为S_字段别名。二．实施流程规范（完善中。。）(1)规划对实施计划的规划.(2)设计设计实施方案(包括统一模型的修改)。(3)实施具体实施过程。(4)测试对实施结果测试。(5)反馈对实施过程中收集到的相关信息(系统需求、实施中遇到的问题和测试结果等)反馈到相关部门和人员。三．数据库安全管理规范为了规范管理，做好经营分析数据仓库的安全管理工作，实现不同的责任人不同的层次，将用户权限尽可能的管理起来同时又不影响正常工作，需要对数据库进行安全管理。数据库安全管理从以下几个方面来进行：3.1.用户组管理对用户进行分类，目前经营分析应用用户可以分为如下几部分前台程序开发人员数据库开发人员数据库管理员外部使用人员数据库管理人员由项目经理和数据经理来掌控，一般情况下不得使用DBA角色登陆数据库。数据人员使用数据库开发人员角色登陆，每个数据人员一个用户，归属数据库开发人员组。前台程序开发人员，由界面开发人员使用，可以查看所有的表，但是无法进行DDL操作。外部使用人员，主要是面向联通用户和临时用户3.2.用户权限设定对不同的用户组，在不影响正常工作的情况下，对用户组及用户权限的设定原则为权限越小越好。3.3.用户密码管理对用户密码进行限制，必须由2位以上数字，2位以上字符，2位以上特殊字符组成不允许用户密码和用户名同名不允许用户密码和用户名相似3.4.用户资源管理除了系统使用的用户（SRC/ODS/DW）等外对用户使用的系统资源进行限定限定用户使用表空间限定用户使用临时表空间限定用户使用回滚断限定用户使用内存3.5.IP限定对于普通用户，实行IP和用户名绑定的策略对于外部开放用户，要进行IP申请，由数据经理或者项目经理审核通过后予以开通3.6.数据库监控数据库监控，主要对以下几个方面进行监控：3.6.1.数据库空间占用率selecta.tablespace_name,free,total,round(((b.total-a.free)/b.total),2)剩余占比from(selecttablespace_name,round(sum(bytes)/power(1024,3),2)freefromdba_free_spacegroupbytablespace_name)a,(selecttablespace_name,round(sum(bytes)/power(1024,3),2)totalfromdba_data_filesgroupbytablespace_name)bwherea.tablespace_name=b.tablespace_name;3.6.2.会话情况select*fromv$sessiona,v$sqlbwherea.sql_address=b.address;3.6.3.aix操作系统中杀掉一些进程的脚本select'kill-9'||p.spid||'',