在商業(yè)智能（BI）項目中，ETL（Extract, Transform, Load）開發(fā)起著至關(guān)重要的作用，被視為項目中不可或缺的關(guān)鍵環(huán)節(jié)。ETL的主要任務(wù)是從各個不同的數(shù)據(jù)源中提取數(shù)據(jù)，經(jīng)過轉(zhuǎn)換處理，然后加載到目標(biāo)數(shù)據(jù)庫或數(shù)據(jù)倉庫中。這一過程將那些分散、零散、標(biāo)準(zhǔn)不統(tǒng)一的數(shù)據(jù)整合成一致、結(jié)構(gòu)清晰的數(shù)據(jù)集，為企業(yè)的決策制定和業(yè)務(wù)分析提供堅實的基礎(chǔ)。

一個高效、穩(wěn)定的ETL系統(tǒng)能夠大大提高數(shù)據(jù)的質(zhì)量和可用性，有助于企業(yè)快速、準(zhǔn)確地進行數(shù)據(jù)分析和報告生成。相反，如果ETL設(shè)計不當(dāng)或執(zhí)行不佳，可能會導(dǎo)致數(shù)據(jù)質(zhì)量下降，進而影響到整個BI項目的準(zhǔn)確性和有效性。據(jù)統(tǒng)計，ETL開發(fā)通常會占據(jù)BI項目總體時間的三分之一，可見其在項目中的重要地位和影響力。

在接下來的文章中，我們將深入探討ETL與BI之間的密切關(guān)系，以及如何優(yōu)化ETL開發(fā)，以實現(xiàn)BI項目的最佳效果。

示例中提到的數(shù)據(jù)模板分享給大家——
https://s.fanruan.com/yhmmx
零基礎(chǔ)快速上手，還能根據(jù)需求進行個性化修改哦

一、相關(guān)名詞簡介

1. 數(shù)據(jù)倉庫

英文全稱為Data Warehouse，簡稱為DW。

