數據倉庫開發(fā)實例

數據倉庫與OLAP

廣東工業(yè)大學艾丹祥數據倉庫開發(fā)實例

案例背景Pub是美國的一家圖書出版公司，希望構建數據倉庫。其中銷售部門的需求比較急迫，希望能在短時間內實現對銷售業(yè)績的分析。根據Pub公司的情況，考慮采用金博爾的DMDW的開發(fā)模式，即先為銷售部門設計一個數據集市，其他部門數據集市的設計可參照此模型。收集項目需求準備：研究選定的業(yè)務過程（術語、步驟、度量方法）采訪：鉆研選定的業(yè)務過程（理解使其運作所需的分析、數據模型和技術）分析：業(yè)務過程相關的數據資源歸檔對于Pub公司：選定“銷售”業(yè)務過程后，通過采訪了解其與銷售分析相關的問題，如通過什么方式銷售、有哪些銷售地區(qū)等。收集項目需求基本業(yè)務狀況Pub公司在美國各地均有出版社，出版社負責聯系作者、出版圖書，已出版的圖書將由各地的代銷書店進行銷售。收集項目需求業(yè)務分析需求銷售部門最關心銷售的業(yè)績，主要通過銷售額和銷售量進行考核。數據倉庫開發(fā)實例業(yè)務分析需求希望針對每個出版社作圖書銷售業(yè)績分析。希望針對每個國家的出版社作圖書銷售業(yè)績分析。希望針對每個州的出版社作圖書銷售業(yè)績分析。希望針對每城市的出版社作圖書銷售業(yè)績分析。希望針對每本圖書作銷售業(yè)績分析。希望針對每類圖書做銷售業(yè)績分析。希望針對每年出版的圖書做銷售業(yè)績分析。希望針對每位作者所編寫的圖書做銷售業(yè)績分析。希望針對每個書店作圖書銷售業(yè)績分析。希望針對每個州的書店作圖書銷售業(yè)績分析。希望針對每個城市的書店作圖書銷售業(yè)績分析。希望針對年、月、日作銷售業(yè)績分析。數據倉庫開發(fā)實例銷售業(yè)務的總線矩陣業(yè)務實體業(yè)務過程出版社圖書書店出版日期銷售XXXX收集項目需求數據資源狀況公司的業(yè)務數據庫為Pubs數據庫收集項目需求數據資源狀況Pubs中與銷售業(yè)務有關的表根據據總總線線矩矩陣陣建建立立模模型型氣氣泡泡圖圖建立立多多維維數數據據模模型型業(yè)務實體業(yè)務過程出版社圖書書店銷售日期銷售XXXX訂單銷售日期圖書書店出版社銷售售日日期期建立立多多維維數數據據模模型型訂單銷售日期圖書書店出版社銷售售日日期期日期期維維度度的的粒粒度度級級別別通通常常為為一一天天日期期維維度度表表需需要要專專門門生生成成（（如如利利用用Excel工具））日期維維度表表中的的時間間范圍圍根據據業(yè)務務需求求決定定建立多多維數數據模模型銷售日日期日期維維度至至少要要包含含年、、月、、日期期三個個屬性性建立多多維數數據模模型DimDate屬性名稱功能描述FullDate日期DateName日期名Month月MonthName月名Year年其中FullDate、Month、MonthName形成層層次。。出版社社建立多多維數數據模模型訂單銷售日期圖書書店出版社出版社社建立多多維數數據模模型字段名稱說明pub_id出版社編號pub_name出版社名city所在市state所在州country所在國出版社社出版社社維度度可以以直接接包含含出版版社名名、市市、州州、國國家等等屬性性建立多多維數數據模模型DimPublisher屬性名稱功能描述pub_id出版社編號pub_name出版社名稱pub_city城市pub_state州pub_country國家其中pub_name、pub_city、pub_state、pub_country形成層層次。。書店建立多多維數數據模模型訂單出版日期圖書書店出版社書店建立多多維數數據模模型字段名稱說明stor_id書店編號stor_name書店名稱city所在市state所在州書店書店維維度可可以直直接包包含書書店名名、市市、州州等屬屬性建立多多維數數據模模型圖書建立多多維數數據模模型訂單出版日期圖書書店出版社圖書建立多多維數數據模模型字段名稱說明title_id圖書編號pub_id出版社編號title圖書名type圖書類型price圖書單價pubdate出版日期圖書圖書維維度可可以直直接包包含圖圖書名名、類類型、、出版版年等等屬性性建立多多維數數據模模型DimBook屬性名稱功能描述book_id圖書編號book_name圖書名book_type圖書類型pubyear出版年其中book_name、book_type形成層層次結結構。。圖書圖書維維度無無法直直接包包含作作者屬屬性建立多多維數數據模模型作者建立多多維數數據模模型訂單出版日期圖書書店出版社作者圖書作作者關關系作者建立多多維數數據模模型字段名稱說明au_id作者編號au_lname作者名au_fname作者姓作者作者維維度可可以直直接包包含作作者名名等屬屬性建立多多維數數據模模型DimAuthor屬性名稱功能描述au_id作者編號au_name作者名au_fname+au_lname圖書作作者關關系建立多多維數數據模模型訂單出版日期圖書書店出版社作者圖書作作者關關系圖書作作者關關系建立多多維數數據模模型字段名稱說明au_id作者編號title_id圖書編號建立多多維數數據模模型圖書作作者關關系圖書作作者關關系事事實表表只包包含鍵鍵值列列，不不包含含度量量值列列，其其目的的是為為了記記錄圖圖書和和作者者之間間的關關系事事實。。FactBookAuthor（中間事實表）屬性名稱功能描述鍵值列book_id圖書編號au_id作者編號訂單建立多多維數數據模模型訂單出版日期圖書書店出版社作者圖書作作者關關系訂單建立多多維數數據模模型字段名稱說明stor_id書店編號title_id圖書編號qty訂購數量ord_date訂購日期建立多多維數數據模模型訂單訂單事事實表表中要要包含含日期期、出出版社社、書書店、、圖書書等鍵鍵值列列，還還要包包含銷銷售額額、銷銷售量量2個度量量值列列，其其目的的是為為了記記錄銷銷售事事實。。建立多多維數數據模模型訂單FactSales屬性名稱來源功能描述鍵值列pub_idpublishers表出版社編號stor_idstores表書店編號book_idtitles表圖書編號ord_datesales表銷售日期度量值列qtysales表銷售量amount計算生成（qty*price）銷售額建立多多維數數據模模型最終，，確定定數據據集市市包括括以下下多維維數據據模型型：主題——圖書銷銷售業(yè)業(yè)績度量值值——圖書銷銷售量量圖書銷銷售額額維度——出版社社（國國別-州-城市-出版社社）作者（（作者者姓名名）圖書（（圖書書類別別-單一圖圖書，，圖書書出版版年））書店（（州-市-書店））銷售日日期（（年-月-日）Microsoft數據倉倉庫工工具集集核心數數據倉倉庫工工具集集——SQLServer關系引引擎（（RDBMS）：實實現關關系數數據庫庫SQLServerIntegrationService(SSIS):實現ETL系統SQLServerAnalysisService(SSAS):實現多多維數數據庫庫，支支持OLAP查詢，，實現現數據據挖掘掘SQLServerReportingService(SSRS):定義報報表開發(fā)和和管理理工具具：SQLServerBIDevelopmentStudio（BIDS）和SQLServerManagementStudio。Microsoft數據倉倉庫系系統的的體系系結構構源系統數據庫ETL聚合(支持OLAP)源系統數據庫源系統數據庫事實表和維度表度量和維度前端工具核心數據倉庫RDBMSSSISSSASSSRSETL過程利用SQLServerIntegrationService對Pubs數據庫庫進行行抽取取和整整理，，形成成Pubs_DW數據倉倉庫。。先將數數據從從源事事務數數據中中提取取到中中間數數據庫庫Pubs_Stage再對中中間數數據庫庫的數數據進進行轉轉換并并加載載到目目標關關系數數據倉倉庫Pubs_DWETL過程Pubs數據庫（業(yè)務數據庫）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSalesStoresDiscountsPubs_Stage（臨時數據庫）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（數據倉庫）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL過程利用DTS和SQLServerIntegrationService對Pubs數據庫庫進行行抽取取和整整理，，形成成Pubs_DW數據倉倉庫。。先將數數據從從源事事務數數據中中提取取到中中間數數據庫庫Pubs_Stage(DTS工具)再對中中間數數據庫庫的數數據進進行轉轉換并并加載載到目目標關關系數數據倉倉庫Pubs_DW(SSIS工具)ETL過程利用DTS將pubs數據庫庫和date.xls中的數數據直直接導導出到到pubs_stage數據庫庫中。。Pubs_Stage的結構構ETL過程新建pubs_DW數據倉倉庫ETL過程pubs_DW數據倉倉庫中中的DimDate表DimDate屬性名稱功能描述Sqlserver數據類型FullDate日期DatetimeDateName日期名varchar（20）Month月intMonthName月名varchar（20）Year年intETL過程pubs_DW數據倉倉庫中中的DimPublisher表DimPublisher屬性名稱功能描述Sqlserver數據類型pub_id出版社編號Varchar（4）pub_name出版社名稱varchar（40）pub_city城市varchar（20）pub_state州varchar（2）pub_country國家varchar（30）ETL過程pubs_DW數據倉倉庫中中的DimStore表DimStore屬性名稱功能描述Sqlserver數據類型store_id書店編號Varchar（4）store_name書店名稱varchar（40）store_city城市varchar（20）store_state州varchar（2）ETL過程pubs_DW數據倉倉庫中中的DimBook表DimBook屬性名稱功能描述Sqlserver數據類型book_id圖書編號Varchar（6）book_name圖書名varchar（80）book_type圖書類型varchar（12）pubyear出版年intETL過程pubs_DW數據倉倉庫中中的DimAuthor表DimAuthor屬性名稱功能描述Sqlserver數據類型au_id作者編號varchar（11）au_name作者名au_fname+au_lnamevarchar（80）ETL過程pubs_DW數據倉倉庫中中的FactBookAuthor表FactBookAuthor屬性名稱功能描述Sqlserver數據類型book_id圖書編號Varchar（6）au_id作者編號varchar（11）ETL過程pubs_DW數據倉倉庫中中的FactSales表FactSales屬性名稱來源功能描述Sqlserver數據類型pub_idpublishers表出版社編號Varchar（4）stor_idstores表書店編號Varchar（4）book_idtitles表圖書編號Varchar（6）ord_datesales表銷售日期datetimeqtysales表銷售量smallintamount計算生成（qty*price）銷售額moneyETL過程新建SSIS項目，，并將將pubs_stage數據庫庫中的的數據據轉換換并加加載到到pubs_DW數據倉倉庫中中。SSIS項目由由若干干SSIS程序包包的構構成，，其中中一個個為主主程序序包（（啟動動）。。SSIS程序包包由一一個控控制流流和若若干個個數據據流構構成。。多維數數據集集和OLAPOLAP的提出出（1993年）1981年圖靈獎獎獲得者者：埃德加·科德(EdgarFrankCodd)—“關系數據據庫之父父”多維數據據集和OLAP1995年OLAP專門研究究機構OLAPReport提出關于于OLAP的簡明定定義FASMI（FastAnalysisofSharedMultidimensionalInformation）?？焖傩裕ǎ‵ast）——OLAP系統必須須能快速速響應用用戶的分分析查詢詢要求，，對于用用戶大部部分分析析要求在在5秒鐘內作作出反應應，否則則超過30秒用戶可可能會失失去分析析的主線線索，影影響分析析質量。。多維數據據集和OLAP1995年OLAP專門研究究機構OLAPReport提出關于于OLAP的簡明定定義FASMI（FastAnalysisofSharedMultidimensionalInformation）。分析性（（Analysis）——OLAP系統能處處理任何何與用戶戶和應用用有關的的邏輯分分析和統統計分析析，在需需要的情情況下還還允許用用戶無需需編程就就可為分分析和生生成報表表定義一一些新的的特殊運運算，將將其作為為分析的的一部分分，并以以用戶理理想的方方式給出出報告。。用戶可可以在OLAP平臺上進進行數據據分析，，也可以以與其他他外部分分析工具具交互信信息，同同時應提提供靈活活開放的的報表處處理功能能，保存存分析結結果。多維數據據集和OLAP1995年OLAP專門研究究機構OLAPReport提出關于于OLAP的簡明定定義FASMI（FastAnalysisofSharedMultidimensionalInformation）。共享性（（Shared）——OLAP系統應能能實現在在多用戶戶環(huán)境下下的安全全保密要要求和并并發(fā)控制制。多個個用戶同同時使用用，能夠夠根據用用戶所屬屬的安全全級別，，讓他們們只能看看到自身身權限下下的信息息。多維數據據集和OLAP1995年OLAP專門研究究機構OLAPReport提出關于于OLAP的簡明定定義FASMI（FastAnalysisofSharedMultidimensionalInformation）。多維性（（Multidimensional）——OLAP系統能對對數據進進行多維維視圖和和分析，，是OLAP的關鍵屬屬性。多維數據據集和OLAP1995年OLAP專門研究究機構OLAPReport提出關于于OLAP的簡明定定義FASMI（FastAnalysisofSharedMultidimensionalInformation）。信息性（（Information）——OLAP系統具有有管理數數據和獲獲得信息息的能力力，能管管理大量量的數據據并即時時地獲得得用戶所所需信息息。多維數據據集和OLAPOLAP與數據倉倉庫關系系緊密OLAP分析與數數據倉庫庫的關系系十分緊緊密。數數據倉庫庫的建立立，解決決了依據據主題進進行數據據存儲的的問題，，提高了了數據的的存取速速度；而而OLAP分析構成成了數據據倉庫的的表現層層，將數數據倉庫庫中的數數據通過過不同的的維和指指標，靈靈活地展展現出來來，提高高了數據據的展現現能力，，進而提提高了數數據的分分析能力力。多維數據據集和OLAP多維數據據集多維數據據集（cube）——OLAP中的主要要對象，，通常也也稱作多多維立方方體。cube是一個數數據集合合，通常常由數據據倉庫的的子集構構造，把把一組維維度和度度量值合合理組織織，最后后匯總成成多維結結構。多維數據據集和OLAP多維數據據集銷售數量10000玩具電器銷售地區(qū)北京上海江蘇1234服裝化妝品產品時間（月）以時間、銷售地區(qū)、產品三個維度構成的多維數據集多維數據據集和OLAP的實現以Pubs_DW數據庫為為基礎，，利用SQLServerAnalysisService開發(fā)PubsAS多維數據據庫以支支持OLAP分析。Pubs_DW（關系數數據庫））FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateSSASPubs_AS（多維數數據集））SalesCube（Sales多維數據據集）Sales度量值組組：SalesAmountSalesQtyBookAuthor中間度量量值組維度：BookAuthorStorePublisherDate多維數據據集和OLAP的實現多維數據庫的數據源（DS）pubs_DW數據源視圖（DSV）多維數據庫pubs_AS多維數據集（Cube）度量值和維度聚合SSAS多維數據據庫體系系結構多維數據據集和OLAP的實現分析服務務項目開開發(fā)步驟驟創(chuàng)建數據據源創(chuàng)建數據據源視圖圖創(chuàng)建維度度（公共共維度））創(chuàng)建多維維數據集集（度量量值）處理、部部署項目目瀏覽多維維數據集集多維數據據集和OLAP的實現分析服務務項目開開發(fā)要點點每個分析析服務項項目必須須至少包包含一個個數據源源每個分析析服務項項目必須須至少包包含一個個數據源源視圖每個分析析服務項項目必須須包含至至少一個個維度和和多維數數據集多維數據據集必須須包含在在一個分分析服務務項目中中每個分析析服務項項目可以以包含多多個多維維數據集集多維數據據集和OLAP多維數據據分析方方法OLAP多維分析析對多維維數據集集中的數數據進行行上卷、下下鉆、切切片、切切塊、旋旋轉等各種分分析操作作，以便便剖析數數據，使使用戶從從多個角角度、多多個側面面、多個個層次來來觀察多多維數據據，從而而發(fā)掘數數據中蘊蘊涵的對對自己有有用的信信息。多維數據據集和OLAP多維數據據分析方方法多維數據據集和OLAP多維數據據分析方方法——切片切片（Slice）——對多維數數據集的的某個維維選定一一個維成成員，這這種選擇擇操作就就稱為切切片。例如：Cube（維1，維2，…，維i，…，維n，度量量值））對維i選定了了某個個維成成員，，則（維1，維2，…，維i成員，…，維n，度量量值））就是該該Cube在維i上的一一個切切片，，切片片的數數量取取決于于維i上的維維成員員的個個數。。多維數數據集集和OLAP多維數數據分分析方方法——切片“時間＝＝第1季度”在三維維立方方體上上切片片的結結果是是得到到一個個二維維的平平面數數據4.1OLAP概述多維數數據分分析方方法——切塊切塊（（Dice）——在一個個多維維數據據集中中對兩兩個及及兩個個以上上的維維選定定維成成員的的操作作可以以稱為為切塊塊。例如：：Cube（維1,維2,……,維i,……,維k,……,維n,度量值值）對維i，…，維k，選定定了維維成員員，則則（維1,維2,……,維i成員,……,維k成員,……,維n,度量值值)就是該該Cube在維i,……,維k上的一一個切切塊。。顯然，，當i=k時，切切塊操操作就就退化化成切切片操操作。。多維數數據集集和OLAP多維數數據分分析方方法——切塊(貸款＝＝“正?！眔r“次級”）And（時間間＝“1季度”or“2季度”）And（經濟濟性質質=“集體“or”個人”or”其它“）切塊的的結果果是得得到了了一個個子立立方體體多維數數據集集和OLAP多維數數據分分析方方法——上卷上卷（（Roll-up）——上卷是是對數數據進進行更更為宏宏觀的的觀察察。通通過在在維的的等級級中上上升或或通過過消除除某個個或某某些維