6數(shù)據(jù)倉庫開發(fā)實例

1、數(shù)據(jù)倉庫與OLAP廣東工業(yè)大學 艾丹祥數(shù)據(jù)倉庫庫開發(fā)實實例案例背景景Pub是美國的的一家圖圖書出版版公司，希望構構建數(shù)據(jù)據(jù)倉庫。其中銷銷售部門門的需求求比較急急迫，希希望能在在短時間間內實現(xiàn)現(xiàn)對銷售售業(yè)績的的分析。根據(jù)Pub公司的情情況，考考慮采用用金博爾爾的DMDW的開發(fā)模模式，即即先為銷銷售部門門設計一一個數(shù)據(jù)據(jù)集市，其他部部門數(shù)據(jù)據(jù)集市的的設計可可參照此此模型。收集項目目需求準備：研研究選定定的業(yè)務務過程（術語、步驟、度量方方法）采訪：鉆鉆研選定定的業(yè)務務過程（理解使使其運作作所需的的分析、數(shù)據(jù)模模型和技技術）分析：業(yè)業(yè)務過程程相關的的數(shù)據(jù)資資源歸檔對于Pub公司：選選定“銷銷售”業(yè)業(yè)務

2、過程程后，通通過采訪訪了解其其與銷售售分析相相關的問問題，如如通過什什么方式式銷售、有哪些些銷售地地區(qū)等。收集項目目需求基本業(yè)務務狀況Pub公司在美美國各地地均有出出版社，出版社社負責聯(lián)聯(lián)系作者者、出版版圖書，已出版版的圖書書將由各各地的代代銷書店店進行銷銷售。收集項目目需求業(yè)務分析析需求銷售部門門最關心心銷售的的業(yè)績，主要通通過銷售售額和銷銷售量進進行考核核。數(shù)據(jù)倉庫庫開發(fā)實實例業(yè)務分析析需求希望針對對每個出出版社作作圖書銷銷售業(yè)績績分析。希望針對對每個國國家的出出版社作作圖書銷銷售業(yè)績績分析。希望針對對每個州州的出版版社作圖圖書銷售售業(yè)績分分析。希望針對對每城市市的出版版社作圖圖書銷售售業(yè)

3、績分分析。希望針對對每本圖圖書作銷銷售業(yè)績績分析。希望針對對每類圖圖書做銷銷售業(yè)績績分析。希望針對對每年出出版的圖圖書做銷銷售業(yè)績績分析。希望針對對每位作作者所編編寫的圖圖書做銷銷售業(yè)績績分析。希望針對對每個書書店作圖圖書銷售售業(yè)績分分析。希望針對對每個州州的書店店作圖書書銷售業(yè)業(yè)績分析析。希望針對對每個城城市的書書店作圖圖書銷售售業(yè)績分分析。希望針對對年、月月、日作作銷售業(yè)業(yè)績分析析。數(shù)據(jù)倉庫庫開發(fā)實實例銷售業(yè)務務的總線線矩陣業(yè)務實體業(yè)務過程出版社圖書書店出版日期銷售XXXX收集項目目需求數(shù)據(jù)資源源狀況公司的業(yè)業(yè)務數(shù)據(jù)據(jù)庫為Pubs數(shù)據(jù)庫收集項目目需求數(shù)據(jù)資源源狀況Pubs中與銷售售業(yè)務有有

4、關的表表根據(jù)總線線矩陣建建立模型型氣泡圖圖建立多維維數(shù)據(jù)模模型業(yè)務實體業(yè)務過程出版社圖書書店銷售日期銷售XXXX訂單銷售日期圖書書店出版社銷售日期期建立多維維數(shù)據(jù)模模型訂單銷售日期圖書書店出版社銷售日期期日期維度度的粒度度級別通通常為一一天日期維度度表需要要專門生生成（如如利用Excel工具）日期維度度表中的的時間范范圍根據(jù)據(jù)業(yè)務需需求決定定建立多維維數(shù)據(jù)模模型銷售日期期日期維度度至少要要包含年年、月、日期三三個屬性性建立多維維數(shù)據(jù)模模型DimDate屬性名稱功能描述FullDate日期DateName日期名Month月MonthName月名Year年其中FullDate、Month、Mont