數(shù)據(jù)倉庫之父比爾·恩門（Bill Inmon）在1991年出版的《Building the Data Warehouse》（《建立數(shù)據(jù)倉庫》）一書中提出了關(guān)于數(shù)據(jù)倉庫的定義——數(shù)據(jù)倉庫（Data Warehouse）是一個面向主題的（Subject Oriented）、集成的（Integrated）、相對穩(wěn)定的（Non-Volatile）、反映歷史變化（Time Variant）的數(shù)據(jù)集合，用于支持管理決策(Decision Making Support）。

換句話說，數(shù)據(jù)倉庫是一個以實現(xiàn)特定分析決策為目的，能夠隨時間的變化，穩(wěn)定地、持續(xù)地為這個需求提供所需要的數(shù)據(jù)的集合。

業(yè)務(wù)庫與數(shù)據(jù)倉庫的不同：

• 業(yè)務(wù)庫通常是面向事務(wù)來設(shè)計的，而數(shù)據(jù)倉庫則是面向主題來設(shè)計的。
• 業(yè)務(wù)庫盡量避免冗余，而數(shù)據(jù)倉庫則會有意引入冗余，以便于快速分析。
• 業(yè)務(wù)庫為捕獲數(shù)據(jù)而設(shè)計，數(shù)據(jù)倉庫是為分析數(shù)據(jù)而設(shè)計。

2. 操作型數(shù)據(jù)存儲

英文全稱為Operational Data Store，簡稱ODS，是數(shù)據(jù)倉庫體系結(jié)構(gòu)中的一個可選部分，也被稱為貼源層。

ODS具備數(shù)據(jù)倉庫的部分特征和OLTP系統(tǒng)的部分特征。( On-Line Transaction Processing 聯(lián)機事務(wù)處理過程，前臺接收的用戶數(shù)據(jù)可以立即傳送到計算中心進行處理，并在很短的時間內(nèi)給出處理結(jié)果。) 它是“面向主題的、集成的、當(dāng)前或接近當(dāng)前的、不斷變化的”數(shù)據(jù)。

3. ETL

數(shù)據(jù)抽取（Extract）

這一部分需要在調(diào)研階段做大量的工作，首先要搞清楚數(shù)據(jù)是從幾個業(yè)務(wù)系統(tǒng)中來,各個業(yè)務(wù)系統(tǒng)的數(shù)據(jù)庫服務(wù)器運行什么DBMS,是否存在手工數(shù)據(jù)，手工數(shù)據(jù)量有多大，是否存在非結(jié)構(gòu)化的數(shù)據(jù)等等，當(dāng)收集完這些信息之后才可以進行數(shù)據(jù)抽取的設(shè)計。

• 對于與存放DW的數(shù)據(jù)庫系統(tǒng)相同的數(shù)據(jù)源處理方法

這一類數(shù)據(jù)源在設(shè)計上比較容易。一般情況下，DBMS(SQLServer、Oracle)都會提供數(shù)據(jù)庫鏈接功能，在DW數(shù)據(jù)庫服務(wù)器和原業(yè)務(wù)系統(tǒng)之間建立直接的鏈接關(guān)系就可以寫Select 語句直接訪問。

• 對于與DW數(shù)據(jù)庫系統(tǒng)不同的數(shù)據(jù)源的處理方法

對于這一類數(shù)據(jù)源，一般情況下也可以通過ODBC的方式建立數(shù)據(jù)庫鏈接——如SQL Server和Oracle之間。如果不能建立數(shù)據(jù)庫鏈接，可以有兩種方式完成，一種是通過工具將源數(shù)據(jù)導(dǎo)出成.txt或者是.xls文件，然后再將這些源系統(tǒng)文件導(dǎo)入到ODS中。另外一種方法是通過程序接口來完成。

• 對于文件類型數(shù)據(jù)源(.txt,.xls)

可以培訓(xùn)業(yè)務(wù)人員利用數(shù)據(jù)庫工具將這些數(shù)據(jù)導(dǎo)入到指定的數(shù)據(jù)庫，然后從指定的數(shù)據(jù)庫中抽取?；蛘哌€可以借助工具實現(xiàn)。

• 增量更新的問題

對于數(shù)據(jù)量大的系統(tǒng)，必須考慮增量抽取。一般情況下，業(yè)務(wù)系統(tǒng)會記錄業(yè)務(wù)發(fā)生的時間，我們可以用來做增量的標(biāo)志,每次抽取之前首先判斷ODS中記錄最大的時間，然后根據(jù)這個時間去業(yè)務(wù)系統(tǒng)取大于這個時間所有的記錄。利用業(yè)務(wù)系統(tǒng)的時間戳，一般情況下，業(yè)務(wù)系統(tǒng)沒有或者部分有時間戳。

數(shù)據(jù)清洗轉(zhuǎn)換（Cleaning、Transform）

一般情況下，數(shù)據(jù)倉庫分為ODS、DW兩部分。通常的做法是從業(yè)務(wù)系統(tǒng)到ODS做清洗，將臟數(shù)據(jù)和不完整數(shù)據(jù)過濾掉，在從ODS到DW的過程中轉(zhuǎn)換，進行一些業(yè)務(wù)規(guī)則的計算和聚合。

• 數(shù)據(jù)清洗

數(shù)據(jù)清洗的任務(wù)是過濾那些不符合要求的數(shù)據(jù)，將過濾的結(jié)果交給業(yè)務(wù)主管部門，確認(rèn)是否過濾掉還是由業(yè)務(wù)單位修正之后再進行抽取。

不符合要求的數(shù)據(jù)主要是有不完整的數(shù)據(jù)、錯誤的數(shù)據(jù)、重復(fù)的數(shù)據(jù)三大類。

(1)不完整的數(shù)據(jù)：這一類數(shù)據(jù)主要是一些應(yīng)該有的信息缺失，如供應(yīng)商的名稱、分公司的名稱、客戶的區(qū)域信息缺失、業(yè)務(wù)系統(tǒng)中主表與明細(xì)表不能匹配等。對于這一類數(shù)據(jù)過濾出來，按缺失的內(nèi)容分別寫入不同Excel文件向客戶提交，要求在規(guī)定的時間內(nèi)補全。補全后才寫入數(shù)據(jù)倉庫。

(2)錯誤的數(shù)據(jù)：這一類錯誤產(chǎn)生的原因是業(yè)務(wù)系統(tǒng)不夠健全，在接收輸入后沒有進行判斷直接寫入后臺數(shù)據(jù)庫造成的，比如數(shù)值數(shù)據(jù)輸成全角數(shù)字字符、字符串?dāng)?shù)據(jù)后面有一個回車操作、日期格式不正確、日期越界等。這一類數(shù)據(jù)也要分類，對于類似于全角字符、數(shù)據(jù)前后有不可見字符的問題，只能通過寫SQL語句的方式找出來，然后要求客戶在業(yè)務(wù)系統(tǒng)修正之后抽取。日期格式不正確的或者是日期越界的這一類錯誤會導(dǎo)致ETL運行失敗，這一類錯誤需要去業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫用SQL的方式挑出來，交給業(yè)務(wù)主管部門要求限期修正，修正之后再抽取。

