文 | 穆晨

?

▍創(chuàng)建數(shù)據(jù)倉庫

數(shù)據(jù)倉庫的創(chuàng)建方法和數(shù)據(jù)庫類似，也是通過編寫DDL語句來實現(xiàn)。在過去，數(shù)據(jù)倉庫系統(tǒng)大都建立在RDBMS上，因為維度建模其實也可以看做是關(guān)系建模的一種。但如今隨著開源分布式數(shù)據(jù)倉庫工具如Hadoop Hive，Spark SQL的興起，開發(fā)人員往往將建模和實現(xiàn)分離。使用專門的建模軟件進行ER建模、關(guān)系建模、維度建模，而具體實現(xiàn)則在Hive/Spark SQL下進行。沒辦法，誰讓這些開源工具沒有提供自帶的可視化建模插件呢。

話說現(xiàn)在的開源分布式工具都是"散兵作戰(zhàn)"，完成一個大的項目要組合N個工具，沒有一個統(tǒng)一的開發(fā)平臺。還有就是可視化效果比較差，界面很難看或者沒有界面。個人建議在資金足夠的情況下盡量使用商用大數(shù)據(jù)平臺來開發(fā)，雖然這些商用產(chǎn)品廣告打得多少有點夸張，但是它們的易用性做的是真好。這里筆者推薦帆軟的BI平臺，附鏈接：https://www.finebi.com/。

?

▍ETL：抽取、轉(zhuǎn)換、加載

在本系列第一篇中，曾大致介紹了該環(huán)節(jié)，它很可能是數(shù)據(jù)倉庫開發(fā)中最耗時的階段。

ETL工作的實質(zhì)就是從各個數(shù)據(jù)源提取數(shù)據(jù)，對數(shù)據(jù)進行轉(zhuǎn)換，并最終加載填充數(shù)據(jù)到數(shù)據(jù)倉庫維度建模后的表中。只有當這些維度/事實表被填充好，ETL工作才算完成。接下來分別對抽取，轉(zhuǎn)換，加載這三個環(huán)節(jié)進行講解：

1. 抽取(Extract)

數(shù)據(jù)倉庫是面向分析的，而操作型數(shù)據(jù)庫是面向應(yīng)用的。顯然，并不是所有用于支撐業(yè)務(wù)系統(tǒng)的數(shù)據(jù)都有拿來分析的必要。因此，該階段主要是根據(jù)數(shù)據(jù)倉庫主題、主題域確定需要從應(yīng)用數(shù)據(jù)庫中提取的數(shù)。

具體開發(fā)過程中，開發(fā)人員必然經(jīng)常發(fā)現(xiàn)某些ETL步驟和數(shù)據(jù)倉庫建模后的表描述不符。這時候就要重新核對、設(shè)計需求，重新進行ETL。正如數(shù)據(jù)庫系列的這篇中講到的，任何涉及到需求的變動，都需要重頭開始并更新需求文檔。

2. 轉(zhuǎn)換(Transform)

轉(zhuǎn)換步驟主要是指對提取好了的數(shù)據(jù)的結(jié)構(gòu)進行轉(zhuǎn)換，以滿足目標數(shù)據(jù)倉庫模型的過程。此外，轉(zhuǎn)換過程也負責數(shù)據(jù)質(zhì)量工作，這部分也被稱為數(shù)據(jù)清洗(data cleaning)。數(shù)據(jù)質(zhì)量涵蓋的內(nèi)容可具體參考這里。

3. 加載(Load)

加載過程將已經(jīng)提取好了，轉(zhuǎn)換后保證了數(shù)據(jù)質(zhì)量的數(shù)據(jù)加載到目標數(shù)據(jù)倉庫。加載可分為兩種L：首次加載(first load)和刷新加載(refresh load)。其中，首次加載會涉及到大量數(shù)據(jù)，而刷新加載則屬于一種微批量式的加載。

多說一句，如今隨著各種分布式、云計算工具的興起，ETL實則變成了ELT。就是業(yè)務(wù)系統(tǒng)自身不會做轉(zhuǎn)換工作，而是在簡單的清洗后將數(shù)據(jù)導入分布式平臺，讓平臺統(tǒng)一進行清洗轉(zhuǎn)換等工作。這樣做能充分利用平臺的分布式特性，同時使業(yè)務(wù)系統(tǒng)更專注于業(yè)務(wù)本身。

?

▍OLAP/BI工具

