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

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

XXXX企业数据仓库概要设计说明书

ETL概要设计分册

（文档编码：OM-BIDW-C008）

（版本01.00.000）

未经许可，不得以任何形式抄袭 XXXX版权所有，翻板必究

OM数据仓库XXXX企业数据组

2009年3月

短彩技术部数据组

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

文档变更历史

评审号 变更控制号 发布日期 日期 版本 作者 修改内容 2009-04-02 01.00.000 黄浩 建立初始版本 2009-04-02 短彩技术部数据组

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

目录

1 2

概述 ............................................................ 4 设计原则和前提 .................................................. 5

2.1 2.2 2.3

整体部署 ....................................................................................................................... 5 前提条件 ....................................................................................................................... 6 设计原则 ....................................................................................................................... 6 ETL系统架构图 .......................................................................................................... 7 ETL系统功能模块描述 .............................................................................................. 7 假设与约定 ................................................................................................................... 9 模块功能图 ................................................................................................................... 9 各子模块功能及处理流程 ......................................................................................... 11 假设与约定 ................................................................................................................. 11 数据加载模块图 ......................................................................................................... 11 数据加载功能模块描述 ............................................................................................. 12 模块概述 ..................................................................................................................... 13 假设与约定 ................................................................................................................. 13 作业调度流程 ............................................................................................................. 13 ETL作业种类及调度实现方法 ................................................................................ 14

3 整体框架 ........................................................ 7

3.1 3.2

4 数据抽取模块 .................................................... 9

4.1 4.2 4.3

5 数据加载模块 ................................................... 11

5.1 5.2 5.3

6 作业调度模块 ................................................... 13

6.1 6.2 6.3 6.4

7 监控管理模块 ................................................... 15

7.1 监控管理模块图 ......................................................................................................... 15 ETL监控内容 ........................................................................................................................ 16

附录1控制表及控制文件设计......................................... 16 附录2：文件目录及编码说明 ......................................... 19

短彩技术部数据组

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

1 概述

ETL是数据仓库系统开发中至关重要的一个过程，它涉及到对源数据的抽取、整合及各种转换，并最终形成面向用户的分析数据。由于数据仓库系统的数据源来自于多个分散的业务系统，对不同业务系统的数据整合及清洗转换将是一个复杂的过程，ETL过程决定了数据仓库系统获取数据的准确性。 另外由于ETL包括数据抽取、数据清洗、数据转换及数据加载等数据处理过程，这些处理过程分散在不同的系统平台及开发工具上，对这些作业过程的统一调度将是一个重要的问题，作业调度涉及到系统的稳定性。

短彩技术部数据组

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

2 设计原则和前提

2.1 整体部署

数据抽取抽取网关（通道、产品）服务器通道服务器1通道服务器...通道服务器N数据接口机FTP服务器数据组服务器数据仓库（DW）服务器数据加载数据仓库ETL整体部署图 ? 网关通道数据库服务器是数据仓库的外围数据库系统，数据仓库中的绝大部分数据都将来自网关通道数据库服务器

? 基于目前短彩部自身情况的考虑，在数据源服务器和数据仓库服务器之

间增加一台FTP文件服务器，其功能有二：

? 接口文件服务器，所有被接入DW的数据文件必须通过该服务器中转 ? 数据备份，来自网关的数据文件将长期保留在该服务器上，作为文件

备份

短彩技术部数据组

数据抽取该服务器完成两项功能：1、接口文件服务器，所有被接入DW的数据文件必须通过该服务器中转2、数据备份，来自网关的数据文件将长期保留在该服务器上，作为文件备份

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

2.2 前提条件

ETL概要设计将基于下面的前提条件

? ETL逻辑：XXXX企业的数据虽然分布在不同的通道，但是各通道数据的

共性度非常高，因此ETL中不存在逻辑复杂的转换（Transformation）及数据质量管理等流程，整个ETL只需要实现抽取（Extraction）和加载（Loading）两个功能即可

