基金管理投資研究系統(tǒng)數(shù)據(jù)清洗及質(zhì)量控制

王立榮，郭 強，付冉冉

(1.山東經(jīng)濟學(xué)院信息管理學(xué)院，山東 濟南 250014;2.山東經(jīng)濟學(xué)院會計學(xué)院，山東 濟南 250014)

隨著信息技術(shù)的快速發(fā)展，管理者在進(jìn)行決策分析和計劃制定時不能單純依靠以往的經(jīng)驗，必須借助必要的數(shù)據(jù)來進(jìn)行科學(xué)的管理。這導(dǎo)致管理人員進(jìn)行決策分析時對數(shù)據(jù)的依賴性更加強烈。為此，人們提出了建立數(shù)據(jù)倉庫的要求[1-4]。但是，由于用戶錯誤錄入或錄入不完整、企業(yè)合并以及企業(yè)環(huán)境隨著時間的推進(jìn)而需要做出變更等原因會不可避免地造成各種各樣的數(shù)據(jù)質(zhì)量問題[5-8]，企業(yè)在歷史和現(xiàn)存的數(shù)據(jù)基礎(chǔ)上為將來的發(fā)展做決策和預(yù)測時，數(shù)據(jù)的質(zhì)量問題就變得非常關(guān)鍵。錯誤的數(shù)據(jù)會導(dǎo)致昂貴的操作費用和過長的響應(yīng)時間，影響數(shù)據(jù)集中抽取模式的正確性和導(dǎo)出規(guī)則的準(zhǔn)確性，使得應(yīng)用于數(shù)據(jù)倉庫前端的決策支持系統(tǒng)產(chǎn)生錯誤的分析結(jié)果，從而導(dǎo)致決策失誤，影響信息服務(wù)質(zhì)量[9-11]。為了過濾掉這些問題數(shù)據(jù)，提高數(shù)據(jù)質(zhì)量，必須進(jìn)行數(shù)據(jù)清洗來消除數(shù)據(jù)中的不一致和錯誤，將數(shù)據(jù)轉(zhuǎn)換為統(tǒng)一的格式。為保證數(shù)據(jù)的正確性和決策的質(zhì)量，還必須根據(jù)一定的業(yè)務(wù)規(guī)則對清洗后的數(shù)據(jù)質(zhì)量進(jìn)行校驗。因此數(shù)據(jù)倉庫構(gòu)造過程中的一個重要的任務(wù)就是進(jìn)行數(shù)據(jù)清洗和對清洗后的數(shù)據(jù)進(jìn)行校驗。目前大部分的ETL程序在處理數(shù)據(jù)的過程中忽略掉了數(shù)據(jù)庫本身強大的數(shù)據(jù)處理能力，對數(shù)據(jù)的抽取、轉(zhuǎn)換和加載工作都通過程序的方式實現(xiàn)，使ETL程序本身變得極其復(fù)雜。當(dāng)業(yè)務(wù)流程變更時，設(shè)計的ETL過程也需要做相應(yīng)的變動，這要求維護人員對ETL產(chǎn)品和業(yè)務(wù)流程都具有清楚的理解，加大了維護的難度。在充分分析基金管理公司業(yè)務(wù)需求的前提下針對投資研究系統(tǒng)開發(fā)了一套數(shù)據(jù)清洗和校驗程序，充分運用了數(shù)據(jù)庫的數(shù)據(jù)處理功能，該開發(fā)模式大大減少了開發(fā)代碼的工作量。

1 ETL程序設(shè)計與實現(xiàn)

ETL程序的主要功能是將文件或數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入到oracle數(shù)據(jù)倉庫，便于后續(xù)的清洗和數(shù)據(jù)核對。

1.1 功能特點

(1)多種格式數(shù)據(jù)文件導(dǎo)入。目前程序需支持 mdb、dbf、excel、txt格式文件的導(dǎo)入，支持從 sql server、oracle數(shù)據(jù)庫讀取數(shù)據(jù)。

(2)異常處理靈活。單個異常不影響其他不相關(guān)過程的處理。

(3)調(diào)度功能靈活?？梢远〞r、批量、按順序等方式執(zhí)行任務(wù)。數(shù)據(jù)異常時可以通過郵件或短信進(jìn)行提醒。

(4)B/S(browser/server)架構(gòu)。支持多任務(wù)并發(fā)執(zhí)行，減少業(yè)務(wù)人員的等待時間。

(5)支持?jǐn)?shù)據(jù)重導(dǎo)。若某個文件原始數(shù)據(jù)有錯誤，修改文件重導(dǎo)數(shù)據(jù)后應(yīng)保證數(shù)據(jù)正確。

1.2 功能模塊

ETL程序包括的功能模塊如圖1所示。

圖1 ETL程序功能模塊

(1)數(shù)據(jù)源設(shè)置。用來設(shè)置數(shù)據(jù)源類型，目前有 access、dbf、excel、txt、sql server幾類。

(2)任務(wù)設(shè)置。設(shè)置ETL任務(wù)。

