商业银行事后监督系统ETL 设计与实现
胡丹，潘维民**
作者简介：胡丹，（1986-），女，硕士生，主要研究方向：数据仓库与数据挖掘
通信联系人：潘维民，（1969-），男，副教授，主要研究方向：计算逻辑学、数据仓库与数据挖掘技术、
分析型应用系统技术、金融工程研究. E-mail: bigpan@bupt.edu.cn
（北京邮电大学计算机学院，北京 100876）
5 摘要：会计事后监督是会计管理的重要组成部分，随着科学的进步，纯手工核对流水的方式
已经不能满足社会需求。会计事后监督系统主要用于对柜台业务进行事后监督，监督前台数
据的准确性、凭证的合法性等，对于规范会计行为，防范风险发挥了重要作用。银行数据具
有数据量大、实时性强、正确性高等特点。如何从大量数据中确定数据范围构建数据库模型
成为商业银行系统设计时所需处理的关键问题。本文指出了传统ETL 架构的缺点，并提出了
10 一种ETL 架构的实现方式，为银行系统的开发提供了相应的实际经验。
关键词：ETL；事后监督；数据库模型
 30 0 引言
随着信息时代的到来，银行办公也逐渐信息化，纯手工的操作方式已不能适应银行业务
发展，不能满足社会的需求。根据银监会对于商业银行内部控制的有关要求，各商业银行都
建立了会计事后监督部门，作为操作风险管理的主要措施之一，通过对各项柜台业务的合规
性检查，监督会计核算的具体执行情况，发现存在的问题开差错并及时进行纠正，在规范会
35 计核算、加强会计内部控制方面发挥了重要的作用[1]。
由于银行业务的特殊性，银行系统一般应具有及时性、准确性、连续性、安全性等特点。
这也对银行系统数据库、数据模型提出了新的要求。本文结合国内某商业银行事后监督系统
的开发，详细介绍了该系统的ETL 设计过程。该系统是以事后监督，再监督模块为主。对
会计执行情况进行监测，如发现问题则开差错，下发相应机构协查处理。该系统对于规范会
40 计行为、防范外部资金诈骗、防范内部人员作案等方面起到积极作用。
 1 ETL 技术简介
1.1 ETL 过程
ETL 分别是抽取（Extraction）、转换（Transformation）和装载（Loading），是将分散、
零乱，标准不统一，码制不一致的数据整合到目标数据库中的技术。它从各种原始的业务系
45 统中提取数据，按照一定的规则进行数据转换，最后将转换的数据按计划导入目标数据库[2]。
1. 数据抽取
从外部数据源抽取数据的时候，并不是所有的数据都有用。因此进行数据抽取前应根据
系统所要实现的功能进行详细的数据分析，提取出有价值的数据，定义正确的抽取策略。
2. 数据转换
50 数据转换是按照目标数据库的数据结构，对外部数据源的每个记录进行转换。外部数据
源通常来自不同的应用系统，这些应用系统往往是异构的。在这个阶段要对异构的数据源进
行清洗转换。例如，数据格式的转换、数据类型转换、数据的拼接等。
3. 数据装载
数据装载就是将外部数据源经过数据抽取、转换后，加载到目标数据库中。
55 2 ETL 设计与实现
2.1 ETL 的整体架构设计
在ETL 的设计中，ETL 的架构设计占非常重要的地位。通常ETL 架构应具备较高的安
全性，保证不同的用户根据自己的权限访问相应的数据。同时应具有较强的可扩展性，能够
满足系统升级，适应数据结构、数据处理模式等的变化。另外具有较强的可维护性和异常处
60 理功能。ETL 应用运行过程中，涉及到很多对中间转换表、对照表的手工维护。在流程中
需要考虑到各种可能的异常，保证进入数据库的数据绝对正确。ETL 的运行效率等也是需
要考虑的问题。
2.1.1 传统ETL 架构及性能缺点
传统的ETL 体系结构如下图所示[3]：
65
图1 传统ETL 体系结构图
Fig. 1 traditional ETL architecture
传统的ETL 体系结构中，系统从商业操作数据中抽取出来，经过数据确认、清洗、集
70 成、聚合等数据转换之后，装载到数据仓库中。然而传统的ETL 数据转换存在如下2 个问
 题：