? ETL工具：自主开发，具体开发语言待定？； ? 作业调度工具：自主开发，具体开发语言待定？。 2.3 设计原则

? ETL应该是基于元数据库中定义好的处理规则；并且应由可复用的过程或相关组件来实现；

? 用户或客户端应用程序不应该直接执行数据获取程序，数据仓库层所有的数据更新应该由数据获取过程自动控制；

? 通过良好的设计和相关处理过程的协调使得系统的CPU处理时间最少；

要充分利用系统和软件的并行处理性能；

? ETL过程尽可能分解为独立的几个子处理过程以便于作业管理和调度； ? 在ETL设计时，需要详细计算并考虑ETL的处理性能，时间窗口及错误

处理控制。并详细考虑各个ETL任务在各台物理主机上的分布。 ? 需要提供一个监控统计模块对ETL的整个过程进行有效的监控和统计，

提供GUI界面对ETL各个任务的处理情况进行统计和监控，例如每个ETL任务的状态、处理记录的条数、处理某个任务所用的时间、出错的情况等。

短彩技术部数据组

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

3 整体框架

本章从宏观体系结构的高度，概要叙述ETL系统的基本架构和设计思想，着重于描述架构的特点、系统主要组成、ETL各个部分的基本功能和它们之间的关系以及方案选择的出发点。

3.1 ETL系统架构图 ETL负责对业务系统数据及其他外部源数据进行数据抽取，并存放在数据仓库系统中的STAGE数据库中。ETL过程包括数据抽取和数据加载等几个逻辑上相对独立的数据处理过程。同时由于在ETL的处理过程中需要对ETL的错误处理以及作业调度等，ETL系统逻辑架构图如下图所示： 监控管理界面作业调度错误处理数据抽取短彩系统数据库数据加载外部程序+BCP文本文件外部程序+SQLLDRSTAGE文本文件ETL逻辑构架图

3.2 ETL系统功能模块描述

从上图可以看到ETL系统包括数据抽取、数据加载、错误处理、作业调度、监控管理等几个功能模块，各功能模块的具体情况如下：

短彩技术部数据组

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

功能模块 功能描述 物理分布 实现工具或方法 数据抽取 该模块获取外部系统数据以形成ETL.SERVER文本文件 1 自主程序开发 ORACLE 的数据加载程序SQLLDR 数据加载 将数据抽取获得的文本文件通过DW SERVER 数据加载阶段入库到STAGE中。 错误处理 错误处理模块针对作业在运行过DW SERVER 程中出现错误时ETL系统应采取的ETL ERVER 作业控制措施 自主开发程序 作业调度 作业调度主要实施整个系统中的DW SERVER 作业运作，实时的监控作业运行的条件是否具备，一旦作业运行的条件具备，就将作业调入作业运行队列。 自主开发程序 监控管理 在ETL的处理过程中需要实时对ETL ETL的作业过程进行监控，以便了SERVER/DW 解ETL的执行状况并根据ETL执行SERVER 过程中遇到的问题采取相应的措施。 自主开发程序 外部数据手工由于市公司数据集市存在一些需PC 输入模块 要手工输入或EXCEL等格式的外部数据，需把这些外部数据录入到市公司数据集市中

EXCEL等辅助工具 短彩技术部数据组

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

4 数据抽取模块

4.1 假设与约定

? 重复文件处理：文件重复上传，则采取覆盖式的处理办法，我们认为最

后上传的文件是接近正确的文件。即如果某个文件先后上传了几次，那么我们最后入库的文件将是最后一个上传的文件。

? 不对数据质量作监控：因为数据仓库的数据源平台比较单一，涉及到的网络比较平稳，因此不对数据抽取的结果文件作质量监控。 ? 系统的所有功能都由程序自动控制，原则上不允许手工干预

4.2 模块功能图

数据抽取模块如下图所示：

短彩技术部数据组

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

