基于SQLServer的數(shù)據(jù)倉庫構(gòu)建及OLAP多維分析實驗

基于SQLServer2005

的數(shù)據(jù)倉庫構(gòu)建與OLAP分析實驗2內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準(zhǔn)備數(shù)據(jù)源三、根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析3一背景概述4SQLServer

數(shù)據(jù)倉庫的BI架構(gòu)數(shù)據(jù)轉(zhuǎn)換中央數(shù)據(jù)倉庫信息展現(xiàn)業(yè)務(wù)用戶源數(shù)據(jù)數(shù)據(jù)獲取數(shù)據(jù)管理數(shù)據(jù)使用項目管理數(shù)據(jù)倉庫實施指導(dǎo)方法論業(yè)務(wù)規(guī)劃信息評估邏輯數(shù)據(jù)模型設(shè)計物理數(shù)據(jù)模型設(shè)計數(shù)據(jù)庫管理元數(shù)據(jù)管理應(yīng)用開發(fā)顧問咨詢知識轉(zhuǎn)移TMIS系統(tǒng)客運系統(tǒng)外部信息MIS系統(tǒng)抽取轉(zhuǎn)化過濾加載隨即查詢報表多維分析數(shù)據(jù)挖掘Portal集成RelationalTransformationOLAPDataMining數(shù)據(jù)集市SQLServer2005直接用戶(客戶端)Olap用戶Web用戶應(yīng)用服務(wù)器決策者分析員研究員查詢?nèi)藛T一背景概述5一背景概述SSISSSRS報表/隨即查詢SSASPortalTMIS系統(tǒng)客運系統(tǒng)外部信息RelationalTransformationOLAPDataMiningSQLServer2005MIS系統(tǒng)

多維分析/數(shù)據(jù)挖掘門戶集成ETL工具數(shù)據(jù)轉(zhuǎn)換中央數(shù)據(jù)倉庫

信息展現(xiàn)應(yīng)用系統(tǒng)源數(shù)據(jù)數(shù)據(jù)獲取數(shù)據(jù)管理數(shù)據(jù)使用SQLServer

數(shù)據(jù)倉庫的BI架構(gòu)6一背景概述Employee_DimEmployeeKeyEmployeeID...EmployeeKeyTime_DimTimeKeyTheDate...TimeKeyProduct_DimProductKeyProductIDProduceNameProductBrandProductCategory...ProductKeyCustomer_DimCustomerKeyCustomerID...CustomerKeyShipper_DimShipperKeyShipperID...ShipperKeySales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...TimeKeyCustomerKeyShipperKeyProductKeyEmployeeKey多個外鍵事實維度鍵數(shù)據(jù)倉庫的星型結(jié)構(gòu)9

設(shè)計數(shù)據(jù)倉庫 導(dǎo)入數(shù)據(jù)倉庫

建設(shè)Cubes

查詢數(shù)據(jù)1342Cube多維數(shù)據(jù)集數(shù)據(jù)倉庫源系統(tǒng)客戶端查詢工具報表分析數(shù)據(jù)挖掘一背景概述10

設(shè)計數(shù)據(jù)倉庫 導(dǎo)入數(shù)據(jù)倉庫

建設(shè)Cubes

查詢數(shù)據(jù)1423Cube多維數(shù)據(jù)集數(shù)據(jù)倉庫源系統(tǒng)客戶端查詢工具報表分析數(shù)據(jù)挖掘一背景概述11內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準(zhǔn)備數(shù)據(jù)源三、根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析二準(zhǔn)備數(shù)據(jù)源

打開SqlServer2005ManegementStudio查看是否已經(jīng)存在Northwind數(shù)據(jù)庫如果不存在，通過數(shù)據(jù)庫任務(wù)——附加，創(chuàng)建并導(dǎo)入Northwind數(shù)據(jù)庫（或運行instnwnd.sql即可）創(chuàng)建新數(shù)據(jù)庫Northwind_DW13內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準(zhǔn)備數(shù)據(jù)源三、根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析二根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫

詳見“03聯(lián)機分析處理.ppt”中的數(shù)據(jù)倉庫案例分析包括：了解Northwind數(shù)據(jù)庫明確業(yè)務(wù)需求（即數(shù)據(jù)倉庫主題）識別事實與維度設(shè)計事實表設(shè)計維度表在Northwind_DW中創(chuàng)建相應(yīng)的事實表與維度表（或運行createnorthwinddw.sql即可）15內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準(zhǔn)備數(shù)據(jù)源三、根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析16三IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL數(shù)據(jù)從數(shù)據(jù)源向目標(biāo)數(shù)據(jù)倉庫抽取（Extract）、轉(zhuǎn)換（Transform）、裝載（Load）的過程構(gòu)建數(shù)據(jù)倉庫的重要一環(huán)，用戶從數(shù)據(jù)源抽取出所需的數(shù)據(jù)，經(jīng)過數(shù)據(jù)清洗，最終按照預(yù)先定義好的數(shù)據(jù)倉庫模型，將數(shù)據(jù)加載到數(shù)據(jù)倉庫中去。抽?。簩?shù)據(jù)從各種原始的業(yè)務(wù)系統(tǒng)中讀取出來。轉(zhuǎn)換：按照預(yù)先設(shè)計好的規(guī)則將抽取得數(shù)據(jù)進行轉(zhuǎn)換、清洗，以及處理一些冗余、歧義的數(shù)據(jù)，使本來異構(gòu)的數(shù)據(jù)格式能統(tǒng)一起來。裝載：將轉(zhuǎn)換完的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)倉庫中。17ETL舉例導(dǎo)入合并計算查找二IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

SQLServer商務(wù)智能開發(fā)平臺是一個圖形工具，它使創(chuàng)建和編輯SSIS包的工作變得更加簡單和輕松，而且它提供了比導(dǎo)入，導(dǎo)出向?qū)Ц鼮閺姶蟮墓δ??？梢韵騍SIS包中添加控制流、數(shù)據(jù)流任務(wù)和事件處理程序。SQLServer2005集成服務(wù)包（SSIS包）中的控制流由不同類型的控制流元素構(gòu)造而成：容器、任務(wù)和優(yōu)先約束。容器提供包中的結(jié)構(gòu)并給任務(wù)提供服務(wù)，任務(wù)在包中提供功能，優(yōu)先約束將容器和任務(wù)連接成一個控制流。SQLServer2005SSIS包中的數(shù)據(jù)流由下列不同類型的數(shù)據(jù)流元素構(gòu)造而成：提取數(shù)據(jù)的源、修改和聚合數(shù)據(jù)的轉(zhuǎn)換、加載數(shù)據(jù)的目標(biāo)以及將數(shù)據(jù)流組件的輸出和輸入連接為數(shù)據(jù)流的路徑。事件處理程序與包類似。事件處理程序可以像包一樣為變量提供作用域，并且包含控制流和可選數(shù)據(jù)流。使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

1.創(chuàng)建包創(chuàng)建SSIS包的步驟如下：（1）打開SQLServerBusinessIntelligenceDevelopmentStudio，使用菜單項文件—>新建—>項目，新建IntegrationServices項目，將名稱修改為“訂單數(shù)據(jù)集成”，并選擇項目保存位置，創(chuàng)建同名解決方案。使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

1.創(chuàng)建包（2）可使用菜單視圖—>解決方案資源管理器，查看已經(jīng)創(chuàng)建的解決方案。右鍵刪除SSIS包中的默認(rèn)包，并右鍵創(chuàng)建新包“NorthwindtoDW”“創(chuàng)建包”對話框

使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

1.創(chuàng)建包（3）在集成服務(wù)項目對話框中，可以向包中添加控制流、數(shù)據(jù)流任務(wù)和事件處理程序。如控制流設(shè)計器用來創(chuàng)建包中的控制流。工具箱的“控制流項”節(jié)點列出多種類型的任務(wù)和容器“創(chuàng)建包”對話框

使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

2.定義和設(shè)置數(shù)據(jù)轉(zhuǎn)換任務(wù)（1）創(chuàng)建源數(shù)據(jù)連接：右鍵單擊連接管理器區(qū)域中的任意位置，再單擊“新建OLEDB連接”。在“配置OLEDB連接管理器”對話框中，單擊“新建”。在“服務(wù)器名稱”中，輸入本地服務(wù)器名稱或輸入localhost，在“配置OLEDB連接管理器”對話框的“數(shù)據(jù)連接”窗格中，確認(rèn)選擇了相應(yīng)的服務(wù)器及數(shù)據(jù)庫Northwind“SQLServer連接管理器”對話框

配置OLEDB連接管理器窗口使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

2.定義和設(shè)置數(shù)據(jù)轉(zhuǎn)換任務(wù)（2）創(chuàng)建目標(biāo)數(shù)據(jù)連接：右鍵單擊連接管理器區(qū)域中的任意位置，再單擊“新建OLEDB連接”。在“配置OLEDB連接管理器”對話框中，單擊“新建”。在“服務(wù)器名稱”中，輸入本地服務(wù)器名稱或輸入localhost，在“配置OLEDB連接管理器”對話框的“數(shù)據(jù)連接”窗格中，確認(rèn)選擇了相應(yīng)的服務(wù)器及數(shù)據(jù)庫Northwind_DW

“SQLServer連接管理器”對話框

配置OLEDB連接管理器窗口使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