（1）性能问题
ETL 过程的数据转换步骤显然是三个步骤中运算最多的一步，传统的ETL 方法转换步
骤完全由ETL 在专门的服务器上运行。ETL 工具逐条地对数据进行转换或者是质量检测，
75 这很容易使转换流程变成整个ETL 过程的瓶颈。此外，数据在源、目的和工具之间转换也
增加了网络通信量并导致附加运行问题。
（2）费用问题
实施ETL 过程中，最明显的花费是购买专用服务器和ETL 引擎软件。由于ETL 引擎是
一种中间级组件，执行大量的预算，因此需要一个强大的服务器。随着数据仓库规模增大，
80 ETL 服务器在运行期间还有不断的硬件和软件维护升级费用。
2.1.2 本系统的ETL 架构
针对传统的ETL 过程的不足之处，本系统主要对数据转换过程进行了改善，ETL 的总
体流程如下图所示：
信贷管理系统
电子银行系统
核心系统
…….
外围业务系统
关系数据
文本数据
数据转换
数据抽取数据装载
目标数据库
ODS表
AIM表
85 图2 ETL 流程图
Fig. 2 ETL flow
由上图可以看出本系统所采用的ETL 过程，将数据转换放在数据加载之后。克服了传
统ETL 过程中数据转换过程的瓶颈问题。首先将外围系统的数据经过数据抽取，加载到目
90 标数据库的ODS 平台对应的表中。这一过程只作数据类型、字段长度等简单的数据转换。
如源系统中日期字段是“date”类型，进入目标数据库要转换成“char(10)”。最后在目标数
据库中通过转换存储过程的方式，完成数据由ODS 平台到AIM 平台的数据转换。其中，
AIM 平台中的数据是系统实际所需要使用的数据。
采用ODS 平台作为过渡有以下几个优点：
95 （1）ODS 表采用加日期标签的办法保留最近3 天的数据，是外部数据源到事后监督系
统中的直接映射。
（2）通过ODS 层可以避免外部数据源直接向AIM 写数据，对AIM 层数据形成一种缓
冲和保护。
2.2 ETL 任务调度设计
100 一个完善的ETL 设计过程，除了要完成数据抽取、数据转换、数据加载这些主要的ETL
过程外，还应该设计到ETL 任务调度。保证数据能够按照预期的方式正确地完成数据转换
并加载到目标数据库中。同时，应具备异常处理、日志查看等功能，便于用户即时了解任务
 执行情况，进行相关的处理[4]。
任务调度流程如下图所示：
监控服务触发信号
监控内容
设定数据采集任务设定指标计算任务设定规则处理任务
监控服务任务状态
监控内容
执行数据采集任务
执行指标计算任务
执行规则处理任务
105
图3 任务调度流程图
Fig. 3 scheduling flow chart
调度服务主要是用来完成任务设置和任务执行。由上图可以看出，首先监控服务由外部
110 的触发信号来触发工作，然后按时间设定数据采集任务，指标计算任务，规则处理任务。任
务设置完成后，按顺序依次执行任务。任务执行过程中可能会产生异常，发生错误，用户可
通过错误日志查看详细信息，并进行相应的处理。
ETL 调度程序的触发方式通常有以下三种：时间触发、手动触发、事件触发。
时间触发：时间触发是到达指定的时间后，开始执行ETL 调度程序。并且可以定义执
115 行的频率和执行的具体时间。
手动触发：手动触发是通过人工的方式控制整个调度程序。需要人为干预和控制。
事件触发：事件触发是指相关的事件出现的时候开始执行调度程序。
本系统中，根据M 银行的实际需求，采用时间触发的方式。到达指定的时间后ETL 服
务器会自动触发整体调度程序。当运行系统出现异常的时候，采用手动触发的方式进行手工
120 纠错、修改相应的控制信息，对异常部分的数据进行处理，并将处理程序提交ETL 服务器
执行，从而保证数据的完整性。
2.3 M 银行事后监督系统ETL 实现
2.3.1 数据源与数据目标
本系统的数据源是M 银行T24 核心库和HDS（sybase IQ 历史库），由于M 银行T24
125 核心使用多维数据库，我们采用同时从汇聚平台读取文本及HDS 抽取数据两种渠道获取数
据。
 数据采集的目标是通过ETL 过程，从数据源抽取数据，经过数据处理之后批量导入事
后监督系统数据集市中去。
2.3.2 数据抽取
130 由本系统所采用的ETL 架构可以看出，事后监督系统的数据库划分为两个区域：ODS
区和AIM 区，如图4 所示。外部数据首先经过数据抽取、数据加载，进入ODS 区域。ODS
数据经过加工处理，完成复杂的逻辑转换，进入AIM 区域。本系统中通过创建ODS 用户和
AIM 用户的方式来实现数据库划分，这样可以保证元数据不被轻易修改、数据结构会更合
理、模型结构更优化。
135 通过在ODS 表中加入时间戳“ODSDATE”字段，来保存三天的源数据。一方面，对
源数据进行备份，程序出错时便于进行查错；另一方面，当上游数据未到达时，可以拿前一
天的数据完成跑批。
具体实现方式如下：
汇聚平台Datastage 直接导入文本进ODS，汇聚平台每天会将文本放在指定目录（格式：
140 表名_日期.dat 和表名_日期.flg），每天通过crontab 定时调起程序Datastage.sh 来扫描flg
文件判断文件是否到齐，到齐后执行导入。
HDS 部分首先通过扫描sybase IQ 里的配置表判断当日表是否到齐，然后先以JDBC 的
方式导出文件到指定目录，再通过加载服务直接sqlload 进ODS。
145 图4 目标数据库划分图
Fig. 4 target database segment
2.3.3 数据转换
ODS 中的数据只是对外部数据源的简单复制，外部数据源通常是以满足业务应用为目
150 的进行组织和存放的，通常不能适应事后监督系统的应用需求，因此需要根据系统实际需求
对ODS 中的数据进行数据转换。这一过程通过转换存储过程实现。
数据转换流程如下图所示：
 核心数据
客户信息、账户信息、传票信息、机构信息、员工信息等
ODS层
客户信息、账户信息、交易流水信息、机构信息、员工信息等
对核心数据进行筛选，提取
事后监督系统所关心的数
据。对需要进行转换的字段
原创学术论文网Tag：代写论文 论文发表 职称论文 计算机论文