數(shù)據(jù)倉庫建模與ETL實踐技巧
?
??? 在上一期的專欄文章中，我們曾經(jīng)提到：數(shù)據(jù)分析系統(tǒng)的總體架構(gòu)分為四個部分 —— 源系統(tǒng)、數(shù)據(jù)倉庫、多維數(shù)據(jù)庫、客戶端。其中，數(shù)據(jù)倉庫(DW)起到了數(shù)據(jù)大集中的作用。通過數(shù)據(jù)抽取，把數(shù)據(jù)從源系統(tǒng)源源不斷地抽取出來，可能每天一次，或者每 3 個小時一次(當(dāng)然是自動的)。這個過程，我們稱之為 ETL 過程。那么，今天，我們就來談一談：如何搭建數(shù)據(jù)倉庫，在這個過程中都應(yīng)該遵循哪些方法和原則；然后介紹一些項目實踐中的技巧。
?
?
一、數(shù)據(jù)倉庫的架構(gòu)
?
??? 數(shù)據(jù)倉庫(Data Warehouse DW)是為了便于多維分析和多角度展現(xiàn)而將數(shù)據(jù)按特定的模式進行存儲所建立起來的關(guān)系型數(shù)據(jù)庫，它的數(shù)據(jù)基于 OLTP 源系統(tǒng)。數(shù)據(jù)倉庫中的數(shù)據(jù)是細節(jié)的、集成的、面向主題的，以 OLAP 系統(tǒng)的分析需求為目的。
?
??? 數(shù)據(jù)倉庫的架構(gòu)模型包括了星型架構(gòu) 與雪花型架構(gòu) 兩種模式。星型架構(gòu)的中間為事實表，四周為維度表，類似星星；而相比較而言，雪花型架構(gòu)的中間為事實表，兩邊的維度表可以再有其關(guān)聯(lián)子表，從而表達了清晰的維度層次關(guān)系。
?
??? 從 OLAP 系統(tǒng)的分析需求和 ETL 的處理效率兩方面來考慮：星型結(jié)構(gòu)聚合快，分析效率高；而雪花型結(jié)構(gòu)明確，便于與 OLTP 系統(tǒng)交互。因此，在實際項目中，我們將綜合運用星型架構(gòu)與雪花型架構(gòu)來設(shè)計數(shù)據(jù)倉庫。
?
??? 那么，下面我們就來看一看，構(gòu)建企業(yè)級數(shù)據(jù)倉庫的流程。
?
?
二、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法
?
(一)、確定主題
?
??? 即確定數(shù)據(jù)分析或前端展現(xiàn)的主題。例如：我們希望分析某年某月某一地區(qū)的啤酒銷售情況，這就是一個主題。主題要體現(xiàn)出某一方面的各分析角度(維度)和統(tǒng)計數(shù)值型數(shù)據(jù)(量度)之間的關(guān)系，確定主題時要綜合考慮。
?
??? 我們可以形象的將一個主題想象為一顆星星：統(tǒng)計數(shù)值型數(shù)據(jù)(量度)存在于星星中間的事實表；分析角度(維度)是星星的各個角；我們將通過維度的組合，來考察量度。那么，“某年某月某一地區(qū)的啤酒銷售情況”這樣一個主題，就要求我們通過時間和地區(qū)兩個維度的組合，來考察銷售情況這個量度。從而，不同的主題來源于數(shù)據(jù)倉庫中的不同子集，我們可以稱之為數(shù)據(jù)集市。數(shù)據(jù)集市體現(xiàn)了數(shù)據(jù)倉庫某一方面的信息，多個數(shù)據(jù)集市構(gòu)成了數(shù)據(jù)倉庫。
?
(二)、確定量度
?
??? 在確定了主題以后，我們將考慮要分析的技術(shù)指標(biāo)，諸如年銷售額之類。它們一般為數(shù)值型數(shù)據(jù)。我們或者將該數(shù)據(jù)匯總，或者將該數(shù)據(jù)取次數(shù)、獨立次數(shù)或取最大最小值等，這樣的數(shù)據(jù)稱為量度。
?
??? 量度是要統(tǒng)計的指標(biāo)，必須事先選擇恰當(dāng)，基于不同的量度可以進行復(fù)雜關(guān)鍵性能指標(biāo)(KPI)等的設(shè)計和計算。
?
(三)、確定事實數(shù)據(jù)粒度
?
??? 在確定了量度之后，我們要考慮到該量度的匯總情況和不同維度下量度的聚合情況?？紤]到量度的聚合程度不同，我們將采用“最小粒度原則”，即將量度的粒度設(shè)置到最小。
?
??? 例如：假設(shè)目前的數(shù)據(jù)最小記錄到秒，即數(shù)據(jù)庫中記錄了每一秒的交易額。那么，如果我們可以確認，在將來的分析需求中，時間只需要精確到天就可以的話，我們就可以在 ETL 處理過程中，按天來匯總數(shù)據(jù)，此時，數(shù)據(jù)倉庫中量度的粒度就是“天”；反過來，如果我們不能確認將來的分析需求在時間上是否需要精確到秒，那么，我們就需要遵循“最小粒度原則”，在數(shù)據(jù)倉庫的事實表中保留每一秒的數(shù)據(jù)，以便日后對“秒”進行分析。
?
??? 在采用“最小粒度原則”的同時，我們不必擔(dān)心海量數(shù)據(jù)所帶來的匯總分析效率問題，因為在后續(xù)建立多維分析模型(CUBE)的時候，我們會對數(shù)據(jù)提前進行匯總，從而保障產(chǎn)生分析結(jié)果的效率。關(guān)于建立多維分析模型(CUBE)的相關(guān)問題，我們將在下期欄目中予以闡述。
?
(四)、確定維度
?
??? 維度是指分析的各個角度。例如我們希望按照時間，或者按照地區(qū)，或者按照產(chǎn)品進行分析，那么這里的時間、地區(qū)、產(chǎn)品就是相應(yīng)的維度。基于不同的維度，我們可以看到各量度的匯總情況，也可以基于所有的維度進行交叉分析。
?
??? 這里我們首先要確定維度的層次(Hierarchy)和級別(Level) 。我們在時間維度上，按照“年 - 季度 - 月”形成了一個層次，其中“年”、“季度”、“月”成為了這個層次的 3 個級別；同理，當(dāng)我們建立產(chǎn)品維度時，我們可以將“產(chǎn)品大類 - 產(chǎn)品子類 - 產(chǎn)品”劃為一個層次，其中包含“產(chǎn)品大類”、“產(chǎn)品子類”、“產(chǎn)品”三個級別。
??? 那么，我們分析中所用到的這些維度，在數(shù)據(jù)倉庫中的存在形式是怎樣的呢？
?
??? 我們可以將 3 個級別設(shè)置成一張數(shù)據(jù)表中的 3 個字段，比如時間維度；我們也可以使用三張表，分別保存產(chǎn)品大類、產(chǎn)品子類、產(chǎn)品三部分數(shù)據(jù)，比如產(chǎn)品維度
?
??? 另外，值得一提的是，我們在建立維度表時要充分使用代理鍵。代理鍵是數(shù)值型的 ID 號碼，它唯一標(biāo)識了每一維度成員。更重要的是，在聚合時，數(shù)值型字段的匹配和比較， JOIN 效率高，便于聚合。同時，代理鍵對緩慢變化維度有著重要的意義，在原數(shù)據(jù)主鍵相同的情況下，它起到了對新數(shù)據(jù)與歷史數(shù)據(jù)的標(biāo)識作用。
?
??? 在此，我們不妨談一談維度表隨時間變化的問題，這是我們經(jīng)常會遇到的情況，我們稱其為緩慢變化維度。
?
??? 比如我們增加了新的產(chǎn)品，或者產(chǎn)品的 ID 號碼修改了，或者產(chǎn)品增加了一個新的屬性，此時，維度表就會被修改或者增加新的記錄行。這樣，我們在 ETL 的過程中，就要考慮到緩慢變化維度的處理。對于緩慢變化維度，有三種情況：
?
??? 1 、緩慢變化維度第一種類型：歷史數(shù)據(jù)需要修改。這種情況下，我們使用 UPDATE 方法來修改維度表中的數(shù)據(jù)。例如：產(chǎn)品的 ID 號碼為 123 ，后來發(fā)現(xiàn) ID 號碼錯了，需要改寫成 456 ，那么，我們就在 ETL 處理時，直接修改維度表中原來的 ID 號碼為 456 。
?
??? 2 、緩慢變化維度第二種類型：歷史數(shù)據(jù)保留，新增數(shù)據(jù)也要保留。這時，要將原數(shù)據(jù)更新，將新數(shù)據(jù)插入，我們使用 UPDATE / INSERT 。比如：某一員工 2005 年在 A 部門， 2006 年時他調(diào)到了 B 部門。那么在統(tǒng)計 2005 年的數(shù)據(jù)時就應(yīng)該將該員工定位到 A 部門；而在統(tǒng)計 2006 年數(shù)據(jù)時就應(yīng)該定位到 B 部門，然后再有新的數(shù)據(jù)插入時，將按照新部門（ B 部門）進行處理，這樣我們的做法是將該維度成員列表加入標(biāo)識列，將歷史的數(shù)據(jù)標(biāo)識為“過期”，將目前的數(shù)據(jù)標(biāo)識為“當(dāng)前的”。另一種方法是將該維度打上時間戳，即將歷史數(shù)據(jù)生效的時間段作為它的一個屬性，在與原始表匹配生成事實表時將按照時間段進行關(guān)聯(lián)，這種方法的好處是該維度成員生效時間明確。
?
??? 3 、緩慢變化維度第三種類型：新增數(shù)據(jù)維度成員改變了屬性。例如：某一維度成員新加入了一列，該列在歷史數(shù)據(jù)中不能基于它瀏覽，而在目前數(shù)據(jù)和將來數(shù)據(jù)中可以按照它瀏覽，那么此時我們需要改變維度表屬性，即加入新的字段列。那么，我們將使用存儲過程或程序生成新的維度屬性，在后續(xù)的數(shù)據(jù)中將基于新的屬性進行查看。
?
(五)、創(chuàng)建事實表
?
??? 在確定好事實數(shù)據(jù)和維度后，我們將考慮加載事實表。
?
??? 在公司的大量數(shù)據(jù)堆積如山時，我們想看看里面究竟是什么，結(jié)果發(fā)現(xiàn)里面是一筆筆生產(chǎn)記錄，一筆筆交易記錄 … 那么這些記錄是我們將要建立的事實表的原始數(shù)據(jù)，即關(guān)于某一主題的事實記錄表。
?
??? 我們的做法是將原始表與維度表進行關(guān)聯(lián)，生成事實表 。注意在關(guān)聯(lián)時有為空的數(shù)據(jù)時(數(shù)據(jù)源臟)，需要使用外連接，連接后我們將各維度的代理鍵取出放于事實表中，事實表除了各維度代理鍵外，還有各量度數(shù)據(jù)，這將來自原始表，事實表中將存在維度代理鍵和各量度，而不應(yīng)該存在描述性信息，即符合“瘦高原則”，即要求事實表數(shù)據(jù)條數(shù)盡量多(粒度最小)，而描述性信息盡量少。
??? 如果考慮到擴展，可以將事實表加一唯一標(biāo)識列，以為了以后擴展將該事實作為雪花型維度，不過不需要時一般建議不用這樣做。
?
??? 事實數(shù)據(jù)表是數(shù)據(jù)倉庫的核心，需要精心維護，在 JOIN 后將得到事實數(shù)據(jù)表，一般記錄條數(shù)都比較大，我們需要為其設(shè)置復(fù)合主鍵和索引，以實現(xiàn)數(shù)據(jù)的完整性和基于數(shù)據(jù)倉庫的查詢性能優(yōu)化。事實數(shù)據(jù)表與維度表一起放于數(shù)據(jù)倉庫中，如果前端需要連接數(shù)據(jù)倉庫進行查詢，我們還需要建立一些相關(guān)的中間匯總表或物化視圖，以方便查詢。
?
?
三、什么是 ETL
?
??? 在數(shù)據(jù)倉庫的構(gòu)建中， ETL 貫穿于項目始終，它是整個數(shù)據(jù)倉庫的生命線，包括了數(shù)據(jù)清洗、整合、轉(zhuǎn)換、加載等各個過程。如果說數(shù)據(jù)倉庫是一座大廈，那么 ETL 就是大廈的根基。 ETL 抽取整合數(shù)據(jù)的好壞直接影響到最終的結(jié)果展現(xiàn)。所以 ETL 在整個數(shù)據(jù)倉庫項目中起著十分關(guān)鍵的作用，必須擺到十分重要的位置。
?
??? ETL 是數(shù)據(jù)抽取(Extract)、轉(zhuǎn)換(Transform)、加載(Load)的簡寫，它是指：將 OLTP 系統(tǒng)中的數(shù)據(jù)抽取出來，并將不同數(shù)據(jù)源的數(shù)據(jù)進行轉(zhuǎn)換和整合，得出一致性的數(shù)據(jù)，然后加載到數(shù)據(jù)倉庫中。
??? 那么，在這一轉(zhuǎn)換過程中，我們就完成了對數(shù)據(jù)格式的更正、對數(shù)據(jù)字段的合并、以及新增指標(biāo)的計算三項操作。類似地，我們也可以根據(jù)其他需求，完善數(shù)據(jù)倉庫中的數(shù)據(jù)。
?
??? 簡而言之，通過 ETL ，我們可以基于源系統(tǒng)中的數(shù)據(jù)來生成數(shù)據(jù)倉庫。 ETL 為我們搭建了 OLTP 系統(tǒng)和 OLAP 系統(tǒng)之間的橋梁。
?
?
四、項目實踐技巧
?
(一)、準(zhǔn)備區(qū)的運用
?
???? 在構(gòu)建數(shù)據(jù)倉庫時，如果數(shù)據(jù)源位于一臺服務(wù)器上，數(shù)據(jù)倉庫在另一臺服務(wù)器端，考慮到數(shù)據(jù)源 Server 端訪問頻繁，并且數(shù)據(jù)量大，需要不斷更新，所以可以建立準(zhǔn)備區(qū)數(shù)據(jù)庫 。先將數(shù)據(jù)抽取到準(zhǔn)備區(qū)中，然后基于準(zhǔn)備區(qū)中的數(shù)據(jù)進行處理，這樣處理的好處是防止了在原 OLTP 系統(tǒng)中頻繁訪問，進行數(shù)據(jù)運算或排序等操作。
??? 例如我們可以按照天將數(shù)據(jù)抽取到準(zhǔn)備區(qū)中，基于數(shù)據(jù)準(zhǔn)備區(qū)，我們將進行數(shù)據(jù)的轉(zhuǎn)換、整合、將不同數(shù)據(jù)源的數(shù)據(jù)進行一致性處理。數(shù)據(jù)準(zhǔn)備區(qū)中將存在原始抽取表、轉(zhuǎn)換中間表和臨時表以及 ETL 日志表等。
?
(二)、時間戳的運用
?
??? 時間維度對于某一事實主題來說十分重要，因為不同的時間有不同的統(tǒng)計數(shù)據(jù)信息，那么按照時間記錄的信息將發(fā)揮很重要的作用。在 ETL 中，時間戳有其特殊的作用，在上面提到的緩慢變化維度中，我們可以使用時間戳標(biāo)識維度成員；在記錄數(shù)據(jù)庫和數(shù)據(jù)倉庫的操作時，我們也將使用時間戳標(biāo)識信息。例如：在進行數(shù)據(jù)抽取時，我們將按照時間戳對 OLTP 系統(tǒng)中的數(shù)據(jù)進行抽取，比如在午夜 00:00 取前一天的數(shù)據(jù)，我們將按照 OLTP 系統(tǒng)中的時間戳取 GETDATE 到 GETDATE 減一天，這樣得到前一天數(shù)據(jù)。
?
(三)、日志表的運用
?
??? 在對數(shù)據(jù)進行處理時，難免會發(fā)生數(shù)據(jù)處理錯誤，產(chǎn)生出錯信息，那么我們?nèi)绾潍@得出錯信息并及時修正呢 ? 方法是我們使用一張或多張 Log 日志表，將出錯信息記錄下來，在日志表中我們將記錄每次抽取的條數(shù)、處理成功的條數(shù)、處理失敗的條數(shù)、處理失敗的數(shù)據(jù)、處理時間等等。這樣，當(dāng)數(shù)據(jù)發(fā)生錯誤時，我們很容易發(fā)現(xiàn)問題所在，然后對出錯的數(shù)據(jù)進行修正或重新處理。
?
(四)、使用調(diào)度
?
??? 在對數(shù)據(jù)倉庫進行增量更新時必須使用調(diào)度，即對事實數(shù)據(jù)表進行增量更新處理。在使用調(diào)度前要考慮到事實數(shù)據(jù)量，確定需要多長時間更新一次。比如希望按天進行查看，那么我們最好按天進行抽取，如果數(shù)據(jù)量不大，可以按照月或半年對數(shù)據(jù)進行更新。如果有緩慢變化維度情況，調(diào)度時需要考慮到維度表更新情況，在更新事實數(shù)據(jù)表之前要先更新維度表。
??? 調(diào)度是數(shù)據(jù)倉庫的關(guān)鍵環(huán)節(jié)，要考慮縝密。在 ETL 的流程搭建好后，要定期對其運行，所以調(diào)度是執(zhí)行 ETL 流程的關(guān)鍵步驟。每一次調(diào)度除了寫入 Log 日志表的數(shù)據(jù)處理信息外，還要使用發(fā)送 Email 或報警服務(wù)等，這樣也方便的技術(shù)人員對 ETL 流程的把握，增強了安全性和數(shù)據(jù)處理的準(zhǔn)確性。
?
?
五、總結(jié)
?
??? 構(gòu)建企業(yè)級數(shù)據(jù)倉庫需要簡單的五步，掌握了這五步的方法，我們可以構(gòu)建一個強大的數(shù)據(jù)倉庫。然而，每一步都有很深的內(nèi)容需要研究與挖掘，尤其在實際項目中，我們要綜合考慮。例如：如果數(shù)據(jù)源的臟數(shù)據(jù)很多，在搭建數(shù)據(jù)倉庫之前我們首先要進行數(shù)據(jù)清洗，以剔除掉不需要的信息和臟數(shù)據(jù)。
?
??? ETL 是 OLTP 系統(tǒng)和 OLAP 系統(tǒng)之間的橋梁，是數(shù)據(jù)從源系統(tǒng)流入數(shù)據(jù)倉庫的通道。在數(shù)據(jù)倉庫的項目實施中，它關(guān)系到整個項目的數(shù)據(jù)質(zhì)量，所以馬虎不得，必須將其擺到重要位置，將數(shù)據(jù)倉庫這一大廈的根基筑牢！?
?
?