數(shù)據(jù)倉庫建設(shè)好以后，用戶就可以編寫SQL語句對其進行訪問并對其中數(shù)據(jù)進行分析。但每次查詢都要編寫SQL語句的話，未免太麻煩，而且對維度建模數(shù)據(jù)進行分析的SQL代碼套路比較固定。于是，便有了OLAP工具，它專用于維度建模數(shù)據(jù)的分析。而BI工具則是能夠?qū)LAP的結(jié)果以圖表的方式展現(xiàn)出來，它和OLAP通常出現(xiàn)在一起。(注：本文所指的OLAP工具均指代這兩者。)

在規(guī)范化數(shù)據(jù)倉庫中OLAP工具和數(shù)據(jù)倉庫的關(guān)系大致是這樣的：?

?這種情況下，OLAP不允許訪問中心數(shù)據(jù)庫。一方面中心數(shù)據(jù)庫是采取規(guī)范化建模的，而OLAP只支持對維度建模數(shù)據(jù)的分析；另一方面規(guī)范化數(shù)據(jù)倉庫的中心數(shù)據(jù)庫本身就不允許上層開發(fā)人員訪問。而在維度建模數(shù)據(jù)倉庫中，OLAP/BI工具和數(shù)據(jù)倉庫的關(guān)系則是這樣的：

在維度建模數(shù)據(jù)倉庫中，OLAP不但可以從數(shù)據(jù)倉庫中直接取數(shù)進行分析，還能對架構(gòu)在其上的數(shù)據(jù)集市群做同樣工作。對該部分講解感到模糊的讀者請重看上篇中三種數(shù)據(jù)倉庫建模體系部分。

?

▍數(shù)據(jù)立方體(Data Cube)

在介紹OLAP工具的具體使用前，先要了解這個概念：數(shù)據(jù)立方體(Data Cube)。

很多年前，當我們要手工從一堆數(shù)據(jù)中提取信息時，我們會分析一堆數(shù)據(jù)報告。通常這些數(shù)據(jù)報告采用二維表示，是行與列組成的二維表格。但在真實世界里我們分析數(shù)據(jù)的角度很可能有多個，數(shù)據(jù)立方體可以理解為就是維度擴展后的二維表格。下圖展示了一個三維數(shù)據(jù)立方體：

?

盡管這個例子是三維的，但更多時候數(shù)據(jù)立方體是N維的。它的實現(xiàn)有兩種方式，本文后面部分會講到。其中上一篇講到的星形模式就是其中一種，該模式其實是一種連接關(guān)系表與數(shù)據(jù)立方體的橋梁。但對于大多數(shù)純OLAP使用者來講，數(shù)據(jù)分析的對象就是這個邏輯概念上的數(shù)據(jù)立方體，其具體實現(xiàn)不用深究。對于這些OLAP工具的使用者來講，基本用法是首先配置好維表、事實表，然后在每次查詢的時候告訴OLAP需要展示的維度和事實字段和操作類型即可。

下面介紹數(shù)據(jù)立方體中最常見的五大操作：切片，切塊，旋轉(zhuǎn)，上卷，下鉆。

1. 切片和切塊(Slice and Dice)

在數(shù)據(jù)立方體的某一維度上選定一個維成員的操作叫切片，而對兩個或多個維執(zhí)行選擇則叫做切塊。下圖邏輯上展示了切片和切塊操作：

這兩種操作的SQL模擬語句如下，主要是對WHERE語句做工作：

# 切片

SELECT Locates.地區(qū), Products.分類, SUM(數(shù)量)FROM Sales, Dates, Products, LocatesWHERE Dates.季度 = 2 AND Sales.Date_key = Dates.Date_key AND Sales.Locate_key = Locates.Locate_key AND Sales.Product_key = Products.Product_keyGROUP BY Locates.地區(qū), Products.分類 # 切塊SELECT Locates.地區(qū), Products.分類, SUM(數(shù)量)FROM Sales, Dates, Products, LocatesWHERE (Dates.季度 = 2 OR Dates.季度 = 3) AND (Locates.地區(qū) = '江蘇' OR Locates.地區(qū) = '上海') AND Sales.Date_key = Dates.Date_key AND Sales.Locate_key = Locates.Locate_key AND Sales.Product_key = Products.Product_keyGROUP BY Dates.季度, Locates.地區(qū), Products.分類

2. 旋轉(zhuǎn)(Pivot)

旋轉(zhuǎn)就是指改變報表或頁面的展示方向。對于使用者來說，就是個視圖操作，而從SQL模擬語句的角度來說，就是改變SELECT后面字段的順序而已。下圖邏輯上展示了旋轉(zhuǎn)操作：

