商業銀行數據倉庫系統中ETL的設計與實現

周飛 劉夢娜 張暉 李志剛 曹帥

摘 要：本文基于國內某商業銀行數據中心的新核心系統項目，首先介紹近年來數據倉庫的國內外發展狀況，其次根據商業銀行各業務系統的特點，分析出建設商業銀行數據倉庫的重大意義和存儲規劃。最后詳細介紹了數據調度ETL系統的開發與設計，從各個源系統中抽取數據到數據倉庫的實現方法，對銀行數據倉庫的設計與實現有一定的參考應用價值。

關鍵詞：數據倉庫；ETL；商業銀行；商業智能

中圖分類號：TP311 文獻標識碼：A

文章編號：2096-1472（2018）-11-42-04

1 引言（Introduction）

數據倉庫是應用于銀行業的重要技術。近年來，隨著商業銀行各業務系統的不斷發展，產生了日益龐大的歷史數據，這些數據是銀行十分重要的戰略資源，將這些重要的業務數據存儲于數據倉庫中統一管理，進行數據分析，挖掘出其中潛在的重要的商業信息，可以很好的輔助銀行領導層管理決策，解決銀行最緊迫的問題，使銀行的經濟效益最大化，從而使銀行更好更快的發展。在大數據時代下，基于數據倉庫的數據挖掘是銀行業之間競爭的重要工具[1]。

在1990年Bill Inmon提出了一種新的數據庫技術，即數據倉庫，目的是用來存儲和組織各業務系統的海量數據，并對數據進行分析。ETL（Extract，Transform，Load）系統工具專用于業務數據到數據倉庫中存儲的處理過程，在數據源和數據倉庫之間起到了橋梁和紐帶的作用，直接影響數據倉庫的建設和運行。對于ETL系統的研究和開發是建設數據倉庫必須考慮的重要問題，通過改進ETL技術提高數據的傳輸和存儲效率，是建設商業銀行數據倉庫永恒的目標，能更好的促進銀行業的發展。

2 ETL技術簡介（Introduction of ETL technology）

完整的數據倉庫系統如圖1所示，數據的ETL是建立數據倉庫系統的第一步[2，3]，是應用于數據倉庫項目的重要步驟，為后續的數據挖掘和決策支持做好準備。

數據倉庫中數據的調度工作是通過ETL工具來實現的。ETL是數據調度系統三個階段抽取（Extract）、轉換（Transform）、裝載（Load）的英文首字母縮寫[4，5]。下面我們來簡要的介紹ETL的三個階段。

2.1 數據抽取

首先分析從每個數據源獲取數據的策略，分析從數據源系統中收集到的原始數據，存入ETL環境的物理存儲磁盤上。每次執行完一次ETL調度后，源系統會產生新的數據，稱這些新的數據為變化的數據，用變化的原因標記有變化的數據，抽取其中所有變化的數據。簡要的抽取步驟為：

（1）Dump：復制來源數據。

（2）Capture：抽選所需資料。

（3）Detect：數據變更獲取。

2.2 數據轉換

將業務數據以Push/Pull方式從源系統環境傳送到數據倉庫環境中。數據轉換加工，完成的是從緩沖區到基礎區的加工，以及基礎區到匯總區的轉換任務。在本項目中這類作業通過JAVA程序調用實現，JAVA程序調用SQL腳本，執行具有特定轉化邏輯的SQL語句。簡要的轉換步驟為：

（1）Staging Load：將數據加載暫存區。

（2）Validation：檢查數據并指定索引鍵。

（3）Transformation：將來源文件的結構轉換成目標倉儲表格結構。

（4）Merge：將多個數據源的數據融合。

2.3 數據裝載

將源系統抽取轉換的數據最終裝載到數據倉庫中，簡要的裝載步驟為：

（1）Apply：將變更記錄更新到數據倉庫區。

（2）Summarize and Mart：建立數據匯總和數據集市。

（3）Cube：建立數據集市的多維分析。

