教學管理數據倉庫中ETL的實現

摘 要:ETL 工具從異構數據源抽取數據，并將數據清洗，規范化后裝載到數據倉庫。文章從前期的數據理解階段入手，分別討論了數據的抽取、清洗轉換、裝載等不同階段需要考慮的設計問題及相應的解決方案。提出了以數據理解為根基，以清洗轉換為中心的設計思想，并給出成績管理模塊的具體實施步驟。

關鍵詞:ETL數據倉庫數據抽取數據轉換數據加載

中圖分類號:TP311.13文獻標識碼:A文章編號:1674-098X(2011)06(a)-0017-02

1 ETL的具體實現

ETL具有以下兩個主要特點:①數據同步;②數據的成批操作。數據倉庫中的數據來源于教師、學生資料、學生考試成績等等，其中一些數據存儲在SQLServer、Foxpro等數據庫中，還有一些以文本、word和excel方式存儲于文件中，這些數據是異構數據，需要進一步處理后，才能加載到數據倉庫中。本系統運用SQL Server2000提供的DTS(數據轉換服務)工具，實現從不同的數據源中轉換數據以創建數據倉庫。

1.1 數據抽取

源數據庫的所有細節數據對于數據倉庫的主題域并不是都有用的，必須根據已確定主題的需要，從原有操作型數據庫中抽取相關數據到數據倉庫。一般在設計數據抽取時要考慮以下幾個方面:源數據庫和目標數據庫各自的數據庫格式是否一致？從源數據庫中要訪問哪些文件和表？從源數據庫中可以提取哪些字段，抽取記錄的條件是什么？目標數據庫中的表結構是什么？應當按照什么時間間隔來重復抽取表，定期更新數據倉庫等？大型數據抽取工作可有專門的數據處理工具來完成。如果有少量數據格式，也可有專業人員編寫抽取程序來完成數據抽取工作。

1.2 數據轉換

該數據倉庫中的數據來自一個或多個異構的數據庫系統，這些數據源之間往往存在著不一致的問題，如不一致的字段長度、不一致的賦值等。數據不一致會嚴重影響數據倉庫的數據質量。數據轉換就是處理這些不一致性的過程。

(1)統一數據名稱及格式。由于不同數據源數據明明及定義沒有統一的標準，因此在源數據載入數據倉庫之前必須對各個數據源的數據名稱及格式進行統一。要處理的內容如下:大小寫字母和文本全部轉換為統一格式;從定點的十進制數據到浮點式二進制數據的格式數值數據均須轉換為一致類型;統一書寫格式。如常見的日期格式(DD/MM/YY，MM/DD/YY，YY/MM/DD等)必須被轉換為同樣的形式。

(2)創建新的數據邏輯視圖。數據倉庫中存在著源數據庫可能不存在的數據，比如學生成績的平均分，通過人數等，因此還需要進行一下轉換:把一個字段的各個部分隔成兩個或多個字段;把一個記錄的兩個或多個字段組合成一個字段;把來自多個記錄的字段結合成一個記錄;增加一個新字段用來存儲匯總記錄;為了多維分析的方便，在導入數據時也常通過Case語句和Convert函數來進行簡單的數據轉換。其他設計復雜的轉換需要單獨編寫轉換函數來實現。

1.3 數據清洗

數據清洗的任務實際上就是過濾不符合要求的數據，將過濾的結果交給業務主管部門，由業務單位確認應該過濾掉或是修正之后再進行抽取。不符合要求的數據主要是有以下幾種:數據源中丟失數據、數據源中有錯誤數據、兩個或多個數據源中的數據不一致或發生沖突。

(1)對于數據的遺漏值和不規范值的處理，例如如生源地區，學生在網上自主錄入的字段，有些學生不遵守錄入規則，導致該字段出現空值和不規則值。對于這一類數據，可以利用系統的數據篩選功能將空值和不規則的值篩選出來加以手工修正。

(2)對于數據雜質和不一致的數據應視情況區別對待，不能一律刪除。例如學期成績，應當查詢該生當前學期每門課程的成績，從而來計算學期平均成績，如果該生當前學期有部分課程成績為空，則認定該生缺考以零值計算這些課程;如果全部課程成績都為空的話，則認定該學生學業發生變更，直接刪除這些數據。

(3)實現數據一致性，如:匯總后的學生信息表和學生成績表中學生人數不同，即一張表中的學生記錄在另一張表中沒有對應學生的數據，這將對日后的數據分析產生相當大的影響。為了兩張表所描述的學生統一起來，查詢并刪除這些記錄。

