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

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

《企业数据仓库概要设计说明书ETL概要设计分册.docx》由会员分享，可在线阅读，更多相关《企业数据仓库概要设计说明书ETL概要设计分册.docx（25页珍藏版）》请在冰豆网上搜索。

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

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

ETL概要设计分册

（文档编码：

OM-BIDW-C008）

（版本01.00.000）

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

XXXX版权所有，翻板必究

OM数据仓库XXXX企业数据组

2009年3月

文档变更历史

日期

版本

作者

修改内容

评审号

变更控制号

发布日期

2009-04-02

01.00.000

黄浩

建立初始版本

2009-04-02

概述

设计原则和前提

2.1

2.2

整体部署

前提条件

设计原则

2.3

整体框架

3.1ETL系统架构图........

3.2ETL系统功能模块描述

数据抽取模块

4.1

4.2

假设与约定模块功能图各子模块功能及处理流程

4.3

数据加载模块

5.1

假设与约定数据加载模块图…..…数据加载功能模块描述

5.2

5.3

作业调度模块

6.1

6.2

6.3

6.4

模块概述假设与约定作业调度流程ETL作业种类及调度实现方法

监控管理模块

7.1监控管理模块图

ETL监控内容

附录1控制表及控制文件设计附录2:

文件目录及编码说明

7

9

9

9

11

11

11

11

12

13

13

13

13

14

15

15

16

16

19

1概述

ETL是数据仓库系统开发中至关重要的一个过程，它涉及到对源数据的抽取、整合及各种转换，并最终形成面向用户的分析数据。

由于数据仓库系统的数据源来自于多个分散的业务系统，对不同业务系统的数据整合及清洗转换将是一个复杂的过程，ETL过程决定了数据仓库系统获取数据的准确性。

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

2设计原则和前提

网关（通道、产

品）服务器

2.1整体部署

通道服务器K

'数据抽_/取

"抽又H数据抽据

I取^

、;

\

y■■

W

该服务器完成两项功能：

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

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

数据仓库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负责对业务系统数据及其他外部源数据进行数据抽取，并存放在数据仓库系统中的STAG救据库中。

ETL过程包括数据抽取和数据加载等几个逻辑上相对独立的数据处理过程。

同时由于在ETL的处理过程中需要对ETL的错误处理以及作业调度等，ETL系统逻辑架构图如下图所示:

ETL逻辑构架图

3.2ETL系统功能模块描述

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

功能模块

功能描述

物理分布

实现工具或

方法

数据抽取

该模块获取外部系统数据以形成文本文件

ETL.SERVEF

1

1自主程序开

发

数据加载

将数据抽取获得的文本文件通过数据加载阶段入库到STAG中。

DWSERVEF

ORACLE的数

据加载程序

SQLLDR

错误处理

错误处理模块针对作业在运行过

程中出现错误时ETL系统应采取的

作业控制措施

DWSERVEF

ETLERVER

自主开发程

序

作业调度

作业调度主要实施整个系统中的作业运作，实时的监控作业运行的条件是否具备，一旦作业运行的条件具备，就将作业调入作业运行队列。

DWSERVER

自主开发程

序

监控管理

在ETL的处理过程中需要实时对ETL的作业过程进行监控，以便了解ETL的执行状况并根据ETL执行过程中遇到的问题采取相应的措施。

ETL

SERVER/DW

SERVER

自主开发程

「序

外部数据手工

输入模块

由于市公司数据集市存在一些需要手工输入或EXCELS格式的外部数据，需把这些外部数据录入到市公司数据集市中

PC

EXCEL等辅助

工具

4.1假设与约定

重复文件处理：

文件重复上传，则采取覆盖式的处理办法，我们认为最后上传的文件是接近正确的文件。

即如果某个文件先后上传了几次，那

么我们最后入库的文件将是最后一个上传的文件。

不对数据质量作监控：

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

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

4.2模块功能图

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

数据抽取子程序

J读取配置信息f数据抽取配置表N

1置F（V

读取数据抽取配置信

息、生成接口文件、记

录日志，并处理异常

接口文件

数据抽取流程图

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

该模块将由两个子程序驱动

数据抽取子程序：

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

FTP传输子程序：

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

输至接口机上。

从上图中可以看到在该模块处理流程中用到一系列的控制表及控制文件，各

控制表及控制文件的具体格式见附录1

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

子模块

