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

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

關鍵詞：數(shù)據(jù)倉庫；ETL；商業(yè)銀行；商業(yè)智能

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

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

1 引言（Introduction）

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

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

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

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

數(shù)據(jù)倉庫中數(shù)據(jù)的調度工作是通過ETL工具來實現(xiàn)的。ETL是數(shù)據(jù)調度系統(tǒng)三個階段抽?。‥xtract）、轉換（Transform）、裝載（Load）的英文首字母縮寫[4，5]。下面我們來簡要的介紹ETL的三個階段。

2.1 數(shù)據(jù)抽取

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

（1）Dump：復制來源數(shù)據(jù)。

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

（3）Detect：數(shù)據(jù)變更獲取。

2.2 數(shù)據(jù)轉換

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

（1）Staging Load：將數(shù)據(jù)加載暫存區(qū)。

（2）Validation：檢查數(shù)據(jù)并指定索引鍵。

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

（4）Merge：將多個數(shù)據(jù)源的數(shù)據(jù)融合。

2.3 數(shù)據(jù)裝載

將源系統(tǒng)抽取轉換的數(shù)據(jù)最終裝載到數(shù)據(jù)倉庫中，簡要的裝載步驟為：

（1）Apply：將變更記錄更新到數(shù)據(jù)倉庫區(qū)。

（2）Summarize and Mart：建立數(shù)據(jù)匯總和數(shù)據(jù)集市。

（3）Cube：建立數(shù)據(jù)集市的多維分析。

3 ETL系統(tǒng)的架構設計（Framework design of the ETL system）

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

3.1 ETL系統(tǒng)的功能與特點

根據(jù)商業(yè)銀行的業(yè)務特點和客戶對產品的需求，此項目設計的ETL系統(tǒng)將實現(xiàn)以下功能：

（1）ETL支持數(shù)據(jù)源多，異構平臺的數(shù)據(jù)關聯(lián)。支持系統(tǒng)的可視化、參數(shù)化。支持集群、負載均衡。

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

（3）ETL支持多并發(fā)處理方式，可以根據(jù)系統(tǒng)處理資源的情況調整并發(fā)程度，處理效率高。

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

3.2 ETL系統(tǒng)架構設計

結合國內某商業(yè)銀行的業(yè)務特點，我們規(guī)劃出ETL系統(tǒng)架構圖如圖2所示。

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

（1）設計全局規(guī)劃

規(guī)劃源數(shù)據(jù)，各系統(tǒng)業(yè)務邏輯，目標數(shù)據(jù)倉庫三級設計架構。根據(jù)各個系統(tǒng)業(yè)務邏輯的不同，開發(fā)相應的ETL系統(tǒng)，解決當前數(shù)據(jù)調度存在的和還未解決的問題。

（2）選擇ETL工具

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

（3）開發(fā)默認策略

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

（4）按照目標表獲取數(shù)據(jù)

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

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

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

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

4.1 ETL任務設計

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

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

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

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

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

4.2 ETL調度設計

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

（1）作業(yè)調度表生成完畢，開始執(zhí)行調度任務程序，根據(jù)作業(yè)調度表中作業(yè)信息，作業(yè)調度執(zhí)行的情況，生成調度執(zhí)行情況表。

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

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

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

4.3 ETL監(jiān)控設計

ETL作業(yè)任務運行過程中，需要實時監(jiān)控所有的作業(yè)執(zhí)行情況，其中包括ETL單個作業(yè)的狀態(tài)監(jiān)控等信息，ETL監(jiān)控的主要作用是監(jiān)控任務的執(zhí)行情況及支持對錯誤任務的手工處理。

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

4.4 ETL調度系統(tǒng)的實現(xiàn)

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

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

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

5 結論（Conclusion）

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

參考文獻（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] 徐俊剛，裴瑩.數(shù)據(jù)ETL研究綜述[J].計算機科學，2011，38（4）：15-20.

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

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

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

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

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

[11] Waas F，Wrembel R，F(xiàn)reudenreich 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] 宋旭東，劉曉冰.數(shù)據(jù)倉庫ETL任務調度模型研究[J].控制與決策，2011，26（2）：271-275.

作者簡介：

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

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

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

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

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