《ETL方法介绍》课件

《ETL方法介绍》PPT课件本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！《ETL方法介绍》PPT课件本课件PPT仅供大家学习内容ETL根本概念ETL过程讲解PICCETL总体架构PICCETL开发方法内容ETL根本概念ETL根本概念－－主要目的将源系统数据经过抽取／转换过程，最终装载到目标系统

将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据ETL根本概念－－主要目的将源系统数据经过抽取／转换过程，最ETL根本概念－－术语解释

ETLExtract-Transform-Load的缩写，数据抽取〔Extract〕、转换〔Transform〕、装载〔Load〕的过程

DWDataWarehousing，根据Bill.Inmon的定义，“数据仓库是面向主题的、集成的、稳定的、随时间变化的，主要用于决策支持的数据库系统〞

MetaData元数据，就是描述数据的数据，指在数据仓库建立过程中所产生的有关数据源定义、目标定义、转换规那么等相关的关键数据ETL根本概念－－术语解释ETLExtract-TransETL根本概念－－数据仓库中的位置ETL根本概念－－数据仓库中的位置ETL根本概念－－数据质量正确性(Accuracy):数据是否正确表达在现实或可证实的来源

完整性(Integrity):数据之间的参照完整性是否存在或一致

一致性(Consistency):数据是否被一致的定义或理解

完备性(Completeness):所有需要的数据是否都存在

有效性(Validity):数据是否在企业定义的可承受的范围之内

时效性(Timeliness):数据在需要的时间是否有效

可获取性(Accessbility):数据是否易于获取、易于理解和易于使用ETL根本概念－－数据质量正确性(Accuracy):数据ETL根本概念－－数据质量原因业务系统不同时期数据模型不一致

业务系统不同时期业务过程有变化

各个源系统之间相关信息不一致

遗留系统和新业务、管理系统数据集成不完备带来的不一致性

源系统缺少输入验证过程，不能阻止非法格式的数据进入系统

可以验证但不能改正数据，验证程序不能发现格式正确但内容不正确的错误

源系统不受控制的更改，而这种更改不能及时的传播到受影响的系统

数据由多个穿插的访问界面，难以统一管理数据质量问题

缺少参照完整性检查低劣的源系统设计

数据转换错误，比方ETL过程错误或数据迁移过程的错误

源系统与数据仓库系统的数据组织方式完全不同ETL根本概念－－数据质量原因业务系统不同时期数据模型不一ETL根本概念－－数据转换空值处理

标准化数据格式

拆分数据

验证数据合法性

数据替换

实现数据规那么过滤

数据排序

数据类型统一转换

......ETL根本概念－－数据转换空值处理

ETL过程讲解－－逻辑架构ETL过程讲解－－逻辑架构ETL过程讲解－－Extract设计原那么为提高ETL效率，数据在进入ETL系统后的EXF文件都将转换为FlatText文件格式从ETL程序设计的灵活性和整体构造的一致性考虑，尽量采用Pull的方式，减少对源系统的影响和对其他开发队伍的依赖，并减少网络压力由于BatchWindows的限制，如果日源数据量大于5GB那么必须考虑采用Push的方式以提高传送速度，如，可以由源系统将数据转换为FlatText文件后，由ETL程序采用FTP的方式进展传送EXF的文件格式接近数据源的数据构造定义在Extract过程中过滤数据仓库不需要的数据记录和字段Push和PullPush在源系统上根据定义的数据格式将每日增量数据生成数据文件，再通过FTP或文件拷贝的方式传送给ETL程序处理Pull由ETL程序通过DRDA或ODBC等数据库协议直接访问源数据库获取所需数据进展处理ETL过程讲解－－Extract设计原那么ETL过程讲解－－数据转换过程中产生的文件EXF(ExtractedFormat)由数据源Extract产生的文件，文件构造与Source相似，经过过滤，局部字段被忽略。

