基金管理投资研究系统数据清洗及质量控制

王立荣，郭 强，付冉冉

(1.山东经济学院信息管理学院，山东 济南 250014;2.山东经济学院会计学院，山东 济南 250014)

随着信息技术的快速发展，管理者在进行决策分析和计划制定时不能单纯依靠以往的经验，必须借助必要的数据来进行科学的管理。这导致管理人员进行决策分析时对数据的依赖性更加强烈。为此，人们提出了建立数据仓库的要求[1-4]。但是，由于用户错误录入或录入不完整、企业合并以及企业环境随着时间的推进而需要做出变更等原因会不可避免地造成各种各样的数据质量问题[5-8]，企业在历史和现存的数据基础上为将来的发展做决策和预测时，数据的质量问题就变得非常关键。错误的数据会导致昂贵的操作费用和过长的响应时间，影响数据集中抽取模式的正确性和导出规则的准确性，使得应用于数据仓库前端的决策支持系统产生错误的分析结果，从而导致决策失误，影响信息服务质量[9-11]。为了过滤掉这些问题数据，提高数据质量，必须进行数据清洗来消除数据中的不一致和错误，将数据转换为统一的格式。为保证数据的正确性和决策的质量，还必须根据一定的业务规则对清洗后的数据质量进行校验。因此数据仓库构造过程中的一个重要的任务就是进行数据清洗和对清洗后的数据进行校验。目前大部分的ETL程序在处理数据的过程中忽略掉了数据库本身强大的数据处理能力，对数据的抽取、转换和加载工作都通过程序的方式实现，使ETL程序本身变得极其复杂。当业务流程变更时，设计的ETL过程也需要做相应的变动，这要求维护人员对ETL产品和业务流程都具有清楚的理解，加大了维护的难度。在充分分析基金管理公司业务需求的前提下针对投资研究系统开发了一套数据清洗和校验程序，充分运用了数据库的数据处理功能，该开发模式大大减少了开发代码的工作量。

1 ETL程序设计与实现

ETL程序的主要功能是将文件或数据库中的数据导入到oracle数据仓库，便于后续的清洗和数据核对。

1.1 功能特点

(1)多种格式数据文件导入。目前程序需支持 mdb、dbf、excel、txt格式文件的导入，支持从 sql server、oracle数据库读取数据。

(2)异常处理灵活。单个异常不影响其他不相关过程的处理。

(3)调度功能灵活。可以定时、批量、按顺序等方式执行任务。数据异常时可以通过邮件或短信进行提醒。

(4)B/S(browser/server)架构。支持多任务并发执行，减少业务人员的等待时间。

(5)支持数据重导。若某个文件原始数据有错误，修改文件重导数据后应保证数据正确。

1.2 功能模块

ETL程序包括的功能模块如图1所示。

图1 ETL程序功能模块

(1)数据源设置。用来设置数据源类型，目前有 access、dbf、excel、txt、sql server几类。

(2)任务设置。设置ETL任务。

(3)任务组设置。将相关的任务定义为一个任务组，共同完成某一功能。

(4)日志检索。用于检索任务及任务组的执行情况。

(5)调度设置。设置任务及任务组的执行时间和两次执行的时间间隔。

(6)调度执行记录。用来查看设置的调度。

1.3 界面设计

程序使用EXT技术保证了界面风格的一致和美观。图2为access任务设置界面，用户可以设置数据源和映射规则。

图2 access任务设置界面

2 数据清洗过程设计与实现

2.1 估值表数据清洗

由于用户错误录入或录入不完整、企业合并以及企业环境随着时间的推进而需要做出变更等原因，造成在不同组合中同一科目编码具有不同的含义以及同一科目属性的科目编码不同的情况。为便于后续建立在数据仓库上的应用，必须做到各个组合中科目编码的一致。为解决该问题，在数据清洗过程中使用了如图3所示的科目匹配表。

图3 部分科目匹配情况

根据上述科目匹配，对估值表数据的清洗分为只抽取明细和既抽取明细又抽取汇总两种类型。根据用户需求，证券类只需存储明细科目的数据，而非证券类既存储明细科目数据又存储汇总科目数据。图3中的每一条记录都是一条匹配规则，估值表中的每一条记录经过这些规则的处理最终达到各个组合中的同一科目编码具有相同含义的目的。该清洗过程通过oracle存储过程实现，具体处理流程为:对估值系统导出的科目表进行分析，找出科目编码相同但科目属性不同及科目属性相同科目编码不同的科目，通过与业务人员交流，确定该科目应具有的含义，作为一条规则保存到匹配表中，图3中第二条记录表示“银行存款_上海”实际的科目属性为“银行存款”，科目编码是“1002”。匹配表中的规则通过科目属性和层级进行区分，如果科目属性和科目层级都相同则通过添加基金代码和原科目编码进行区分;循环估值表中的数据，首先根据科目属性、科目层级、基金代码和原科目编码在匹配表中查找适应的规则，如果找不到相应的规则，则通过科目属性和科目层级在匹配表中查找使用的规则;根据找到的规则更新原估值表的科目编码和科目属性。

