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

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

2、么方式銷售、有哪些銷售地域等。數(shù)據(jù)倉庫開發(fā)實例詳解第4頁搜集項目需求基本業(yè)務情況Pub企業(yè)在美國各地都有出版社，出版社負責聯(lián)絡作者、出版圖書，已出版圖書將由各地代銷書店進行銷售。數(shù)據(jù)倉庫開發(fā)實例詳解第5頁搜集項目需求業(yè)務分析需求銷售部門最關心銷售業(yè)績，主要經(jīng)過銷售額和銷售量進行考評。數(shù)據(jù)倉庫開發(fā)實例詳解第6頁數(shù)據(jù)倉庫開發(fā)實例業(yè)務分析需求希望針對每個出版社作圖書銷售業(yè)績分析。希望針對每個國家出版社作圖書銷售業(yè)績分析。希望針對每個州出版社作圖書銷售業(yè)績分析。希望針對每城市出版社作圖書銷售業(yè)績分析。希望針對每本圖書作銷售業(yè)績分析。希望針對每類圖書做銷售業(yè)績分析。希望針對每年出版圖書做銷售業(yè)績分析。希

3、望針對每位作者所編寫圖書做銷售業(yè)績分析。希望針對每個書店作圖書銷售業(yè)績分析。希望針對每個州書店作圖書銷售業(yè)績分析。希望針對每個城市書店作圖書銷售業(yè)績分析。希望針對年、月、日作銷售業(yè)績分析。 數(shù)據(jù)倉庫開發(fā)實例詳解第7頁數(shù)據(jù)倉庫開發(fā)實例銷售業(yè)務總線矩陣業(yè)務實體業(yè)務過程出版社圖書書店出版日期銷售XXXX數(shù)據(jù)倉庫開發(fā)實例詳解第8頁搜集項目需求數(shù)據(jù)資源情況企業(yè)業(yè)務數(shù)據(jù)庫為Pubs數(shù)據(jù)庫數(shù)據(jù)倉庫開發(fā)實例詳解第9頁搜集項目需求數(shù)據(jù)資源情況Pubs中與銷售業(yè)務相關表數(shù)據(jù)倉庫開發(fā)實例詳解第10頁依據(jù)總線矩陣建立模型氣泡圖建立多維數(shù)據(jù)模型業(yè)務實體業(yè)務過程出版社圖書書店銷售日期銷售XXXX訂單銷售日期圖書書店出版社

4、數(shù)據(jù)倉庫開發(fā)實例詳解第11頁銷售日期建立多維數(shù)據(jù)模型訂單銷售日期圖書書店出版社數(shù)據(jù)倉庫開發(fā)實例詳解第12頁銷售日期日期維度粒度級別通常為一天日期維度表需要專門生成（如利用Excel工具）日期維度表中時間范圍依據(jù)業(yè)務需求決定建立多維數(shù)據(jù)模型數(shù)據(jù)倉庫開發(fā)實例詳解第13頁銷售日期日期維度最少要包含年、月、日期三個屬性建立多維數(shù)據(jù)模型DimDate屬性名稱功效描述FullDate日期DateName日期名Month月MonthName月名Year年其中FullDate、Month、MonthName形成層次。數(shù)據(jù)倉庫開發(fā)實例詳解第14頁出版社建立多維數(shù)據(jù)模型訂單銷售日期圖書書店出版社數(shù)據(jù)倉庫開發(fā)實例詳

5、解第15頁出版社建立多維數(shù)據(jù)模型字段名稱說明pub_id出版社編號pub_name出版社名city所在市state所在州country所在國數(shù)據(jù)倉庫開發(fā)實例詳解第16頁出版社出版社維度能夠直接包含出版社名、市、州、國家等屬性建立多維數(shù)據(jù)模型DimPublisher屬性名稱功效描述pub_id出版社編號pub_name出版社名稱pub_city城市pub_state州pub_country國家其中pub_name、pub_city、pub_state、pub_country形成層次。數(shù)據(jù)倉庫開發(fā)實例詳解第17頁書店建立多維數(shù)據(jù)模型訂單出版日期圖書書店出版社數(shù)據(jù)倉庫開發(fā)實例詳解第18頁書店建立多維