开始数据抽取子程序读取数据抽取配置信息、生成接口文件、记录日志，并处理异常全量读取配置信息数据抽取配置表判断抽取类型增量BCP全量抽取程序BCP增量抽取程序否是否压缩是压缩程序接口文件FTP传输子程序读取FTP传输配置信息、将接口文件传输到指定服务器、记录日志，并处理异常读取FTP目录信息向数据抽取子程序发出异常消息，并修改FTP目录配置表相关信息FTP配置表否文件是否到达FTP传输程序结束数据抽取流程图

该模块由自主程序开发实现，除主程序外，还包括目录扫描进程，文件处理进程，文件压缩，文件传输等几个子模块。该模块将由两个子程序驱动 ? 数据抽取子程序：该程序主要完成定时的数据抽取功能，并将抽取后得

到的文本文件放到指定的目录下面。

? FTP传输子程序：该程序定时扫描指定目录，如果文件到达，则将文件传

短彩技术部数据组

是

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

输至接口机上。

从上图中可以看到在该模块处理流程中用到一系列的控制表及控制文件，各控制表及控制文件的具体格式见附录1

4.3 各子模块功能及处理流程

子模块 文件抽取进程 作用及功能 该进程定时从数据源信息表中获取数据，对原始数据源进行抽取工作 该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。 若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面 需用到的控制表或控制文件 《数据源文件信息表》《数据源定义表》《数据ETL日志表》 《数据源定义表》《数据ETL日志表》 目录扫描进程 FTP传输进程 《数据源定义表》《数据ETL日志表》《FTP状态表》 对于以上处理步骤的关键过程信息都要记录到《数据ETL日志表》表中。 5 数据加载模块 5.1 假设与约定 ? 因为数据抽取与数据加载是在不同的服务器上执行，为了同步两者之间的事件消息，我们需要建立一个消息同步的机制，即当数据抽取完成时，需要发给数据加载一个消息。即在传输数据接口文件完成后，附带一个传输完成标志文件。

5.2 数据加载模块图

数据加载模块流程如下图所示：

短彩技术部数据组

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

开始文件扫描程序定时监控临时目录下的接口文件否定时文件扫描文件是否到达是FTP获取程序定时将接口文件获取到服务器上,并进行登记FTP获取文件接口文件登记数据加载程序根据配置信息，调用不同的sqlldr控制文件，并记录日志解压文件数据加载更新加载状态数据加载信息表记录加载日志数据加载日志表结束数据加载模块流程图 5.3 数据加载功能模块描述 子模块 目录扫描进程 作用及功能 该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。 若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面 该进程定时对指定目录下的文件进行扫描，如果文件存在，则对文件进行解压、加载处理，短彩技术部数据组

需用到的控制表或控制文件 《数据源定义表》《数据ETL日志表》 FTP传输进程 《数据源定义表》《数据ETL日志表》《FTP状态表》《接口文件登记表》 《数据源定义表》《数据ETL日志表》 数据加载进程 XXX企业数据仓库设计说明书-ETL概要设计分册

最终将数据加载进STAGE数据库中

6 作业调度模块

6.1 模块概述

作业调度是系统运转的支点，从数据加载到数据处理的全部脚本都由作业调度系统自动完成。其功能包括： ?

事件扫描。按照作业的执行周期属性，周期性定时扫描作业所依赖的事件是否完成，以决定该作业是否执行； ?

参数生成。根据作业配置信息，自动生成作业执行参数，并将参数传递给作业； ?

追跑历史数据。因为某些原因，导致作业执行延时，在作业满足事件依赖后，需要追跑历史作业。

6.2 假设与约定

? 数据抽取模块分散到各数据源服务器，由各数据源服务器自行按照文档要求进行数据抽取，并将抽取结果FTP到指定的接口机的制定目录下 ? 为了简化调度流程，OMDW将采用事件扫描模式，而不是事件触发模式。