2.2 债券代码清洗

对一些新发行未上市的债券，因无正式的交易代码，若基金买入这些债券则在估值系统中使用临时编码，而外部资讯中使用的也是内部编码，造成了同一证券代码不同的情况。对此，系统在数据清洗的过程中，若根据债券代码找不到外部信息，则根据证券名称模糊匹配，在外部资讯中找到对应的代码后，替代估值系统的代码插入到持仓表中，并将相应的信息插入到债券代码匹配表中等待业务人员确认匹配是否正确。经过确认的匹配在下次抽取数据时直接使用该规则，匹配错误的规则经人工修改后，调用债券代码处理存储过程，对持仓表中的数据根据修改后的规则更新。

3 数据核对方式及实现

目前系统的数据核对分清洗前数据核对和清洗后数据核对两类。通过这两种类型的核对，保证投资研究系统数据仓库中的数据一致和正确。

清洗前的数据核对包括:

(1)检查原始数据文件是否已经导入。参数包括开始日期和结束日期。过程是按天循环检查原始数据表中是否存在该日的数据，将异常结果存储到日志表中。

(2)检查估值系统科目表中明细科目下是否还有子科目。其参数为年份。过程是根据循环科目表中标记为明细的科目，查找科目表中是否存在长度大于循环科目(长度为n)并且前n位与循环科目相同的科目，将异常结果存储到日志表中。

(3)检查估值表中的明细科目在科目表中是否是明细科目。其参数为开始日期和结束日期。当循环估值表中不存在下级科目时，查找循环科目在科目表中是否标记为明细科目，将异常结果存储到日志表中。

清洗后的数据核对包括:

(1)清洗后持仓表资产类明细科目之和与负债类明细科目之和是否等于估值表中的净资产。其参数为开始日期和结束日期。其过程为将持仓表中资产类明细科目和负债类明细科目相减，查看是否等于估值表中的资产净值，若相同则结束校验，否则继续检查不等的原因，将异常结果存储到日志表中。

(2)一个组合在一天中同一科目编码的记录是否只有一条。其参数为开始日期和结束日期。其过程是查找介于开始日期和结束日期之间的科目，将多于一条的科目的日期、基金代码存储到日志表中。

(3)上级科目的数值是否等于下级科目的数值之和。其参数为开始日期和结束日期。其过程是循环持仓表中的非明细科目，将循环科目下级科目的数值相加，若不等则将结果存储到日志表中。

(4)T+1日的持仓数量是否等于T日的持仓数量加上T+1日的交易数量。其参数为开始日期和结束日期。其过程是按天循环，计算所有基金T+1日的持仓数量之和、T+1日交易数量之和及T日持仓数据之和，若T+1日的持仓数量与T日的持仓数量加T+1日的交易数量不等，则循环各个证券查找具体原因，并将结果存储到日志表中。

4 结论

笔者对基金管理公司投资平台数据仓库数据清洗和核对方式进行了探讨，设计并开发了一套ETL程序和oracle存储过程，完成了数据的抽取、清洗和核对，保证了数据仓库中数据的一致性和正确性，为建立在投资数据仓库上的应用提供了数据基础。通过在某基金管理公司投资研究系统的运用，证明该数据清洗和核对方式大大减少了开发和维护成本，对银行、保险等金融机构类似应用的开发有较高的参考价值。

[1] 朱前磊.电子政务系统中海量数据清洗方法研究与应用[D].上海:东华大学图书馆，2008.

[2] 中国证券业协会.证券投资基金[M].北京:中国财政经济出版社，2010:42-102.

[3] 张宇.数据清洗技术研究[J].山东科技大学学报，2004，23(2):55-57.

[4] 安淑芝.数据仓库与数据挖掘[M].北京:清华大学出版社，2005:102-185.

[5] 郭永生.数据仓库增量维护技术研究[D].南京:东南大学图书馆，2004.

[6] 徐春艳.面向实时数据仓库的ETL研究[D].南京:南京航空航天大学图书馆，2007.

[7] 韩鹏.ETL工具的设计实现与应用[D].长春:吉林大学图书馆，2006.

[8] 陈京民.数据仓库与数据挖掘技术[M].北京:电子工业出版社，2002:95-150.

[9] 何晨钢.ETL系统的设计和实现技术研究[J].计算机应用与软件，2009，26(4):198-201.

[10] 郝宏宇，李雁春.通过数据仓库技术提升企业信息化管理层次[J].信息科学，2008，22(8):32-34.

[11] 刘建本.证券公司数据仓库构建研究[J].福建电脑，2009，26(9):150-151.