星型數(shù)據(jù)庫設(shè)計

1、星型數(shù)據(jù)庫設(shè)計byCraigUtley介紹創(chuàng)建一個星型數(shù)據(jù)庫(StarSchemaDatabase)數(shù)據(jù)倉庫開發(fā)中最重要的步驟之一。要知道這一步驟有多重要，就需要了解一個標(biāo)準(zhǔn)的在線事務(wù)處理系統(tǒng)(OLTP)是如何轉(zhuǎn)移到最終的星型數(shù)據(jù)庫系統(tǒng)(也叫OLAP系統(tǒng))。作為初學(xué)者，當(dāng)你考慮如何建立一個數(shù)據(jù)倉庫的時候，以下問題一定會讓你犯暈： 什么是數(shù)據(jù)倉庫(DataWarehouse)什么是數(shù)據(jù)集市(DataMart)? 什么是星型數(shù)據(jù)庫(StarSchemaDatabase) 為什么我需要一個星型數(shù)據(jù)庫(StarSchemaDatabase) 星型數(shù)據(jù)庫不符合范式標(biāo)準(zhǔn)，使用中會出問題嗎？ 上面這些術(shù)語的

2、具體意思是什么？本文將幫你澄清上述問題，并告訴你如何來創(chuàng)建一個數(shù)據(jù)倉庫，為你們單位的決策服務(wù)。術(shù)語通常，讀者都會對每個章節(jié)或者書本最后附錄里的術(shù)語列表感到厭煩。但是我還是不得不把這些術(shù)語放到前面來介紹。這樣做的目的不是為了讓你更早地覺得厭煩，而是為我們后面討論做好準(zhǔn)備。因為在數(shù)據(jù)倉庫領(lǐng)域，各個組織對相同的術(shù)語都有不同的定義。數(shù)據(jù)倉庫學(xué)院(http:/www.dw-)曾嘗試統(tǒng)一定義這些術(shù)語和概念。在本文中，我會給出我自己認(rèn)為的最好的理解。但是請注意，我并不是在給數(shù)據(jù)倉庫學(xué)院代言。OLTPOLTP代表在線事務(wù)處理(OnlineTransactionProcessing)這是一種標(biāo)準(zhǔn)的，范式化的數(shù)據(jù)

3、庫結(jié)構(gòu)。OLTP是金門為事務(wù)處理設(shè)計的，要求插入(insert)、修改(update'刪除(delete臊作一定要快。設(shè)想一個訂單錄入的呼叫中心(callcenter),工作人員不停地接受呼叫，并把所有訂單和訂單條目的詳細(xì)內(nèi)容錄入到數(shù)據(jù)庫中。在這種情況下，數(shù)據(jù)庫的性能顯得至關(guān)重要，插入(修改、刪除)速度要設(shè)法達(dá)到最快。為達(dá)到這一目的，數(shù)據(jù)庫的數(shù)據(jù)一般都不會有冗余，保留盡可能少的數(shù)據(jù)。OLAP和星型(StarSchema)OLAP代表在線分析處理(OnlineAnalyticalProcessing)。不同的人對OLAP有不同的理解。在這里，我們認(rèn)為OLAP和StarSchema

4、4;艮大程度上是可以互換的概念，一個星型數(shù)據(jù)庫就是一個OLAP系統(tǒng)。這跟微軟所說的OLAP不一樣，微軟將OLAP概念擴展到了用他們的產(chǎn)品OLAPService創(chuàng)建出來的立方體結(jié)構(gòu)(cubestructure)!面。我們定義，任何存放只讀的(read-only)、歷史的(historical)>聚合(aggregated)勺數(shù)據(jù)的系統(tǒng)都是OLAP系統(tǒng)。另外，我們認(rèn)為OLAP/StarSchema系統(tǒng)就是數(shù)據(jù)倉庫。這樣理解不會產(chǎn)生任何問題，雖然在數(shù)據(jù)倉庫中，為了提高查詢速度而經(jīng)常使用立方體結(jié)構(gòu)(cubestructure)數(shù)據(jù)倉庫(DataWarehouse和數(shù)據(jù)集市(DataMart)你可

5、能認(rèn)為我把兩個完全不同的概念混在了一起，實際上，數(shù)據(jù)倉庫和數(shù)據(jù)集市只是在范圍上不一樣，而在創(chuàng)建方法和流程上完全一致。所以我把它們放在一起討論。數(shù)據(jù)倉庫(或者數(shù)據(jù)集市)是為了日后查詢而采取的一種數(shù)據(jù)存儲方法。這種查詢幾乎總是用來為某個單位的決策服務(wù)的。這也是為什么許多數(shù)據(jù)倉庫都被認(rèn)為是決策支持系統(tǒng)(DSS,Decision-SupportSystem)有些人會堅持認(rèn)為不是所有的數(shù)據(jù)倉庫都是DSS,有些數(shù)據(jù)倉庫只是純粹的數(shù)據(jù)歸檔?？墒俏覀?yōu)槭裁磿〞r間和精力去創(chuàng)建一個星型數(shù)據(jù)庫，還有立方體結(jié)構(gòu)？目的是為了提高查詢速度。這些查詢通常要花費大量時間。人們?yōu)槭裁丛敢饣ù罅繒r間去查看數(shù)據(jù)？可能是為了發(fā)現(xiàn)某

