基于SQLServer2005

的數(shù)據(jù)倉庫構(gòu)建與OLAP分析實驗2內(nèi)容提要一、背景概述基于Northwind數(shù)據(jù)庫實現(xiàn)數(shù)據(jù)倉庫構(gòu)建與OLAP分析二、準備數(shù)據(jù)源三、根據(jù)業(yè)務需求設計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析3一背景概述2005年底，微軟公司正式推出SQLServer2000的后繼產(chǎn)品SQLServer2005。與前一代相比，SQLServer2005不僅提供了更加優(yōu)秀的數(shù)據(jù)庫管理功能，而且提供了一套完整的數(shù)據(jù)倉庫和數(shù)據(jù)挖掘技術的解決方案。SQLServer2005負責底層的數(shù)據(jù)庫和數(shù)據(jù)倉庫管理，SQLServer2005集成服務(SSIS)負責數(shù)據(jù)的抽取、轉(zhuǎn)換和裝載(ETL)SQLServer2005分析服務負責OLAP分析和數(shù)據(jù)挖掘，SQLServer2005報表服務(SSRS)負責前端展示。4SQLServer

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

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

信息展現(xiàn)應用系統(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)7Cube多維數(shù)據(jù)集源系統(tǒng)客戶端設計數(shù)據(jù)倉庫

導入數(shù)據(jù)倉庫 建設Cubes查詢數(shù)據(jù)134查詢工具報表分析數(shù)據(jù)挖掘2數(shù)據(jù)倉庫一背景概述8設計數(shù)據(jù)倉庫

導入數(shù)據(jù)倉庫

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

設計數(shù)據(jù)倉庫 導入數(shù)據(jù)倉庫

建設Cubes

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

設計數(shù)據(jù)倉庫 導入數(shù)據(jù)倉庫

建設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分析二、準備數(shù)據(jù)源三、根據(jù)業(yè)務需求設計數(shù)據(jù)倉庫四、使用IntegrationServices實現(xiàn)數(shù)據(jù)倉庫與ETL五、使用AnalysisService實現(xiàn)數(shù)據(jù)分析二準備數(shù)據(jù)源

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

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

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

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

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

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

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

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

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

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

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

“SQLServer連接管理器”對話框

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

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

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

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

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

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

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

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

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

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

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

3.定義和設置多任務

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

3.定義和設置多任務

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

4.實驗任務要求

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

4.實驗任務要求

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

導入數(shù)據(jù)倉庫 建設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)建復雜的聯(lián)機分析處理（OLAP）和數(shù)據(jù)挖掘解決方案。AnalysisServices工具提供了設計、創(chuàng)建和管理來自數(shù)據(jù)倉庫的多維數(shù)據(jù)集和數(shù)據(jù)挖掘模型的功能，還提供對OLAP數(shù)據(jù)和挖掘數(shù)據(jù)的客戶端訪問。35使用SQLServer圖形設計界面實現(xiàn)分析服務

1.創(chuàng)建AnalysisServices項目步驟如下：（1）打開SQLServerB