數(shù)據(jù)倉(cāng)庫(kù)建模詳解和建模技巧

1、一、構(gòu)建企業(yè)級(jí)數(shù)據(jù)倉(cāng)庫(kù)五步法(一、確定主題即確定數(shù)據(jù)分析或前端展現(xiàn)的主題。例如:我們希望分析某年某月某一地區(qū)的啤酒銷售情況,這就是一個(gè)主題。主題要體現(xiàn)出某一方面的各分析角度(維度和統(tǒng)計(jì)數(shù)值型數(shù)據(jù)(量度之間的關(guān)系,確定主題時(shí)要綜合考慮。我們可以形象的將一個(gè)主題想象為一顆星星:統(tǒng)計(jì)數(shù)值型數(shù)據(jù)(度量存在于星星中間的事實(shí)表;分析角度(維度是星星的各個(gè)角;我們將通過維度的組合,來考察量度。那么,“某年某月某一地區(qū)的啤酒銷售情況”這樣一個(gè)主題,就要求我們通過時(shí)間和地區(qū)兩個(gè)維度的組合,來考察銷售情況這個(gè)量度。從而,不同的主題來源于數(shù)據(jù)倉(cāng)庫(kù)中的不同子集,我們可以稱之為數(shù)據(jù)集市。數(shù)據(jù)集市體現(xiàn)了數(shù)據(jù)倉(cāng)庫(kù)某一方面的

2、信息,多個(gè)數(shù)據(jù)集市構(gòu)成了數(shù)據(jù)倉(cāng)庫(kù)。(二、確定量度在確定了主題以后,我們將考慮要分析的技術(shù)指標(biāo),諸如年銷售額之類。它們一般為數(shù)值型數(shù)據(jù)。我們或者將該數(shù)據(jù)匯總,或者將該數(shù)據(jù)取次數(shù)、獨(dú)立次數(shù)或取最大最小值等,這樣的數(shù)據(jù)稱為量度。量度是要統(tǒng)計(jì)的指標(biāo),必須事先選擇恰當(dāng),基于不同的量度可以進(jìn)行復(fù)雜關(guān)鍵性能指標(biāo)(KPI等的設(shè)計(jì)和計(jì)算。(三、確定事實(shí)數(shù)據(jù)粒度在確定了量度之后,我們要考慮到該量度的匯總情況和不同維度下量度的聚合情況。考慮到量度的聚合程度不同,我們將采用“最小粒度原則”,即將量度的粒度設(shè)置到最小。例如:假設(shè)目前的數(shù)據(jù)最小記錄到秒,即數(shù)據(jù)庫(kù)中記錄了每一秒的交易額。那么,如果我們可以確認(rèn),在將來的分析

3、需求中,時(shí)間只需要精確到天就可以的話,我們就可以在ETL處理過程中,按天來匯總數(shù)據(jù),此時(shí),數(shù)據(jù)倉(cāng)庫(kù)中量度的粒度就是“天”;反過來,如果我們不能確認(rèn)將來的分析需求在時(shí)間上是否需要精確到秒,那么,我們就需要遵循“最小粒度原則”,在數(shù)據(jù)倉(cāng)庫(kù)的事實(shí)表中保留每一秒的數(shù)據(jù),以便日后對(duì)“秒”進(jìn)行分析。在采用“最小粒度原則”的同時(shí),我們不必?fù)?dān)心海量數(shù)據(jù)所帶來的匯總分析效率問題,因?yàn)樵诤罄m(xù)建立多維分析模型(CUBE的時(shí)候,我們會(huì)對(duì)數(shù)據(jù)提前進(jìn)行匯總,從而保障產(chǎn)生分析結(jié)果的效率。關(guān)于建立多維分析模型(CUBE的相關(guān)問題,我們將在下期欄目中予以闡述。(四、確定維度維度是指分析的各個(gè)角度。例如我們希望按照時(shí)間,或者按照

4、地區(qū),或者按照產(chǎn)品進(jìn)行分析,那么這里的時(shí)間、地區(qū)、產(chǎn)品就是相應(yīng)的維度。基于不同的維度,我們可以看到各量度的匯總情況,也可以基于所有的維度進(jìn)行交叉分析。這里我們首先要確定維度的層次(Hierarchy和級(jí)別(Level(圖四:pic4.bmp。如圖所示,我們?cè)跁r(shí)間維度上,按照“年 -季度-月”形成了一個(gè)層次,其中“年”、“季度”、“月”成為了這個(gè)層次的3個(gè)級(jí)別;同理,當(dāng)我們建立產(chǎn)品維度時(shí),我們可以將“產(chǎn)品大類-產(chǎn)品子類-產(chǎn)品”劃為一個(gè)層次,其中包含“產(chǎn)品大類”、“產(chǎn)品子類”、“產(chǎn)品”三個(gè)級(jí)別。那么,我們分析中所用到的這些維度,在數(shù)據(jù)倉(cāng)庫(kù)中的存在形式是怎樣的呢?我們可以將3個(gè)級(jí)別設(shè)置成一張數(shù)據(jù)表中

5、的3個(gè)字段,比如時(shí)間維度;我們也可以使用三張表,分別保存產(chǎn)品大類、產(chǎn)品子類、產(chǎn)品三部分?jǐn)?shù)據(jù),比如產(chǎn)品維度.另外,值得一提的是,我們?cè)诮⒕S度表時(shí)要充分使用代理鍵。代理鍵是數(shù)值型的ID號(hào)碼(例如圖六中每張表的第一個(gè)字段,它唯一標(biāo)識(shí)了每一維度成員。更重要的是,在聚合時(shí),數(shù)值型字段的匹配和比較,JOIN效率高,便于聚合。同時(shí),代理鍵對(duì)緩慢變化維度有著重要的意義,在原數(shù)據(jù)主鍵相同的情況下,它起到了對(duì)新數(shù)據(jù)與歷史數(shù)據(jù)的標(biāo)識(shí)作用。在此,我們不妨談一談維度表隨時(shí)間變化的問題,這是我們經(jīng)常會(huì)遇到的情況,我們稱其為緩慢變化維度。比如我們?cè)黾恿诵碌漠a(chǎn)品,或者產(chǎn)品的ID號(hào)碼修改了,或者產(chǎn)品增加了一個(gè)新的屬性,此時(shí),

6、維度表就會(huì)被修改或者增加新的記錄行。這樣,我們?cè)贓TL的過程中,就要考慮到緩慢變化維度的處理。對(duì)于緩慢變化維度,有三種情況:1、緩慢變化維度第一種類型:歷史數(shù)據(jù)需要修改。這種情況下,我們使用UPDATE方法來修改維度表中的數(shù)據(jù)。例如:產(chǎn)品的ID號(hào)碼為123,后來發(fā)現(xiàn)ID號(hào)碼錯(cuò)了,需要改寫成456,那么,我們就在ETL處理時(shí),直接修改維度表中原來的ID號(hào)碼為456。2、緩慢變化維度第二種類型:歷史數(shù)據(jù)保留,新增數(shù)據(jù)也要保留。這時(shí),要將原數(shù)據(jù)更新,將新數(shù)據(jù)插入,我們使用UPDATE / INSERT。比如:某一員工2005年在A部門,2006年時(shí)他調(diào)到了B部門。那么在統(tǒng)計(jì)2005年的數(shù)據(jù)時(shí)就應(yīng)該將

7、該員工定位到A部門;而在統(tǒng)計(jì) 2006年數(shù)據(jù)時(shí)就應(yīng)該定位到B部門,然后再有新的數(shù)據(jù)插入時(shí),將按照新部門(B部門進(jìn)行處理,這樣我們的做法是將該維度成員列表加入標(biāo)識(shí)列,將歷史的數(shù)據(jù)標(biāo)識(shí)為“過期”,將目前的數(shù)據(jù)標(biāo)識(shí)為“當(dāng)前的”。另一種方法是將該維度打上時(shí)間戳,即將歷史數(shù)據(jù)生效的時(shí)間段作為它的一個(gè)屬性,在與原始表匹配生成事實(shí)表時(shí)將按照時(shí)間段進(jìn)行關(guān)聯(lián),這種方法的好處是該維度成員生效時(shí)間明確。3、緩慢變化維度第三種類型:新增數(shù)據(jù)維度成員改變了屬性。例如:某一維度成員新加入了一列,該列在歷史數(shù)據(jù)中不能基于它瀏覽,而在目前數(shù)據(jù)和將來數(shù)據(jù)中可以按照它瀏覽,那么此時(shí)我們需要改變維度表屬性,即加入新的字段列。那么,

8、我們將使用存儲(chǔ)過程或程序生成新的維度屬性,在后續(xù)的數(shù)據(jù)中將基于新的屬性進(jìn)行查看。(五、創(chuàng)建事實(shí)表在確定好事實(shí)數(shù)據(jù)和維度后,我們將考慮加載事實(shí)表。在公司的大量數(shù)據(jù)堆積如山時(shí),我們想看看里面究竟是什么,結(jié)果發(fā)現(xiàn)里面是一筆筆生產(chǎn)記錄,一筆筆交易記錄 那么這些記錄是我們將要建立的事實(shí)表的原始數(shù)據(jù),即關(guān)于某一主題的事實(shí)記錄表。我們的做法是將原始表與維度表進(jìn)行關(guān)聯(lián),生成事實(shí)表(圖六:pic6.bmp。注意在關(guān)聯(lián)時(shí)有為空的數(shù)據(jù)時(shí)(數(shù)據(jù)源臟,需要使用外連接,連接后我們將各維度的代理鍵取出放于事實(shí)表中,事實(shí)表除了各維度代理鍵外,還有各量度數(shù)據(jù),這將來自原始表,事實(shí)表中將存在維度代理鍵和各量度,而不應(yīng)該存在描述性

9、信息,即符合“瘦高原則”， 即要求事實(shí)表數(shù)據(jù)條數(shù)盡量多(粒度最小， 而描述性信息盡量少。 如果考慮到擴(kuò)展，可以將事實(shí)表加一唯一標(biāo)識(shí)列，以為了以 后擴(kuò)展將該事實(shí)作為雪花型維度， 不過不需要時(shí)一般建議不用這 樣做。 事實(shí)數(shù)據(jù)表是數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)的核心，需要精心維護(hù)，在 JOIN 后 數(shù)據(jù)倉(cāng)庫(kù) 將得到事實(shí)數(shù)據(jù)表，一般記錄條數(shù)都比較大，我們需要為其設(shè)置 復(fù)合主鍵和索引， 以實(shí)現(xiàn)數(shù)據(jù)的完整性和基于數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)的查詢性 數(shù)據(jù)倉(cāng)庫(kù) 能優(yōu)化。事實(shí)數(shù)據(jù)表與維度表一起放于數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)中，如果前端需 數(shù)據(jù)倉(cāng)庫(kù) 要連接數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)進(jìn)行查詢， 我們還需要建立一些相關(guān)的中間匯總 數(shù)據(jù)倉(cāng)庫(kù) 表或物化視圖

10、，以方便查詢。 二、項(xiàng)目模型實(shí)踐技巧 (一、準(zhǔn)備區(qū)的運(yùn)用 在構(gòu)建數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)時(shí)，如果數(shù)據(jù)源位于一臺(tái)服務(wù)器上，數(shù)據(jù)倉(cāng) 數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng) 庫(kù)在另一臺(tái)服務(wù)器端，考慮到數(shù)據(jù)源 Server 端訪問頻繁，并且 數(shù)據(jù)量大，需要不斷更新，所以可以建立準(zhǔn)備區(qū)數(shù)據(jù)庫(kù)(圖八： pic8.bmp。先將數(shù)據(jù)抽取到準(zhǔn)備區(qū)中，然后基于準(zhǔn)備區(qū)中的數(shù) 據(jù)進(jìn)行處理，這樣處理的好處是防止了在原 OLTP 系統(tǒng)中頻繁訪 問，進(jìn)行數(shù)據(jù)運(yùn)算或排序等操作。 例如我們可以按照天將數(shù)據(jù)抽取到準(zhǔn)備區(qū)中， 基于數(shù)據(jù)準(zhǔn)備 區(qū)，我們將進(jìn)行數(shù)據(jù)的轉(zhuǎn)換、整合、將不同數(shù)據(jù)源的數(shù)據(jù)進(jìn)行一 致性處理。數(shù)據(jù)準(zhǔn)備區(qū)中將存在原始抽取表、轉(zhuǎn)換中間表和臨時(shí) 表以

11、及 ETL 日志表等。 (二、時(shí)間戳的運(yùn)用 時(shí)間維度對(duì)于某一事實(shí)主題來說十分重要， 因?yàn)椴煌臅r(shí)間 有不同的統(tǒng)計(jì)數(shù)據(jù)信息， 那么按照時(shí)間記錄的信息將發(fā)揮很重要 的作用。在 ETL 中，時(shí)間戳有其特殊的作用，在上面提到的緩慢 變化維度中，我們可以使用時(shí)間戳標(biāo)識(shí)維度成員;在記錄數(shù)據(jù)庫(kù) 和數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)的操作時(shí)，我們也將使用時(shí)間戳標(biāo)識(shí)信息。例如：在 數(shù)據(jù)倉(cāng)庫(kù) 進(jìn)行數(shù)據(jù)抽取時(shí)，我們將按照時(shí)間戳對(duì) OLTP 系統(tǒng)中的數(shù)據(jù)進(jìn)行 抽取，比如在午夜 0：00 取前一天的數(shù)據(jù)，我們將按照 OLTP 系 統(tǒng)中的時(shí)間戳取 GETDATE 到 GETDATE 減一天，這樣得到前一天 數(shù)據(jù)。 (三、日志表的運(yùn)用 在

12、對(duì)數(shù)據(jù)進(jìn)行處理時(shí)，難免會(huì)發(fā)生數(shù)據(jù)處理錯(cuò)誤，產(chǎn)生出錯(cuò) 信息， 那么我們?nèi)绾潍@得出錯(cuò)信息并及時(shí)修正呢? 方法是我們使 用一張或多張 Log 日志表，將出錯(cuò)信息記錄下來，在日志表中我 們將記錄每次抽取的條數(shù)、處理成功的條數(shù)、處理失敗的條數(shù)、 處理失敗的數(shù)據(jù)、處理時(shí)間等等。這樣，當(dāng)數(shù)據(jù)發(fā)生錯(cuò)誤時(shí)，我 們很容易發(fā)現(xiàn)問題所在， 然后對(duì)出錯(cuò)的數(shù)據(jù)進(jìn)行修正或重新處理。 (四、使用調(diào)度 在對(duì)數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù)進(jìn)行增量更新時(shí)必須使用調(diào)度(圖九： 數(shù)據(jù)倉(cāng)庫(kù) pic9.bmp，即對(duì)事實(shí)數(shù)據(jù)表進(jìn)行增量更新處理。在使用調(diào)度前 要考慮到事實(shí)數(shù)據(jù)量，確定需要多長(zhǎng)時(shí)間更新一次。比如希望按 天進(jìn)行查看，那么我們最好按天進(jìn)行抽取，如果數(shù)據(jù)量不大，可 以按照月或半年對(duì)數(shù)據(jù)進(jìn)行更新。如果有緩慢變化維度情況，調(diào) 度時(shí)需要考慮到