BI主要環節ETL相關知識

BI架構-BI主要環節ETL相關知識 主要功能 ：將源系統的數據加載到數據倉庫 及數據集市層中；主要問題體現： 復雜的源數據環境，包括繁多的數據種類、巨大的加載數據量、錯綜復雜的數據關系和參差不齊的數據質量常見術語 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應用架構的數據組裝部分，支撐包含駕駛艙、指標查詢、主題分析、戰略管理等高效的 安全的數據應用服務，應用服務器要滿足高可用和性能平行擴展的要求。 德邦采用集群的方式進行部署 數據集成服務器、支撐數據抽取、數據轉換、數據稽核，服務器要滿足高可用和性能平行擴展的要求 系統管理服務器 支撐系統監控，預警管理和用戶權限的管理 Web服務器 支撐Web網頁的訪問服務。靜態內容，包括HTML，JavaScript，IMG和JPG圖片等， 服務器要滿足高可用的要求 數據資源層 包含緩沖區、TDR、數據倉庫與集市、文件庫、日志庫以及備份庫
系統整體模塊流程

---

模塊依賴關系

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com