3 ETL系統的架構設計（Framework design of the ETL system）

我們分析了國內A商業銀行各業務系統的數據源，對數據倉庫存儲的數據做一個總體的預估，如表1所示。根據數據的存儲情況，設計相應的ETL系統架構，把數據從數據源系統中抽取出數據，加載至數據倉庫中[6]。由于在源系統開發中有著非常多的挑戰，導致ETL系統開發存在著極高的難度。任何意外情況的發生都會導致ETL系統的開發延期，耽誤項目整體進度，增加項目成本。因此ETL調度系統的架構設計一定要非常仔細謹慎。

3.1 ETL系統的功能與特點

根據商業銀行的業務特點和客戶對產品的需求，此項目設計的ETL系統將實現以下功能：

（1）ETL支持數據源多，異構平臺的數據關聯。支持系統的可視化、參數化。支持集群、負載均衡。

（2）ETL支持事件觸發、消息觸發等多種觸發機制。

（3）ETL支持多并發處理方式，可以根據系統處理資源的情況調整并發程度，處理效率高。

（4）ETL支持多節點處理，可以根據系統性能的要求，增加硬件功能來提高系統的生成效率。

3.2 ETL系統架構設計

結合國內某商業銀行的業務特點，我們規劃出ETL系統架構圖如圖2所示。

對于詳細的ETL系統架構，我們參照下面四個方面來設計[7-9]。

（1）設計全局規劃

規劃源數據，各系統業務邏輯，目標數據倉庫三級設計架構。根據各個系統業務邏輯的不同，開發相應的ETL系統，解決當前數據調度存在的和還未解決的問題。

（2）選擇ETL工具

在數據倉庫市場上存在多種ETL工具，本文采用調度抽取工具Datastage主動到各個源系統中獲取數據。

（3）開發默認策略

對ETL工具的基本需求進行整體考慮，開發默認策略。這些包括：從每個源系統獲取數據，歸檔獲取的數據或分級的數據，監管維度和特定事實的數據質量，維度屬性變化的管理，確保數據倉庫和ETL系統滿足系統可用性需求，設計數據審計子系統，組織ETL過渡區。

（4）按照目標表獲取數據

開發完所有的ETL調度任務后，還需要深入研究詳細的轉換工作。確定數據倉庫中數據來源的目標表，完成源數據到目標表的映射后，繼續完成數據概要描述工作，全部理解每個表所需要的數據轉換。

4 ETL在BI項目中的應用（ETL application in the Business Intelligence project）

下面在A商業銀行BI（Business Intelligence）項目，我們通過ETL調度系統來完成數據的轉換和存儲。在遵循ETL邏輯架構的基礎上，為各個源數據系統中的每一張業務表設計一個調度作業，表中數據的開發就是對調度作業數據的ETL操作過程。完成調度作業中數據的ETL階段代碼編寫、ETL調度開發，實現整個ETL數據存儲到數據倉庫中[10，11]。

進行ETL調度系統開發，首先要搭建開發環境，包括：導入初始化數據、設計調度程序、設計監控程序。其中調度程序的設計分為ETL任務設計和ETL調度設計，此外還要開發一個數據檢測程序SendMessage作為調度程序的輔助工具。監控程序的設計就是ETL監控設計。

4.1 ETL任務設計

首先要分析整理出調度作業的任務信息，包括數據源系統、各個系統中所有調度作業的編號、調度作業的階段信息、調度作業之間的前后驅關系等。具體設計步驟如下：

（1）按照數據源系統和作業處理階段收集任務信息，包括任務的執行周期、任務類型、任務渠道、任務階段等。把收集到的所有數據源系統依次編號，例如現在已經收集到了核心系統、信貸系統、ECIF系統、財管系統，依次將核心系統編號為10000、信貸系統編號為20000、ECIF系統編號為30000，財管系統編號為40000，后續出現的數據源系統以10000為單位編排下去。將作業每個調度階段進行編號，如數據檢測階段編號為1000，數據導出階段編號為2000，數據傳輸階段編號為3000，數據裝載階段編號為4000，數據備份階段編號為5000，其他階段編號為9000等。