5、hName形成層次次。出版社建立多維維數(shù)據(jù)模模型訂單銷售日期圖書書店出版社出版社建立多維維數(shù)據(jù)模模型字段名稱說明pub_id出版社編號pub_name出版社名city所在市state所在州country所在國出版社出版社維維度可以以直接包包含出版版社名、市、州州、國家家等屬性性建立多維維數(shù)據(jù)模模型DimPublisher屬性名稱功能描述pub_id出版社編號pub_name出版社名稱pub_city城市pub_state州pub_country國家其中pub_name、pub_city、pub_state、pub_country形成層次次。書店建立多維維數(shù)據(jù)模模型訂單出版日期圖書書店出版社書店

6、建立多維維數(shù)據(jù)模模型字段名稱說明stor_id書店編號stor_name書店名稱city所在市state所在州書店書店維度度可以直直接包含含書店名名、市、州等屬屬性建立多維維數(shù)據(jù)模模型圖書建立多維維數(shù)據(jù)模模型訂單出版日期圖書書店出版社圖書建立多維維數(shù)據(jù)模模型字段名稱說明title_id圖書編號pub_id出版社編號title圖書名type圖書類型price圖書單價pubdate出版日期圖書圖書維度度可以直直接包含含圖書名名、類型型、出版版年等屬屬性建立多維維數(shù)據(jù)模模型DimBook屬性名稱功能描述book_id圖書編號book_name圖書名book_type圖書類型pubyear出版年其中b

7、ook_name、book_type形成層次次結構。圖書圖書維度度無法直直接包含含作者屬屬性建立多維維數(shù)據(jù)模模型作者建立多維維數(shù)據(jù)模模型訂單出版日期圖書書店出版社作者圖書作者者關系作者建立多維維數(shù)據(jù)模模型字段名稱說明au_id作者編號au_lname作者名au_fname作者姓作者作者維度度可以直直接包含含作者名名等屬性性建立多維維數(shù)據(jù)模模型DimAuthor屬性名稱功能描述au_id作者編號au_name作者名au_fname + au_lname圖書作者者關系建立多維維數(shù)據(jù)模模型訂單出版日期圖書書店出版社作者圖書作者者關系圖書作者者關系建立多維維數(shù)據(jù)模模型字段名稱說明au_id作者編號ti

8、tle_id圖書編號建立多維維數(shù)據(jù)模模型圖書作者者關系圖書作者者關系事事實表只只包含鍵鍵值列，不包含含度量值值列，其其目的是是為了記記錄圖書書和作者者之間的的關系事事實。FactBookAuthor（中間事實表）屬性名稱功能描述鍵值列book_id圖書編號au_id作者編號訂單建立多維維數(shù)據(jù)模模型訂單出版日期圖書書店出版社作者圖書作者者關系訂單建立多維維數(shù)據(jù)模模型字段名稱說明stor_id書店編號title_id圖書編號qty訂購數(shù)量ord_date訂購日期建立多維維數(shù)據(jù)模模型訂單訂單事實實表中要要包含日日期、出出版社、書店、圖書等等鍵值列列，還要要包含銷銷售額、銷售量量2個度量值值列，其其目

9、的是是為了記記錄銷售售事實。建立多維維數(shù)據(jù)模模型訂單FactSales屬性名稱來源功能描述鍵值列pub_idpublishers表出版社編號stor_idstores表書店編號book_idtitles表圖書編號ord_datesales表銷售日期度量值列qtysales表銷售量amount計算生成（qty*price）銷售額建立多維維數(shù)據(jù)模模型最終，確確定數(shù)據(jù)據(jù)集市包包括以下下多維數(shù)數(shù)據(jù)模型型：主題圖書銷售售業(yè)績度量值圖書銷售售量圖書銷售售額維度出版社（國別-州-城市-出版社）作者（作作者姓名名）圖書（圖圖書類別別-單一圖書書，圖書書出版年年）書店（州州-市-書店）銷售日期期（年-月-日）M