(3)重復(fù)的數(shù)據(jù)：對于這一類數(shù)據(jù)——特別是維表中會出現(xiàn)這種情況——將重復(fù)數(shù)據(jù)記錄的所有字段導(dǎo)出來，讓客戶確認(rèn)并整理。

數(shù)據(jù)清洗是一個反復(fù)的過程，不可能在幾天內(nèi)完成，只有不斷的發(fā)現(xiàn)問題，解決問題。對于是否過濾，是否修正一般要求客戶確認(rèn)，對于過濾掉的數(shù)據(jù)，寫入Excel文件或者將過濾數(shù)據(jù)寫入數(shù)據(jù)表，在ETL開發(fā)的初期可以每天向業(yè)務(wù)單位發(fā)送過濾數(shù)據(jù)的郵件，促使他們盡快地修正錯誤,同時也可以做為將來驗證數(shù)據(jù)的依據(jù)。數(shù)據(jù)清洗需要注意的是不要將有用的數(shù)據(jù)過濾掉，對于每個過濾規(guī)則認(rèn)真進行驗證，并要用戶確認(rèn)。

• 數(shù)據(jù)轉(zhuǎn)換

數(shù)據(jù)轉(zhuǎn)換的任務(wù)主要進行不一致的數(shù)據(jù)轉(zhuǎn)換、數(shù)據(jù)粒度的轉(zhuǎn)換，以及一些商務(wù)規(guī)則的計算。

(1)不一致數(shù)據(jù)轉(zhuǎn)換：這個過程是一個整合的過程，將不同業(yè)務(wù)系統(tǒng)的相同類型的數(shù)據(jù)統(tǒng)一，比如同一個供應(yīng)商在結(jié)算系統(tǒng)的編碼是XX0001,而在CRM中編碼是YY0001，這樣在抽取過來之后統(tǒng)一轉(zhuǎn)換成一個編碼。

(2)數(shù)據(jù)粒度的轉(zhuǎn)換：業(yè)務(wù)系統(tǒng)一般存儲非常明細(xì)的數(shù)據(jù)，而數(shù)據(jù)倉庫中數(shù)據(jù)是用來分析的，不需要非常明細(xì)的數(shù)據(jù)。一般情況下，會將業(yè)務(wù)系統(tǒng)數(shù)據(jù)按照數(shù)據(jù)倉庫粒度進行聚合。

(3)商務(wù)規(guī)則的計算：不同的企業(yè)有不同的業(yè)務(wù)規(guī)則、不同的數(shù)據(jù)指標(biāo)，這些指標(biāo)有的時候不是簡單的加加減減就能完成，這個時候需要在ETL中將這些數(shù)據(jù)指標(biāo)計算好了之后存儲在數(shù)據(jù)倉庫中，以供分析使用。

數(shù)據(jù)的加載（Load）