6、數(shù)據(jù)模型字段名稱說明stor_id書店編號stor_name書店名稱city所在市state所在州數(shù)據(jù)倉庫開發(fā)實例詳解第19頁書店書店維度能夠直接包含書店名、市、州等屬性建立多維數(shù)據(jù)模型數(shù)據(jù)倉庫開發(fā)實例詳解第20頁圖書建立多維數(shù)據(jù)模型訂單出版日期圖書書店出版社數(shù)據(jù)倉庫開發(fā)實例詳解第21頁圖書建立多維數(shù)據(jù)模型字段名稱說明title_id圖書編號pub_id出版社編號title圖書名type圖書類型price圖書單價pubdate出版日期數(shù)據(jù)倉庫開發(fā)實例詳解第22頁圖書圖書維度能夠直接包含圖書名、類型、出版年等屬性建立多維數(shù)據(jù)模型DimBook屬性名稱功效描述book_id圖書編號book_nam

7、e圖書名book_type圖書類型pubyear出版年其中book_name、book_type形成層次結(jié)構(gòu)。數(shù)據(jù)倉庫開發(fā)實例詳解第23頁圖書圖書維度無法直接包含作者屬性建立多維數(shù)據(jù)模型數(shù)據(jù)倉庫開發(fā)實例詳解第24頁作者建立多維數(shù)據(jù)模型訂單出版日期圖書書店出版社作者圖書作者關系數(shù)據(jù)倉庫開發(fā)實例詳解第25頁作者建立多維數(shù)據(jù)模型字段名稱說明au_id作者編號au_lname作者名au_fname作者姓數(shù)據(jù)倉庫開發(fā)實例詳解第26頁作者作者維度能夠直接包含作者名等屬性建立多維數(shù)據(jù)模型DimAuthor屬性名稱功效描述au_id作者編號au_name作者名au_fname + au_lname數(shù)據(jù)倉庫開發(fā)

8、實例詳解第27頁圖書作者關系建立多維數(shù)據(jù)模型訂單出版日期圖書書店出版社作者圖書作者關系數(shù)據(jù)倉庫開發(fā)實例詳解第28頁圖書作者關系建立多維數(shù)據(jù)模型字段名稱說明au_id作者編號title_id圖書編號數(shù)據(jù)倉庫開發(fā)實例詳解第29頁建立多維數(shù)據(jù)模型圖書作者關系圖書作者關系事實表只包含鍵值列，不包含度量值列，其目標是為了統(tǒng)計圖書和作者之間關系事實。FactBookAuthor（中間事實表）屬性名稱功效描述鍵值列book_id圖書編號au_id作者編號數(shù)據(jù)倉庫開發(fā)實例詳解第30頁訂單建立多維數(shù)據(jù)模型訂單出版日期圖書書店出版社作者圖書作者關系數(shù)據(jù)倉庫開發(fā)實例詳解第31頁訂單建立多維數(shù)據(jù)模型字段名稱說明sto

9、r_id書店編號title_id圖書編號qty訂購數(shù)量ord_date訂購日期數(shù)據(jù)倉庫開發(fā)實例詳解第32頁建立多維數(shù)據(jù)模型訂單訂單事實表中要包含日期、出版社、書店、圖書等鍵值列，還要包含銷售額、銷售量2個度量值列，其目是為了記錄銷售事實。數(shù)據(jù)倉庫開發(fā)實例詳解第33頁建立多維數(shù)據(jù)模型訂單FactSales屬性名稱起源功效描述鍵值列pub_idpublishers表出版社編號stor_idstores表書店編號book_idtitles表圖書編號ord_datesales表銷售日期度量值列qtysales表銷售量amount計算生成（qty*price）銷售額數(shù)據(jù)倉庫開發(fā)實例詳解第34頁建立多維數(shù)

10、據(jù)模型最終，確定數(shù)據(jù)集市包含以下多維數(shù)據(jù)模型：主題圖書銷售業(yè)績度量值圖書銷售量圖書銷售額維度出版社（國別-州-城市-出版社）作者（作者姓名）圖書（圖書類別-單一圖書，圖書出版年）書店（州-市-書店）銷售日期（年-月-日）數(shù)據(jù)倉庫開發(fā)實例詳解第35頁Microsoft 數(shù)據(jù)倉庫工具集關鍵數(shù)據(jù)倉庫工具集SQL Server關系引擎（RDBMS）：實現(xiàn)關系數(shù)據(jù)庫SQL Server Integration Service(SSIS):實現(xiàn)ETL系統(tǒng)SQL Server Analysis Service(SSAS):實現(xiàn)多維數(shù)據(jù)庫，支持OLAP查詢，實現(xiàn)數(shù)據(jù)挖掘SQL Server Reporting