(3)任務(wù)組設(shè)置。將相關(guān)的任務(wù)定義為一個任務(wù)組，共同完成某一功能。

(4)日志檢索。用于檢索任務(wù)及任務(wù)組的執(zhí)行情況。

(5)調(diào)度設(shè)置。設(shè)置任務(wù)及任務(wù)組的執(zhí)行時間和兩次執(zhí)行的時間間隔。

(6)調(diào)度執(zhí)行記錄。用來查看設(shè)置的調(diào)度。

1.3 界面設(shè)計

程序使用EXT技術(shù)保證了界面風(fēng)格的一致和美觀。圖2為access任務(wù)設(shè)置界面，用戶可以設(shè)置數(shù)據(jù)源和映射規(guī)則。

圖2 access任務(wù)設(shè)置界面

2 數(shù)據(jù)清洗過程設(shè)計與實現(xiàn)

2.1 估值表數(shù)據(jù)清洗

由于用戶錯誤錄入或錄入不完整、企業(yè)合并以及企業(yè)環(huán)境隨著時間的推進(jìn)而需要做出變更等原因，造成在不同組合中同一科目編碼具有不同的含義以及同一科目屬性的科目編碼不同的情況。為便于后續(xù)建立在數(shù)據(jù)倉庫上的應(yīng)用，必須做到各個組合中科目編碼的一致。為解決該問題，在數(shù)據(jù)清洗過程中使用了如圖3所示的科目匹配表。

圖3 部分科目匹配情況

根據(jù)上述科目匹配，對估值表數(shù)據(jù)的清洗分為只抽取明細(xì)和既抽取明細(xì)又抽取匯總兩種類型。根據(jù)用戶需求，證券類只需存儲明細(xì)科目的數(shù)據(jù)，而非證券類既存儲明細(xì)科目數(shù)據(jù)又存儲匯總科目數(shù)據(jù)。圖3中的每一條記錄都是一條匹配規(guī)則，估值表中的每一條記錄經(jīng)過這些規(guī)則的處理最終達(dá)到各個組合中的同一科目編碼具有相同含義的目的。該清洗過程通過oracle存儲過程實現(xiàn)，具體處理流程為:對估值系統(tǒng)導(dǎo)出的科目表進(jìn)行分析，找出科目編碼相同但科目屬性不同及科目屬性相同科目編碼不同的科目，通過與業(yè)務(wù)人員交流，確定該科目應(yīng)具有的含義，作為一條規(guī)則保存到匹配表中，圖3中第二條記錄表示“銀行存款_上海”實際的科目屬性為“銀行存款”，科目編碼是“1002”。匹配表中的規(guī)則通過科目屬性和層級進(jìn)行區(qū)分，如果科目屬性和科目層級都相同則通過添加基金代碼和原科目編碼進(jìn)行區(qū)分;循環(huán)估值表中的數(shù)據(jù)，首先根據(jù)科目屬性、科目層級、基金代碼和原科目編碼在匹配表中查找適應(yīng)的規(guī)則，如果找不到相應(yīng)的規(guī)則，則通過科目屬性和科目層級在匹配表中查找使用的規(guī)則;根據(jù)找到的規(guī)則更新原估值表的科目編碼和科目屬性。

2.2 債券代碼清洗

對一些新發(fā)行未上市的債券，因無正式的交易代碼，若基金買入這些債券則在估值系統(tǒng)中使用臨時編碼，而外部資訊中使用的也是內(nèi)部編碼，造成了同一證券代碼不同的情況。對此，系統(tǒng)在數(shù)據(jù)清洗的過程中，若根據(jù)債券代碼找不到外部信息，則根據(jù)證券名稱模糊匹配，在外部資訊中找到對應(yīng)的代碼后，替代估值系統(tǒng)的代碼插入到持倉表中，并將相應(yīng)的信息插入到債券代碼匹配表中等待業(yè)務(wù)人員確認(rèn)匹配是否正確。經(jīng)過確認(rèn)的匹配在下次抽取數(shù)據(jù)時直接使用該規(guī)則，匹配錯誤的規(guī)則經(jīng)人工修改后，調(diào)用債券代碼處理存儲過程，對持倉表中的數(shù)據(jù)根據(jù)修改后的規(guī)則更新。

3 數(shù)據(jù)核對方式及實現(xiàn)

目前系統(tǒng)的數(shù)據(jù)核對分清洗前數(shù)據(jù)核對和清洗后數(shù)據(jù)核對兩類。通過這兩種類型的核對，保證投資研究系統(tǒng)數(shù)據(jù)倉庫中的數(shù)據(jù)一致和正確。

清洗前的數(shù)據(jù)核對包括:

(1)檢查原始數(shù)據(jù)文件是否已經(jīng)導(dǎo)入。參數(shù)包括開始日期和結(jié)束日期。過程是按天循環(huán)檢查原始數(shù)據(jù)表中是否存在該日的數(shù)據(jù)，將異常結(jié)果存儲到日志表中。

