BI 主要环节 ETL 相关知识

BI架构-BI主要环节ETL相关知识 主要功能 ：将源系统的数据加载到数据仓库 及数据集市层中；主要问题体现： 复杂的源数据环境，包括繁多的数据种类、巨大的加载数据量、错综复杂的数据关系和参差不齐的数据质量常见术语 ETL：数据抽

BI架构-BI 主要环节 ETL 相关知识

---

主要功能  ：将源系统的数据加载到数据仓库 及数据集市层中； 主要问题体现：  复杂的源数据环境，包括繁多的数据种类、巨大的加载数据量、错综复杂的数据关系和参差不齐的数据质量 常见术语    ETL：数据抽取、转换、加载（Extract/Transform/Load）  EXF：抽取的文件（Extract File）  CIF：共用接口文件（Common Interface File）  PLF：预加载文件（Preload File）  LDF：加载文件（Load File）  DW：数据仓库（Data Warehouse）  DM：数据集市（Data Mart）  GC：共用接口文件组（CIF Group），将一对EX（抽取）和CV（变换）程序组合的程序  GE：实体组（Entity Group），将TR（转换）和LD（加载）程序组合的程序 ETL 功能架构：   
由上图 可以看出 架构可分为三个部分 1、管理调度            根据目标数据表的更新周期和源数据就绪时间，制定日常数据的ETL的时刻表。管理员通过ETL工具的作业调度功能进行运行时刻设置，使得ETL工具自动在规定条件满足时启动相应的ETL作业。每个目标数据表ETL过程对应一组顺序执行的实体作业（包括转换作业和加载作业）形成的一个 序列（Sequence），每个CIF（共用接口文件）的ETL过程则对应一组顺序执行CIF作业(包括抽取作业和变换作业)形成的一个序列。这些ETL作业将其中的每个步骤，即抽取、变换、转换、加载等ETL功能模块有机地联系起来。而作业调度是将CIF逻辑的作业和实体逻辑的作业按照GC（CIF组）与GE（实体组）的对应关系联系起来，从而控制该ETL过程的运作 2、应用功能         ETL应用模块层次中包含实现每个ETL步骤的程序及对这些步骤进行归并及设定依赖性的程序，即 抽取（Extract）、变换（Convert）、转换（Transform）、加载（Load）程序。每个模块实现一个特定的功能，详述如下：      数据抽取（Extract）      数据变换（Convert/Clean）      数据转换（Transform）      数据加载（Load）      每个阶段之间以数据文件作为接口，即数据抽取（Extract）阶段读取数据源产生EXF数据，数据变换（Converting/Cleansing）阶段读取EXF数据产生CIF数据，数据转换（Transform）阶段读取CIF数据产生LDF数据（如果有预加载过程还可能产生中间PLF数据），数据加载（Load）阶段读取LDF数据加载到数据仓库或数据集市。      上述将数据抽取、转换和加载分隔开，以CIF格式作为目标表和数据源之间的桥梁，从而使每个功能相对独立，减少各功能相互间的耦合度，同时，每个模块的功能被细分后，逻辑更加简单，更容易控制开发错误，提高开发效率。另外，也便于系统运行过程中的错误追踪和异常恢复。为了验证ETL加载的数据的正确性，根据业务的要求，可能需要在数据仓库或数据集市中进行数据总计平衡检查（Amount Balance Check），该过程必须在相关的ETL表数据加载完成以后进行。     ETL各模块之间无任何调用关系，它们之间可能的关系仅仅是一个模块的输出文件是另一个模块需要读取和加工的文件。一个ETL功能模块就是一个ETL作业，每个功能模块的逻辑都只是ETL过程中一个相对独立的部分。 3、控制环境      ETL功能模块的运行需要由相应的参数进行控制，同时在各模块之间也存在很多控制文件及调用一些公共的功能，功能模块在运行过程中可能产生拒绝文件，针对功能模块的运行状况会有产生一些监控信息等等，这些对于ETL功能模块的运行起到控制与支撑的环境以及相应的维护管理程序构成ETL架构环境    上两层构成ETL应用，而ETL控制环境则为以上层次的每个应用程序提供支持，应用层次上独立的功能模块都通过更上一个层次的逻辑关系联系起来，使每个模块的功能更加清晰、明确 ETL模式      完全刷新（Refresh，Type 1）：数据库数据表中只包括最新的数据，每次加载均删除原有数据，然后完全加载最新的源数据。如大多数参数表的加载都采用这种模式。这种模式下，数据抽取程序抽取源数据中的所有记录，在加载前，将目标数据表清空，然后加载所有记录。为提高删除数据的速度，一般是采用Truncate清空数据表而不采用SQL Delete进行删除。      镜像增量（Snapshot Append，Type 2）：源数据中的记录定期更新，但记录中包括记录时间字段，源数据中保存了数据历史的记录，ETL可以通过记录时间将增量数据从源数据抽取出来以附加的方式加载到数据库中，数据的历史记录也会被保留在数据库中。      事件增量（Event Append，Type 3）：每一个记录是一个新的事件，相互之间没有必然的联系，新记录不是对原有记录数值的变更，记录包括时间字段，可以通过时间字段将新增数据抽取出来加载到数据库中      镜像比较（Snapshot Delta，Type 4）：数据仓库数据具有生效日期字段以保存数据的历史信息，而源数据不保留历史并且每天都可能被更新。因此，只能将新的镜像数据与上次加载的数据的镜像进行比较，找出变更部分（即Delta），更新历史数据被更新记录的生效终止日期，并添加变更后的数据。 ------------------------------------------------------------------------------------------  数据抽取(Extract) 数据抽取是从数据源获取所需数据的过程。数据抽取的主要工作有：  数据范围过滤，完全抽取源表所有记录或按指定日期进行增量抽取  抽取字段过滤，全部抽取源表所有字段或包括过滤掉不需要的源数据字段  抽取条件过滤，如过滤到指定条件的记录  数据排序，如按照抽取的指定字段进行排序 数据抽取可以采用 PULL和PUSH两种方式。PUSH就是指由源系统按照双方定义的数据格式，主动将符合要求的数据抽取出来，形成接口数据表或数据视图供ETL系统使用。PULL则是由ETL程序直接访问数据源来获取数据的方式。  数据变换(Convert) 任务是逐条记录的检查数据，将每个字段转换为遵循数据仓库标准的数据格式，即对数据类型和数据格式进行转换，并对空字段赋予适当的缺省值，形成规整的数据结构，对于不符合要求的数据，写入拒绝文件（Reject文件）中。 数据变换主要的工作有：  格式变换，如所有日期格式统一为yyyy-mm-dd；  赋缺省值，在数据仓库中定义取值不为空的字段在源数据对应的字段可能存在没有取值的记录，这时根据业务需要，可能有两种处理办法，一是将该记录写入到Reject文件中，由业务部门根据Reject文件检查并修补源数据，另一种是在Convert阶段直接赋一个缺省值；  类型变换, 如将源系统的Number类型转为Varchar2类型等  代码转换，某些字段经过代码升级以后，将老的代码转为新的代码等。  数值转换，如数值单位由万元转为元等。  去除空格，去除字符类型的数据中的前后空格 数据转换(Transform)     按照目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。     数据转换主要包括格式和字段合并与拆分、数据翻译、数据匹配、数据聚合以及其它复杂计算等。  字段合并与拆分 字段合并是指源数据的多个字段合并为目标数据的一个字段。 字段拆分是指将源数据中一个表的一个字段拆分为目标数据的多个字段。  赋缺省值 对于数据库中有的字段，在源系统中并没有相对应的源字段，这时根据模型的设计，可能需要缺省赋一个值。与数据变换阶段的赋缺省值功能相比，这里可能涉及更复杂的数据修正规则。  数据排序(Sort) 转换程序有时需要对于两个或多个CIF文件合并，在合并之前需要将CIF文件按所要求的键值排好序，这样可以加快合并的速度，排序的过程在Transform之前进行。  数据查找(Lookup) 将源系统中一些表示状态、类型等的代码直接翻译为其所表达的意思，或反之。数据翻译需要用到参考表（Reference Table），数据参考表一般是字典表或根据源数据与目标数据的定义手工产生，如果数据翻译时在参考表中找不到对应的对照，根据业务规则，需要将对应的记录Reject出来或赋缺省值。  数据合并(Merge) 按一定条件（一般是key值相等）对数据进行合并，找出描述同一对象的分布在不同数据表中的记录，并把这些记录联系起来。数据合并其实是数据查找的一种特殊情况，主要用于数据量特别大的情况，数据合并在实现方式上一般先对要合并的两个表分别排序(Sort)，然后顺序对两个表的记录进行匹配合并，这样可以大大加快处理的速度。  数据聚合(Aggregate) 对数据按照不同分组进行汇总等统计计算，一般是用于汇总表的计算，主要的聚合种类有：                                  求和                                  求平均值                                  求记录数                                  求最小值                                  求最大值 原则上，ETL只处理规律而重复性大的数据聚合，如汇总、取平均值、找最大、最小值等，而不用于复杂计算，以减少开发成本和系统负载。对于不规律而且复杂的计算，应该由源系统端将数据计算好或在数据仓库端开发专门的计算程序（如存储过程）在ETL加载完成以后调用  文件比较(File Compare) 对应于四种ETL模式，数据转换为PLF文件后需要进行不同的处理，在Type1、Type2和Type3模式下，生成LDF文件可以直接由数据加载过程加载到数据仓库中，而Type4则由于存在有效日期，需要将当日的snapshot数据（PLF文件）与历史数据镜像（PLF文件）进行比较，找出需要添加和需要更新的记录，然后产生真正可以向数据库加载的LDF文件，再由数据加载过程将PLF文件加载到数据仓库中。  代理键值(Surrogate Key)分配 对于数据仓库中设计的代理键，代理键值的分配一般有两种方法，一种是在数据库中通过数据库的功能将该字段设为自动增加类型，一种是由ETL过程来分配键值，代理键值一般为数值型，并且必须保证键值分配不重复。在本项目中采取的是后者。  缓慢变化增量获取(Slow Change Capture) 对于含有生效日期、截止日期等字段的缓慢变化维度表（拉链表），如果源端无法提供增量的信息（包括通过时间戳获取），则ETL需要将当日的快照数据生成PLF文件，再与上一日的数据镜像（从目标数据快照区中获取）进行比较，找出需要添加和需要更新的记录，然后再产生真正可以向数据库加载的LDF文件。  行列转换(Pivot) 根据模型的一些特殊要求，需要将源系统的横表转成纵表或将纵表转成横表，如源表将12个月设计为12个字段，而目标表需要用一个月份字段替换，而需要将源表的一条记录按月份对应转成12条记录等。  RI检查(RI Check) 对于存在RI关系的表，进行RI的检查，将有RI问题的数据拒绝出来。由于本项目中使用的Greenplum数据库无法执行外键约束，使用ETL检查RI是确保数据质量的关键点。  其它复杂计算 在数据库中定义的某些字段需要按照业务规则进行复杂计算才能得到，主要有两类： 1．主要针对一些在数据源中找不到直接对应的字段，需要在ETL过程中通过相关字段计算才能得出的字段； 2．原则上复杂的计算并不在ETL中完成，对于一定需要由ETL来完成的复杂计算字段，采取在ETL加载时该字段先留空，在加载完成以后调用的存储过程来计算，但为了管理与调度的统一，可以在ETL 调度工具中调用存储过程以统一调度。 数据加载(Load)     经过数据转换生成的PLF文件的结构与数据库数据表的结构完全一致，可以直接通过数据加载工具，以Bulk Load的方式加载到数据仓库中。数据加载工作将分为3步进行。  预加载（Pre-Load） 在真正进行数据加载之前根据实际要加载的表的情况，主要是针对明细及大事实表，基于性能考虑，还可能需要完成以下准备工作：                                   删除数据仓库中数据表的索引。                                   删除主键。  加载（Load） Load主要完成将PLF文件的数据加载到数据库的表中，需要用到的加载方式有三种：                              Insert：只需要将PLF文件所有数据完全Insert到目标表中。                              Upsert：需要对目标表同时做Update及Insert操作，根据主键，对于已有的记录进行Update操作，对于不存在的记录做Insert的操作，对于数据量大的表，由于此操作的效率非常低，可以采用先将PLF文件分割为Delete文件及Insert文件，然后先将Delete文件中的记录根据主键对应从数据仓库中删除，然后再从Insert文件中将所有记录全部Insert到目标表中。                              Refresh：即将目标表的数据完全更新，一般的做法是先Truncate目标表的数据，然后再完全Insert要加载的记录  后加载（Post-Load） Post-Load阶段主要完成在数据加载完成以后的相关的环境整理工作，主要包括如下一些工作：                  重新生成索引：如果在Pre-Load阶段做了删除数据表索引动作，则在Post-Load阶段需要重建。                  重新创建主键：如果在Pre-Load阶段做了删除主键动作，则在Post-Load阶段需要重建。                  文件清理：删除不需要的临时文件，如空拒绝文件等                  生成加载后数据键文件：数据加载完成以后，如果后续有ETL过程需要根据该表进行RI检查，则需要将该表的主键抽取出单独的键文件。                  后抽取数据文件：如果所加载的数据表后续被其它表加载时大量查找（Lookup），可以考虑抽取出文件。 ETL作业依赖和进程调度 ETL作业之间存在很多依赖，关系到相关数据表的加载顺序，本设计从两个方面体现ETL作业的依赖关系：一是每个作业组完成以后应产生与作业名称相对应的消息文件，对于后续需要依赖该作业完成的作业组，首先应等待该消息文件的出现再继续。二是将一组在同一时间开始运行并具有依赖关系的作业组合在一起，开发成网状关系的序列作业（Sequence Job）以供ETL的进程调度用 ETL作业进程调度的功能比较单纯，就是在规定的时刻启动程序，并记录系统运行情况和运行结果。 不同数据表的更新周期不同，因此，进程调度需要能够支持日周月等多种不同的启动周期，并通过设定启动时间来确定每个任务在何时启动运行。         只有日常数据加载才需要考虑进程调度的问题。而对于初始数据及历史数据的加载，由于是一次性的工作，将采取手工启动加载的方式，所以无需制定对初始数据及历史数据加载制度化的进程调度。         在ETL程序的抽取过程运行之前一定要保证其抽取的源数据已经准备就绪，本项目使用Oracle Golden Gate在数据缓存区保留一份源系统源表的实时副本，在指定的时间启动夜间批量处理窗口时统一抓取快照，载入快照区（在第3章详述）。手工上传数据在上传当日接受数据质量稽查，并载入过渡表。但此类数据往下游数据集市的流转只在夜间跑批窗口与系统来源数据合并进行。即当日上传的手工数据原则上只在次日从前端应用中体现。
逻辑部署部分

---

大体看来分为三层  用户层 ---用户接入 三种方式 移动端、web端、系统用户 应用层 ---负载均衡器 ：  支撑BI相关服务请求的均衡应用负载，提高应用的可用性。负载均衡设备安装于两台或多台Server之前                               ，                                  它将请求交给负载最轻的服务器                 数据应用服务器 ：整个BI应用架构的数据组装部分，支撑包含驾驶舱、指标查询、主题分析、战略管理等高效的                                              安全的数据应用服务，应用服务器要满足高可用和性能平行扩展的要求。                                               德邦采用集群的方式进行部署                 数据集成服务器、支撑数据抽取、数据转换、数据稽核，服务器要满足高可用和性能平行扩展的要求                 系统管理服务器  支撑系统监控，预警管理和用户权限的管理                 Web服务器      支撑Web网页的访问服务。静态内容，包括HTML，JavaScript，IMG和JPG图片等，                                          服务器要满足高可用的要求       数据资源层    包含缓冲区、TDR、数据仓库与集市、文件库、日志库以及备份库
系统整体模块流程

---

模块依赖关系