11、 Service(SSRS): 定義報表開發(fā)和管理工具：SQL Server BI Development Studio（BIDS）和SQL Server Management Studio。數(shù)據(jù)倉庫開發(fā)實例詳解第36頁Microsoft 數(shù)據(jù)倉庫系統(tǒng)體系結(jié)構(gòu)源系統(tǒng)數(shù)據(jù)庫ETL聚合(支持OLAP)源系統(tǒng)數(shù)據(jù)庫源系統(tǒng)數(shù)據(jù)庫事實表和維度表度量和維度前端工具關鍵數(shù)據(jù)倉庫RDBMSSSISSSASSSRS數(shù)據(jù)倉庫開發(fā)實例詳解第37頁ETL過程利用SQL Server Integration Service 對Pubs數(shù)據(jù)庫進行抽取和整理，形成Pubs_DW數(shù)據(jù)倉庫。先將數(shù)據(jù)從源事務數(shù)據(jù)中提取到中間數(shù)據(jù)

12、庫Pubs_Stage再對中間數(shù)據(jù)庫數(shù)據(jù)進行轉(zhuǎn)換并加載到目標關系數(shù)據(jù)倉庫Pubs_DW數(shù)據(jù)倉庫開發(fā)實例詳解第38頁ETL過程Pubs數(shù)據(jù)庫（業(yè)務數(shù)據(jù)庫）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSalesStoresDiscountsPubs_Stage（暫時數(shù)據(jù)庫）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（數(shù)據(jù)倉庫）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDat

13、eDTSSSISDate.xls數(shù)據(jù)倉庫開發(fā)實例詳解第39頁ETL過程利用DTS和SQL Server Integration Service 對Pubs數(shù)據(jù)庫進行抽取和整理，形成Pubs_DW數(shù)據(jù)倉庫。先將數(shù)據(jù)從源事務數(shù)據(jù)中提取到中間數(shù)據(jù)庫Pubs_Stage(DTS工具)再對中間數(shù)據(jù)庫數(shù)據(jù)進行轉(zhuǎn)換并加載到目標關系數(shù)據(jù)倉庫Pubs_DW(SSIS工具)數(shù)據(jù)倉庫開發(fā)實例詳解第40頁ETL過程利用DTS將pubs數(shù)據(jù)庫和date.xls中數(shù)據(jù)直接導出到pubs_stage數(shù)據(jù)庫中。Pubs_Stage結(jié)構(gòu)數(shù)據(jù)倉庫開發(fā)實例詳解第41頁ETL過程新建pubs_DW數(shù)據(jù)倉庫數(shù)據(jù)倉庫開發(fā)實例詳解第42頁

14、ETL過程pubs_DW數(shù)據(jù)倉庫中DimDate表DimDate屬性名稱功效描述Sqlserver數(shù)據(jù)類型FullDate日期DatetimeDateName日期名varchar（20）Month月intMonthName月名varchar（20）Year年int數(shù)據(jù)倉庫開發(fā)實例詳解第43頁ETL過程pubs_DW數(shù)據(jù)倉庫中DimPublisher表DimPublisher屬性名稱功效描述Sqlserver數(shù)據(jù)類型pub_id出版社編號Varchar（4）pub_name出版社名稱varchar（40）pub_city城市varchar（20）pub_state州varchar（2）pub_c

15、ountry國家varchar（30）數(shù)據(jù)倉庫開發(fā)實例詳解第44頁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）數(shù)據(jù)倉庫開發(fā)實例詳解第45頁ETL過程pubs_DW數(shù)據(jù)倉庫中DimBook表DimBook屬性名稱功效描述Sqlserver數(shù)據(jù)類型book_id圖書編號Varchar（6）book_name圖書名varchar（80）book_type