（2）對收集到的所有作業進行任務編碼，例如核心系統共100個作業，每個作業都經過檢測裝載備份三個階段，第一個作業檢測階段編號11001，其中萬位數的1代表核心系統10000，千位數的1代表檢測階段1000，后三位001代表第一個作業。第一個作業生成裝載階段的作業編號14001，備份階段的作業編號15001，其他作業依次按順序生成相應的檢測，裝載，備份作業編號。統計所有的作業編號，階段信息和渠道信息，生成作業信息表。

（3）根據作業信息表中的任務信息，分析其所屬的階段和數據源，確定各任務之間的前后驅關系。一個ETL調度系統一般都是由上萬個不同的任務共同協作完成，任務之間的關系決定整個數據調度過程是否能夠順利進行。ETL調度系統中處理作業是按順序來的。先執行的作業被稱為前驅任務，用來決定其他作業是否執行，后執行的作業被稱為后驅任務。ETL任務中只有前一個作業處理執行成功才會執行后一個作業，所以作業之間的前后驅關系很重要。每個數據源系統設有一個首作業，首作業的后驅作業是所有的檢測作業，檢測作業的后驅作業是裝載作業，裝載作業的后驅作業是備份作業。梳理完成所有作業的前后驅關系，整合到調度系統中，在作業信息表中添加階段開始和結束的標志性任務，生成作業流程表。

（4）將前面整理好的作業任務轉化為SQL語句程序，在ETL工作站上部署該程序，系統啟動后，調度生成程序根據作業信息表和作業流程表生成作業調度表。

4.2 ETL調度設計

由于數據源系統非常多，所需加載的數據也非常多，導致ETL調度程序的設計非常復雜，因此設計一個完善且易于維護的ETL調度程序，對數據倉庫的正常工作運行是非常重要[12]。ETL調度流程如圖3所示。結合業務系統的特點，設計出高效的ETL調度程序，具體設計步驟如下：

（1）作業調度表生成完畢，開始執行調度任務程序，根據作業調度表中作業信息，作業調度執行的情況，生成調度執行情況表。

（2）根據作業優先級關系向各個ETL工作站分配相應數量的作業任務。如果某ETL工作站執行作業已經完成，則將新的作業分配給該ETL工作站，并更新調度執行情況表。

（3）假如某個調度任務執行出錯，則根據作業錯誤處理級別對作業進行相應處理，如等待人工處理、自動跳過錯誤任務、重復執行等。如果需要人工處理調度任務，則人工對調度任務進行相應處理，并記錄日志。

（4）當作業調度表中任務全部執行完成后，暫停調度程序，等待下一批次調度任務，ETL工作站也處于等待狀態。

4.3 ETL監控設計

ETL作業任務運行過程中，需要實時監控所有的作業執行情況，其中包括ETL單個作業的狀態監控等信息，ETL監控的主要作用是監控任務的執行情況及支持對錯誤任務的手工處理。

接收調度執行終端的反饋結果并進行相應的處理，反饋結果有任務成功，失敗狀態。接收數據檢測消息發送器的消息并做相應的處理，如接收到核心系統渠道2018-03-01的數據準備好，調度則開始跑批對應的任務。ETL監控相關流程如圖4所示。

4.4 ETL調度系統的實現

在上述設計的ETL調度系統中，所有的操作過程都是通過SQL程序語句來實現的。開發外部SQL腳本程序，調用該SQL程序來實現ETL系統的數據調度功能。SQL腳本邏輯可以根據不同的需求分別編寫，實現其功能，這樣做的好處是配置作業比較容易。