即为了触发某个作业，程序会定时的扫描作业所依赖的事件，如果所依赖的事件全都完成，则执行该作业。

6.3 作业调度流程

作业调度流程如下图：

短彩技术部数据组

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

开始事件依赖时间依赖判断调度依赖类型获取脚本所依赖的全部表依赖的数据是否已经完成是否是否已经到达时间点是否是否存在历史数据调度是否退出补跑历史数据读取作业配置信息,生成作业执行参数调度执行队列作业调度流程图 6.4 ETL作业种类及调度实现方法 作业类型 作业调度方式 作业实现的作业的物理分布 功能 DW SERVER ORACLE 存ORACLE存储过程调度存在两个数据处理 储过程 要点：参数、依赖。因此，在作业 调度设置页面要设置好参数与依赖的具体内容。 ORACLE 的数据加载主要依赖于接口文件，要数据加载 DW SERVER 短彩技术部数据组

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

数据加载 点有：加载类型、文件信息。加载作业会根据加载类型及文件的相关信息（字段、文件名、字段分隔符等）生成控制文件

7 监控管理模块

7.1 监控管理模块图 ETL监控管理示意图如下： ETL监控JOB作业状态查询数据加载日志查询JOB处理时间查询JOB处理日志查询JOB出错告警ETL监控管理示意图功能模块 功能描述 物理分布 方法 Job作业状态可以根据时间及job名查询job的 查询 处理的状态（成功或失败） 开发 开发 实现工具或Job处理时间查询某次job完成处理任务的开始 查询 时间和结束时间 Job处理日志查看job处理的详细日志 明细 短彩技术部数据组

开发

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

数据加载日志查看数据加载的详细日志 明细 工具提供（查询ORACLE的日志文件） Job出错告警 在job出错时提供声音，短信等告 警

开发 ETL监控内容 从上图可以看到ETL监控包括如下几方面内容： ? ETL系统在ETL作业出现错误或ETL数据处理质量没达到要求时通过ETL监控系统进行短信息、BP、EMAIL、声音、特殊图像等多种告警手段提供现场和远程告警； ? ETL在作业处理过程中需把作业的处理时间、作业完成或失败信息等记录到数据库中并在ETL监控系统中进行显示以了解ETL作业的状态以及历史状况； ? 另一方面需通过ETL监控系统了解ETL各作业的数据处理质量情况（如处理的记录数等）； 附录1：控制表及控制文件设计 Table Name: TE_ETL_CYC Table Comment: TE_调度周期表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK CYC_CD CYC_NAM varchar2(10) VARCHAR2(20) 周期编号 周期名称 Yes No No Table Name: TE_ETL_DS_INF

Table Comment: TE_数据抽取信息表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK DS_CD

varchar2(10) 数据源编号 短彩技术部数据组

Yes No XXX企业数据仓库设计说明书-ETL概要设计分册

DS_NAM DS_TYP_CD ETL_CYC_CD ETL_TYP_CD ETL_STAT FLD_SPLIT COMPRESS_FLAG LOCAL_PATH JKJ_PATH CYC_CD DW_PATH VARCHAR2(20) varchar2(10) VARCHAR2(20) INTEGER VARCHAR2(20) varchar2(10) VARCHAR2(20) 数据源名称 数据源文件名 数据源类型 数据抽取周期 抽取类型 文件状态 字段分隔符 压缩标识 本地路径 远程路径 周期编号 No Yes No DS_FILE_NAM 仓库服务器路径 Table Name: TE_ETL_DS_LOG Table Comment: TE_数据抽取日志表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK LOG_SEQ DS_CD LOG_TYP LOG_DTIM LOG_STAT FILE_FULL_NAM varchar2(10) VARCHAR2(20) DATE VARCHAR2(20) 日志序号 数据源编号 日志类型 日志时间 日志状态 文件全拼 Yes No No Yes No Table Name: TE_ETL_FILE_INF Table Comment: TE_数据源文件信息表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK DS_CD FLD_NAM FLD_TYP FLD_LEN FLD_REMARK varchar2(10) VARCHAR2(20) NUMBER(5) VARCHAR2(20) 数据源编号 字段名称 字段类型 字段长度 字段描述 Yes No Yes No Table Name: TE_ETL_FILE_REG Table Comment: TE_接口文件登记表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK REG_SEQ DS_CD FILE_DTIM

