資料倉儲資料庫設計之一

by adonis 9. 一月 2014 14:18

前言   



最近發現似乎還沒有寫過資料倉儲資料庫設計的主題,只有寫過一般關聯式資料庫的設計文章,既然如此,接下來就來設計資料倉儲的資料庫吧,從簡單的資料庫開始設計起,順便來檢視各位公司的資料倉儲資料庫的設計是否有按照一定的程序。

資料倉儲資料庫設計



要設計資料倉儲前,先來設定一個主題,就是你想利用這個資料倉儲做什麼事?例如資料倉儲大多數都是做決策支援,其實這個「決策支援」的範圍相當的廣泛,並不一定是只有商業決策才叫做決策支援,也並不一定是公司的決策者(CEO之類的)才是決策人員,任何事情,任何人,只要有廣泛的數據,都可以拿來分析,就好比舉個例子來說,線上購物網站搜集線上顧客瀏覽的商品,並且對這些商品進行分析,就可以提供一些資訊給線上購物的使用者「該買何種商品」,可惜台灣的線上購物網站多半只有「你曾經瀏覽過的商品」,以及「看(買)了這個商品的人,也同時看(買)了這個商品」的機制,感覺似乎把資料倉儲的概念做小了。
像知名線上購物網站 Amazon對於這方面的用心是可以感覺的出來的,每個商品不但有顧客評分,買過這個商品的人還可以對該商品留言寫評價,甚至同種類的商品,還會有百分比顯示有多少比例的人買了A商品,而有多少比例的人買了B商品,這時後決策的人是線上購物的顧客,而不是企業的高層主管。
線上購物就是要展現購買者的「決策力」,為何 A商品會被購買而同性質的 B商品購買比例會比較低?難道只有價格比較低嗎?有時後不見得是如此。
所以我們在設計資料倉儲之前,必需要了解你想要分析什麼?
1.    誰要這些分析資料?是顧客?長官?教練還是政府官員?
2.    分析什麼資料內容?銷售資訊?購買記錄?運動數據還是打擊分析?
3.    想以什麼角度分析?以顧客地區角度?以日期角度?還是以年齡角度分析?

如果想要分析這些資料,其實正確的方式還是先將 OLTP的資料庫產生,才能夠再進階的完成資料倉儲(OLAP)資料庫的後續。

換句話說,設計一個資料倉儲資料庫,還是得從 OLTP資料庫設計起,為何?因為資料倉儲資料總得先把資料匯入,也有可能會有刪除或是修改的可能性,而 OLTP資料庫在設計的當下都需要做三個正規化,這三個正規化其實說穿了就是避免資料的重複存放。

資料重複存放主要有兩個缺點,第一個是浪費空間,第二個則是如果資料有修改或刪除則需要重複修改或刪除資料,所以 OLTP資料庫的設計原理,三個正規化主要是對新增、修改、刪除是有利的。

但是 OLTP的最大缺點就是在資料的查詢,因為正規化勢必會造成資料表的數會變多,而查資料時,就必需大量的使用 join的手法串連多個資料表才能將資料查詢出,如果把 OLTP的資料庫當做是資料分析的資料,勢必會讓產生分析資料的效能大打折扣。

資料倉儲資料多半具有下面幾個特性:

1.    資料多半是歷史資料:你曾經做過的歷史動作,我們方能針對這些歷史資料分析。
2.    需要大量的加總或分組運算,例如會大量的使用到SUM、Group by這類型的 SQL句法。

以第2點來說,資料需要大量的加總或是分組運算,並不會因為你設定了索引就可以讓效能得以提升多少,因為索引這個東西對資料的彙總並沒有什麼太大的幫助,也就是說,傳統的決策支援系統可能是以 OLTP的資料庫當成資料來源,運用大量的 SQL技巧,幫助使用者動態的彙總分組成想看,或是想分析的結果,它的優點是資料可以是最即時的狀態,但是每次都要動態的取得彙總資料,無疑就是浪費 CPU的資源使用(而且決大多數的 SQL指令,是不會使用到多顆處理器的)。

所以我們需要在原先的 OLTP資料庫上,再建構一層資料來源,如果 OLTP是一個正規化的資料來源的話,那麼我們就要在這個正規化的資料來源上方再產生一道反正規化的資料來源,也就是如下圖:

image

這個反正規化的資料來源,平常是不會對它新增修改刪除的,更精確的說,應用程式是不會對這個反正規化的資料來源新增修改刪除,它的資料主要是透過 OLTP定期的將資料利用匯入匯出的方式產生。