2.定義和設(shè)置數(shù)據(jù)轉(zhuǎn)換任務(wù)（3）為源數(shù)據(jù)和目標(biāo)數(shù)據(jù)創(chuàng)建了連接管理器后，

下一個任務(wù)是在包中添加一個數(shù)據(jù)流任務(wù)。具體如下：

向包中添加一個數(shù)據(jù)流源并對其進行配置（必選）。

向包中添加一個數(shù)據(jù)流轉(zhuǎn)換并對其進行配置（可選）。

向包中添加一個數(shù)據(jù)流目標(biāo)并對其進行配置（必選）。

則數(shù)據(jù)流任務(wù)可表示為下圖所示。

使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

2.定義和設(shè)置數(shù)據(jù)轉(zhuǎn)換任務(wù)（3）為源數(shù)據(jù)和目標(biāo)數(shù)據(jù)創(chuàng)建了連接管理器后，一個數(shù)據(jù)流任務(wù)完成對一張表/視圖的轉(zhuǎn)換操作完成對多張表的轉(zhuǎn)換操作需要多個數(shù)據(jù)流任務(wù)可在控制流中創(chuàng)建多個數(shù)據(jù)流任務(wù)，如下圖所示雙擊控制流中的某個數(shù)據(jù)流任務(wù)，可進入對應(yīng)的數(shù)據(jù)流任務(wù)設(shè)計窗口

使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

2.定義和設(shè)置數(shù)據(jù)轉(zhuǎn)換任務(wù)（4）對包進行調(diào)試和運行。若執(zhí)行控制流中的單個任務(wù)，則選中該任務(wù)，右鍵，執(zhí)行任務(wù)若運行整個包，則選中解決方案資源管理器中的SSIS包，右鍵，執(zhí)行包任務(wù)；或點擊工具欄中的綠色三角形按鈕進行調(diào)試運行，運行成功則控制流中的任務(wù)顯綠色，失敗顯紅色，正在運行顯黃色使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

3.定義和設(shè)置多任務(wù)

如果包中包含兩個或更多任務(wù)，則可以通過將它們的連接線從一項拖動到其他項而將它們連接成控制流。兩個項之間的連接器表示優(yōu)先約束。優(yōu)先約束定義了兩個連接項之間的關(guān)系。它指定了運行時任務(wù)的執(zhí)行順序以及任務(wù)的運行條件。例如，優(yōu)先約束可以指定某任務(wù)必須成功，才能運行控制流中的下一個任務(wù)。如果在前面的數(shù)據(jù)轉(zhuǎn)換任務(wù)成功完成后，需要完成另一個任務(wù)“執(zhí)行T-SQL語句”，則在控制流界面中，可將這兩個任務(wù)連接成控制流，。使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

3.定義和設(shè)置多任務(wù)

雙擊兩個任務(wù)之間的連接線，打開“優(yōu)先約束編輯器”窗口。在此窗口中可定義兩個連接項之間的關(guān)系，例如前一個任務(wù)執(zhí)行成功后，可執(zhí)行后一個任務(wù)。雙擊“執(zhí)行T-SQL語句”任務(wù)圖標(biāo)，可編輯要執(zhí)行的T-SQL語句。點擊“確定”按鈕完成編輯?！皟?yōu)先約束編輯器”窗口編輯“執(zhí)行T-SQL語句”任務(wù)窗口使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

4.實驗任務(wù)要求

可在控制流設(shè)計中：首先，創(chuàng)建一個“執(zhí)行T-SQL語句”任務(wù)，將任務(wù)重命名為“createTables”；其T-SQL語句，即為創(chuàng)建Northwind_DW中的各個表(如果數(shù)據(jù)庫中已創(chuàng)建表，這一步就不做)其次，創(chuàng)建一系列的針對維表的數(shù)據(jù)流轉(zhuǎn)換任務(wù)，用于將northwind中的相應(yīng)數(shù)據(jù)抽取到northwind_DW相應(yīng)維表中。最后，創(chuàng)建一個針對事實表的數(shù)據(jù)流流轉(zhuǎn)換任務(wù)，用于將northwind中的相應(yīng)數(shù)據(jù)抽取到northwind_DW相應(yīng)事實中。按照任務(wù)創(chuàng)建次序，順序連接已創(chuàng)建的各個任務(wù)調(diào)試并執(zhí)行整個包使用SQLServer圖形設(shè)計界面來創(chuàng)建SSIS包

4.實驗任務(wù)要求

實驗結(jié)果：31內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準(zhǔn)備數(shù)據(jù)源三、根據(jù)業(yè)務(wù)需求設(shè)計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析32Cube多維數(shù)據(jù)集源系統(tǒng)客戶端設(shè)計數(shù)據(jù)倉庫