10、icrosoft數(shù)據(jù)倉庫庫工具集集核心數(shù)據(jù)據(jù)倉庫工工具集SQLServer關系引擎擎（RDBMS）：實現(xiàn)現(xiàn)關系數(shù)數(shù)據(jù)庫SQLServer Integration Service(SSIS):實現(xiàn)ETL系統(tǒng)SQLServer Analysis Service(SSAS):實現(xiàn)多維維數(shù)據(jù)庫庫，支持持OLAP查詢，實實現(xiàn)數(shù)據(jù)據(jù)挖掘SQLServer ReportingService(SSRS):定義報表表開發(fā)和管管理工具具：SQLServer BI Development Studio（BIDS）和SQLServer ManagementStudio。Microsoft數(shù)據(jù)倉庫庫系統(tǒng)的的體系結結構源

11、系統(tǒng)數(shù)據(jù)庫ETL聚合(支持OLAP)源系統(tǒng)數(shù)據(jù)庫源系統(tǒng)數(shù)據(jù)庫事實表和維度表度量和維度前端工具核心數(shù)據(jù)倉庫RDBMSSSISSSASSSRSETL過程利用SQLServer Integration Service對Pubs數(shù)據(jù)庫進進行抽取取和整理理，形成成Pubs_DW數(shù)據(jù)倉庫庫。先將數(shù)據(jù)據(jù)從源事事務數(shù)據(jù)據(jù)中提取取到中間間數(shù)據(jù)庫庫Pubs_Stage再對中間間數(shù)據(jù)庫庫的數(shù)據(jù)據(jù)進行轉轉換并加加載到目目標關系系數(shù)據(jù)倉倉庫Pubs_DWETL過程Pubs數(shù)據(jù)庫（業(yè)務數(shù)據(jù)庫）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSale

12、sStoresDiscountsPubs_Stage（臨時數(shù)據(jù)庫）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（數(shù)據(jù)倉庫）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL過程利用DTS和SQLServer Integration Service對Pubs數(shù)據(jù)庫進進行抽取取和整理理，形成成Pubs_DW數(shù)據(jù)倉庫庫。先將數(shù)據(jù)據(jù)從源事事務數(shù)據(jù)據(jù)中提取取到中間間數(shù)據(jù)庫庫Pubs_Stage(DTS工具)再對中間間數(shù)據(jù)庫

13、庫的數(shù)據(jù)據(jù)進行轉轉換并加加載到目目標關系系數(shù)據(jù)倉倉庫Pubs_DW(SSIS工具)ETL過程利用DTS將pubs數(shù)據(jù)庫和和date.xls中的數(shù)據(jù)據(jù)直接導導出到pubs_stage數(shù)據(jù)庫中中。Pubs_Stage的結構ETL過程新建pubs_DW數(shù)據(jù)倉庫庫ETL過程pubs_DW數(shù)據(jù)倉庫庫中的DimDate表DimDate屬性名稱功能描述Sqlserver數(shù)據(jù)類型FullDate日期DatetimeDateName日期名varchar（20）Month月intMonthName月名varchar（20）Year年intETL過程pubs_DW數(shù)據(jù)倉庫庫中的DimPublisher表DimPub

14、lisher屬性名稱功能描述Sqlserver數(shù)據(jù)類型pub_id出版社編號Varchar（4）pub_name出版社名稱varchar（40）pub_city城市varchar（20）pub_state州varchar（2）pub_country國家varchar（30）ETL過程pubs_DW數(shù)據(jù)倉庫庫中的DimStore表DimStore屬性名稱功能描述Sqlserver數(shù)據(jù)類型store_id書店編號Varchar（4）store_name書店名稱varchar（40）store_city城市varchar（20）store_state州varchar（2）ETL過程pubs_DW數(shù)據(jù)

15、倉庫庫中的DimBook表DimBook屬性名稱功能描述Sqlserver數(shù)據(jù)類型book_id圖書編號Varchar（6）book_name圖書名varchar（80）book_type圖書類型varchar（12）pubyear出版年intETL過程pubs_DW數(shù)據(jù)倉庫庫中的DimAuthor表DimAuthor屬性名稱功能描述Sqlserver數(shù)據(jù)類型au_id作者編號varchar（11）au_name作者名au_fname + au_lnamevarchar（80）ETL過程pubs_DW數(shù)據(jù)倉庫庫中的FactBookAuthor表FactBookAuthor屬性名稱功能描述Sql