作用及功能

需用到的控制表或控制文件

文件抽取进程

该进程定时从数据源信息表中获取数据，对原始数据源进行抽取工作

《数据源文件信息表》《数据源定义表》《数据ETL日志表》

目录扫描进程

该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。

由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。

《数据源定义表》《数据ETL日志表》

FTP传输进程

若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面

《数据源定义表》《数据ETL日志表》《FTP状态表》

对于以上处理步骤的关键过程信息都要记录到《数据ETL日志表》表中。

5数据加载模块

5.1假设与约定

因为数据抽取与数据加载是在不同的服务器上执行，为了同步两者之间的事件消息，我们需要建立一个消息同步的机制，即当数据抽取完成时,

需要发给数据加载一个消息。

即在传输数据接口文件完成后，附带一个传输完成标志文件。

5.2数据加载模块图

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

数据加载模块流程图

5.3数据加载功能模块描述

子模块

作用及功能

需用到的控制表或控制文件

目录扫描进程

该子功能模块将定时对相关接口目录进行扫描，以检查是否有新的接口文件需进行处理。

由于各类型数据源文件抽取的频率不一样，因此对各接口目录的频率也不同。

《数据源定义表》《数据ETL日志表》

FTP传输进程

若目录扫描进程检测到某接口目录下有新的文件到达，则调用FTP命令，将文件传输到指定服务器的指定目录下面

《数据源定义表》《数据ETL日志表》《FTP状态表》《接口文件登记表》

数据加载进程

该进程定时对指定目录下的文件进行扫描，如果文件存在，则对文件进行解压、加载处理，

《数据源定义表》《数据ETL日志表》

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

6作业调度模块

6.1模块概述

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

其功能包括:

事件扫描。

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

参数生成。

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

给作业;

追跑历史数据。

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

6.2假设与约定

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

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

6.3作业调度流程

作业调度流程如下图:

作业调度流程图

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

作业类型

作业调度方式

作业实现的功能

作业的物理分布

ORACLE存

储过程

ORACLE存储过程调度存在两个要点：

参数、依赖。

因此，在作业调度设置页面要设置好参数与依赖的具体内容。

数据处理

DWSERVER

ORACLE的

数据加载主要依赖于接口文件，要

数据加载

DWSERVER

数据加载

点有：

加载类型、文件信息。

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

7监控管理模块

7.1监控管理模块图

ETL监控管理示意图如下:

ETL监控

JOB处理时间查询

ETL监控管理示意图

功能模块

功能描述

物理分布

实现工具或

方法

Job作业状态

查询

可以根据时间及job名查询job的处理的状态（成功或失败）

开发

Job处理时间

查询

查询某次job完成处理任务的开始时间和结束时间

开发

Job处理日志明细

查看job处理的详细日志

开发

数据加载日志

明细

查看数据加载的详细日志

工具提供（查

询ORACLE勺

日志文件）

Job出错告警

在job出错时提供声音，短信等告

警

开发

ETL监控内容

从上图可以看到ETL监控包括如下几方面内容:

ETL系统在ETL作业出现错误或ETL数据处理质量没达到要求时通过ETL

监控系统进行短信息、BPEMAIL声音、特殊图像等多种告警手段提供

现场和远程告警;

ETL在作业处理过程中需把作业的处理时间、作业完成或失败信息等记

录到数据库中并在ETL监控系统中进行显示以了解ETL作业的状态以及

历史状况;

另一方面需通过ETL监控系统了解ETL各作业的数据处理质量情况（如

处理的记录数等）；

附录1：

控制表及控制文件设计

TableName:

TE_ETL_CYC

TableComment:

TE调度周期表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

CYCCD

varchar2（10）

周期编号

Yes

No

CYCNAM

VARCHAR2（20）

周期名称

No

TableName:

TE_ETL_DS_INF

TableComment:

TE数据抽取信息表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

DSCD

varchar2（10）

数据源编号

Yes

No

DS_NAM

VARCHAR2（20）

数据源名称

No

DS_FILE_NAM

数据源文件名

DS_TYP_CD

varchar2（10）

数据源类型

ETL_CYC_CD

数据抽取周期

ETLTYPCD

抽取类型

ETL_STAT

VARCHAR2（20）

文件状态

FLD_SPLIT

字段分隔符

COMPRESS_F

LAG

INTEGER

压缩标识

LOCAL_PATH