在數(shù)據(jù)加載中，提取、轉(zhuǎn)換、加載（ETL）工具會將轉(zhuǎn)換后的數(shù)據(jù)從暫存區(qū)移動到目標(biāo)數(shù)據(jù)倉庫。對于大多數(shù)使用 ETL 的組織來說，該過程是自動化的、定義明確的、連續(xù)的和批處理驅(qū)動的。下面是兩種加載數(shù)據(jù)的方法。

• 完全加載

在完全加載時，來自源的全部數(shù)據(jù)被轉(zhuǎn)換并移動到數(shù)據(jù)倉庫。完全加載通常發(fā)生在您第一次將數(shù)據(jù)從源系統(tǒng)加載到數(shù)據(jù)倉庫中時。

• 增量加載

在增量加載中，ETL 工具會定期加載目標(biāo)系統(tǒng)和源系統(tǒng)之間的增量（或差異）。它會存儲最后提取日期，以便僅加載在此日期之后添加的記錄。有兩種方法可以實現(xiàn)增量加載：

1）流式增量加載

如果您的數(shù)據(jù)量較小，您可以通過數(shù)據(jù)管道將持續(xù)更改流式傳輸?shù)侥繕?biāo)數(shù)據(jù)倉庫。當(dāng)數(shù)據(jù)速度增加到每秒數(shù)百萬個事件時，您可以使用事件流處理來監(jiān)控和處理數(shù)據(jù)流，從而更及時地做出決策。

2）批量增量加載

如果您的數(shù)據(jù)量很大，您可以定期分批收集將負(fù)載數(shù)據(jù)更改。在此設(shè)定的時間段內(nèi)，由于數(shù)據(jù)同步，源系統(tǒng)或目標(biāo)系統(tǒng)都不會發(fā)生任何操作。

二、如何優(yōu)化ETL開發(fā)？

ETL是BI項目的關(guān)鍵部分，也是一個長期的過程，只有不斷的發(fā)現(xiàn)問題并解決問題，才能使ETL運行效率更高，為BI項目后期開發(fā)提供準(zhǔn)確與高效的數(shù)據(jù)。

要優(yōu)化ETL（提取、轉(zhuǎn)換、加載）的開發(fā)，可以采取以下一些措施：

1. 需求分析和設(shè)計階段的充分準(zhǔn)備

在項目開始階段，與業(yè)務(wù)部門充分溝通，了解數(shù)據(jù)需求和業(yè)務(wù)規(guī)則。清晰定義數(shù)據(jù)源和目標(biāo)系統(tǒng)的結(jié)構(gòu)、字段映射、數(shù)據(jù)轉(zhuǎn)換規(guī)則等，可以避免后續(xù)開發(fā)過程中的重大變更和調(diào)整，提高開發(fā)效率。

2. 選擇合適的ETL工具

根據(jù)項目的需求和復(fù)雜性，選擇適合的ETL工具。市面上有許多商業(yè)和開源的ETL工具，開源的ETL工具包括Apache NiFi、Talend Open Studio、Pentaho Data Integration（Kettle）、Apache Kafka、Apache Airflow和StreamSets等。

企業(yè)級的ETL工具推薦FineDataLink！FineDataLink是帆軟旗下低代碼/高時效的一站式數(shù)據(jù)集成平臺，主要面向企業(yè)IT人員（信息部門/ITBP）人群，幫助他們解決各類不同數(shù)據(jù)源的數(shù)據(jù)對接和處理問題（數(shù)據(jù)庫、接口API、業(yè)務(wù)系統(tǒng)等），賦予用戶僅通過單一平臺，實現(xiàn)實時數(shù)據(jù)傳輸、數(shù)據(jù)調(diào)度、數(shù)據(jù)治理等各類復(fù)雜組合場景的能力，為企業(yè)業(yè)務(wù)的數(shù)字化轉(zhuǎn)型提供支持。

對比其他數(shù)據(jù)集成工具，F(xiàn)ineDataLink有以下優(yōu)勢：