導(dǎo)入數(shù)據(jù)倉庫 建設(shè)Cubes

查詢數(shù)據(jù)134查詢工具報表分析數(shù)據(jù)挖掘2使用AnalysisService實現(xiàn)數(shù)據(jù)分析33“嘿…2003年4月份我在北京賣掉了價值十萬美元的可樂”

RegionWSN維度的層次概念：

產(chǎn)品

地域

時間

類別國家年產(chǎn)品名稱省月

市日

$10萬使用AnalysisService實現(xiàn)數(shù)據(jù)分析使用AnalysisService實現(xiàn)數(shù)據(jù)分析在MicrosoftSQLServer2005AnalysisServices（SSAS）中，可以方便地創(chuàng)建復(fù)雜的聯(lián)機分析處理（OLAP）和數(shù)據(jù)挖掘解決方案。AnalysisServices工具提供了設(shè)計、創(chuàng)建和管理來自數(shù)據(jù)倉庫的多維數(shù)據(jù)集和數(shù)據(jù)挖掘模型的功能，還提供對OLAP數(shù)據(jù)和挖掘數(shù)據(jù)的客戶端訪問。35使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

1.創(chuàng)建AnalysisServices項目步驟如下：（1）打開SQLServerBusinessIntelligenceDevelopmentStudio，使用菜單項文件—>新建—>項目，新建AnalysisServices項目，將名稱修改為“訂單分析”，并選擇項目保存位置，創(chuàng)建同名解決方案。一、創(chuàng)建數(shù)據(jù)倉庫36使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

1.創(chuàng)建AnalysisServices項目步驟如下：（2）使用菜單視圖—>解決方案資源管理器，查看已經(jīng)創(chuàng)建的解決方案。一、創(chuàng)建數(shù)據(jù)倉庫37使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

2.創(chuàng)建數(shù)據(jù)源

在解決方案資源管理器中，選擇訂單分析項目下的數(shù)據(jù)源，右鍵，選擇“新建數(shù)據(jù)源”。按數(shù)據(jù)源向?qū)нx擇數(shù)據(jù)源。在選擇如何連接數(shù)據(jù)源中，選擇“基于現(xiàn)有連接或新連接創(chuàng)建數(shù)據(jù)源”，單擊新建按鈕，在打開的連接管理器窗口，選擇數(shù)據(jù)庫服務(wù)器名與數(shù)據(jù)庫（Northwind_DW）在下一步賬戶選擇中，選擇“使用服務(wù)賬戶”一、創(chuàng)建數(shù)據(jù)倉庫38使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

3.創(chuàng)建數(shù)據(jù)源視圖

在解決方案資源管理器中，選擇訂單分析項目下的數(shù)據(jù)源視圖，右鍵，選擇“新建數(shù)據(jù)源視圖”。按數(shù)據(jù)源視圖向?qū)нx擇相應(yīng)表。

一、創(chuàng)建數(shù)據(jù)倉庫39使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

4.創(chuàng)建多維數(shù)據(jù)集

在解決方案資源管理器中，選擇訂單分析項目下的多維數(shù)據(jù)集，右鍵，選擇“新建多維數(shù)據(jù)集”。按向?qū)нx擇相應(yīng)的事實表，事實表的度量字段，維表。得到如下所示多維數(shù)據(jù)集結(jié)構(gòu)

一、創(chuàng)建數(shù)據(jù)倉庫40使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

5.創(chuàng)建維度、指定屬性與層次選定某一某一維度，編輯維度，包括指定維度屬性，設(shè)置維度屬性關(guān)系，構(gòu)建用戶自定義層次等右鍵，新建維度，如創(chuàng)建時間維度表新建度量值，編輯度量值的聚集/計算方式一、創(chuàng)建數(shù)據(jù)倉庫41使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

維度屬性在”維度結(jié)構(gòu)”選項卡中看屬性在”瀏覽”選項卡中看屬性成員維度屬性成員用戶自定義層次結(jié)構(gòu)屬性5.創(chuàng)建維度、指定屬性與層次在進入某一維度的維度結(jié)構(gòu)視圖后，可在“維度結(jié)構(gòu)”項查看屬性、自定義用戶層次結(jié)構(gòu)，在“屬性關(guān)系”項查看與設(shè)置屬性關(guān)系，在瀏覽器項目查看維度各層次維成員一、創(chuàng)建數(shù)據(jù)倉庫42使用SQLServer圖形設(shè)計界面實現(xiàn)分析服務(wù)

5.生成并部署所創(chuàng)建的數(shù)據(jù)倉庫選擇菜單中的生成——生成“訂單分析”數(shù)據(jù)倉庫——部署“訂單分析”數(shù)據(jù)倉庫一、創(chuàng)