《ETL方法介绍》PPT课件

Header text,Level One Text,Level Two Text,Level Three Text,Level Four Text,Level Five Text,中国人民保险公司保险数据模型及业务标准化|保密,|,*,*,Business Consulting Services,中国人民保险公司,保险数据模型及业务标准化,数据模型及业绩指标和风险测算分析,ETL,方法介绍,200,4,年,4,月,15,日,内容,ETL,基本概念,ETL,过程讲解,PICC ETL,总体架构,PICC ETL,开发方法,2,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念主要目的,将源系统数据经过抽取转换过程，最终装载到目标系统,将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据,3,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念术语解释,ETL,Extract-Transform-Load,的缩写，数据抽取（,Extract）、,转换（,Transform）、,装载（,Load）,的过程,DW,DataWarehousing，,根据,Bill.Inmon,的定义，,“,数据仓库是面向主题的、集成的、稳定的、随时间变化的，主要用于决策支持的数据库系统,”,MetaData,元数据，就是描述数据的数据，指在数据仓库建设过程中所产生的有关数据源定义、目标定义、转换规则等相关的关键数据,4,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念数据仓库中的位置,5,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念数据质量,正确性(,Accuracy):,数据是否正确体现在现实或可证实的来源,完整性(,Integrity):,数据之间的参照完整性是否存在或一致,一致性(,Consistency):,数据是否被一致的定义或理解,完备性(,Completeness):,所有需要的数据是否都存在,有效性(,Validity):,数据是否在企业定义的可接受的范围之内,时效性(,Timeliness):,数据在需要的时间是否有效,可获取性(,Accessbility):,数据是否易于获取、易于理解和易于使用,6,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念数据质量原因,业务系统不同时期数据模型不一致,业务系统不同时期业务过程有变化,各个源系统之间相关信息不一致,遗留系统和新业务、管理系统数据集成不完备带来的不一致性,源系统缺少输入验证过程，不能阻止非法格式的数据进入系统,可以验证但不能改正数据，验证程序不能发现格式正确但内容不正确的错误,源系统不受控制的更改，而这种更改不能及时的传播到受影响的系统,数据由多个交叉的访问界面，难以统一管理数据质量问题,缺少参照完整性检查低劣的源系统设计,数据转换错误，比如,ETL,过程错误或数据迁移过程的错误,源系统与数据仓库系统的数据组织方式完全不同,7,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,基本概念数据转换,空值处理,规范化数据格式,拆分数据,验证数据合法性,数据替换,实现数据规则过滤,数据排序,数据类型统一转换,.,8,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解逻辑架构,9,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解,Extract,设计原则,为提高,ETL,效率，数据在进入,ETL,系统后的,EXF,文件都将转换为,Flat Text,文件格式,从,ETL,程序设计的灵活性和整体结构的一致性考虑，尽量采用,Pull,的方式，减少对源系统的影响和对其他开发队伍的依赖，并减少网络压力,由于,Batch Windows,的限制，如果日源数据量大于5,GB,则必须考虑采用,Push,的方式以提高传送速度，如，可以由源系统将数据转换为,Flat Text,文件后，由,ETL,程序采用,FTP,的方式进行传送,EXF,的文件格式接近数据源的数据结构定义,在,Extract,过程中过滤数据仓库不需要的数据记录和字段,Push,和,Pull,Push,在源系统上根据定义的数据格式将每日增量数据生成数据文件，再通过,FTP,或文件拷贝的方式传送给,ETL,程序处理,Pull,由,ETL,程序通过,DRDA,或,ODBC,等数据库协议直接访问源数据库获取所需数据进行处理,10,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解数据转换过程中产生的文件,EXF(Extracted Format),由数据源,Extract,产生的文件，文件结构与,Source,相似，经过过滤，部分字段被忽略。,CIF(Common Interface Format),CIF,是,ETL,经过,C/S/S,过程产生的中间数据文件。,PLF(Pre-Load Format),经过数据转换，用于直接加载到数据仓库的文本文件，其数据结构与数据仓库中的表定义一致。,11,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解数据转换过程中一般需要完成的操作,数据检查与稽核,数据类型统一转换,赋缺省值,数据排序,数据拆分,筛选不合格的数据并记录到,Rejected,文件中,根据加载要求，,CIF,文件分为,Load/Append,和,Snapshot,两类,数据拷贝,数据翻译,数据聚合,复杂计算,数据匹配,对于必须通过比较才能确定变化的记录的数据，通过比较产生,Delta,数据和新的基准数据,12,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解数据处理流程,13,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,ETL,过程讲解数据对照开发流程,Source,Table,CIF,Target,Table,PLF,1,2,3,6,EXF,5,6,4,1、收集整理所有数据源，定义源数据结构(与,EXF,相同）,2、根据物理模型设计定义数据仓库数据（与,PLF,相同）结构,3、设计源数据表（文件）与数据仓库数据表对应关系（,Table Mapping)，,确定,Pilot,的数据源范围,4、设计源数据字段与数据仓库字段的数据对照,5、设计,CIF,的数据结构,6、设计源数据字段,EXFCIFPLF,数据仓库数据字段的数 据对照,14,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构处理机制,中间数据存放机制,通用方式：文本文件或者数据库表,PICC：,数据库表,数据装载机制,通用方式：全量装载、增量装载和全增量混合装载,PICC：,全量装载,数据装载周期,通用方式：日、周、旬、月、季、半年、年,PICC：,月以上,数据映射完成机制,通用方式：程序处理、工具处理以及混合处理,PICC：,程序处理,15,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构应用框架,16,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构数据抽取（,Extract）,从旧系统中导出20张表（详见应用框架组成部分），每个表的最后都要增加一个字段字段名称（,Source_system_labe)，,字段数据类型（,char(10)，,字段的值（,V2001A）,从新系统（车险数据库）中导出24张表（详见应用框架组成部分），每个表的最后都要增加一个字段：字段名称（,Source_system_labe)，,字段数据类型（,char(10)，,字段的值（,V2003A）,从新系统（财险数据库）中导出如下8张表（详见应用框架组成部分），每个表的最后都要增加一个字段：字段名称（,Source_system_labe)，,字段数据类型（,char(10)，,字段的值（,V2003P）,手工整理,Excel,表格共20张（详见应用框架组成部分）,把旧系统导出的20张表数据导入到表群,EXF-Core System,中,把新系统车险数据导出的24张表数据导入到表群,EXF-New System,中,把新系统财险数据导出的8张表数据导入到表群,EXF-New System,中,把手工整理的20张,Excel,表数据导入到表群,EXF-External,中,17,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构数据抽取（,Extract）,18,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构数据转换（1）（,Transform1）,在,CIF,第一层表中每生成一条记录产生一个唯一的记录,ID,进行数据类型转换,进行空值替换,进行数据格式统一,进行新旧系统到本系统的代码映射,根据业务需求对数据进行必要的转换,其它一些代码的转换（例如,RiskCode、KindCode,替换成,Product_id,等）,把不符合数据质量要求的记录写入相应的,Reject,表中，并调查原因，重新制定映射和清洗规则,在,CIF,第二层表中每生成一条记录产生一个表内唯一的,Fundamental ID，,注意在这个过程不进行任何整合过程，而是从,CIF,第一层每过来一条相关记录就生成一个,Fundamental ID,根据数据的,Mapping，,生成各个数据域的值,在,CIF,第二层表中写入相关的,CIF,第一层表的唯一记录,ID,在,CIF,第二层表中写入与数据级别相关的标识字段值，例如,Policy_no、Cliam_no、Coverage_no,等,需要回写的整合过的,Fundamental ID，,放入空值,把不符合数据质量要求的记录写入相应的,Reject,表中，并调查原因，重新制定映射和整合规则,19,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构数据转换（1）（,Transform1）,20,中国人民保险公司保险数据模型及业务标准化|保密,|,2024/10/6,PICC ETL,总体架构数据转换（2）（,Transform2）,进行从,CIF,第二层表到,PLF,的,Fundamental Entities,的整合过程，生成真正的全局唯一的,Fundamental ID,向,CIF,第二层表中回写全局唯一的,Fundamental ID,把,CIF-Program,表群导入到,PLF,相应表中,把,EXF-External,表群导入到,PLF,相应表中,由,PLF,的,Fundamental Entities,生成相应的,Anchor Entities,数据,把全局唯一的,Anchor Entity ID,回写到相应的,Fundamental Entity,中,根据,CIF,第二层表中的附加字段（例如,Policy_no,等），生成,Fundamental Entity,级别的,Relationship Entity,数据,如果,CIF,第二层不足以满足生成,Relationship Entity,数据的需要，则根据附加的标识,CIF,第一层数据来源的唯一记录字段中的值（唯一记录,ID），,回溯到,CIF,第一层表中找到相关字段的值，并据此生成相应的,Relationship Entity,数据,Anchor Entity,级别的,Relationship Entity,数据要根据,Anchor Entity,的,ID,回溯到,Fundamental Entity，,并进一步回溯到,CIF,第二层表中才能获取到,根据,PLF