1.4 數據匯總

源數據庫中的細節數據進入數據倉庫后，還需要將這些數據在各種層次結構上進行匯總。例如，教學管理數據倉庫中存儲的細節數據時每個學生每門課考試的考試成績，由于時間維為學期、學年兩個層次。教師要對大面積的基礎課的學生成績作趨勢分析時，可能要獲取每門基礎課程的每學期、每學年的各個年級，各個學生的成績值時，就必須分別在時間維的學期、學年這兩個層次結構上對細節數據進行匯總。為了提高數據倉庫的查詢效率，我們往往要將這些匯總數據存儲到數據倉庫中。根據匯總級別不同可分為輕度匯總數據和高度匯總數據。

1.5 數據加載

數據加載就是將從源應用系統中抽取、轉換后的數據加載到數據倉庫系統中。教學管理數據倉庫中，主要采用以下幾種方法加載數據:(1)數據結構相匹配的SQL Server關系表，用SQL insert語句加載;(2)存儲于異構數據源的數據，如FoxPro關系數據庫，excel文件等，可以通過SQL Server的DTS來實現加載。(3)對需要調整的數據，經程序重整后轉變為固定格式的文本文件，再導入數據倉庫。(4)對少量的數據，利用手工錄入。

分析數據裝載進數據倉庫中以后，還需要驗證事實表與相關維表的引用完整性，確保所有事實表中的記錄都與維表中的適當記錄相關。但維表中的每條記錄不一定要與事實表中的數據相關。

2 ETL工具選擇原則

目前已有眾多廠商推出數據倉庫產品。 IBM、Sybase、 Oracle、CA、 SAS、NCR、 Microsoft等公司已相繼推出了自己的數據倉庫解決方案，它們的ETL工具也都各有其優勢和不足。在選擇ETL工具時我們必須遵守以下原則:可以支持多種平臺，支持多種數據庫;可以支持多種數據源，如 DBMS、電子表格、平面文件;具有規范的數據訪問接口;工具生成的代碼必須是在開發環境中可維護的;具有靈活的可編程性和調用外部程序的功能;能只抽取滿足指定條件的數據和源數據的指定部分;具有直觀的視圖、靈活的配置，能自動調用以定期實現管理工作;能在抽取過程中進行數據類型轉換和字符集轉換并能計算生成衍生的字段。

3 教學管理系統中ETL的實現

本教學管理系統的主題主要有三個方面:學生成績管理、學生就業管理、教師科研管理。根據以上需求建立教學管理數據倉庫，在經過總體需求分析后，建立了數據倉庫的邏輯模型和物理模型，基本確定了數據倉庫中事實表和維表的結構。下面的工作就是將原 MIS系統中的相關數據轉移到數據倉庫的事實表和維表中。主要包括:確定數據源、指定數據目的地以及操縱和轉換從數據源到數據目的地的數據。現在各大廠商提出的數據倉庫的解決方案中都提供了 ETL工具，在眾多產品中，一致認為 DTS是系統最易使用、擴展性最好、編程效率最高的數據抽取工具。

(1)DTS可以自動或交互地從多個異構數據源向數據倉庫裝入數據;(2)DTS支持快速的非記錄的塊拷貝程序向 SQL Server數據庫插入數據。這是目前為止將大量的數據移動到SQL Server表中最快的方法;(3)DTS基于OLE DB接口能夠在關系數據源、 非關系數據源以及ODBC數據源之間進行轉移數據;(4)DTS支持使用VBScript或 JavaScript等腳本語言創建自定義的轉換腳本。也允許使用編程語言(如Visual Basic或Visual C++)編寫自定義的組件，能夠在轉換中對數據進行各種操作;(5)DTS同SQL Server 2000結合緊密，可以自動調度導入或操作任務，也可以使用SQL代理服務來進行調度。

因此在本系統中選用 Microsoft的 DTS作為ET L工具。DTS主要的功能有導入和導出數據、變換數據和傳送數據庫對象。

