科技大数据平台数据仓库开发指南

1T/GDMA4—2018科技大数据平台数据仓库开发指南本标准规定了科技大数据平台数据仓库开发过程中各环节应遵循的流程及标准。本标准适用于广东省科技厅基于数据仓库的信息中心负责人及相应开发/运维厂商共同使用。2规范性引用文件下列文件对于本文件的应用是必不可少的。凡是注日期的引用文件，仅注日期的版本适用于本文件。凡是不注日期的引用文件，其最新版本（包括所有的修改单）适用于本文件。GB18030信息技术中文编码字符集GB/T20273信息安全技术数据库管理系统安全技术要求GB/T12991.1信息技术数据库语言SQL第1部分:框架3术语和定义及缩略语GB18030、GB/T20273和GB/T12991.1确立的以及下列术语和定义适用于本文件。3.1数据仓库datawarehouse数据仓库是科技厅所有业务数据存储载体，是企业级的数据集合。3.2ETLExtract-Transform-LoadETL是指数据的抽取(Extract)，转换(Transform)和加载(Loading)，它是一个数据转移、重组的过程，是数据仓库系统实施的一个非常重要的环节。3.3ODS操作数据存储operationaldatastore操作数据存储是数据仓库体系结构中的一个部分，ODS具备数据仓库的部分特征和OLTP系统的部分特征，它是“面向主题的、集成的、当前或接近当前的、不断变化的”数据。3.4ODBC开放数据库互连opendatabaseconnectivity开放数据库连接是为解决异构数据库间的数据共享而产生的,是基于Windows环境的一种数据库访问接口标准，ODBC为异构数据库访问提供统一接口，允许应用程序以SQL为数据存取标准，存取不同2T/GDMA4—2018DBMS管理的数据；使应用程序直接操纵DB中的数据，免除随DB的改变而改变，使用ODBC可以访问各类计算机上的数据库文件。3.5缩略语下列缩略与适用与本标准：CWM数据仓库元模型CommonWarehouseMetamodelETL数据提取、转换和加载Extraction-Transformation-LoadingSQL结构化查询语言StructuredQueryLanguageXML可扩展置标语言ExtensibleMarkupLanguage4数据仓库架构4.1数据仓库架构图数据仓库中的指标数据就可以为各种分析应用提供深加工的数据资源。中间数据库和数据仓库将有相应的管理子系统进行管理，见图1数据仓库架构图。图1数据仓库架构图4.2数据仓库层次4.2.1源数据层以统一规范的方式存储数据。4.2.2中间层解析应用层的业务逻辑，使应用层和原始数据相互独立，提高应用层系统（程序）的可扩展性、可移植性。3T/GDMA4—20184.2.3应用层面向最终用户，提供友好、简洁、方便的用户界面，具有良好的业务无关性。4.3数据仓库实施流程数据仓库实施，按照三大阶段拆分如下具体前后执行的操作步骤,见表1。表1数据仓库实施阶段5数据仓库建设阶段划分5.1设计阶段5.1.1数据库模型设计5.1.1.1数据仓库建模流程图数据仓库数据模型设计，包含概念模型，逻辑模型和物理模型设计，见图2。图2数据仓库建模流程图5.1.1.2概念模型4T/GDMA4—2018数据仓库架构组的模型设计师通过业务流程及业务流转，将数据主题下的数据对象（实体）相互关系描述清晰，形成数据概念模型实体-关系图，输出《概念数据模型》。通过这种模型来数据化描述科技厅具体业务运营和管理过程中涉及的主要业务概念和相关规则。5.1.1.3逻辑模型在概念数据模型的基础上，针对业务实体和属性定义进行细化，形成逻辑数据模型，具体参考业务系统中数据具备的属性，同时需要保证逻辑模型能支撑上层分析需求。5.1.1.4物理模型在对数据仓库进行物理模型设计时，需要遵循以下标准：a)设计存储结构：对于经常查询的一些数据，可放在一张表中，避免表间关联，消耗服务器性能；b)设计索引策略：数据仓库的数据量很大且很少更新，因而可以设计索引结构来提高数据存取效率，一般都是按主关键字和大多数外部关键字建立索引，通常不要添加很多的其他索引；c)设计存储策略：将需要同时访问的表在物理上顺序存放，或者直接通过公共关键字将相互关联的记录放在一起。5.1.2设计命名5.1.2.1数据模型命名数据模型命名针对数据仓库物理表命名进行规范，包括：ODS、数据仓库的物理模型命名。a)ODS物理命名说明见表2；表22ODS物理命名说明表V代表Viewb)数据仓库物理命名说明见表3。表3数据仓库物理命名说明表5T/GDMA4—2018W代表数据仓库层 5.1.2.2同义词命名本次数据仓库优化工作，在优化过程中，为了避免对上层应用的调整过大，对于部分表建立同义词，确保上层应用访问时保证调整量最小。数据仓库ODS的同义词命名按照用途分为两类：a)对于优化后的表，表名按照数据模型命名规范来进行表名设计；同义词按照原数据仓库中表的命名进行，保证上层应用访问无需进行太多表的调整；b)对于部分ODS明细表，由于访问需要兼顾物理存储的原因，当数据仓库区或者数据交换区需要访问该表时，可对该表进行同义词命名，命名规范参考相应数据区的命名规范进行。5.1.2.3用户权限命名本文针对数据仓库进行内部用户及外部用户权限命名规范：a)仓库内部用户：设置共用用户DW_ADMIN，部分数据考虑保密性，单独设计对应的用户，如：财务数据用户FS_ADMIN。b)据仓库外部用户：用户名命名规范按“项目名称_访问权限”进行设置，如企业驾驶舱项目的用户仅有查询权限，则对应用户的命名为：QYJSC_CX。5.2开发阶段5.2.1开发要求5.2.1.1数据仓库代码要求要求如下：a)开发人员应使用管理员指定的账号登陆客户端进行开发，不得使用他人的账号登陆开发范围外的其他项目；b)开发人员不得修改、删除或运行指定文件夹以外的程序；c)开发人员不得使用未授权的数据源抽取数据，如需建立新的数据源连接，应向数据仓库架构师提出申请，获准后建立数据源并抽取数据；d)开发、测试期间，只可以使用数据仓库的测试平台存储数据，不得连接、使用生产环境的数据仓库；e)开发人员需要进行严格的文档版本控制；6T/GDMA4—2018f)开发人员应遵循下文中提及的程序开发命名规范进行程序开发；g)开发人员应维护《ETL详细设计》保证设计与程序的一致性。5.2.1.2数据仓库代码测试要求依据《程序概要设计》及《程序详细设计》文档对数据抽取、转换、加载的正确性、稳定性、运行效率及调度进行测试。因此测试工作应分为四个部分，并遵循以下标准进行：a)作业测试：测试目的是验证各模块是否依照《ETL详细设计》，正确实现了处理功能，相关联数据的处理逻辑是否正确，处理任务规划是否合；b)性能测试：性能测试的目的是验证ETL在多任务并行及大数据量运行时，处理流程能否在合理的运行时间内完成；c)记录数核查：记录数的核对主要是核查数据从源系统提取出来，加载到数据仓库的过程中，是否存在数据丢失和不一致的情况，逐层对业务系统－>ODS这一过程中记录条数是否正确进行核对；d)数据质量核查：对各个数据实体的相应属性字段取值是否规范进行检查，包括主键检查、外键检查、非规范性编码检查、数据类型及格式检查、数据值域检查、业务规则检查、非空检查等；e)ETL调度测试：通过将ETL单独程序构建成工作流，按照ETL调度设计对调度运行效率、调度前后顺序、调度运行条件是否正确等进行测试。最后确保整个ETL程序是在客户需求的时间窗内按照正确的运行条件进行程序运行。5.2.1.3数据仓库代码迁移部署要求由于代码迁移部署在生产环境进行，因此有可能对系统已运行的程序造成影响，因此代码迁移部署工作应遵循以下前提：部署条件如下：a)程序应在充分测试，保证正确，并对各种可能异常情况有明确的处理方案；在迁移程序较多，涉及面较广的情况下，应申请程序备份；b)对新增程序对生产服务器的存储占用及性能压力，有明确的说明，以期对其资源占用有较早评估，避免造成生产环境服务器压力过大，超负荷挂机；c)如果需更新的程序对公用组件或数据有影响，数据仓库架构师应与其他相关团队负责人一起协商程序更新策略，制订系统恢复方案。5.2.2开发命名5.2.2.1转换模块命名Informatica转换模块命名的规则见表4。表4转换模块命名标准表Exp_主要功能（转换-TRA/计算-CAL）_Fil_过滤字段名/Cob（多字段组和）Agg_聚合计算类型（sum/max/min/count）_聚合组7T/GDMA4—20185.2.2.2组件命名规则要求如下：a)ODBC命名规则：Informatica源数据表的数据使用ODBC和文本文件两种导入方式，使用文本文件导入的数据源表将集中存放在FlatFile文件夹下，而用ODBC方式导入的数据源表将存放在以ODBC名称命名的文件夹下，因此ODBC的命名会直接影响源数据表的存放结构。ODBC数据源可以分为关系型数据库与Excel文件两种：对于关系数据库，Informatica服务器端ODBC使用原系统名称缩写方式命名，本地ODBC命名需与服务器相同，而在Informatica建立的数据源采用ODBC_ODBC_原系统名方式命名；对于Excel文件，Informatica服务器端Excel的ODBC数据源采用Excel数据区名称命名，本地ODBC命名需与服务器相同，而在Informatica建立的数据源采用ODBC_EXCEL_文件名命名方式命名；b)Mapping命名规则：ETL处理分为源到ODS、ODS到DW二个处理步骤，Mapping的命名也应符合处理步骤的需求：对于数据接口至ODS层Mapping：M_ODS_源系统_ODS表名；对于ODS层至DW层Mapping：M_DW_主题_DW表名；c)Session命名规则：S_mapping名命名；d)WorkLet命名规则：WorkLet是对Session处理流程的包装，是任务处理的最小单元，因此Worklet设计应保证数据处理的原子性与独立性。WorkLet的命名也应反映WorkLet的设计思路，包括WorkLet处理的数据层级、数据主题、处理频率等信息；e)WorkLet命名使用如下规则：WL_业务主题/分析主题_源数据层_目标数据层_处理频率。其中业务主题和分析主题应使用主题英文单词的缩写，可以参考数据模型标准提供的词典表；处理频率应使用Day（天）/Mon（月）/Qua（季度）/Yea（年）的三位缩写形式；f)WorkFlow命名规则：WorkFlow的基本设计原则是将所有具有数据依赖性的数据处理流程划分在同一个WorkFlow中，以保证相关数据处理的正确性。WorkFlow的设计按照具体需求，推荐采用项目+主题+处理频度：WF_项目名_主题名_Day，如WF_企业驾驶舱_营销_Day。5.3数据仓库运维阶段5.3.1需求变更5.3.1.1概述数据仓库运维期间的少量需求变更：遵循数据仓库实施流程开展，即修改模型设计。目前针对概念模型的调整一般都为业务模式调整，一般会作为一个项目来进行调整，在一般的运维工作中涉及的主要为逻辑模型及物理模型的调整。因此本文主要讲解逻辑模型及物理模型的运维标准。5.3.1.2逻辑模型运维要求如下：8T/GDMA4—2018a)数据源变更：需要评估数据源系统由于系统升级或业务变更而导致的数据源变更对企业逻辑数据模型及其映射的影响，对于影响现有数据接口的数据源变更，需要调整企业逻辑数据模型和数据映射关系，进行模型的版本化管理；b)分析需求变更：需要评估分析体系在运维阶段的业务分析需求变更及调整对数据模型的影响，对于需要新增或变更接口的分析需求，调整《数据仓库逻辑模型》、和《ETL详细设计》（业务部分），进行模型的版本化管理。5.3.1.3物理模型运维在数据仓库运维阶段，物理模型的日常维护工作主要分为逻辑模型调整导致的变更和技术程序优化导致的变更两种类型。a)逻辑模型导致的变更:评估由于逻辑数据模型及其映射的变更，对于现有物理模型变更，需要调整相关物理数据模型和《ETL详细设计》（技术部分），进行模型的版本化管理；b)技术优化导致的变更:评估为提升程序运行效率，而对数据物理模型进行技术性的字段调整。5.3.2异常处理5.3.2.1概述数据仓库异常处理：异常处理主要针对ETL异常处理进行说明，存储过程的运行异常，则由开发人员进行基