數(shù)據(jù)倉庫設(shè)計技巧

商業(yè)智能核心技術(shù)與應用數(shù)據(jù)倉庫設(shè)計數(shù)據(jù)倉庫的基本概念數(shù)據(jù)倉庫是面向主題的、集成的、穩(wěn)定的、隨時間不斷變化的數(shù)據(jù)庫系統(tǒng)。它介于數(shù)據(jù)源與多維數(shù)據(jù)庫之間，起到承上啟下的作用。數(shù)據(jù)倉庫是通過對來自不同的數(shù)據(jù)源進行統(tǒng)一處理及管理，通過靈活的展現(xiàn)方法來幫助決策支持。數(shù)據(jù)源數(shù)據(jù)倉庫決策數(shù)據(jù)倉庫的設(shè)計理念在設(shè)計倉庫數(shù)據(jù)庫之初把用戶的分析需求納入考慮范圍是十分有必要的。同時，數(shù)據(jù)倉庫的構(gòu)建必需基于業(yè)務(wù)數(shù)據(jù)庫，業(yè)務(wù)數(shù)據(jù)源的結(jié)構(gòu)也是不得不考慮的問題。因此在設(shè)計數(shù)據(jù)倉庫的時候，應該堅持用戶驅(qū)動與數(shù)據(jù)驅(qū)動相結(jié)合的設(shè)計理念。業(yè)務(wù)數(shù)據(jù)模型設(shè)計的設(shè)計范式1NF

如果一個表中沒有重復組（即行與列的交叉點上只有一個值，而不是一組值）,且定義了關(guān)鍵字、所有非關(guān)鍵屬性都依賴于關(guān)鍵字，則這個表屬于第一范式（常記成1NF）

2NF

如果一個表屬于1NF，且不包含部分依賴性，既沒有任何屬性只依賴于關(guān)鍵字的一部分，則這個表屬于第二范式（常記成2NF）。3NF

如果一個表屬于2NF，且不包含傳遞依賴性（即沒有一個非關(guān)鍵屬性依賴于另一個非關(guān)鍵屬性，或者說沒有一個非關(guān)鍵屬性決定另一個非關(guān)鍵屬性），則這個表是第三范式（常記成3NF）。業(yè)務(wù)數(shù)據(jù)模型設(shè)計的ER圖表示ER模型（實體聯(lián)系模型）簡稱ER圖。它是描述概念世界，建立概念模型的實用工具。ER圖包括三個要素：實體、屬性、實體之間的聯(lián)系聯(lián)系歸結(jié)為三種類型：

一對一聯(lián)系（1:1）

設(shè)A、B為兩個實體集。若A中的每個實體至多和B中的一個實體有聯(lián)系，反過來，B中的每個實體至多和A中的一個實體有聯(lián)系，稱A對B或B對A是1:1聯(lián)系。一對多聯(lián)系（1:n）

如果A實體集中的每個實體可以和B中的幾個實體有聯(lián)系，而B中的每個實體至我和A中的一個實體有聯(lián)系，那么A對B屬于1:n聯(lián)系。多對多聯(lián)系（m:n）

若實體集A中的每個實體可與和B中的多個實體有聯(lián)系，反過來，B中的每個實體也可以與A中的多個實體有聯(lián)系，稱A對B或B對A是m:n聯(lián)系。