根據調度作業信息表的結構創建一個臨時作業表INIT_JOB，INIT_JOB表起到一個調度作業中轉的作用。INIT_JOB表結構如圖5所示。該臨時表可以記錄SQL腳本程序調用作業所需的大部分信息，例如：作業類型、作業名稱、所屬階段和渠道、調用腳本及參數等。這樣做的好處是可以方便的初始化作業信息。向INIT_JOB表中添加新增作業信息，通過INIT_JOB臨時表完成新增作業的信息存儲，調用SQL腳本完成作業的數據開發過程，最終將表中的業務數據存儲備份至數據倉庫中。當需要重新調度新的作業時，初始化INIT_JOB表為空，插入新增作業信息，根據新作業的業務特點，修改SQL腳本邏輯，實現新增作業的調度功能。

當作業調度開發結束后，通過查詢調度作業日志，查看數據調度是否成功，調度成功則數據成功存儲至數據倉庫中，如圖6所示。若調度失敗，則查看日志，找到調度失敗原因并修改，重新執行調度任務，直至調度作業執行成功。

5 結論（Conclusion）

本論文以國內某商業銀行的BI項目為背景，設計開發了商業銀行數據倉庫的ETL調度系統，實現了銀行各業務數據到數據倉庫的集中存儲，實現全行數據的統一化、標準化、高質量、高效率，極大的提升了商業銀行數據信息化水平。在大數據時代下，數據挖掘是銀行業之間競爭的重要工具，基于數據倉庫的數據分析，挖掘出其中潛在的重要的商業信息，可以很好的輔助銀行領導層管理決策，解決銀行最緊迫的問題，使銀行的經濟效益最大化，從而使銀行更好更快的發展。

參考文獻（References）

[1] Ren S，Sun Q，Shi Y.Customer Segmentation of Bank Based on Data Warehouse and Data Mining[C].The IEEE International Conference on Information Management and Engineering.IEEE，2010：349-353.

[2] L Baldacci，M Golfarelli，S Graziani，et al.QETL：An Approach to On-Demand ETL from Non-Owned Data Sources[J].Data & Knowledge Engineering，2017.

[3] V Theodorou，A Abelló，M Thiele，et al.Frequent Patterns in ETL Workflows： An Empirical Approach[J].Data & Knowledge Engineering，2017：112.

[4] Dupor S，Jovanovic V.An approach to conceptual modelling of ETL processes[C].International Convention on Information and Communication Technology，Electronics and Microelectronics.IEEE，2014：1485-1490.

[5] 徐俊剛，裴瑩.數據ETL研究綜述[J].計算機科學，2011，38（4）：15-20.

[6] 常艷莉，李一凡，趙懷慈.基于銀行數據倉庫的ETL系統的設計與實現[J].微計算機信息，2006， 22（24）：21-23.

[7] 沈琦，陳博.基于大數據處理的ETL框架的研究與設計[J].電子設計工程，2016，24（2）：25-27.

[8] 張同楊.一種ETL數據整合架構設計與實現[J].價值工程，2016，35（31）：88-91.

[9] 張靖，雷航，唐雪飛，等.ETL應用優化設計與實現研究[J].微電子學與計算機，2012，29（4）：134-137.

[10] 林尤惠，項穎，張鳳祺.ETL在銀行數據中心系統中的應用[J].自動化與信息工程，2009，30（2）：17-20.

[11] Waas F，Wrembel R，Freudenreich T，et al.On-Demand ELT Architecture for Right-Time BI：Extending the Vision[J].International Journal of Data Warehousing & Mining，2013，9（2）：21-38.

[12] 宋旭東，劉曉冰.數據倉庫ETL任務調度模型研究[J].控制與決策，2011，26（2）：271-275.

作者簡介：

周 飛（1989-），男，碩士生.研究領域：機器學習，數據挖掘.

劉夢娜（1992-），女，碩士生.研究領域：機器學習，數據挖掘.

張 暉（1994-），男，碩士生.研究領域：機器學習，數據挖掘.

李志剛（1994-），男，碩士生.研究領域：機器學習，數據挖掘.

曹 帥（1993-），男，碩士生.研究領域：機器學習，數據挖掘.