與 OLTP正規化明細不同的地方在於, OLTP明細是生資料,要變成有意義,可被分析的資料完全得靠應用程式邏輯以及 SQL指令達成,而 OLAP資料來源主要是為了能方便產生所謂的 CUBE,事先計算想看的資料彙總,並且用 CUBE的結構方式儲存起來,以方便日後的資料分析,所以如下圖:

image

這張圖說明了在早期沒有資料倉儲概念時,應用程式是直接透過 SQL指令硬幹,將想看的結果集回傳給應用程式處理,但缺點是速度慢以及即便是設定了索引效能也不會增加。

而資料倉儲的設計方式則是先把正規化的明細資料匯入到 OLAP的反正規化明細,這裡的動作我們稱為 B,接下來因為資料已經是反正規化了,所以無論在做資料彙總或是 join的效能就會很明顯的比原本 OLTP來的好。

換句話說,以 OLTP資料庫做出的決策支援系統而言,效能最佳化通常是落在 SQL指令語法的好壞,而設計成資料倉儲資料來源的反正規化方式的效能最佳化是落在如何快速的處理完 A以及 B這兩個動作。

只要 A這個「事先處理」或是事先彙總完成後所產生的 cube做完後,應用程式所看到的 cube資料即是資料已經計算完的內容,所以效能來說是相當快速的。

也就是因為這個原因,所以我們如果一開始就要做資料倉儲或是商業智慧,必然的結果是應該按步就班開始,由 OLTP資料庫開始設計,並且將這些資料反正規化製作成 OLAP反正規化的資料明細,才算是某些程度上的資料設計完成。
資料倉儲資料庫情境
大略說明完資料倉儲資料庫設計的一些事項後,接下來我們由簡單的資料倉儲資料庫開始設計起,範例不用難,隨便都可以找到可以,或是想分析的資料。

由於我最近開始騎自行車,所以第一個例子我們就來用自行車的里程記錄分析當做範例吧!
是這樣的,現在騎車都會在車上綁上一個 GPS,也會戴上心跳帶,並且在車上裝上測迴轉數的裝置,每次騎完後可以知道這趟路程所需要的里程數、迴轉數、溫度之類的資訊。
在設計資料庫之前,先預想自己想看哪些方面的資料,或是想分析哪方面的資料,這樣才好知道我們要截取哪些欄位,當然這些資料如果你日後才想要看的話,是無法再回到過去把當初沒搜集的資料取出來的,例如你想知道平均每個月份到底騎車會耗多少卡落里,你就必需要有一個記錄卡落里的欄位,如果你當初在設計時沒有規劃到這個欄位,過了兩個月才想到應該要來分析卡洛里與月份的關係時,你已經沒有當時騎車的消耗卡落里數據(難不成你要回到過去搜集嗎?)。

所以資料倉儲或是商業智慧如果無法得知自己想分析或是想看什麼數據,那一切都是白搭(攤手),這是務必得記住的事情。

我們先連到 Garmin connect的網站看一下它的主控台吧(以下是我的騎車數據圖表):

image

是的,這張圖基本上是你有註冊 Garmin connect,並且將你的騎車資訊上傳到這個網站,久而久之你就會看到的相關的分析圖表,其中可以發現,這個圖表其實預設只有每個月的活動個數的統計,例如看起來九月份騎車活動是最多的,而一月份的活動是最少的。

當然這樣的數據是有點失真,因為我自己知道,活動多但騎乘距離可能短,與活動少騎乘距離遠根本無法透過這樣的圖表呈現出什麼東西,因為如果真要分析的話,下面這種圖可能才是我要的:

image

是的,也就是說,各位在設計資料倉儲資料庫之前,就必需在腦海中自行浮現出你想看到的圖表長什麼樣子,如果你想不出來,你也不用設計了,因為你根本不知道你要拿這些資料幹什麼,而就算以後設計出來,也會因為臨時需要分析當初沒規劃的資料而傷透了腦筋。

我們下期將從 OLTP資料庫開始設計起,再設計 OLAP的資料來源。

image

Tags:

評論 (1) -

WizardWu
WizardWu Taiwan
2014/3/11 下午 10:53:17 #

推一下,希望能有續集。


------------------------------------------------------------
臉書「資料庫之道」粉絲團:
http://www.facebook.com/DBtaoist


回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List