• 畫布式開發(fā)，數(shù)據(jù)梳理，過程所見即所得：傳統(tǒng)ETL工具上手復(fù)雜，而FDL采用圖形化界面，智能化的異構(gòu)數(shù)據(jù)類型匹配，通過簡單的拖拉拽實現(xiàn)數(shù)據(jù)加工計算。從任務(wù)管理頁面到每一個ETL任務(wù)的節(jié)點，都致力于做到簡單易懂、直接上手可用，降低開發(fā)難度和時間成本。
• 雙核引擎、提升數(shù)據(jù)處理效率：FineDataLink提供ELT、ETL雙核引擎，針對不同業(yè)務(wù)場景提供定制化解決方案，提高數(shù)據(jù)處理效率和準(zhǔn)確性。比如較大數(shù)據(jù)量的同步（單表數(shù)據(jù)超過1kw行）可以采用ELT（數(shù)據(jù)同步）原表原樣的從數(shù)據(jù)源端同步至目標(biāo)庫中，當(dāng)數(shù)據(jù)需要經(jīng)過復(fù)雜處理時可以通過ETL（數(shù)據(jù)轉(zhuǎn)換）實現(xiàn)。

3. 模塊化設(shè)計和重用

將ETL任務(wù)劃分為小模塊，每個模塊專注于特定的功能或任務(wù)。利用模塊化設(shè)計，可以提高代碼的可維護性和復(fù)用性，降低開發(fā)和維護成本。同時，建立ETL任務(wù)庫或模板，可以重復(fù)使用已經(jīng)開發(fā)和測試過的代碼片段，減少重復(fù)勞動和錯誤。

4. 優(yōu)化數(shù)據(jù)處理流程

在數(shù)據(jù)提取、轉(zhuǎn)換和加載過程中，盡量減少不必要的數(shù)據(jù)移動和轉(zhuǎn)換操作。優(yōu)化SQL查詢、使用索引、合并和拆分步驟，可以提高數(shù)據(jù)處理的效率和性能。此外，考慮使用增量加載、并行處理和分布式計算等技術(shù)，進一步提高ETL任務(wù)的執(zhí)行速度和擴展性。

5. 自動化測試和監(jiān)控

建立完善的測試和監(jiān)控機制，確保ETL任務(wù)的穩(wěn)定性和可靠性。使用自動化測試工具對ETL流程進行全面的單元測試、集成測試和系統(tǒng)測試，及時發(fā)現(xiàn)和修復(fù)問題。同時，建立監(jiān)控系統(tǒng)，實時跟蹤ETL任務(wù)的執(zhí)行情況和性能指標(biāo)，及時發(fā)現(xiàn)并處理異常和性能瓶頸。

6. 持續(xù)優(yōu)化和改進

ETL開發(fā)是一個持續(xù)優(yōu)化和改進的過程。定期審查和分析ETL任務(wù)的執(zhí)行情況和性能數(shù)據(jù)，找出潛在的瓶頸和問題，并采取相應(yīng)的優(yōu)化措施。同時，與業(yè)務(wù)部門和其他團隊保持密切溝通，及時了解業(yè)務(wù)需求的變化和反饋，調(diào)整和優(yōu)化ETL任務(wù)以適應(yīng)新的需求和挑戰(zhàn)。

三、結(jié)語

總結(jié)來說，ETL在BI項目中扮演著不可或缺的角色，它是將企業(yè)分散、零散、標(biāo)準(zhǔn)不統(tǒng)一的數(shù)據(jù)整合成有價值、有意義的信息的關(guān)鍵步驟。一個高效、穩(wěn)定的ETL系統(tǒng)能夠為企業(yè)提供準(zhǔn)確、一致、及時的數(shù)據(jù)支持，從而支持企業(yè)的決策制定和業(yè)務(wù)分析。

然而，要確保ETL過程的順利進行和數(shù)據(jù)質(zhì)量的高標(biāo)準(zhǔn)，需要多方面的考慮和精心的規(guī)劃。ETL的設(shè)計、開發(fā)和實施都需要高度專業(yè)的技能和經(jīng)驗，以應(yīng)對各種復(fù)雜的數(shù)據(jù)源和業(yè)務(wù)需求。