16、server數(shù)據(jù)類型book_id圖書編號Varchar（6）au_id作者編號varchar（11）ETL過程pubs_DW數(shù)據(jù)倉庫庫中的FactSales表FactSales屬性名稱來源功能描述Sqlserver數(shù)據(jù)類型pub_idpublishers表出版社編號Varchar（4）stor_idstores表書店編號Varchar（4）book_idtitles表圖書編號Varchar（6）ord_datesales表銷售日期datetimeqtysales表銷售量smallintamount計算生成（qty*price）銷售額moneyETL過程新建SSIS項目，并并將pubs_sta

17、ge數(shù)據(jù)庫中中的數(shù)據(jù)據(jù)轉換并并加載到到pubs_DW數(shù)據(jù)倉庫庫中。SSIS項目由若若干SSIS程序包的的構成，其中一一個為主主程序包包（啟動動）。SSIS程序包由由一個控控制流和和若干個個數(shù)據(jù)流流構成。多維數(shù)據(jù)據(jù)集和OLAPOLAP的提出（1993年）1981年圖靈獎獎獲得者者：埃德加科德(EdgarFrankCodd)“關系數(shù)據(jù)據(jù)庫之父父”多維數(shù)據(jù)據(jù)集和OLAP1995年OLAP專門研究究機構OLAP Report提出關于于OLAP的簡明定定義FASMI（Fast Analysis of SharedMultidimensional Information）?？焖傩裕‵ast）OLAP系統(tǒng)必

18、須須能快速速響應用用戶的分分析查詢詢要求，對于用用戶大部部分分析析要求在在5秒鐘內作作出反應應，否則則超過30秒用戶可可能會失失去分析析的主線線索，影影響分析析質量。多維數(shù)據(jù)據(jù)集和OLAP1995年OLAP專門研究究機構OLAP Report提出關于于OLAP的簡明定定義FASMI（Fast Analysis of SharedMultidimensional Information）。分析性（Analysis）OLAP系統(tǒng)能處處理任何何與用戶戶和應用用有關的的邏輯分分析和統(tǒng)統(tǒng)計分析析，在需需要的情情況下還還允許用用戶無需需編程就就可為分分析和生生成報表表定義一一些新的的特殊運運算，將將其作為

19、為分析的的一部分分，并以以用戶理理想的方方式給出出報告。用戶可可以在OLAP平臺上進進行數(shù)據(jù)據(jù)分析，也可以以與其他他外部分分析工具具交互信信息，同同時應提提供靈活活開放的的報表處處理功能能，保存存分析結結果。多維數(shù)據(jù)據(jù)集和OLAP1995年OLAP專門研究究機構OLAP Report提出關于于OLAP的簡明定定義FASMI（Fast Analysis of SharedMultidimensional Information）。共享性（Shared）OLAP系統(tǒng)應能能實現(xiàn)在在多用戶戶環(huán)境下下的安全全保密要要求和并并發(fā)控制制。多個個用戶同同時使用用，能夠夠根據(jù)用用戶所屬屬的安全全級別，讓他們們只

20、能看看到自身身權限下下的信息息。多維數(shù)據(jù)據(jù)集和OLAP1995年OLAP專門研究究機構OLAP Report提出關于于OLAP的簡明定定義FASMI（Fast Analysis of SharedMultidimensional Information）。多維性（Multidimensional）OLAP系統(tǒng)能對對數(shù)據(jù)進進行多維維視圖和和分析，是OLAP的關鍵屬屬性。多維數(shù)據(jù)據(jù)集和OLAP1995年OLAP專門研究究機構OLAP Report提出關于于OLAP的簡明定定義FASMI（Fast Analysis of SharedMultidimensional Information）。信息性

21、（Information）OLAP系統(tǒng)具有有管理數(shù)數(shù)據(jù)和獲獲得信息息的能力力，能管管理大量量的數(shù)據(jù)據(jù)并即時時地獲得得用戶所所需信息息。多維數(shù)據(jù)據(jù)集和OLAPOLAP與數(shù)據(jù)倉倉庫關系系緊密OLAP分析與數(shù)數(shù)據(jù)倉庫庫的關系系十分緊緊密。數(shù)數(shù)據(jù)倉庫庫的建立立，解決決了依據(jù)據(jù)主題進進行數(shù)據(jù)據(jù)存儲的的問題，提高了了數(shù)據(jù)的的存取速速度；而而OLAP分析構成成了數(shù)據(jù)據(jù)倉庫的的表現(xiàn)層層，將數(shù)數(shù)據(jù)倉庫庫中的數(shù)數(shù)據(jù)通過過不同的的維和指指標，靈靈活地展展現(xiàn)出來來，提高高了數(shù)據(jù)據(jù)的展現(xiàn)現(xiàn)能力，進而提提高了數(shù)數(shù)據(jù)的分分析能力力。多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)集多維數(shù)據(jù)據(jù)集（cube）OLAP中的主要要對象，通常也也稱