6、種趨勢。如果是為了發(fā)現(xiàn)趨勢，那你可以發(fā)誓他們是在做某個決定，比如說需要訂購多少原材料。還是為決策服務(wù)！數(shù)據(jù)倉庫和數(shù)據(jù)集市都是為了存儲任何只讀的(read-only)、歷史的(historical)>聚合的(aggregated數(shù)據(jù)的一種存儲機制。只讀的，就是說數(shù)據(jù)不會被更改。如果某個用戶要查看昨天某種產(chǎn)品的銷售信息，那他不應(yīng)該有權(quán)限去修改銷售值，除非已經(jīng)知道那個銷售值有錯誤。歷史的，可能只是幾分鐘以前的數(shù)據(jù)，但一般都指至少一天前的數(shù)據(jù)。數(shù)據(jù)倉庫都會保存某個階段，比如五年以來的數(shù)據(jù)。而標(biāo)準(zhǔn)的OLTP系統(tǒng)通常只保存當(dāng)前的，或者是活動的數(shù)據(jù)。比如在訂購表中，當(dāng)商品完成發(fā)貨并確認(rèn)客戶已經(jīng)接收，就

7、可以把定購信息轉(zhuǎn)移到歸檔表中。聚合的。當(dāng)我們說數(shù)據(jù)倉庫和數(shù)據(jù)集市存放聚合值的時候，我們要強調(diào)一個典型數(shù)據(jù)倉庫中，都有許多層的聚合(aggregation這里我們只假設(shè)基本的聚合：數(shù)據(jù)倉庫中的所有數(shù)據(jù)都按一個時間段聚合。請看下面的例子：店鋪銷售2種商品，狗糧和貓糧。每天都記錄每種商品的銷售信息，若干天后我們會得到如下的數(shù)據(jù)：日期訂單序號銷售,攵量狗糧貓糧4/24/991522303264225334/25/99137221340表1表1中的數(shù)據(jù)是我們在標(biāo)準(zhǔn)的OLTP系統(tǒng)中能看到的數(shù)據(jù)。但是在數(shù)據(jù)倉庫系統(tǒng)中，通常不會紀(jì)錄這么細(xì)節(jié)的信息。相反，這些數(shù)據(jù)會按每日的總數(shù)進行聚合。數(shù)據(jù)倉庫中記錄看上去會是

8、這樣：銷售學(xué)我量日期狗糧貓糧4/24/9915134/25/9998表2可以看出，通過按日期聚合，我們減少了記錄數(shù)，只顯示了各種商品每天的銷售數(shù)量，而不是每次交易的數(shù)量。我們當(dāng)然也可以在一個OLTP系統(tǒng)中，通過查詢獲得OLAP系統(tǒng)能提供的相同的結(jié)果。但是后面我們會看到，有很多理由讓我們不要這么做。聚合（Aggregation）聚合并不是一個神秘的概念，它只是指概要的，加起來的值。在星型數(shù)據(jù)庫中，聚合的程度可以不同。OLTP系統(tǒng)OLTP系統(tǒng)是標(biāo)準(zhǔn)的、范式化的數(shù)據(jù)庫，對事務(wù)處理（insert,update,delete）!最優(yōu)秀的。OLTP系統(tǒng)通過下面途徑，獲取事務(wù)處理的更快速度：最小化重復(fù)數(shù)據(jù)，

9、限制索引數(shù)量。（鑒于OLTP系統(tǒng)、三范式已為大家熟知，OLTP介紹部分略）OLTP系統(tǒng)對于查詢分析，有諸多缺點。首先，為了獲取所需的完整信息，用戶經(jīng)常要同時訪問多張表，并把這些表連接（join）起來。連接（Join）查詢通常比只訪問一張表要慢。其次，OLTP系統(tǒng)限制每張表上的索引數(shù)量，這對Insert/update/delet好操作有利，但是對查詢只會起到反作用。最后，OLTP系統(tǒng)的數(shù)據(jù)對用戶不友好。多數(shù)IT專業(yè)人員并不想整天為客戶創(chuàng)建各種報表，而是希望能提供查詢工具給客戶，讓他們自己創(chuàng)建各種報表?？墒嵌鄶?shù)客戶，并不熟悉關(guān)系數(shù)據(jù)庫，他們會對連接(join)覺得神秘，會對復(fù)雜的表結(jié)構(gòu)覺得恐怖。如

10、何查看信息一個現(xiàn)實問題：我們該如何查看數(shù)據(jù)庫中的數(shù)據(jù)？比如說下面幾個問題： 上周Aniseed糖漿銷售了多少瓶？ 跟去年相比，今年的調(diào)味品銷量是升了還是降了？ 以季節(jié)或月份為標(biāo)準(zhǔn)，乳制品的銷量有周期性嗎？ 跟去年同期相比，今年哪些地區(qū)的銷量降了？什么商品銷量降幅最大？這些問題都是商業(yè)活動中常見的問題。他們有一些共同的地方。第一，每個問題都有時間因素。第二，查看的都是聚合值，都是總量或者總數(shù)，而不是單獨的事務(wù)。最后，都是按某個“by”條件查看數(shù)據(jù)。按“by”條件是指查看數(shù)據(jù)時按某些指定的條件。比如"以季節(jié)或者月份為標(biāo)準(zhǔn)，乳制品的銷量有周期性嗎”這個問題，我們可以細(xì)分成”我們想按商品種類

11、(這里只有乳制品一個種類)查看銷量，按季度/月份查看找出我們要查看的聚合值，像銷售總額，購買某種商品的顧客數(shù)量，并且找出相應(yīng)的“by”條件，就是促使我們進行星型數(shù)據(jù)庫設(shè)計的驅(qū)動源。讓數(shù)據(jù)庫符合我們的期望如果總是要通過一系列“by”條件來查看聚合值，我們?yōu)槭裁床恢苯影褦?shù)據(jù)按這種格式存儲呢？這就是星型數(shù)據(jù)庫！OLTP并不是決策支持系統(tǒng)的基礎(chǔ)。OLTP中的T代表Transaction,事務(wù)。事務(wù)只關(guān)心處理訂單，修改存貨清單，而不關(guān)心如何處理復(fù)雜的銷售趨勢分析。與其在OLTP系統(tǒng)中執(zhí)行巨大的，耗時的查詢，還不如創(chuàng)建一個符合我們觀察角度的數(shù)據(jù)庫。事實(Fact)和維(Dimension)當(dāng)我們觀察數(shù)據(jù)時