varchar2(10) NUMBER(10) 登记序号 数据源编号 数据文件日期 短彩技术部数据组

Yes No No Yes No XXX企业数据仓库设计说明书-ETL概要设计分册

FILE_SEQ REG_DTIM NUMBER(5) DATE 文件序号 登记时间 Table Name: TE_FTP_STAT

Table Comment: TE_FTP状态表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK FTP_CD DS_CD FILE_NAM BEGIN_DTIM END_DTIM FTP_STAT varchar2(10) VARCHAR2(20) DATE INTEGER FTP传输编码 数据源编号 数据源名称 开始时间 结束时间 状态 Yes No No Yes No Table Name: TE_PRO_EXE_STAT Table Comment: TE_程序执行状态表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK EXE_CD PRO_CD REC_EXE_DTIM BEGIN_EXE_DTIM END_EXE_DTIM EXE_STAT varchar2(10) DATE VARCHAR2(20) 执行编号 程序编号 程序执行时间 程序执行开始时间 程序执行结束时间 执行状态 Yes No No Yes No Table Name: TE_PRO_INF Table Comment: TE_程序信息表 Table Column Table Name Datatype PRO_CD PRO_NAM PRO_PATH CYC_CD VALID_DTIM INVALID_DTIM Column Table Column Table Column Table Column Comment Is PK Is FK 程序编号 程序名称 程序中文名 程序所在路径 周期编号 生效时间 失效时间 Yes No No Yes No varchar2(10) VARCHAR2(20) VARCHAR2(50) varchar2(10) DATE PRO_REMARK Table Name: TE_PRO_PRM_INF Table Comment: TE_程序参数信息表 Table Column Table Column Table Column Table Column Table Column 短彩技术部数据组

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

Name PRO_CD PRM_CD PRM_TYP PRM_VALUE Datatype varchar2(10) VARCHAR2(20) Comment 程序编号 参数编号 参数类型 参数值 Is PK Yes No Is FK Yes No

Table Name: TE_PRO_REAL_INF Table Comment: TE_程序依赖关系表 Table Column Table Column Table Column Table Column Table Column Name Datatype Comment Is PK Is FK RELA_CD PRO_CD BY_PRO_CD CYC_CD varchar2(10) 关系编号 程序编号 依赖程序编号 周期编号 Yes No No Yes No Yes 附录2：文件目录及编码说明 ? 目录结构 数据存放目录定义如下：/应用类型目录/通道目录/数据目录； 与外部系统的接口及相关消息文件的目录定义如下：/应用类型目录/接口目录（或消息文件目录）； 说明：数据存放目录由三层结构组成，第一层为数据应用类型目录，第二层为通道编码目录，第三层为数据目录； 说明：对于代码资料，只有应用类型目录一层目录。 ? 应用类型目录编码规则 应用类型目录编码规则为：S_C S为数据源类型，编码如下 编码 数据源名称 SMS 短彩系统 WAP WAP系统 说明

短彩技术部数据组

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

C为周期类型，编码如下 编码 周期类型 说明 H D W M Y Q Z 时 日 周 月 年 季 无周期性 ? 通道目录编码规则 属性 约定名 说明 云博移动 YBYD 云博联通 YBLT 众智移动 ZZYD 众智联通 ZZLT 凤凰移动 FHYD ……… ? 接口目录（或消息文件目录） 接口目录（或消息文件目录） 名称 _EXF _MSG _BACK 说明 数据接口目录 消息文件目录 备份目录 短彩技术部数据组

本文来源：https://www.bwwdw.com/article/f3ux.html