16、圖書類型varchar（12）pubyear出版年int數(shù)據(jù)倉庫開發(fā)實例詳解第46頁ETL過程pubs_DW數(shù)據(jù)倉庫中DimAuthor表DimAuthor屬性名稱功效描述Sqlserver數(shù)據(jù)類型au_id作者編號varchar（11）au_name作者名au_fname + au_lnamevarchar（80）數(shù)據(jù)倉庫開發(fā)實例詳解第47頁ETL過程pubs_DW數(shù)據(jù)倉庫中FactBookAuthor表FactBookAuthor屬性名稱功效描述Sqlserver數(shù)據(jù)類型book_id圖書編號Varchar（6）au_id作者編號varchar（11）數(shù)據(jù)倉庫開發(fā)實例詳解第48頁ETL過程

17、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）銷售額money數(shù)據(jù)倉庫開發(fā)實例詳解第49頁ETL過程新建SSIS項目，并將pubs_stage數(shù)據(jù)庫中數(shù)據(jù)轉(zhuǎn)換并加載到pubs_DW數(shù)據(jù)倉庫中。SSIS項目由若干SSIS程序包組成

18、，其中一個為主程序包（開啟）。SSIS程序包由一個控制流和若干個數(shù)據(jù)流組成。數(shù)據(jù)倉庫開發(fā)實例詳解第50頁多維數(shù)據(jù)集和OLAPOLAP提出（1993年）1981年圖靈獎取得者：埃德加科德(Edgar Frank Codd)“關系數(shù)據(jù)庫之父” 數(shù)據(jù)倉庫開發(fā)實例詳解第51頁多維數(shù)據(jù)集和OLAP1995年OLAP專門研究機構(gòu)OLAP Report提出關于OLAP簡明定義FASMI （Fast Analysis of Shared Multidimensional Information）。快速性（Fast）OLAP系統(tǒng)必須能快速響應用戶分析查詢要求，對于用戶大部分分析要求在5秒鐘內(nèi)作出反應，不然超出3

19、0秒用戶可能會失去分析根本索，影響分析質(zhì)量。數(shù)據(jù)倉庫開發(fā)實例詳解第52頁多維數(shù)據(jù)集和OLAP1995年OLAP專門研究機構(gòu)OLAP Report提出關于OLAP簡明定義FASMI （Fast Analysis of Shared Multidimensional Information）。分析性（Analysis）OLAP系統(tǒng)能處理任何與用戶和應用相關邏輯分析和統(tǒng)計分析，在需要情況下還允許用戶無需編程就可為分析和生成報表定義一些新特殊運算，將其作為分析一部分，并以用戶理想方式給出匯報。用戶能夠在OLAP平臺上進行數(shù)據(jù)分析，也能夠與其它外部分析工具交互信息，同時應提供靈活開放報表處理功效，保留分

20、析結(jié)果。數(shù)據(jù)倉庫開發(fā)實例詳解第53頁多維數(shù)據(jù)集和OLAP1995年OLAP專門研究機構(gòu)OLAP Report提出關于OLAP簡明定義FASMI （Fast Analysis of Shared Multidimensional Information）。共享性（Shared）OLAP系統(tǒng)應能實現(xiàn)在多用戶環(huán)境下安全保密要求和并發(fā)控制。多個用戶同時使用，能夠依據(jù)用戶所屬安全級別，讓他們只能看到本身權限下信息。數(shù)據(jù)倉庫開發(fā)實例詳解第54頁多維數(shù)據(jù)集和OLAP1995年OLAP專門研究機構(gòu)OLAP Report提出關于OLAP簡明定義FASMI （Fast Analysis of Shared Mul

21、tidimensional Information）。多維性（Multidimensional）OLAP系統(tǒng)能對數(shù)據(jù)進行多維視圖和分析，是OLAP關鍵屬性。數(shù)據(jù)倉庫開發(fā)實例詳解第55頁多維數(shù)據(jù)集和OLAP1995年OLAP專門研究機構(gòu)OLAP Report提出關于OLAP簡明定義FASMI （Fast Analysis of Shared Multidimensional Information）。信息性（Information）OLAP系統(tǒng)含有管理數(shù)據(jù)和取得信息能力，能管理大量數(shù)據(jù)并即時地取得用戶所需信息。數(shù)據(jù)倉庫開發(fā)實例詳解第56頁多維數(shù)據(jù)集和OLAPOLAP與數(shù)據(jù)倉庫關系緊密OLAP分析