12、，通常想察看聚合數(shù)據(jù)的某種順序。這些數(shù)據(jù)叫做度量(measure)度量就是可以度量和相加的數(shù)值。比如銷售金額就是一種度量，每個訂單都有銷售金額。假設(shè)每天銷售20個產(chǎn)品，每個5美元，銷售總額就是100美元。銷售金額就是我們想關(guān)注的一種度量。此外我們可能還想知道當(dāng)天的顧客數(shù)，是5位顧客一共買了20個產(chǎn)品，還是1位顧客買了所有的20個產(chǎn)品呢？銷售金額和顧客數(shù)量就是我們想關(guān)注的兩個度量。僅僅關(guān)心度量還不夠。我們觀察度量的時候都需要“by”條件。這些“by”條件就叫做維(dimension)。討論銷售金額的時候，總要指定是某一吳，某個季度或者某年的銷售金額。幾乎我們關(guān)心的任何度量都離不開時間維。我們可能

13、還想按照產(chǎn)品名稱或者產(chǎn)品類型查看銷售金額，這些條件都要對應(yīng)到相應(yīng)的維上。由上可知，設(shè)計星型數(shù)據(jù)庫的時候，我們首先要確定我們想看什么信息(確定度量)，如何看這些信息(確定維)。維表我們?yōu)槭裁匆指顢?shù)據(jù)？維表(dimensiontable)回答了這個問題。比如說，我們通常會根據(jù)時間來觀察數(shù)據(jù)，而不會不考慮時間只看總值。如果我們的銷售記錄最早開始于1989年7月14日，我們會關(guān)心從那天起到現(xiàn)在的銷售總額，還是更關(guān)心某一年和其他年份的銷售金額對比情況呢？將某一年和上一年作對比，然后做趨勢分析，這是人們用星型數(shù)據(jù)庫來實現(xiàn)的常見功能。我們可能還需要一個地域維，用來對比某個地區(qū)和其他地區(qū)的銷售額，并發(fā)現(xiàn)銷售