(2)檢查估值系統(tǒng)科目表中明細(xì)科目下是否還有子科目。其參數(shù)為年份。過程是根據(jù)循環(huán)科目表中標(biāo)記為明細(xì)的科目，查找科目表中是否存在長度大于循環(huán)科目(長度為n)并且前n位與循環(huán)科目相同的科目，將異常結(jié)果存儲到日志表中。

(3)檢查估值表中的明細(xì)科目在科目表中是否是明細(xì)科目。其參數(shù)為開始日期和結(jié)束日期。當(dāng)循環(huán)估值表中不存在下級科目時，查找循環(huán)科目在科目表中是否標(biāo)記為明細(xì)科目，將異常結(jié)果存儲到日志表中。

清洗后的數(shù)據(jù)核對包括:

(1)清洗后持倉表資產(chǎn)類明細(xì)科目之和與負(fù)債類明細(xì)科目之和是否等于估值表中的凈資產(chǎn)。其參數(shù)為開始日期和結(jié)束日期。其過程為將持倉表中資產(chǎn)類明細(xì)科目和負(fù)債類明細(xì)科目相減，查看是否等于估值表中的資產(chǎn)凈值，若相同則結(jié)束校驗，否則繼續(xù)檢查不等的原因，將異常結(jié)果存儲到日志表中。

(2)一個組合在一天中同一科目編碼的記錄是否只有一條。其參數(shù)為開始日期和結(jié)束日期。其過程是查找介于開始日期和結(jié)束日期之間的科目，將多于一條的科目的日期、基金代碼存儲到日志表中。

(3)上級科目的數(shù)值是否等于下級科目的數(shù)值之和。其參數(shù)為開始日期和結(jié)束日期。其過程是循環(huán)持倉表中的非明細(xì)科目，將循環(huán)科目下級科目的數(shù)值相加，若不等則將結(jié)果存儲到日志表中。

(4)T+1日的持倉數(shù)量是否等于T日的持倉數(shù)量加上T+1日的交易數(shù)量。其參數(shù)為開始日期和結(jié)束日期。其過程是按天循環(huán)，計算所有基金T+1日的持倉數(shù)量之和、T+1日交易數(shù)量之和及T日持倉數(shù)據(jù)之和，若T+1日的持倉數(shù)量與T日的持倉數(shù)量加T+1日的交易數(shù)量不等，則循環(huán)各個證券查找具體原因，并將結(jié)果存儲到日志表中。

4 結(jié)論

筆者對基金管理公司投資平臺數(shù)據(jù)倉庫數(shù)據(jù)清洗和核對方式進(jìn)行了探討，設(shè)計并開發(fā)了一套ETL程序和oracle存儲過程，完成了數(shù)據(jù)的抽取、清洗和核對，保證了數(shù)據(jù)倉庫中數(shù)據(jù)的一致性和正確性，為建立在投資數(shù)據(jù)倉庫上的應(yīng)用提供了數(shù)據(jù)基礎(chǔ)。通過在某基金管理公司投資研究系統(tǒng)的運用，證明該數(shù)據(jù)清洗和核對方式大大減少了開發(fā)和維護成本，對銀行、保險等金融機構(gòu)類似應(yīng)用的開發(fā)有較高的參考價值。

[1] 朱前磊.電子政務(wù)系統(tǒng)中海量數(shù)據(jù)清洗方法研究與應(yīng)用[D].上海:東華大學(xué)圖書館，2008.

[2] 中國證券業(yè)協(xié)會.證券投資基金[M].北京:中國財政經(jīng)濟出版社，2010:42-102.

[3] 張宇.數(shù)據(jù)清洗技術(shù)研究[J].山東科技大學(xué)學(xué)報，2004，23(2):55-57.

[4] 安淑芝.數(shù)據(jù)倉庫與數(shù)據(jù)挖掘[M].北京:清華大學(xué)出版社，2005:102-185.

[5] 郭永生.數(shù)據(jù)倉庫增量維護技術(shù)研究[D].南京:東南大學(xué)圖書館，2004.

[6] 徐春艷.面向?qū)崟r數(shù)據(jù)倉庫的ETL研究[D].南京:南京航空航天大學(xué)圖書館，2007.

[7] 韓鵬.ETL工具的設(shè)計實現(xiàn)與應(yīng)用[D].長春:吉林大學(xué)圖書館，2006.

[8] 陳京民.數(shù)據(jù)倉庫與數(shù)據(jù)挖掘技術(shù)[M].北京:電子工業(yè)出版社，2002:95-150.

[9] 何晨鋼.ETL系統(tǒng)的設(shè)計和實現(xiàn)技術(shù)研究[J].計算機應(yīng)用與軟件，2009，26(4):198-201.

[10] 郝宏宇，李雁春.通過數(shù)據(jù)倉庫技術(shù)提升企業(yè)信息化管理層次[J].信息科學(xué)，2008，22(8):32-34.

[11] 劉建本.證券公司數(shù)據(jù)倉庫構(gòu)建研究[J].福建電腦，2009，26(9):150-151.