多對多聯(lián)系舉例統(tǒng)計特征FatLeanUglyWealthy統(tǒng)計特征客戶客戶統(tǒng)計特征銷售Fact客戶統(tǒng)計特征JohnFatRichardLeanJohnUglyRichardWealthy客戶JohnRichardCristian客戶銷售額John1.0Richard3.0Cristian10.0每個統(tǒng)計特征對銷售人員來說是怎么樣的呢？統(tǒng)計特征銷售額AllDemographics14.0Fat1.0Lean3.0Ugly1.0Wealthy3.0數(shù)據(jù)倉庫模型設(shè)計最佳實踐數(shù)據(jù)倉庫模型設(shè)計主要分三個階段：1、概念設(shè)計2、邏輯設(shè)計3、詳細設(shè)計并分別產(chǎn)生三類設(shè)計模型1、主題域模型2、業(yè)務(wù)數(shù)據(jù)模型3、物理模型數(shù)據(jù)倉庫模型設(shè)計采用迭代式開發(fā)，這一點也符合數(shù)據(jù)倉庫系統(tǒng)迭代開發(fā)的特點。它具有較好的靈活性和易變性，適應于主題不明確或不確定的需求。概念設(shè)計數(shù)據(jù)倉庫是面向主題來組織數(shù)據(jù)，一個數(shù)據(jù)倉庫有若干個主題，而每個主題又有一個數(shù)據(jù)集合體做支撐，這個數(shù)據(jù)集合稱為主題域。概念設(shè)計的中心工作是在需求分析基礎(chǔ)上設(shè)計的主題域模型。主體域模型是客觀到主觀之間的橋梁，是與硬件環(huán)境、軟件選擇無關(guān)的數(shù)據(jù)抽象模型，是為下一步建立業(yè)務(wù)數(shù)據(jù)模型、物理模型服務(wù)的概念性工具。主題域具有兩個特性1、獨立性，即主題域具有明確的邊界與獨立的內(nèi)涵，雖然主題間可以有交叉，但不影響其獨立性。2、完備性，即每個主題的分析要求所需的數(shù)據(jù)均應能在主題域中得到。采用概念數(shù)據(jù)模型設(shè)計就是要設(shè)計主題域的數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)倉庫項目的主題域模型示例邏輯設(shè)計邏輯設(shè)計設(shè)計計到的知識點點包括：業(yè)務(wù)數(shù)據(jù)模型型設(shè)計的建模模對象應包含含實體、屬性性、關(guān)鍵字和和聯(lián)系。業(yè)務(wù)數(shù)據(jù)模型型設(shè)計應該遵遵守規(guī)范化準準則：即第三三范式設(shè)計準準則。業(yè)務(wù)數(shù)據(jù)模型型的ER圖表示法。邏輯設(shè)計的中中心工作是設(shè)設(shè)計業(yè)務(wù)數(shù)據(jù)據(jù)模型，業(yè)務(wù)務(wù)數(shù)據(jù)模型是是用具體的軟軟件結(jié)構(gòu)來實實現(xiàn)概念數(shù)據(jù)據(jù)模型。目前數(shù)據(jù)倉庫庫一般是建立立在關(guān)系數(shù)據(jù)據(jù)庫基礎(chǔ)上，，因此數(shù)據(jù)倉倉庫的設(shè)計中中采用的業(yè)務(wù)務(wù)數(shù)據(jù)模型就就是關(guān)系模型型。業(yè)務(wù)數(shù)據(jù)模型型設(shè)計的建模模對象實體我們把客觀存存在并且可以以相互區(qū)別的的事物稱為實實體。實體可可以是實際事事物，也可以以是抽象事件件。屬性描述實體的特特性稱為屬性性。關(guān)鍵字如果某個屬性性值能唯一地地標識出實體體集中的每一一個實體，可可以選作關(guān)鍵鍵字。聯(lián)系實體集之間的的對應關(guān)系稱稱為聯(lián)系，它它反映現(xiàn)實世世界事物之間間的相互關(guān)聯(lián)聯(lián)。聯(lián)系分為為兩種，一種種是實體內(nèi)部部各屬性之間間的聯(lián)系。另另一種是實體體之間的聯(lián)系系。邏輯設(shè)計方法法—定義實體主題域主題實體粒度類型說明生皮分選物料物料物料代碼，唯一標識每種物料維度物料分類物料分類代碼，唯一標識每種物料分類維度分選結(jié)果分選結(jié)果物料代碼*倉庫*供應商*產(chǎn)地*皮種量度組邏輯設(shè)計方法法—定義實體層層級日月季年物料物料分類供應商邏輯設(shè)計方法法—定義量度組組主題域量度組量度聚合計算公式說明生皮分選分選結(jié)果分選結(jié)果數(shù)量累加財務(wù)員工員工工資平均邏輯設(shè)計方法法—定義關(guān)系主題實體采購分選結(jié)果預算日期日**月*季年物料物料**物料分類邏輯設(shè)計的關(guān)關(guān)鍵因素粒度特例詳細設(shè)計物理模型是從從業(yè)務(wù)數(shù)據(jù)模模型創(chuàng)建而來來的，建立物物理模型通過過擴展業(yè)務(wù)數(shù)數(shù)據(jù)模型，使使模型中包含含關(guān)鍵字和物物理特性。物理模型設(shè)計計包括：設(shè)計存儲結(jié)構(gòu)構(gòu)——創(chuàng)建實體設(shè)計索引策略略——創(chuàng)建索引設(shè)計存儲策略略——創(chuàng)建分區(qū)設(shè)計存儲結(jié)構(gòu)構(gòu)——創(chuàng)建實體創(chuàng)建物理實體體表視圖約束創(chuàng)建事實表只包括主鍵、、外鍵和度量量事實表一個OLAP模型可以有多多個事實表事實表經(jīng)常有有millionsofrows事實往往是數(shù)數(shù)字‘量度’有些事實可以以被累加，另另一些不能最小粒度原則則不歡迎描述性性屬性（瘦瘦高vs.矮胖）創(chuàng)建維度表維度描述事實實邏輯上通過key關(guān)聯(lián)維度表往往包包含相當多的的屬性典型的屬性是是文本的、離離散的維度往往含有有層次主鍵往往是系系統(tǒng)產(chǎn)生的primarykey很可能是共享享的維度表設(shè)計常規(guī)維度主鍵代理鍵（可選選）名稱排序鍵（可選選）自定義匯總公公式（可選））父子維度父鍵一元運算符（（可選）數(shù)據(jù)倉庫的星星型結(jié)構(gòu)Employee_DimEmployeeKeyEmployeeID...EmployeeKeyTime_DimTimeKeyTheDate...TimeKeyProduct_DimProductKeyProductIDProduceNameProductBrandProductCategory...ProductKeyCustomer_DimCustomerKeyCustomerID...CustomerKeyShipper_DimShipperKeyShipperID...ShipperKeySales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...TimeKeyCustomerKeyShipperKeyProductKeyEmployeeKey多個外鍵事實維度鍵數(shù)據(jù)倉庫的雪雪花型結(jié)構(gòu)二級維度表Sales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...Product_Brand_IdProductBrandProductCategoryIDProduct_Category_IdProductCategoryProductCategoryIDProduct_DimProductKeyProductNameProductSizeProductBrandID主維度度表雪花型型結(jié)構(gòu)構(gòu)的特特點節(jié)省存存儲空空間一定程程度上上的范范式星型vs.雪花型型Whichoneisbetter?長期以以來的的爭論論兩種觀觀點各各有支支持者者爭論在在繼續(xù)續(xù)……目前看看來，，大部部分更更加傾傾向于于星型型支持星星型維維度的的論點點事實表表總會會是很很大的的，在在維度度表上上節(jié)省省的空空間相相對來來說是是很小小的增加了了數(shù)據(jù)據(jù)模型型的復復雜度度查詢操操作概概念上上更復復雜了了從數(shù)據(jù)據(jù)倉庫庫到多多維數(shù)數(shù)據(jù)庫庫的加加載時時間會會更長長因此，，只有有當維維度表表極大大，存存儲空空間是是個問問題時時，才才考慮慮雪花花型維維度簡而言言之，，最好好就用用星型型維度度即可可支持雪雪花型型維度度的論論點從數(shù)據(jù)據(jù)倉庫庫到多多維數(shù)數(shù)據(jù)庫庫的加加載過過程中中，雪雪花型型維度度的效效率更更高。。雪花型型維度度描述述了更更清晰晰的層層次概概念。。只有當當最終終用戶戶可能能直接接訪問問數(shù)據(jù)據(jù)倉庫庫時才才考慮慮星型型。（（而這這是不不被建建議的的）。。思考：：PrimaryKey與KeyPrimaryKeykeyNameColorSizeDescription1A001捷達銀灰…………2B003寶馬火紅…...……3C010切諾基寶石藍…………………………………………本身已已經(jīng)有有了Key的標識識以后后，是是否還還需要要PrimaryKey?技巧：：創(chuàng)建建數(shù)字字主鍵鍵數(shù)字之之間的的比較較永遠遠比字字符比比較快快得多多。物理存存儲時時，數(shù)數(shù)字簡簡單得得多，，因為為它們們長度度一樣樣。字字符則則不同同。內(nèi)存中中，字字符占占的空空間大大得多多。(4byte的指針針+文本長長度*2（Unicode)+2。數(shù)字字則僅僅有4bytes支持變變化維維度注意維度表表與事事實表表不是是絕對對的同一個個表，，可以以同時時是維維度表表與事事實表表同一個個表，，可以以有時時是維維度表表，有有時是是事實實表創(chuàng)建““父子子型””維度EmployeeManagerTheBoard<None>SteveBTheBoardBillGTheBoardJimAllSteveBPaulMaSteveBBobMuSteveBTodNPaulMaDavidVPaulMaPaulFleDavidV父子維維度緩慢變變化維維不變化化更新新增舉例：：當某某銷售售人員員從一一個部部門調(diào)調(diào)至另另一個個部門門，他他的歷歷史業(yè)業(yè)績該該如何何核算算？創(chuàng)建物物理模模型主鍵：：PK_，整整型自自增外鍵：：FK_排序鍵鍵：Sort_字符類類型：：nvarchar量度類類型：：float、numeric創(chuàng)建視視圖、、約束束視圖盡量少少用或或不用用視圖圖，以以避免免意想想不到到的邏邏輯陷陷阱約束盡量使使用約約束，，以確確保數(shù)數(shù)據(jù)的的完整整性。。正確確的數(shù)數(shù)據(jù)所所帶來來的益益處會會遠遠遠大于于ETL性性能的的損失失。設(shè)計索索引策策略——創(chuàng)建索索引創(chuàng)建索索引用用以提提高查查詢速速度避免索索引對對ETL的不不良影影響設(shè)計存存儲策策略——創(chuàng)建分分區(qū)分區(qū)存存儲后后的數(shù)數(shù)據(jù)單單元易易于：：重構(gòu)索引重組恢復監(jiān)控設(shè)計數(shù)數(shù)據(jù)更更新數(shù)據(jù)提取是捕獲獲源數(shù)數(shù)據(jù)的的過程程。