14、能力比較弱的地區(qū)。這可能意味著那個地區(qū)出現(xiàn)了新的競爭者，廣告做得不夠，或者其他有待進一步調(diào)查的原因。當(dāng)我們開始創(chuàng)建維表的時候，有一些規(guī)則要牢記在心。第一，所有維表都要有一個基于單列的主鍵。這一主鍵列通常只是一標(biāo)識列，包含自動遞增的數(shù)值，并沒有真正的含義。有含義的信息都在其他列中，這些列包含了我們要查看的所有描述信息。比如在產(chǎn)品維中，包含了產(chǎn)品描述、類別、子類等等。這些字段不能用來作為連接字段和其他表關(guān)聯(lián)，但是包含了產(chǎn)品的所有描述信息。維表通常都比較胖，因為字段都比較多，每一字段都比較寬。同時，維表的另一個特性是一般都比較矮。我們的產(chǎn)品種類可能很多，但還是無法跟事實表(facttable)相比。

15、假設(shè)我們的產(chǎn)品表中有30,000種產(chǎn)品，我們跟蹤這些產(chǎn)品的每天銷售記錄。就算每天實際上只銷售3,000種產(chǎn)品，十年之后，事實表中的記錄數(shù)將是3,000(種產(chǎn)品)*365(天/年)*10年=10,950,000!因此，跟事實表相比，維表中的30,000條記錄就顯得很少。既然維表這么胖，就會讓人產(chǎn)生一種通過范式化精簡的沖動。先別急著這么做，隨后討論雪花模型的時候會告訴你原因。維的層次我們經(jīng)常在OLTP系統(tǒng)中使用層次結(jié)構(gòu)。但是OLAP系統(tǒng)中的層次結(jié)構(gòu)有所不同，因為維(dimension)的所有層次都存在同一張維表中。比如產(chǎn)品維中，包含了所有產(chǎn)品。這些產(chǎn)品分屬于不同的類別，各產(chǎn)品類別可能又包含子類。像