CIF(CommonInterfaceFormat)CIF是ETL经过C/S/S过程产生的中间数据文件。

PLF(Pre-LoadFormat)经过数据转换，用于直接加载到数据仓库的文本文件，其数据构造与数据仓库中的表定义一致。ETL过程讲解－－数据转换过程中产生的文件EXF(ExtrETL过程讲解－－数据转换过程中一般需要完成的操作数据检查与稽核数据类型统一转换赋缺省值数据排序数据拆分筛选不合格的数据并记录到Rejected文件中根据加载要求，CIF文件分为Load/Append和Snapshot两类数据拷贝数据翻译数据聚合复杂计算数据匹配对于必须通过比较才能确定变化的记录的数据，通过比较产生Delta数据和新的基准数据ETL过程讲解－－数据转换过程中一般需要完成的操作数据检查与ETL过程讲解－－数据处理流程ETL过程讲解－－数据处理流程ETL过程讲解－－数据对照开发流程SourceTableCIFTargetTablePLF1236EXF5641、收集整理所有数据源，定义源数据构造(与EXF一样〕2、根据物理模型设计定义数据仓库数据〔与PLF一样〕构造3、设计源数据表〔文件〕与数据仓库数据表对应关系〔Table

Mapping)，确定Pilot的数据源范围4、设计源数据字段与数据仓库字段的数据对照5、设计CIF的数据构造6、设计源数据字段－EXF－CIF－PLF－数据仓库数据字段的数

据对照ETL过程讲解－－数据对照开发流程SourceCIFTargPICCETL总体架构－－处理机制中间数据存放机制通用方式：文本文件或者数据库表PICC：数据库表

数据装载机制通用方式：全量装载、增量装载和全／增量混合装载PICC：全量装载

数据装载周期通用方式：日、周、旬、月、季、半年、年PICC：月以上

数据映射完成机制通用方式：程序处理、工具处理以及混合处理PICC：程序处理PICCETL总体架构－－处理机制中间数据存放机制PICCETL总体架构－－应用框架PICCETL总体架构－－应用框架PICCETL总体架构－－数据抽取〔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->CoreSystem中

把新系统车险数据导出的24张表数据导入到表群EXF->NewSystem中

把新系统财险数据导出的8张表数据导入到表群EXF->NewSystem中

把手工整理的20张Excel表数据导入到表群EXF->External中PICCETL总体架构－－数据抽取〔Extract〕从旧PICCETL总体架构－－数据抽取〔Extract〕PICCETL总体架构－－数据抽取〔Extract〕PICCETL总体架构－－数据转换〔1〕〔Transform1〕在CIF第一层表中每生成一条记录产生一个唯一的记录ID进展数据类型转换进展空值替换进展数据格式统一进展新旧系统到本系统的代码映射根据业务需求对数据进展必要的转换其它一些代码的转换〔例如RiskCode、KindCode替换成Product_id等〕把不符合数据质量要求的记录写入相应的Reject表中，并调查原因，重新制定映射和清洗规那么在CIF第二层表中每生成一条记录产生一个表内唯一的FundamentalID，注意在这个过程不进展任何整合过程，而是从CIF第一层每过来一条相关记录就生成一个FundamentalID根据数据的Mapping，生成各个数据域的值在CIF第二层表中写入相关的CIF第一层表的唯一记录ID在CIF第二层表中写入与数据级别相关的标识字段值，例如Policy_no、Cliam_no、Coverage_no等需要回写的整合过的FundamentalID，放入空值把不符合数据质量要求的记录写入相应的Reject表中，并调查原因，重新制定映射和整合规那么PICCETL总体架构－－数据转换〔1〕〔TransforPICCETL总体架构－－数据转换〔1〕〔Transform1〕PICCETL总体架构－－数据转换〔1〕〔TransforPICCETL总体架构－－数据转换〔2〕〔Transform2〕进展从CIF第二层表到PLF的FundamentalEntities的整合过程，生成真正的全局唯一的FundamentalID向CIF第二层表中回写全局唯一的FundamentalID把CIF-Program表群导入到PLF相应表中把EXF-External表群导入到PLF相应表中由PLF的FundamentalEntities生成相应的AnchorEntities数据把全局唯一的AnchorEntityID回写到相应的FundamentalEntity中根据CIF第二层表中的附加字段〔例如Policy_no等〕，生成FundamentalEntity级别的RelationshipEntity数据如果CIF第二层缺乏以满足生成RelationshipEntity数据的需要，那么根据附加的标识CIF第一层数据来源的唯一记录字段中的值〔唯一记录ID〕，回溯到CIF第一层表中找到相关字段的值，并据此生成相应的RelationshipEntity数据AnchorEntity级别的RelationshipEntity数据要根据AnchorEntity的ID回溯到FundamentalEntity，并进一步回溯到CIF第二层表中才能获取到根据PLF-Atomic表群生成PLF-Aanlytical表群的数据，把此过程需要临时存放的数据写入到CIF第二层的Analytical表群中把不符合数据质量要求的记录写入相应的Reject表中，并调查原因，重新制定映射和整合规那么PICCETL总体架构－－数据转换〔2〕〔TransforPICCETL总体架构－－数据转换〔2〕〔Transform2〕PICCETL总体架构－－数据转换〔2〕〔TransforPICCETL总体架构－－数据装载〔Load〕把PLF-Atomic表群按照外键约束顺序依次倒入到EnterpriseModel的Atomic表群中

把PLF-Analytical表群按照外键约束顺序依次倒入到EnterpriseMode的Analytical表群中

把PLF-Dimension表群按照外键约束顺序依次倒入到EnterpriseModel的Dimension表群中PICCETL总体架构－－数据装载〔Load〕把PLFPICCETL总体架构－－数据装载〔Load〕PICCETL总体架构－－数据装载〔Load〕PICCETL开发方法－－开发流程程序设计开发阶段框架程序设计与开发映射程序模板与例子设计与开发映射程序设计与开发控制程序设计与开发

程序测试阶段映射程序单元测试结合框架和控制程序集成测试PICCETL开发方法－－开发流程程序设计开发阶段PICCETL开发方法－－程序调用流程PICCETL开发方法－－程序调用流程PICCETL开发方法－－EXF－CIF程序模板讲解PICCETL开发方法－－EXF－CIF程序模板讲解PICCETL开发方法－－－CIF〔Transform2〕程序模板讲解PICCETL开发方法－－－CIF〔Transform2〕PICCETL开发方法－－CIF内部转换程序模板讲解PICCETL开发方法－－CIF内部转换程序模板讲解PICCETL开发方法－－CIF－PLF程序模板讲解PICCETL开发方法－－CIF－PLF程序模板讲解PICCETL开发方法－－PLFAnchorEntity程序模板讲解PICCETL开发方法－－PLFAnchorEntitPICCETL开发方法－－PLFRelationship程序模板讲解PICCETL开发方法－－PLFRelationship《ETL方法介绍》PPT课件本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！本课件PPT仅供大家学习使用学习完请自行删除，谢谢！《ETL方法介绍》PPT课件本课件PPT仅供大家学习内容ETL根本概念ETL过程讲解PICCETL总体架构PICCETL开发方法内容ETL根本概念ETL根本概念－－主要目的将源系统数据经过抽取／转换过程，最终装载到目标系统

将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据ETL根本概念－－主要目的将源系统数据经过抽取／转换过程，最ETL根本概念－－术语解释

ETLExtract-Transform-Load的缩写，数据抽取〔Extract〕、转换〔Transform〕、装载〔Load〕的过程

DWDataWarehousing，根据Bill.Inmon的定义，“数据仓库是面向主题的、集成的、稳定的、随时间变化的，主要用于决策支持的数据库系统〞

MetaData元数据，就是描述数据的数据，指在数据仓库建立过程中所产生的有关数据源定义、目标定义、转换规那么等相关的关键数据ETL根本概念－－术语解释ETLExtract-TransETL根本概念－－数据仓库中的位置ETL根本概念－－数据仓库中的位置ETL根本概念－－数据质量正确性(Accuracy):数据是否正确表达在现实或可证实的来源

完整性(Integrity):数据之间的参照完整性是否存在或一致

一致性(Consistency):数据是否被一致的定义或理解

完备性(Completeness):所有需要的数据是否都存在

有效性(Validity):数据是否在企业定义的可承受的范围之内

时效性(Timeliness):数据在需要的时间是否有效

可获取性(Accessbility):数据是否易于获取、易于理解和易于使用ETL根本概念－－数据质量正确性(Accuracy):数据ETL根本概念－－数据质量原因业务系统不同时期数据模型不一致

业务系统不同时期业务过程有变化

各个源系统之间相关信息不一致

遗留系统和新业务、管理系统数据集成不完备带来的不一致性

源系统缺少输入验证过程，不能阻止非法格式的数据进入系统

可以验证但不能改正数据，验证程序不能发现格式正确但内容不正确的错误

源系统不受控制的更改，而这种更改不能及时的传播到受影响的系统

数据由多个穿插的访问界面，难以统一管理数据质量问题

缺少参照完整性检查低劣的源系统设计

数据转换错误，比方ETL过程错误或数据迁移过程的错误

源系统与数据仓库系统的数据组织方式完全不同ETL根本概念－－数据质量原因业务系统不同时期数据模型不一ETL根本概念－－数据转换空值处理

标准化数据格式

拆分数据

验证数据合法性

数据替换

实现数据规那么过滤

数据排序

数据类型统一转换

......ETL根本概念－－数据转换空值处理

ETL过程讲解－－逻辑架构ETL过程讲解－－逻辑架构ETL过程讲解－－Extract设计原那么为提高ETL效率，数据在进入ETL系统后的EXF文件都将转换为FlatText文件格式从ETL程序设计的灵活性和整体构造的一致性考虑，尽量采用Pull的方式，减少对源系统的影响和对其他开发队伍的依赖，并减少网络压力由于BatchWindows的限制，如果日源数据量大于5GB那么必须考虑采用Push的方式以提高传送速度，如，可以由源系统将数据转换为FlatText文件后，由ETL程序采用FTP的方式进展传送EXF的文件格式接近数据源的数据构造定义在Extract过程中过滤数据仓库不需要的数据记录和字段Push和PullPush在源系统上根据定义的数据格式将每日增量数据生成数据文件，再通过FTP或文件拷贝的方式传送给ETL程序处理Pull由ETL程序通过DRDA或ODBC等数据库协议直接访问源数据库获取所需数据进展处理ETL过程讲解－－Extract设计原那么ETL过程讲解－－数据转换过程中产生的文件EXF(ExtractedFormat)由数据源Extract产生的文件，文件构造与Source相似，经过过滤，局部字段被忽略。

CIF(CommonInterfaceFormat)CIF是ETL经过C/S/S过程产生的中间数据文件。

PLF(Pre-LoadFormat)经过数据转换，用于直接加载到数据仓库的文本文件，其数据构造与数据仓库中的表定义一致。ETL过程讲解－－数据转换过程中产生的文件EXF(ExtrETL过程讲解－－数据转换过程中一般需要完成的操作数据检查与稽核数据类型统一转换赋缺省值数据排序数据拆分筛选不合格的数据并记录到Rejected文件中根据加载要求，CIF文件分为Load/Append和Snapshot两类数据拷贝数据翻译数据聚合复杂计算数据匹配对于必须通过比较才能确定变化的记录的数据，通过比较产生Delta数据和新的基准数据ETL过程讲解－－数据转换过程中一般需要完成的操作数据检查与ETL过程讲解－－数据处理流程ETL过程讲解－－数据处理流程ETL过程讲解－－数据对照开发流程SourceTableCIFTargetTablePLF1236EXF5641、收集整理所有数据源，定义源数据构造(与EXF一样〕2、根据物理模型设计定义数据仓库数据〔与PLF一样〕构造3、设计源数据表〔文件〕与数据仓库数据表对应关系〔Table

Mapping)，确定Pilot的数据源范围4、设计源数据字段与数据仓库字段的数据对照5、设计CIF的数据构造6、设计源数据字段－EXF－CIF－PLF－数据仓库数据字段的数

据对照ETL过程讲解－－数据对照开发流程SourceCIFTargPICCETL总体架构－－处理机制中间数据存放机制通用方式：文本文件或者数据库表PICC：数据库表

数据装载机制通用方式：全量装载、增量装载和全／增量混合装载PICC：全量装载

数据装载周期通用方式：日、周、旬、月、季、半年、年PICC：月以上

数据映射完成机制通用方式：程序处理、工具处理以及混合处理PICC：程序处理PICCETL总体架构－－处理机制中间数据存放机制PICCETL总体架构－－应用框架PICCETL总体架构－－应用框架PICCETL总体架构－－数据抽取〔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->CoreSystem中

把新系统车险数据导出的24张表数据导入到表群EXF->NewSystem中

把新系统财险数据导出的8张表数据导入到表群EXF->NewSystem中

把手工整理的20张Excel表数据导入到表群EXF->External中PICCETL总体架构－－数据抽取〔Extract〕从旧PICCETL总体架构－－数据抽取〔Extract〕PICCETL总体架构－－数据抽取〔Extract〕PICCETL总体架构－－数据转换〔1〕〔Transform1〕在CIF第一层表中每生成一条记录产生一个唯一的记录ID进展数据类型转换进展空值替换进展数据格式统一进展新旧系统到本系统的代码映射根据业务需求对数据进展必要的转换其它一些代码的转换〔例如RiskCode、KindCode替换成Product_id等〕把不符合数据质量要求的记录写入相应的Reject表中，并调查原因，重新制定映射和清洗规那么在CIF第二层表中每生成一条记录产生一个表内唯一的FundamentalID，注意在这个过程不进展任何整合过程，而是从CIF第一层每过来一条相关记录就生成一个FundamentalID根据数据的Mapping，生成各个数据域的值在CIF第二层表中写入相关的CIF第一层表的唯一记录ID在CIF第二层表中写入与数据级别相关的标识字段值，例如Policy_no、Cliam_no、Coverage_no等需要回写的整合过的FundamentalID，放入空值把不符合数据质量要求的记录写入相应的Reject表中，并调查原因，重新制定映射和整合规那么PICCETL总体架构－－数据转换〔1〕〔TransforPICCETL总体架构－－数据转换〔1〕〔Transform1〕PICCETL总体架构－－数据转换〔1〕〔TransforPICCETL总体架构－－数据转换〔2〕〔Transform2〕进展从CIF第二层表到PLF的FundamentalEntities的整合过程，生成真正的全局唯一的FundamentalID向CIF第二层表中回写全局唯一的FundamentalID把CIF-Program表群导入到PLF相应表中把EXF-External表群导入到PLF相应表中由PLF的FundamentalEntities生成相应的AnchorEntities数据把全局唯一的AnchorEntityID回写到相应的FundamentalEntity中根据CIF第二层表中的附加字段〔例如Policy_no等〕，生成FundamentalEntity级别的RelationshipEntity数据如果CIF第二层缺乏以满足生成RelationshipEntity数据的需要，那么根据附加的标识CIF第一层数据来源的唯一记录字段中的值〔唯一记录ID〕，回溯到CIF第一层表中找到相关字段的值，并据此生成相应的RelationshipEntity数据AnchorEntity级别的RelationshipEntity数