3. 上卷和下鉆

上卷可以理解為"無視"某些維度；下鉆則是指將某些維度進行細分。下圖邏輯上展示了上卷和下鉆操作：

這兩種操作的SQL模擬語句如下，主要是對GROUP BY語句做工作：

# 上卷

SELECT Locates.地區(qū), Products.分類, SUM(數(shù)量)FROM Sales, Products, LocatesWHERE Sales.Locate_key = Locates.Locate_keyAND Sales.Product_key = Products.Product_keyGROUP BY Locates.地區(qū), Products.分類 # 下鉆SELECT Locates.地區(qū), Dates.季度, Products.分類, SUM(數(shù)量)FROM Sales, Dates, Products, LocatesWHERE Sales.Date_key = Dates.Date_keyAND Sales.Locate_key = Locates.Locate_keyAND?Sales.Product_key?=?Products.Product_keyGROUP BY Dates.季度.月份, Locates.地區(qū), Products.分類

?4. 其他OLAP操作

除了上述的幾個基本操作，不同的OLAP工具也會提供自有的OLAP查詢功能，如鉆過，鉆透等，本文不一一進行講解。通常一個復(fù)雜的OLAP查詢是多個這類OLAP操作疊加的結(jié)果。

?

▍OLAP的架構(gòu)模式

1. MOLAP

?MOLAP架構(gòu)會生成一個新的多維數(shù)據(jù)集，也可以說是構(gòu)建了一個實際數(shù)據(jù)立方體。其架構(gòu)如下圖所示：

在該立方體中，每一格對應(yīng)一個直接地址，且常用的查詢已被預(yù)先計算好。因此每次的查詢都是非常快速的，但是由于立方體的更新比較慢，所以是否使用這種架構(gòu)得具體問題具體分析。

2. ROLAP

ROLAP架構(gòu)并不會生成實際的多維數(shù)據(jù)集，而是使用星形模式以及多個關(guān)系表對數(shù)據(jù)立方體進行模擬。其架構(gòu)如下圖所示：

顯然，這種架構(gòu)下的查詢沒有MOLAP快速。因為ROLAP中，所有的查詢都是被轉(zhuǎn)換為SQL語句執(zhí)行的。而這些SQL語句的執(zhí)行會涉及到多個表之間的JOIN操作，沒有MOLAP速度快。

3. HOLAP

這種架構(gòu)綜合參考MOLAP和ROLAP而采用一種混合解決方案，將某些需要特別提速的查詢放到MOLAP引擎，其他查詢則調(diào)用ROLAP引擎。

筆者發(fā)現(xiàn)一個有趣的現(xiàn)象，很多工具的發(fā)展都滿足這個規(guī)律：工具A被創(chuàng)造，投入使用后發(fā)現(xiàn)缺點；然后工具B為了彌補這個缺點而被創(chuàng)造，但是帶來了新的缺點；然后就會用工具C被創(chuàng)造，根據(jù)不同情況調(diào)用A和B。比較無語......

?

▍小結(jié)

本文是數(shù)據(jù)倉庫系列的最后一篇。一路下來，讀者有木有發(fā)現(xiàn)數(shù)據(jù)倉庫系統(tǒng)的開發(fā)是一個非常浩大的工程呢？

確實，整個數(shù)據(jù)倉庫系統(tǒng)的開發(fā)會涉及到各種團隊：數(shù)據(jù)建模團隊，業(yè)務(wù)分析團隊，系統(tǒng)架構(gòu)團隊，平臺維護團隊，前端開發(fā)團隊等等。對于志在從事這方面工作的人來說，需要學習的還有很多。但對于和筆者一樣志在成為一名優(yōu)秀"數(shù)據(jù)科學家"的人來說，這些數(shù)據(jù)基礎(chǔ)知識已經(jīng)夠用了。筆者看來，數(shù)據(jù)科學家的核心競爭優(yōu)勢在三個方面：數(shù)據(jù)基礎(chǔ)，數(shù)據(jù)可視化，算法模型。這三個方面需要投入的時間成本遞增，而知識的重要性遞減。因此，數(shù)據(jù)庫系列和數(shù)據(jù)倉庫系列是性價比最高的兩個系列哦。

歡迎關(guān)注我的公眾號“商業(yè)智能研究”，私信回復(fù)“資料包”，即可領(lǐng)取大數(shù)據(jù)、數(shù)據(jù)中臺、商業(yè)智能、數(shù)據(jù)倉庫等6G精華資料！

?

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的数据仓库的ETL、OLAP和BI应用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。