16、產(chǎn)品號為X12JC的商品，它屬于大家電(category)里面的冰箱(sub-category)子類?？赡苓€有更細(xì)的分類，但是這里問題的關(guān)鍵是所有這些信息都存儲在同一張維表中。維表的結(jié)構(gòu)會如下圖所示：ProductIDProductCodeProductNameCategorySubCategoryBrandHeightWidthetc請注息category列和sub-category列都存在同一張表中，而不是存在不同的表中，然后關(guān)聯(lián)起來。這種一張表中存儲多個層次的存儲關(guān)系，有助于實現(xiàn)數(shù)據(jù)鉆取(drill-down)功能。我們可以按category統(tǒng)計總數(shù)，然后對某個子類，進行鉆取一分別按各個

17、子類統(tǒng)計總數(shù)，進一步可以按各個產(chǎn)品統(tǒng)計總數(shù)。下面是一個簡單的例子。有2張維表(dimensiontabled1張事實表(facttable)關(guān)聯(lián)，事實表中只有一個數(shù)值：銷售總額(SalesDollars)。ProductIDProductCodeProductNameCategorySubCategoryBrandHeightWidthSalesFact<ProductlD(FK)TimelD(FK)SalesDollarsProductDimensionTimelDDayOfWeekDayOfMorithDayOfYearMonthQuarterYearHolidayWeekend圖2

18、現(xiàn)在要查看某個月份某類商品的銷售總額，需要的SQL如下：SELECTSum(SalesFact.SalesDollars)ASSumOfSalesDollarsFROMTimeDimensionINNERJOIN(ProductDimensionINNERJOINSalesFactONProductDimension.ProductID=SalesFact.ProductID)ONTimeDimension.TimeID=SalesFact.TimeIDWHEREProductDimension.Category='BrassGoods'ANDTimeDimension.Mon

19、th=3ANDTimeDimension.Year=1999要鉆取，查看子類的銷售總額，只需把SQL改成如下語句：SELECTSum(SalesFact.SalesDollars)ASSumOfSalesDollarsFROMTimeDimensionINNERJOIN(ProductDimensionINNERJOINSalesFactONProductDimension.ProductID=SalesFact.ProductID)ONTimeDimension.TimeID=SalesFact.TimeIDWHEREProductDimension.SubCategory='Wid

20、gets'ANDTimeDimension.Month=3ANDTimeDimension.Year=1999雪花模型有時候，維表中的層次會存到獨立的表中。這是一種更符合范式的結(jié)構(gòu)，但是會導(dǎo)致查詢更困難，相應(yīng)速度也更慢。圖3表示一個雪花模型。類型層次從產(chǎn)品維ProductionDimension中分離出來，形成單獨的一張表。我們可以看出這種結(jié)構(gòu)增加了連接(join)的數(shù)量，減慢了查詢速度。OLAP系統(tǒng)的目的就是為了加快查詢速度，所以雪花模型并不是我們要推薦的數(shù)據(jù)庫模型。有些人認(rèn)為范式化維表可以節(jié)省存儲空間。但實際上，在數(shù)據(jù)倉庫中，維表的記錄數(shù)通常只有所有記錄數(shù)的1%。因此，通過范式化維

21、表，或者采用雪花模型來節(jié)省空間，并不可取。ProductDirnensionProductlDCategorylD(Fk5ProductCodeProductNameBrandHeightWidthEHbhFs)匚trProductlD(FK).TimelD(FK)SalesDollarsJimeDimensionTimelDDyOfWeekDayOfMonthDayOfYearMonthQuarterYearHolidayWeekend創(chuàng)建事實表（FactTable）事實表存放度量（measure猜息，或者稱事實（fact）信息。度量是根據(jù)各個維計算出來的一些數(shù)值。比如說銷售金額是個數(shù)值，我

22、們可以按產(chǎn)品、安類型查看總數(shù)，可以查看任何時間段的所有總數(shù)。跟維表的又矮又胖相比，事實表一般顯得又高又瘦。事實表很高，是因為他們擁有的記錄數(shù)一般都很巨大。比如看下面這個簡單的例子：JimeDim己n。口TimelD斗(址tDimensionProductlDProductcodeProductNameCategorySubCategoryBrandHeightWidthSalesFactProductlD(FK)TimelD(Fk)StorelD(FQSalesDollarsDayOfWeekDayOfMonthDayOfYesrMonthQuarterYearHolidayWeekendSt

23、QrEDimensignStorelDStoreNameParentChainRegionTerritoryZoneAddressCityState如在這個例子中，有產(chǎn)品維，時間維和店鋪維。假設(shè)有10年的日常銷售紀(jì)錄，一共200家店鋪，銷售500種產(chǎn)品。那么事實表的記錄數(shù)將達(dá)到365,000,000（3650天*200家店鋪*500種產(chǎn)品）。這就是事實表的徜事實表的“瘦”是因為它所存放的列。首先，事實表的主鍵由參照其他維表的外鍵構(gòu)成，這些列都是數(shù)值型數(shù)據(jù)。此外度量本身也是數(shù)值。因此，事實表的單行紀(jì)錄跟維表的單行紀(jì)錄相比，都顯得很小。事實表的粒度（granularity）創(chuàng)建星型數(shù)據(jù)庫中，一個很

24、重要的步驟是確定事實表的粒度。數(shù)據(jù)的粒度，或者頻率，通常由時間維決定。比如你只想存儲每周、每月的銷售總數(shù)，而不需要每大的總數(shù)。粒度決定了用戶可以在不查詢OLTP系統(tǒng)的前提下，數(shù)據(jù)倉庫中可鉆取的深度。當(dāng)粒度越低，可向下鉆取的深度就越深，但事實表中需要存放的記錄數(shù)也越多；當(dāng)粒度越高，可以減少事實表中需要存放的記錄數(shù)，但是一些比較細(xì)節(jié)的信息，就無法在OLAP系統(tǒng)中查尋。事實表的大小從前面的例子中，我們已經(jīng)發(fā)現(xiàn)，如果按大記錄銷售金額的話，500種產(chǎn)品，200個店鋪，10年時間將在事實表中產(chǎn)生365,000,000條記錄。但這是最大的可能值。實際上很多情況下我們都不會達(dá)到這個最大記錄數(shù)。在某些日期里，某

25、些產(chǎn)品可能一件也沒有賣出，這樣的話銷售記錄就是0或者空值，這些0或者空值不應(yīng)該放到事實表中去。盡管這樣，事實表還是占據(jù)了數(shù)據(jù)庫中的絕大多數(shù)紀(jì)錄，占用了絕大多數(shù)的磁盤空間。粒度越低，事實表就越大。從上面的例子可以看出，如果把每天的銷售記錄改成每周的銷售記錄的話，最大的可能紀(jì)錄總數(shù)就會減少到差不多52,000,000條。事實表字段的數(shù)據(jù)類型有助于使事實表所占用的空間盡可能地小，因為其字段類型都是數(shù)值型。最后請注意，每增加一張維表，就有可能極大地增加事實表的記錄數(shù)。上面的例子中，如果再增加一維，維表包含20條記錄，那么事實表的最多紀(jì)錄數(shù)就可能達(dá)到73億條！改變屬性屬性的改變是星型模型面臨的最大的挑戰(zhàn)之一?？磮D4的例子，在StoreDime