VARCHAR2（20）

本地路径

JKJPATH

远程路径

CYCCD

varchar2（10）

周期编号

Yes

DWPATH

VARCHAR2（20）

仓库服务器路径

No

TableName:

TE_ETL_DS_LOG

TableComment:

TE数据抽取日志表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

LOGSEQ

varchar2（10）

日志序号

Yes

No

DSCD

数据源编号

No

Yes

LOGTYP

VARCHAR2（20）

日志类型

No

LOGDTIM

DATE

日志时间

LOGSTAT

VARCHAR2（20）

日志状态

FILE_FULL_NA

M

文件全拼

TableName:

TEETLFILEINF

TableComment:

TE数据源文件信息表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

DSCD

varchar2（10）

数据源编号

Yes

Yes

FLDNAM

VARCHAR2（20）

字段名称

No

FLDTYP

字段类型

No

FLDLEN

NUMBER（5）

字段长度

FLDREMARK

VARCHAR2（20）

字段描述

TableName:

TEETLFILEREG

TableComment:

TE接口文件登记表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

REGSEQ

varchar2（10）

登记序号

Yes

No

DSCD

数据源编号

No

Yes

FILEDTIM

NUMBER（10）

数据文件日期

No

FILESEQ

NUMBER（5）

文件序号

REG_DTIM

DATE

登记时间

TableName:

TE_FTP_STAT

TableComment:

TEFTP状态表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

FTP_CD

varchar2（10）

FTP传输编码

Yes

No

DS_CD

数据源编号

No

Yes

FILE_NAM

VARCHAR2（20）

数据源名称

No

BEGIN_DTIM

DATE

开始时间

END_DTIM

结束时间

FTPSTAT

INTEGER

状态

1

TableName:

TEPROEXESTAT

TableComment:

TE程序执行状态表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

EXECD

varchar2（10）

执行编号

Yes

No

PROCD

程序编号

No

Yes

REC_EXE_DTI

M

DATE

程序执行时间

No

BEGIN_EXE_D

TIM

程序执行开始时间

END_EXE_DTI

M

程序执行结束时间

EXESTAT

VARCHAR2（20）

执行状态

TableName:

TEPROINF

TableComment:

TE程序信息表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

PROCD

varchar2（10）

程序编号

Yes

No

PRONAM

VARCHAR2（20）

程序名称

No

PROREMARK

程序中文名

PROPATH

VARCHAR2（50）

程序所在路径

CYCCD

varchar2（10）

周期编号

Yes

VALIDDTIM

DATE

生效时间

No

INVALIDDTIM

失效时间

TableName:

TEPROPRMINF

TableComment:

TE程序参数信息表

TableColumn

TableColumn

TableColumn

TableColumn

Name

Datatype

Comment

IsPK

IsFK

PRO_CD

varchar2（10）

程序编号

Yes

Yes

PRM_CD

参数编号

No

PRM_TYP

VARCHAR2（20）

参数类型

No

PRMVALUE

参数值

TableName:

TE_PRO_REAL_INF

TableComment:

TE程序依赖关系表

TableColumn

Name

TableColumn

Datatype

TableColumn

Comment

TableColumn

IsPK

TableColumn

IsFK

RELA_CD

varchar2（10）

关系编号

Yes

No

PRO_CD

程序编号

No

Yes

BYPROCD

依赖程序编号

No

CYCCD

周期编号

Yes

附录2:

文件目录及编码说明

目录结构

数据存放目录定义如下：

/应用类型目录/通道目录/数据目录;

与外部系统的接口及相关消息文件的目录定义如下：

/应用类型目录/接口目

录（或消息文件目录）；

说明：

数据存放目录由三层结构组成，第一层为数据应用类型目录，第二层

为通道编码目录，第三层为数据目录;

说明：

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

应用类型目录编码规则

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

SC

S为数据源类型，编码如下

编码

数据源名称

说明

SMS

短彩系统

WAP

WAP系统

C为周期类型，编码如下

编码

周期类型

说明

H

时

D

日

W

周

M

月

Y

年

Q

季

Z

无周期性

通道目录编码规则

属性

约定名

说明

云博移动

YBYD

云博联通

YBLT

众智移动

ZZYD

众智联通

ZZLT

凤凰移动

FHYD

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

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

名称

说明

EXF

数据接口目录

MSG

消息文件目录

BACK

备份目录