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



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

ETL概要设计分册

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

（版本01.00.000）

未经许可，不得以任何形式抄袭

XXXX版权所有，翻板必究

OM数据仓库XXXX企业数据组

2009年3月

文档变更历史

日期	版本	作者	修改内容	评审号	变更控制号	发布日期
2009-04-02	01.00.000	黄浩	建立初始版本			2009-04-02

目录

1    概述    4

2    设计原则和前提    5

2.1    整体部署    5

2.2    前提条件    6

2.3    设计原则    6

3    整体框架    7

3.1    ETL系统架构图    7

3.2    ETL系统功能模块描述    7

4    数据抽取模块    9

4.1    假设与约定    9

4.2    模块功能图    9

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

5    数据加载模块    11

5.1    假设与约定    11

5.2    数据加载模块图    11

5.3    数据加载功能模块描述    12

6    作业调度模块    13

6.1    模块概述    13

6.2    假设与约定    13

6.3    作业调度流程    13

6.4    ETL作业种类及调度实现方法    14

7    监控管理模块    15

7.1    监控管理模块图    15

ETL监控内容    16

附录1控制表及控制文件设计    16

附录2：文件目录及编码说明    19

1概述

ETL是数据仓库系统开发中至关重要的一个过程，它涉及到对源数据的抽取、整合及各种转换，并最终形成面向用户的分析数据。由于数据仓库系统的数据源来自于多个分散的业务系统，对不同业务系统的数据整合及清洗转换将是一个复杂的过程，ETL过程决定了数据仓库系统获取数据的准确性。

另外由于ETL包括数据抽取、数据清洗、数据转换及数据加载等数据处理过程，这些处理过程分散在不同的系统平台及开发工具上，对这些作业过程的统一调度将是一个重要的问题，作业调度涉及到系统的稳定性。

2设计原则和前提

2.1整体部署

数据仓库ETL整体部署图

✧网关通道数据库服务器是数据仓库的外围数据库系统，数据仓库中的绝大部分数据都将来自网关通道数据库服务器

✧基于目前短彩部自身情况的考虑，在数据源服务器和数据仓库服务器之间增加一台FTP文件服务器，其功能有二：

◆接口文件服务器，所有被接入DW的数据文件必须通过该服务器中转

◆数据备份，来自网关的数据文件将长期保留在该服务器上，作为文件备份

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.1ETL系统架构图

ETL负责对业务系统数据及其他外部源数据进行数据抽取，并存放在数据仓库系统中的STAGE数据库中。ETL过程包括数据抽取和数据加载等几个逻辑上相对的数据处理过程。同时由于在ETL的处理过程中需要对ETL的错误处理以及作业调度等，ETL系统逻辑架构图如下图所示：

3.2ETL系统功能模块描述

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

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

4数据抽取模块

4.1假设与约定

✧重复文件处理：文件重复上传，则采取覆盖式的处理办法，我们认为最后上传的文件是接近正确的文件。即如果某个文件先后上传了几次，那么我们最后入库的文件将是最后一个上传的文件。

✧不对数据质量作监控：因为数据仓库的数据源平台比较单一，涉及到的网络比较平稳，因此不对数据抽取的结果文件作质量监控。

✧系统的所有功能都由程序自动控制，原则上不允许手工干预

4.2模块功能图

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

该模块由自主程序开发实现，除主程序外，还包括目录扫描进程，文件处理进程，文件压缩，文件传输等几个子模块。该模块将由两个子程序驱动 

✧数据抽取子程序：该程序主要完成定时的数据抽取功能，并将抽取后得到的文本文件放到指定的目录下面。

✧FTP传输子程序：该程序定时扫描指定目录，如果文件到达，则将文件传输至接口机上。

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

4.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.4ETL作业种类及调度实现方法

作业类型	作业调度方式	作业实现的功能	作业的物理分布
ORACLE 存储过程	ORACLE存储过程调度存在两个要点：参数、依赖。因此，在作业调度设置页面要设置好参数与依赖的具体内容。	数据处理	DW SERVER
ORACLE 的数据加载	数据加载主要依赖于接口文件，要点有：加载类型、文件信息。加载作业会根据加载类型及文件的相关信息（字段、文件名、字段分隔符等）生成控制文件	数据加载	DW SERVER

7监控管理模块

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_CYC

Table Comment: TE_调度周期表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
CYC_CD	varchar2(10)	周期编号	Yes	No
CYC_NAM	VARCHAR2(20)	周期名称	No

Table Name: TE_ETL_DS_INF

