企业数据仓库概要设计说明书ETL概要设计分册.doc

XXXX企业数据仓库概要设计说明书ETL概要设计分册（文档编码：OM-BIDW-C008）（版本01.00.000）未经许可，不得以任何形式抄袭XXXX版权所有，翻板必究OM数据仓库XXXX企业数据组2009年3月文档变更历史日期版本作者修改内容评审号变更控制号发布日期2009-04-0201.00.000黄浩建立初始版本2009-04-02目录1概述42设计原则和前提52.1整体部署52.2前提条件62.3设计原则63整体框架73.1ETL系统架构图73.2ETL系统功能模块描述74数据抽取模块94.1假设与约定94.2模块功能图94.3各子模块功能及处理流程115数据加载模块115.1假设与约定115.2数据加载模块图115.3数据加载功能模块描述126作业调度模块136.1模块概述136.2假设与约定136.3作业调度流程136.4ETL作业种类及调度实现方法147监控管理模块157.1监控管理模块图15ETL监控内容16附录1控制表及控制文件设计16附录2：文件目录及编码说明191 概述ETL是数据仓库系统开发中至关重要的一个过程，它涉及到对源数据的抽取、整合及各种转换，并最终形成面向用户的分析数据。由于数据仓库系统的数据源来自于多个分散的业务系统，对不同业务系统的数据整合及清洗转换将是一个复杂的过程，ETL过程决定了数据仓库系统获取数据的准确性。另外由于ETL包括数据抽取、数据清洗、数据转换及数据加载等数据处理过程，这些处理过程分散在不同的系统平台及开发工具上，对这些作业过程的统一调度将是一个重要的问题，作业调度涉及到系统的稳定性。2 设计原则和前提2.1 整体部署数据仓库ETL整体部署图² 网关通道数据库服务器是数据仓库的外围数据库系统，数据仓库中的绝大部分数据都将来自网关通道数据库服务器² 基于目前短彩部自身情况的考虑，在数据源服务器和数据仓库服务器之间增加一台FTP文件服务器，其功能有二：u 接口文件服务器，所有被接入DW的数据文件必须通过该服务器中转u 数据备份，来自网关的数据文件将长期保留在该服务器上，作为文件备份2.2 前提条件ETL概要设计将基于下面的前提条件² ETL逻辑：XXXX企业的数据虽然分布在不同的通道，但是各通道数据的共性度非常高，因此ETL中不存在逻辑复杂的转换（Transformation）及数据质量管理等流程，整个ETL只需要实现抽取（Extraction）和加载（Loading）两个功能即可² ETL工具：自主开发，具体开发语言待定？；² 作业调度工具：自主开发，具体开发语言待定？。2.3 设计原则² ETL应该是基于元数据库中定义好的处理规则；并且应由可复用的过程或相关组件来实现；² 用户或客户端应用程序不应该直接执行数据获取程序，数据仓库层所有的数据更新应该由数据获取过程自动控制；² 通过良好的设计和相关处理过程的协调使得系统的CPU处理时间最少；要充分利用系统和软件的并行处理性能；² ETL过程尽可能分解为独立的几个子处理过程以便于作业管理和调度；² 在ETL设计时，需要详细计算并考虑ETL的处理性能，时间窗口及错误处理控制。并详细考虑各个ETL任务在各台物理主机上的分布。² 需要提供一个监控统计模块对ETL的整个过程进行有效的监控和统计，提供GUI界面对ETL各个任务的处理情况进行统计和监控，例如每个ETL任务的状态、处理记录的条数、处理某个任务所用的时间、出错的情况等。3 整体框架本章从宏观体系结构的高度，概要叙述ETL系统的基本架构和设计思想，着重于描述架构的特点、系统主要组成、ETL各个部分的基本功能和它们之间的关系以及方案选择的出发点。3.1 ETL系统架构图ETL负责对业务系统数据及其他外部源数据进行数据抽取，并存放在数据仓库系统中的STAGE数据库中。ETL过程包括数据抽取和数据加载等几个逻辑上相对独立的数据处理过程。同时由于在ETL的处理过程中需要对ETL的错误处理以及作业调度等，ETL系统逻辑架构图如下图所示：3.2 ETL系统功能模块描述从上图可以看到ETL系统包括数据抽取、数据加载、错误处理、作业调度、监控管理等几个功能模块，各功能模块的具体情况如下：功能模块功能描述物理分布实现工具或方法数据抽取该模块获取外部系统数据以形成文本文件ETL.SERVER1 自主程序开发数据加载将数据抽取获得的文本文件通过数据加载阶段入库到STAGE中。DW SERVERORACLE 的数据加载程序SQLLDR错误处理错误处理模块针对作业在运行过程中出现错误时ETL系统应采取的作业控制措施DW SERVERETL ERVER自主开发程序作业调度作业调度主要实施整个系统中的作业运作，实时的监控作业运行的条件是否具备，一旦作业运行的条件具备，就将作业调入作业运行队列。DW SERVER自主开发程序监控管理在ETL的处理过程中需要实时对ETL的作业过程进行监控，以便了解ETL的执行状况并根据ETL执行过程中遇到的问题采取相应的措施。ETL SERVER/DW SERVER自主开发程序外部数据手工输入模块由于市公司数据集市存在一些需要手工输入或EXCEL等格式的外部数据，需把这些外部数据录入到市公司数据集市中PCEXCEL等辅助工具4 数据抽取模块4.1 假设与约定² 重复文件处理：文件重复上传，则采取覆盖式的处理办法，我们认为最后上传的文件是接近正确的文件。即如果某个文件先后上传了几次，那么我们最后入库的文件将是最后一个上传的文件。² 不对数据质量作监控：因为数据仓库的数据源平台比较单一，涉及到的网络比较平稳，因此不对数据抽取的结果文件作质量监控。² 系统的所有功能都由程序自动控制，原则上不允许手工干预4.2 模块功能图数据抽取模块如下图所示：该模块由自主程序开发实现，除主程序外，还包括目录扫描进程，文件处理进程，文件压缩，文件传输等几个子模块。该模块将由两个子程序驱动 ² 数据抽取子程序：该程序主要完成定时的数据抽取功能，并将抽取后得到的文本文件放到指定的目录下面。² FTP传输子程序：该程序定时扫描指定目录，如果文件到达，则将文件传输至接口机上。从上图中可以看到在该模块处理流程中用到一系列的控制表及控制文件，各控制表及控制文件的具体格式见附录14.3 各子模块功能及处理流程子模块作用及功能需用到的控制表或控制文件文件抽取进程该进程定时从数据源信息表中获取数据，对原始数据源进行抽取工作数据源文件信息表数据源定义表数据ETL日志表目录扫描进程该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。数据源定义表数据ETL日志表FTP传输进程若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面数据源定义表数据ETL日志表FTP状态表对于以上处理步骤的关键过程信息都要记录到数据ETL日志表表中。5 数据加载模块5.1 假设与约定² 因为数据抽取与数据加载是在不同的服务器上执行，为了同步两者之间的事件消息，我们需要建立一个消息同步的机制，即当数据抽取完成时，需要发给数据加载一个消息。即在传输数据接口文件完成后，附带一个传输完成标志文件。5.2 数据加载模块图数据加载模块流程如下图所示：5.3 数据加载功能模块描述子模块作用及功能需用到的控制表或控制文件目录扫描进程该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。数据源定义表数据ETL日志表FTP传输进程若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面数据源定义表数据ETL日志表FTP状态表接口文件登记表数据加载进程该进程定时对指定目录下的文件进行扫描，如果文件存在，则对文件进行解压、加载处理，最终将数据加载进STAGE数据库中数据源定义表数据ETL日志表6 作业调度模块6.1 模块概述作业调度是系统运转的支点，从数据加载到数据处理的全部脚本都由作业调度系统自动完成。其功能包括：² 事件扫描。按照作业的执行周期属性，周期性定时扫描作业所依赖的事件是否完成，以决定该作业是否执行；² 参数生成。根据作业配置信息，自动生成作业执行参数，并将参数传递给作业；² 追跑历史数据。因为某些原因，导致作业执行延时，在作业满足事件依赖后，需要追跑历史作业。6.2 假设与约定² 数据抽取模块分散到各数据源服务器，由各数据源服务器自行按照文档要求进行数据抽取，并将抽取结果FTP到指定的接口机的制定目录下² 为了简化调度流程，OMDW将采用事件扫描模式，而不是事件触发模式。即为了触发某个作业，程序会定时的扫描作业所依赖的事件，如果所依赖的事件全都完成，则执行该作业。6.3 作业调度流程 作业调度流程如下图：6.4 ETL作业种类及调度实现方法作业类型作业调度方式作业实现的功能作业的物理分布ORACLE 存储过程ORACLE存储过程调度存在两个要点：参数、依赖。因此，在作业调度设置页面要设置好参数与依赖的具体内容。数据处理DW SERVERORACLE 的数据加载数据加载主要依赖于接口文件，要点有：加载类型、文件信息。加载作业会根据加载类型及文件的相关信息（字段、文件名、字段分隔符等）生成控制文件数据加载DW SERVER7 监控管理模块7.1 监控管理模块图ETL监控管理示意图如下：功能模块功能描述物理分布实现工具或方法Job作业状态查询可以根据时间及job名查询job的处理的状态（成功或失败）开发Job处理时间查询查询某次job完成处理任务的开始时间和结束时间开发Job处理日志明细查看job处理的详细日志开发数据加载日志明细查看数据加载的详细日志工具提供（查询ORACLE的日志文件）Job出错告警在job出错时提供声音，短信等告警开发ETL监控内容从上图可以看到ETL监控包括如下几方面内容：² ETL系统在ETL作业出现错误或ETL数据处理质量没达到要求时通过ETL监控系统进行短信息、BP、EMAIL、声音、特殊图像等多种告警手段提供现场和远程告警；² ETL在作业处理过程中需把作业的处理时间、作业完成或失败信息等记录到数据库中并在ETL监控系统中进行显示以了解ETL作业的状态以及历史状况；² 另一方面需通过ETL监控系统了解ETL各作业的数据处理质量情况（如处理的记录数等）；附录1：控制表及控制文件设计Table Name: TE_ETL_CYCTable Comment: TE_调度周期表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKCYC_CDvarchar2(10)周期编号YesNoCYC_NAMVARCHAR2(20)周期名称NoTable Name: TE_ETL_DS_INFTable Comment: TE_数据抽取信息表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKDS_CDvarchar2(10)数据源编号YesNoDS_NAMVARCHAR2(20)数据源名称NoDS_FILE_NAM数据源文件名DS_TYP_CDvarchar2(10)数据源类型ETL_CYC_CD数据抽取周期ETL_TYP_CD抽取类型ETL_STATVARCHAR2(20)文件状态FLD_SPLIT字段分隔符COMPRESS_FLAGINTEGER压缩标识LOCAL_PATHVARCHAR2(20)本地路径JKJ_PATH远程路径CYC_CDvarchar2(10)周期编号YesDW_PATHVARCHAR2(20)仓库服务器路径NoTable Name: TE_ETL_DS_LOGTable Comment: TE_数据抽取日志表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKLOG_SEQvarchar2(10)日志序号YesNoDS_CD数据源编号NoYesLOG_TYPVARCHAR2(20)日志类型NoLOG_DTIMDATE日志时间LOG_STATVARCHAR2(20)日志状态FILE_FULL_NAM文件全拼Table Name: TE_ETL_FILE_INFTable Comment: TE_数据源文件信息表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKDS_CDvarchar2(10)数据源编号YesYesFLD_NAMVARCHAR2(20)字段名称NoFLD_TYP字段类型NoFLD_LENNUMBER(5)字段长度FLD_REMARKVARCHAR2(20)字段描述Table Name: TE_ETL_FILE_REGTable Comment: TE_接口文件登记表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKREG_SEQvarchar2(10)登记序号YesNoDS_CD数据源编号NoYesFILE_DTIMNUMBER(10)数据文件日期NoFILE_SEQNUMBER(5)文件序号REG_DTIMDATE登记时间Table Name: TE_FTP_STATTable Comment: TE_FTP状态表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKFTP_CDvarchar2(10)FTP传输编码YesNoDS_CD数据源编号NoYesFILE_NAMVARCHAR2(20)数据源名称NoBEGIN_DTIMDATE开始时间END_DTIM结束时间FTP_STATINTEGER状态Table Name: TE_PRO_EXE_STATTable Comment: TE_程序执行状态表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKEXE_CDvarchar2(10)执行编号YesNoPRO_CD程序编号NoYesREC_EXE_DTIMDATE程序执行时间NoBEGIN_EXE_DTIM程序执行开始时间END_EXE_DTIM程序执行结束时间EXE_STATVARCHAR2(20)执行状态Table Name: TE_PRO_INFTable Comment: TE_程序信息表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKPRO_CDvarchar2(10)程序编号YesNoPRO_NAMVARCHAR2(20)程序名称NoPRO_REMARK程序中文名PRO_PATHVARCHAR2(50)程序所在路径CYC_CDvarchar2(10)周期编号YesVALID_DTIMDATE生效时间NoINVALID_DTIM失效时间Table Name: TE_PRO_PRM_INFTable Comment: TE_程序参数信息表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKPRO_CDvarchar2(10)程序编号YesYesPRM_CD参数编号NoPRM_TYPVARCHAR2(20)参数类型NoPRM_VALUE参数值Table Name: TE_PRO_REAL_INFTable Comment: TE_程序依赖关系表Table Column NameTable Column DatatypeTable Column CommentTable Column Is PKTable Column Is FKRELA_CDvarchar2(10)关系编号YesNoPRO_CD程序编号NoYesBY_PRO_CD依赖程序编号NoCYC_CD周期编号Yes附录2：文件目录及编码说明² 目录结构数据存放目录定义如下：/应用类型目录/通道目录/数据目录；与外部系统的接口及相关消息文件的目录定义如下：/应用类型目录/接口目录（或消息文件目录）；说明：数据存放目录由三层结构组成，第一层为数据应用类型目录，第二层为通道编码目录，第三层为数据目录；说明：对于代码资料，只有应用类型目录一层目录。² 应用类型目录编码规则应用类型目录编码规则为：S_C S为数据源类型，编码如下编码数据源名称说明SMS短彩系统WAPWAP系统C为周期类型，编码如下编码周期类型说明H时D日W周M月Y年Q季Z无周期性² 通道目录编码规则属性约定名说明云博移动YBYD云博联通YBLT众智移动ZZYD众智联通ZZLT凤凰移动FHYD² 接口目录（或消息文件目录）接口目录（或消息文件目录）名称说明_EXF数据接口目录_MSG消息文件目录_BACK备份目录