22、作多多維立方方體。cube是一個數(shù)數(shù)據(jù)集合合，通常常由數(shù)據(jù)據(jù)倉庫的的子集構構造，把把一組維維度和度度量值合合理組織織，最后后匯總成成多維結結構。多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)集銷售數(shù)量10000玩具電器銷售地區(qū)北京上海江蘇 1 2 3 4服裝化妝品產品時間（月）以時間、銷售地區(qū)、產品三個維度構成的多維數(shù)據(jù)集多維數(shù)據(jù)據(jù)集和OLAP的實現(xiàn)以Pubs_DW數(shù)據(jù)庫為為基礎，利用SQLServer Analysis Service開發(fā)Pubs AS多維數(shù)據(jù)據(jù)庫以支支持OLAP分析。Pubs_DW（關系數(shù)數(shù)據(jù)庫）FactSalesFactBookAuthorDimPublisherDimBookDimA

23、uthorDimStoreDimDateSSASPubs_AS（多維數(shù)數(shù)據(jù)集）SalesCube（Sales多維數(shù)據(jù)據(jù)集）Sales度量值組組：SalesAmountSalesQtyBookAuthor中間度量量值組維度：BookAuthorStorePublisherDate多維數(shù)據(jù)據(jù)集和OLAP的實現(xiàn)多維數(shù)據(jù)庫的數(shù)據(jù)源（DS）pubs_DW數(shù)據(jù)源視圖（DSV）多維數(shù)據(jù)庫pubs_AS多維數(shù)據(jù)集（Cube）度量值和維度聚合SSAS多維數(shù)據(jù)據(jù)庫體系系結構多維數(shù)據(jù)據(jù)集和OLAP的實現(xiàn)分析服務務項目開開發(fā)步驟驟創(chuàng)建數(shù)據(jù)據(jù)源創(chuàng)建數(shù)據(jù)據(jù)源視圖圖創(chuàng)建維度度（公共共維度）創(chuàng)建多維維數(shù)據(jù)集集（度量量值）處理

24、、部部署項目目瀏覽多維維數(shù)據(jù)集集多維數(shù)據(jù)據(jù)集和OLAP的實現(xiàn)分析服務務項目開開發(fā)要點點每個分析析服務項項目必須須至少包包含一個個數(shù)據(jù)源源每個分析析服務項項目必須須至少包包含一個個數(shù)據(jù)源源視圖每個分析析服務項項目必須須包含至至少一個個維度和和多維數(shù)數(shù)據(jù)集多維數(shù)據(jù)據(jù)集必須須包含在在一個分分析服務務項目中中每個分析析服務項項目可以以包含多多個多維維數(shù)據(jù)集集多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)分析方方法OLAP多維分析析對多維維數(shù)據(jù)集集中的數(shù)數(shù)據(jù)進行行上卷、下下鉆、切切片、切切塊、旋旋轉等各種分分析操作作，以便便剖析數(shù)數(shù)據(jù)，使使用戶從從多個角角度、多多個側面面、多個個層次來來觀察多多維數(shù)據(jù)據(jù)，從而而發(fā)掘數(shù)數(shù)據(jù)中蘊蘊涵的對對自己有有用的信信息。多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)分析方方法多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)分析方方法切片切片（Slice）對多維數(shù)數(shù)據(jù)集的的某個維維選定一一個維成成員，這這種選擇擇操作就就稱為切切片。例如：Cube（維1，維2，維i，維n，度量值值）對維i選定了某某個維成成員，則則（維1，維2，維i成員，維n，度量值值）就是該Cube在維i上的一個個切片，切片的的數(shù)量取取決于維維i上的維成成員的個個數(shù)。多維數(shù)據(jù)據(jù)集和OLAP多維數(shù)據(jù)據(jù)分析方方