22、與數(shù)據(jù)倉庫關系十分緊密。數(shù)據(jù)倉庫建立，處理了依據(jù)主題進行數(shù)據(jù)存放問題，提升了數(shù)據(jù)存取速度；而OLAP分析組成了數(shù)據(jù)倉庫表現(xiàn)層，將數(shù)據(jù)倉庫中數(shù)據(jù)經(jīng)過不一樣維和指標，靈活地展現(xiàn)出來，提升了數(shù)據(jù)展現(xiàn)能力，進而提升了數(shù)據(jù)分析能力。數(shù)據(jù)倉庫開發(fā)實例詳解第57頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)集多維數(shù)據(jù)集（cube）OLAP中主要對象，通常也稱作多維立方體。cube是一個數(shù)據(jù)集合，通常由數(shù)據(jù)倉庫子集結(jié)構(gòu)，把一組維度和度量值合理組織，最終匯總成多維結(jié)構(gòu)。數(shù)據(jù)倉庫開發(fā)實例詳解第58頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)集銷售數(shù)量10000玩具電器銷售地域北京上海江蘇 1 2 3 4服裝化裝品產(chǎn)品時間（月）以時間、銷售地域

23、、產(chǎn)品三個維度組成多維數(shù)據(jù)集數(shù)據(jù)倉庫開發(fā)實例詳解第59頁多維數(shù)據(jù)集和OLAP實現(xiàn)以Pubs_DW數(shù)據(jù)庫為基礎，利用SQL Server Analysis Service開發(fā)Pubs AS多維數(shù)據(jù)庫以支持OLAP分析。Pubs_DW（關系數(shù)據(jù)庫）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateSSASPubs_AS（多維數(shù)據(jù)集）Sales Cube（Sales多維數(shù)據(jù)集）Sales度量值組：Sales AmountSales QtyBookAuthor中間度量值組維度：BookAuthorStorePublish

24、erDate數(shù)據(jù)倉庫開發(fā)實例詳解第60頁多維數(shù)據(jù)集和OLAP實現(xiàn)多維數(shù)據(jù)庫數(shù)據(jù)源（DS）pubs_DW數(shù)據(jù)源視圖（DSV）多維數(shù)據(jù)庫pubs_AS多維數(shù)據(jù)集（Cube）度量值和維度聚合SSAS多維數(shù)據(jù)庫體系結(jié)構(gòu)數(shù)據(jù)倉庫開發(fā)實例詳解第61頁多維數(shù)據(jù)集和OLAP實現(xiàn)分析服務項目開發(fā)步驟創(chuàng)建數(shù)據(jù)源創(chuàng)建數(shù)據(jù)源視圖創(chuàng)建維度（公共維度）創(chuàng)建多維數(shù)據(jù)集（度量值）處理、布署項目瀏覽多維數(shù)據(jù)集數(shù)據(jù)倉庫開發(fā)實例詳解第62頁多維數(shù)據(jù)集和OLAP實現(xiàn)分析服務項目開發(fā)關鍵點每個分析服務項目必須最少包含一個數(shù)據(jù)源每個分析服務項目必須最少包含一個數(shù)據(jù)源視圖每個分析服務項目必須包含最少一個維度和多維數(shù)據(jù)集多維數(shù)據(jù)集必須包含在

25、一個分析服務項目中每個分析服務項目能夠包含多個多維數(shù)據(jù)集數(shù)據(jù)倉庫開發(fā)實例詳解第63頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)分析方法OLAP多維分析對多維數(shù)據(jù)集中數(shù)據(jù)進行上卷、下鉆、切片、切塊、旋轉(zhuǎn)等各種分析操作，方便剖析數(shù)據(jù)，使用戶從多個角度、多個側(cè)面、多個層次來觀察多維數(shù)據(jù)，從而發(fā)掘數(shù)據(jù)中蘊涵對自己有用信息。數(shù)據(jù)倉庫開發(fā)實例詳解第64頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)分析方法數(shù)據(jù)倉庫開發(fā)實例詳解第65頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)分析方法切片切片（Slice）對多維數(shù)據(jù)集某個維選定一個維組員，這種選擇操作就稱為切片。比如：Cube（維1，維2，維i，維n，度量值）對維i選定了某個維組員，則（維1，維2，維i組員，維n，度量值）就是該Cube在維i上一個切片，切片數(shù)量取決于維i上維組員個數(shù)。數(shù)據(jù)倉庫開發(fā)實例詳解第66頁多維數(shù)據(jù)集和OLAP多維數(shù)據(jù)分析方法切片“時間第1季度” 在三維立方體上切片結(jié)果是得到一個