BI 主要環節 ETL 相關知識

標籤：

BI架構-BI 主要環節 ETL 相關知識主要功能  ：將源系統的資料載入到資料倉儲 及資料集市層中；主要問題體現：  複雜的來源資料環境，包括繁多的資料種類、巨大的載入資料量、錯綜複雜的資料關係和參差不齊的資料品質常見術語   ETL：資料幫浦、轉換、載入（Extract/Transform/Load） EXF：抽取的檔案（Extract File） CIF：共用介面檔案（Common Interface File） PLF：預先載入檔案（Preload File） LDF：負載檔案（Load File） DW：資料倉儲（Data Warehouse） DM：資料集市（Data Mart） GC：共用介面檔案組（CIF Group），將一對EX（抽取）和CV（變換）程式組合的程式 GE：實體組（Entity Group），將TR（轉換）和LD（載入）程式組合的程式ETL 功能架構：  
由 可以看出 架構可分為三個部分1、管理調度           根據目標資料表的更新周期和來源資料就緒時間，制定日常資料的ETL的時刻表。管理員通過ETL工具的作業調度功能進行運行時刻設定，使得ETL工具自動在規定條件滿足時啟動相應的ETL作業。每個目標資料表ETL過程對應一組順序執行的實體作業（包括轉換作業和載入作業）形成的一個 序列（Sequence），每個CIF（共用介面檔案）的ETL過程則對應一組順序執行CIF作業(包括抽取作業和變換作業)形成的一個序列。這些ETL作業將其中的每個步驟，即抽取、變換、轉換、載入等ETL功能模組有機地聯絡起來。而作業調度是將CIF邏輯的作業和實體邏輯的作業按照GC（CIF組）與GE（實體組）的對應關係聯絡起來，從而控制該ETL過程的運作2、應用功能        ETL應用模組層次中包含實現每個ETL步驟的程式及對這些步驟進行歸併及設定依賴性的程式，即 抽取（Extract）、變換（Convert）、轉換（Transform）、載入（Load）程式。每個模組實現一個特定的功能，詳述如下：     資料幫浦（Extract）     資料變換（Convert/Clean）     資料轉換（Transform）     資料載入（Load）     每個階段之間以資料檔案作為介面，即資料幫浦（Extract）階段讀取資料來源產生EXF資料，資料變換（Converting/Cleansing）階段讀取EXF資料產生CIF資料，資料轉換（Transform）階段讀取CIF資料產生LDF資料（如果有預先載入過程還可能產生中間PLF資料），資料載入（Load）階段讀取LDF資料載入到資料倉儲或資料集市。     上述將資料幫浦、轉換和載入分隔開，以CIF格式作為目標表和資料來源之間的橋樑，從而使每個功能相對獨立，減少各功能相互間的耦合度，同時，每個模組的功能被細分後，邏輯更加簡單，更容易控制開發錯誤，提高開發效率。另外，也便於系統運行過程中的錯誤追蹤和異常恢複。為了驗證ETL載入的資料的正確性，根據業務的要求，可能需要在資料倉儲或資料集市中進行資料總計平衡檢查（Amount Balance Check），該過程必須在相關的ETL表資料載入完成以後進行。    ETL各模組之間無任何調用關係，它們之間可能的關係僅僅是一個模組的輸出檔案是另一個模組需要讀取和加工的檔案。一個ETL功能模組就是一個ETL作業，每個功能模組的邏輯都只是ETL過程中一個相對獨立的部分。3、控制環境     ETL功能模組的運行需要由相應的參數進行控制，同時在各模組之間也存在很多控制檔案及調用一些公用的功能，功能模組在運行過程中可能產生拒絕檔案，針對功能模組的健全狀態會有產生一些監控資訊等等，這些對於ETL功能模組的運行起到控制與支撐的環境以及相應的維護管理程式構成ETL架構環境   上兩層構成ETL應用，而ETL控制環境則為以上層次的每個應用程式提供支援，應用程式層次上獨立的功能模組都通過更上一個層次的邏輯關係聯絡起來，使每個模組的功能更加清晰、明確ETL模式     完全重新整理（Refresh，Type 1）：資料庫資料表中只包括最新的資料，每次載入均刪除原有資料，然後完全載入最新的來源資料。如大多數參數表的載入都採用這種模式。這種模式下，資料幫浦程式抽取來源資料中的所有記錄，在載入前，將目標資料表清空，然後載入所有記錄。為提高刪除資料的速度，一般是採用Truncate清空資料表而不採用SQL Delete進行刪除。     鏡像增量（Snapshot Append，Type 2）：來源資料中的記錄定期更新，但記錄中包括記錄時間欄位，來源資料中儲存了資料曆史的記錄，ETL可以通過記錄時間將增量資料從來源資料抽取出來以附加的方式載入到資料庫中，資料的記錄也會被保留在資料庫中。     事件增量（Event Append，Type 3）：每一個記錄是一個新的事件，相互之間沒有必然的聯絡，新記錄不是對原有記錄數值的變更，記錄包括時間欄位，可以通過時間欄位將新增資料幫浦出來載入到資料庫中     鏡像比較（Snapshot Delta，Type 4）：資料倉儲資料具有生效日期欄位以儲存資料的曆史資訊，而來源資料不保留曆史並且每天都可能被更新。因此，只能將新的鏡像資料與上次載入的資料的鏡像進行比較，找出變更部分（即Delta），更新歷史資料被更新記錄的生效終止日期，並添加變更後的資料。------------------------------------------------------------------------------------------ 資料幫浦(Extract)資料幫浦是從資料來源擷取所需資料的過程。資料幫浦的主要工作有： 資料範圍過濾，完全抽取源表所有記錄或按指定日期進行增量抽取 抽取欄位過濾，全部抽取源表所有欄位或包括過濾掉不需要的來源資料欄位 抽取條件過濾，如過濾到指定條件的記錄 資料排序，如按照抽取的指定欄位進行排序資料幫浦可以採用 PULL和PUSH兩種方式。PUSH就是指由源系統按照雙方定義的資料格式，主動將符合要求的資料幫浦出來，形成介面資料表或資料檢視供ETL系統使用。PULL則是由ETL程式直接存取資料來源來擷取資料的方式。 資料變換(Convert)任務是逐條記錄的檢查資料，將每個欄位轉換為遵循資料倉儲標準的資料格式，即對資料類型和資料格式進行轉換，並對空欄位賦予適當的預設值，形成規整的資料結構，對於不符合要求的資料，寫入拒絕檔案（Reject檔案）中。資料變換主要的工作有： 格式變換，如所有日期格式統一為yyyy-mm-dd； 賦預設值，在資料倉儲中定義取值不為空白的欄位在來源資料對應的欄位可能存在沒有取值的記錄，這時根據業務需要，可能有兩種處理辦法，一是將該記錄寫入到Reject檔案中，由業務部門根據Reject檔案檢查並修補來源資料，另一種是在Convert階段直接賦一個預設值； 類型變換, 如將源系統的Number類型轉為Varchar2類型等 代碼轉換，某些欄位經過代碼升級以後，將老的代碼轉為新的代碼等。 數值轉換，如數值單位由萬元轉為元等。 去除空格，去除字元類型的資料中的前後空格資料轉換(Transform)    按照目標表的資料結構，對一個或多個來源資料的欄位進行翻譯、匹配、彙總等操作得到目標資料的欄位。    資料轉換主要包括格式和欄位合并與拆分、資料翻譯、資料匹配、資料彙總以及其它複雜計算等。 欄位合并與拆分欄位合并是指來源資料的多個欄位合并為目標資料的一個欄位。欄位拆分是指將來源資料中一個表的一個欄位拆分為目標資料的多個欄位。 賦預設值對於資料庫中有的欄位，在源系統中並沒有相對應的源欄位，這時根據模型的設計，可能需要預設賦一個值。與資料變換階段的賦預設值功能相比，這裡可能涉及更複雜的資料修正規則。 資料排序(Sort)轉換程式有時需要對於兩個或多個CIF檔案合并，在合并之前需要將CIF檔案按所要求的鍵值排好序，這樣可以加快合并的速度，排序的過程在Transform之前進行。 資料尋找(Lookup)將源系統中一些表示狀態、類型等的代碼直接翻譯為其所表達的意思，或反之。資料翻譯需要用到參考表（Reference Table），資料參考表一般是字典表或根據來源資料與目標資料的定義手工產生，如果資料翻譯時在參考表中找不到對應的對照，根據商務規則，需要將對應的記錄Reject出來或賦預設值。 資料合併(Merge)按一定條件（一般是key值相等）對資料進行合并，找出描述同一對象的分布在不同資料表中的記錄，並把這些記錄聯絡起來。資料合併其實是資料尋找的一種特殊情況，主要用於資料量特別大的情況，資料合併在實現方式上一般先對要合并的兩個表分別排序(Sort)，然後順序對兩個表的記錄進行匹配合并，這樣可以大大加快處理的速度。 資料彙總(Aggregate)對資料按照不同分組進行匯總等統計計算，一般是用於匯總表的計算，主要的彙總種類有：                                 求和                                 求平均值                                 求記錄數                                 求最小值                                 求最大值原則上，ETL只處理規律而重複性大的資料彙總，如匯總、取平均值、找最大、最小值等，而不用於複雜計算，以減少開發成本和系統負載。對於不規律而且複雜的計算，應該由源系統端將資料計算好或在資料倉儲端開發專門的計算程式（如預存程序）在ETL載入完成以後調用 檔案比較(File Compare)對應於四種ETL模式，資料轉換為PLF檔案後需要進行不同的處理，在Type1、Type2和Type3模式下，產生LDF檔案可以直接由資料載入過程載入到資料倉儲中，而Type4則由於存在有效日期，需要將當日的snapshot資料（PLF檔案）與曆史資料鏡像（PLF檔案）進行比較，找出需要添加和需要更新的記錄，然後產生真正可以向資料庫載入的LDF檔案，再由資料載入過程將PLF檔案載入到資料倉儲中。 代理鍵值(Surrogate Key)分配對於資料倉儲中設計的代理鍵，代理鍵值的分配一般有兩種方法，一種是在資料庫中通過資料庫的功能將該欄位設為自動增加類型，一種是由ETL過程來分配鍵值，代理鍵值一般為數值型，並且必須保證鍵值分配不重複。在本項目中採取的是後者。 緩慢變化增量擷取(Slow Change Capture)對於含有生效日期、到期日等欄位的緩慢變化維度資料表（拉鏈表），如果源端無法提供增量的資訊（包括通過時間戳記擷取），則ETL需要將當日的快照資料產生PLF檔案，再與上一日的資料鏡像（從目標資料快照區中擷取）進行比較，找出需要添加和需要更新的記錄，然後再產生真正可以向資料庫載入的LDF檔案。 行列轉換(Pivot)根據模型的一些特殊要求，需要將源系統的橫錶轉成縱表或將縱錶轉成橫表，如源表將12個月設計為12個欄位，而目標表需要用一個月份欄位替換，而需要將源表的一條記錄按月份對應轉成12條記錄等。 RI檢查(RI Check)對於存在RI關係的表，進行RI的檢查，將有RI問題的資料拒絕出來。由於本項目中使用的Greenplum資料庫無法執行外鍵約束，使用ETL檢查RI是確保資料品質的關鍵點。 其它複雜計算在資料庫中定義的某些欄位需要按照商務規則進行複雜計算才能得到，主要有兩類：1．主要針對一些在資料來源中找不到直接對應的欄位，需要在ETL過程中通過相關欄位計算才能得出的欄位；2．原則上複雜的計算並不在ETL中完成，對於一定需要由ETL來完成的複雜計算欄位，採取在ETL載入時該欄位先留空，在載入完成以後調用的預存程序來計算，但為了管理與調度的統一，可以在ETL 調度工具中調用預存程序以統一調度。資料載入(Load)    經過資料轉換產生的PLF檔案的結構與資料庫資料表的結構完全一致，可以直接通過資料載入工具，以Bulk Load的方式載入到資料倉儲中。資料載入工作將分為3步進行。 預先載入（Pre-Load）在真正進行資料載入之前根據實際要載入的表的情況，主要是針對明細及大事實表，基於效能考慮，還可能需要完成以下準備工作：                                  刪除資料倉儲中資料表的索引。                                  刪除主鍵。 載入（Load）Load主要完成將PLF檔案的資料載入到資料庫的表中，需要用到的載入方式有三種：                             Insert：只需要將PLF檔案所有資料完全Insert到目標表中。                             Upsert：需要對目標表同時做Update及Insert操作，根據主鍵，對於已有的記錄進行Update操作，對於不存在的記錄做Insert的操作，對於資料量大的表，由於此操作的效率非常低，可以採用先將PLF檔案分割為Delete檔案及Insert檔案，然後先將Delete檔案中的記錄根據主鍵對應從資料倉儲中刪除，然後再從Insert檔案中將所有記錄全部Insert到目標表中。                             Refresh：即將目標表的資料完全更新，一般的做法是先Truncate目標表的資料，然後再完全Insert要載入的記錄 後載入（Post-Load）Post-Load階段主要完成在資料載入完成以後的相關的環境整理工作，主要包括如下一些工作：                 重建索引：如果在Pre-Load階段做了刪除資料表索引動作，則在Post-Load階段需要重建。                 重新建立主鍵：如果在Pre-Load階段做了刪除主鍵動作，則在Post-Load階段需要重建。                 檔案清理：刪除不需要的臨時檔案，如空拒絕檔案等                 產生載入後資料鍵檔案：資料載入完成以後，如果後續有ETL過程需要根據該表進行RI檢查，則需要將該表的主鍵抽取出單獨的鍵檔案。                 後抽取資料檔案：如果所載入的資料表後續被其它表載入時大量尋找（Lookup），可以考慮抽取出檔案。ETL作業依賴和進程調度ETL作業之間存在很多依賴，關係到相關資料表的載入順序，本設計從兩個方面體現ETL作業的依賴關係：一是每個工作群組完成以後應產生與作業名稱相對應的訊息檔案，對於後續需要依賴該作業完成的工作群組，首先應等待該訊息檔案的出現再繼續。二是將一組在同一時間開始運行並具有依賴關係的工作群組合在一起，開發成網狀關係的序列作業（Sequence Job）以供ETL的進程調度用ETL作業進程調度的功能比較單純，就是在規定的時刻啟動程式，並記錄系統運行情況和運行結果。 不同資料表的更新周期不同，因此，進程調度需要能夠支援日周月等多種不同的啟動周期，並通過設定啟動時間來確定每個任務在何時啟動運行。        只有日常資料載入才需要考慮進程調度的問題。而對於初始資料及曆史資料的載入，由於是一次性的工作，將採取手工啟動載入的方式，所以無需制定對初始資料及曆史資料載入制度化的進程調度。        在ETL程式的抽取過程運行之前一定要保證其抽取的來源資料已經準備就緒，本項目使用Oracle Golden Gate在資料緩衝區保留一份源系統源表的即時副本，在指定的時間啟動夜間批量處理視窗時統一抓取快照，載入快照區（在第3章詳述）。手工上傳資料在上傳當日接受資料品質稽查，並載入過渡表。但此類資料往下遊資料集市的流轉只在夜間跑批視窗與系統來來源資料合并進行。即當日上傳的手工資料原則上只在次日從前端應用中體現。
邏輯部署部分
大體看來分為三層 使用者層 ---使用者接入 三種方式 移動端、web端、系統使用者應用程式層 ---負載平衡器 ：  支撐BI相關服務要求的均衡應用負載，提高應用的可用性。負載平衡裝置安裝於兩台或多台Server之前                               ，                                  它將請求交給負載最輕的伺服器                資料應用伺服器 ：整個BI應用架構的資料群組裝部分，支撐包含駕駛艙、指標查詢、主題分析、戰略管理等高效的                                             安全的資料應用服務，應用伺服器要滿足高可用和效能平行擴充的要求。                                              德邦採用叢集的方式進行部署                Data Integration伺服器、支撐資料幫浦、資料轉換、資料稽核，伺服器要滿足高可用和效能平行擴充的要求                系統管理伺服器  支撐系統監控，預警管理和使用者權限的管理                Web伺服器      支撐Web網頁的訪問服務。靜態內容，包括HTML，JavaScript，IMG和JPG圖片等，                                         伺服器要滿足高可用的要求     資料資源層    包含緩衝區、TDR、資料倉儲與集市、檔案庫、日誌庫以及備份庫
系統整體模組流程
模組依賴關係 

BI 主要環節 ETL 相關知識