DTS允許在一個過程中完成導入、導出和變換數據。這個過程的定義可以保存在包當中。DTS包含三種類型對象:連接對象、任務對象和步驟對象。連接對象定義數據源的連接，即與轉換的源和目標的連接;任務對象定義了包中的動作，例如執行 SQL語句、拷貝一個表的內容或執行一段腳本;步驟對象定義任務對象的執行的順序。定義包有三種方式:使用 DTS設計器(DTS Designer)、DTS導入和導出向導、DTS編程接口。DTS設計器定義包。包可以三種方式保存:基于COM的文件、MS SQL Server的msdb數據庫、作為外部Visual Basic文件。在本系統中我們用SQL語言和VBScript腳本語言對加載過程進行編程控制，以正確完成加載任務。對所定義的包保存在msdb數據庫中。DTS組件在定義數據源和目的連接以后，可以在兩者之間進行數據轉換。這是數據轉移的主要階段。DTS既可以復制整個表和視圖，又可以復制特定SQL語句返回的數據，還可以針對源和目的都是 SQL數據庫時，復制所有數據庫對象和數據。對事實表的轉換任務如下:由于事實表的字段全部來自原管理系統中的成績表，只是字段名稱不同，所以用SQL查詢語句即可:select ts.學號，ts.教師編號as教師號，ts.課程編號as課程號，ts.成績，ts.考試時間

from教學成績表ts

查詢語句編寫完成并分析有效以后，需要對目標數據庫的表進行選擇，在目的選項卡中單擊“創建”按鈕，填入事實表名scores fact和新增字段“學期編號”即可完成后如圖1

最后是對應字段的映射處理，在轉換選項卡中只要進行字段一對一的復制即可(除學期編號字段外)，如圖2。

由于“學期編號”字段在源成績表中只有考試時間，所以需要把考試時間按照一定的規則轉換成“學期編號”，在此選擇“VB Script Language”語言將源腳本替換為如下腳本。

Function Main()

if year(DTSSource(\"考試時間\"))=2006 and month(DTSSource(\"考試時間\"))>=9 then

DTSDestination(\"學期編號\") =\"2006200701\"

end if

if year(DTSSource(\"考試時間\"))=2007 and month(DTSSource(\"考試時間\"))<=7 then

DTSDestination(\"學期編號\") =\"2006200702\"

end if

if year(DTSSource(\"考試時間\"))=2007 and month(DTSSource(\"考試時間\"))>=9 then DTSDestination(\"學期編號\") =\"2007200801\"

end if

if year(DTSSource(\"考試時間\"))=2008 and month(DTSSource(\"考試時間\"))<=7 then

DTSDestination(\"學期編號\") =\"2007200802\"

end if

if year(DTSSource(\"考試時間\"))=2008 and month(DTSSource(\"考試時間\"))>=9 then

DTSDestination(\"學期編號\") =\"2008200901\"

end if

if year(DTSSource(\"考試時間\"))=2009 and month(DTSSource(\"考試時間\"))<=7 then

DTSDestination(\"學期編號\") =\"2008200902\"

end if

Main=DTSTransformStat_OK

End Function

各維表的轉換過程與此類似，只是在進行學期維表的轉換時，由于學期維表中的“學年”“學期”字段都來自于原操作型數據庫成績表的“考試時間”字段，轉換方法同“學期編號”字段通過ActiveX腳本對數據進行一些編程的轉換才能實現，在此就不敘述了。

事實表和各個維表轉換好以后，執行這個定義好的DTS轉換任務，數據將會按照設定步驟和規則導入數據倉庫維表和事實表中，從而完成了數據倉庫的數據轉載任務。同時還可以設置DTS包，將原操作型數據庫中變動數據定期自動地更新到數據倉庫中。

4 結語

ETL是數據倉庫開發項目的關鍵部分，也是一個長期的過程，同時這部分的工作直接關系數據倉庫中數據的質量，從而影響到決策分析的結果和質量。在ETL過程中的每一步都會發現大量的問題，有些可以直接解決，有些則需要回溯到前一個甚至幾個過程。通常情況下，每次對 ETL過程的修改都需要重新運行整個ETL過程并對結果進行驗證。這樣一來，開發整個ETL過程的所需的時間必然很長。因此，只有認真、仔細地設計ETL過程中的每一步，盡量使得 ETL過程每一步的運行效率更高、結果更準確同時更容易修改，才能有效保證整個項目的最終成功。

參考文獻

[1]W.H.I nmon，Building the data bridge，the ten critical success fact ors ofbuilding a data warehouse.DataBase Pr ogrammingDesign.1992(11):70～73.

[2]W.H.I nmon等著，王志海等譯，數據倉庫1第二版[M]，北京:機械工業出版社， 20001.

[3]張寧、賈自艷、史忠植，“數據倉庫中ET L技術的研究”[J]，計算機工程與應用，2002(24):213～216.

[4]沙笑笑，等.DTS工具在建立數據倉庫過程中的應用[J].科技創新導報，2008，10:26.