Table Comment: TE_数据抽取信息表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
DS_CD	varchar2(10)	数据源编号	Yes	No
DS_NAM	VARCHAR2(20)	数据源名称	No
DS_FILE_NAM		数据源文件名
DS_TYP_CD	varchar2(10)	数据源类型
ETL_CYC_CD		数据抽取周期
ETL_TYP_CD		抽取类型
ETL_STAT	VARCHAR2(20)	文件状态
FLD_SPLIT		字段分隔符
COMPRESS_FLAG	INTEGER	压缩标识
LOCAL_PATH	VARCHAR2(20)	本地路径
JKJ_PATH		远程路径
CYC_CD	varchar2(10)	周期编号		Yes
DW_PATH	VARCHAR2(20)	仓库服务器路径		No

Table Name: TE_ETL_DS_LOG

Table Comment: TE_数据抽取日志表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
LOG_SEQ	varchar2(10)	日志序号	Yes	No
DS_CD		数据源编号	No	Yes
LOG_TYP	VARCHAR2(20)	日志类型		No
LOG_DTIM	DATE	日志时间
LOG_STAT	VARCHAR2(20)	日志状态
FILE_FULL_NAM		文件全拼

Table Name: TE_ETL_FILE_INF

Table Comment: TE_数据源文件信息表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
DS_CD	varchar2(10)	数据源编号	Yes	Yes
FLD_NAM	VARCHAR2(20)	字段名称		No
FLD_TYP		字段类型	No
FLD_LEN	NUMBER(5)	字段长度
FLD_REMARK	VARCHAR2(20)	字段描述

Table Name: TE_ETL_FILE_REG

Table Comment: TE_接口文件登记表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
REG_SEQ	varchar2(10)	登记序号	Yes	No
DS_CD		数据源编号	No	Yes
FILE_DTIM	NUMBER(10)	数据文件日期		No
FILE_SEQ	NUMBER(5)	文件序号
REG_DTIM	DATE	登记时间

Table Name: TE_FTP_STAT

Table Comment: TE_FTP状态表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
FTP_CD	varchar2(10)	FTP传输编码	Yes	No
DS_CD		数据源编号	No	Yes
FILE_NAM	VARCHAR2(20)	数据源名称		No
BEGIN_DTIM	DATE	开始时间
END_DTIM		结束时间
FTP_STAT	INTEGER	状态

Table Name: TE_PRO_EXE_STAT

Table Comment: TE_程序执行状态表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
EXE_CD	varchar2(10)	执行编号	Yes	No
PRO_CD		程序编号	No	Yes
REC_EXE_DTIM	DATE	程序执行时间		No
BEGIN_EXE_DTIM		程序执行开始时间
END_EXE_DTIM		程序执行结束时间
EXE_STAT	VARCHAR2(20)	执行状态

Table Name: TE_PRO_INF

Table Comment: TE_程序信息表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
PRO_CD	varchar2(10)	程序编号	Yes	No
PRO_NAM	VARCHAR2(20)	程序名称	No
PRO_REMARK		程序中文名
PRO_PATH	VARCHAR2(50)	程序所在路径
CYC_CD	varchar2(10)	周期编号		Yes
VALID_DTIM	DATE	生效时间		No
INVALID_DTIM		失效时间

Table Name: TE_PRO_PRM_INF

Table Comment: TE_程序参数信息表

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

Table Name: TE_PRO_REAL_INF

Table Comment: TE_程序依赖关系表

Table Column Name	Table Column Datatype	Table Column Comment	Table Column Is PK	Table Column Is FK
RELA_CD	varchar2(10)	关系编号	Yes	No
PRO_CD		程序编号	No	Yes
BY_PRO_CD		依赖程序编号		No
CYC_CD		周期编号		Yes

附录2：文件目录及编码说明

✧目录结构

数据存放目录定义如下：/应用类型目录/通道目录/数据目录；

与外部系统的接口及相关消息文件的目录定义如下：/应用类型目录/接口目录（或消息文件目录）；

说明：数据存放目录由三层结构组成，第一层为数据应用类型目录，第二层为通道编码目录，第三层为数据目录；

说明：对于代码资料，只有应用类型目录一层目录。

✧应用类型目录编码规则

应用类型目录编码规则为：S_C 

S为数据源类型，编码如下
编码	数据源名称	说明
SMS	短彩系统
WAP	WAP系统

C为周期类型，编码如下
编码	周期类型	说明
H	时
D	日
W	周
M	月
Y	年
Q	季
Z	无周期性

✧通道目录编码规则

属性	约定名	说明
云博移动	YBYD
云博联通	YBLT
众智移动	ZZYD
众智联通	ZZLT
凤凰移动	FHYD
………

✧接口目录（或消息文件目录）

接口目录（或消息文件目录）

名称	说明
_EXF	数据接口目录
_MSG	消息文件目录
_BACK	备份目录