基于SQLServer2005

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

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

多維分析/數據挖掘門戶集成ETL工具數據轉換中央數據倉庫

信息展現應用系統源數據數據獲取數據管理數據使用SQLServer

數據倉庫的BI架構6一背景概述Employee_DimEmployeeKeyEmployeeID...EmployeeKeyTime_DimTimeKeyTheDate...TimeKeyProduct_DimProductKeyProductIDProduceNameProductBrandProductCategory...ProductKeyCustomer_DimCustomerKeyCustomerID...CustomerKeyShipper_DimShipperKeyShipperID...ShipperKeySales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...TimeKeyCustomerKeyShipperKeyProductKeyEmployeeKey多個外鍵事實維度鍵數據倉庫的星型結構9

設計數據倉庫 導入數據倉庫

建設Cubes

查詢數據1342Cube多維數據集數據倉庫源系統客戶端查詢工具報表分析數據挖掘一背景概述10

設計數據倉庫 導入數據倉庫

建設Cubes

查詢數據1423Cube多維數據集數據倉庫源系統客戶端查詢工具報表分析數據挖掘一背景概述11內容提要一、背景概述基于Northwind數據庫實現數據倉庫構建與OLAP分析二、準備數據源三、根據業務需求設計數據倉庫四、使用IntegrationServices實現數據倉庫與ETL五、使用AnalysisService實現數據分析二準備數據源

翻開SqlServer2005ManegementStudio查看是否已經存在Northwind數據庫如果不存在，通過數據庫任務——附加，創立并導入Northwind數據庫〔或運行instnwnd.sql即可〕創立新數據庫Northwind_DW13內容提要一、背景概述基于Northwind數據庫實現數據倉庫構建與OLAP分析二、準備數據源三、根據業務需求設計數據倉庫四、使用IntegrationServices實現數據倉庫與ETL五、使用AnalysisService實現數據分析二根據業務需求設計數據倉庫

詳見“03聯機分析處理.ppt〞中的數據倉庫案例分析包括：了解Northwind數據庫明確業務需求〔即數據倉庫主題〕識別事實與維度設計事實表設計維度表在Northwind_DW中創立相應的事實表與維度表〔或運行createnorthwinddw.sql即可〕15內容提要一、背景概述基于Northwind數據庫實現數據倉庫構建與OLAP分析二、準備數據源三、根據業務需求設計數據倉庫四、使用IntegrationServices實現數據倉庫與ETL五、使用AnalysisService實現數據分析16三IntegrationServices實現數據倉庫與ETL數據從數據源向目標數據倉庫抽取〔Extract〕、轉換〔Transform〕、裝載〔Load〕的過程構建數據倉庫的重要一環，用戶從數據源抽取出所需的數據，經過數據清洗，最終按照預先定義好的數據倉庫模型，將數據加載到數據倉庫中去。抽取：將數據從各種原始的業務系統中讀取出來。轉換：按照預先設計好的規那么將抽取得數據進行轉換、清洗，以及處理一些冗余、歧義的數據，使本來異構的數據格式能統一起來。裝載：將轉換完的數據導入到數據倉庫中。17ETL舉例導入合并計算查找二IntegrationServices實現數據倉庫與ETL使用SQLServer圖形設計界面來創立SSIS包SQLServer商務智能開發平臺是一個圖形工具，它使創立和編輯SSIS包的工作變得更加簡單和輕松，而且它提供了比導入，導出向導更為強大的功能。可以向SSIS包中添加控制流、數據流任務和事件處理程序。SQLServer2005集成效勞包〔SSIS包〕中的控制流由不同類型的控制流元素構造而成：容器、任務和優先約束。容器提供包中的結構并給任務提供效勞，任務在包中提供功能，優先約束將容器和任務連接成一個控制流。SQLServer2005SSIS包中的數據流由以下不同類型的數據流元素構造而成：提取數據的源、修改和聚合數據的轉換、加載數據的目標以及將數據流組件的輸出和輸入連接為數據流的路徑。事件處理程序與包類似。事件處理程序可以像包一樣為變量提供作用域，并且包含控制流和可選數據流。使用SQLServer圖形設計界面來創立SSIS包1.創立包創立SSIS包的步驟如下：〔1〕翻開SQLServerBusinessIntelligenceDevelopmentStudio，使用菜單項文件—>新建—>工程，新建IntegrationServices工程，將名稱修改為“訂單數據集成〞，并選擇工程保存位置，創立同名解決方案。使用SQLServer圖形設計界面來創立SSIS包1.創立包〔2〕可使用菜單視圖—>解決方案資源管理器，查看已經創立的解決方案。右鍵刪除SSIS包中的默認包，并右鍵創立新包“NorthwindtoDW〞“創立包〞對話框使用SQLServer圖形設計界面來創立SSIS包1.創立包〔3〕在集成效勞工程對話框中，可以向包中添加控制流、數據流任務和事件處理程序。如控制流設計器用來創立包中的控制流。工具箱的“控制流項〞節點列出多種類型的任務和容器“創立包〞對話框使用SQLServer圖形設計界面來創立SSIS包2.定義和設置數據轉換任務〔1〕創立源數據連接：右鍵單擊連接管理器區域中的任意位置，再單擊“新建OLEDB連接〞。在“配置OLEDB連接管理器〞對話框中，單擊“新建〞。在“效勞器名稱〞中，輸入本地效勞器名稱或輸入localhost，在“配置OLEDB連接管理器〞對話框的“數據連接〞窗格中，確認選擇了相應的效勞器及數據庫Northwind“SQLServer連接管理器〞對話框配置OLEDB連接管理器窗口使用SQLServer圖形設計界面來創立SSIS包2.定義和設置數據轉換任務〔2〕創立目標數據連接：右鍵單擊連接管理器區域中的任意位置，再單擊“新建OLEDB連接〞。在“配置OLEDB連接管理器〞對話框中，單擊“新建〞。在“效勞器名稱〞中，輸入本地效勞器名稱或輸入localhost，在“配置OLEDB連接管理器〞對話框的“數據連接〞窗格中，確認選擇了相應的效勞器及數據庫Northwind_DW“SQLServer連接管理器〞對話框配置OLEDB連接管理器窗口使用SQLServer圖形設計界面來創立SSIS包2.定義和設置數據轉換任務〔3〕為源數據和目標數據創立了連接管理器后，下一個任務是在包中添加一個數據流任務。具體如下：向包中添加一個數據流源并對其進行配置〔必選〕。向包中添加一個數據流轉換并對其進行配置〔可選〕。向包中添加一個數據流目標并對其進行配置〔必選〕。那么數據流任務可表示為以下圖所示。

使用SQLServer圖形設計界面來創立SSIS包2.定義和設置數據轉換任務〔3〕為源數據和目標數據創立了連接管理器后，一個數據流任務完成對一張表/視圖的轉換操作完成對多張表的轉換操作需要多個數據流任務可在控制流中創立多個數據流任務，如以下圖所示雙擊控制流中的某個數據流任務，可進入對應的數據流任務設計窗口使用SQLServer圖形設計界面來創立SSIS包2.定義和設置數據轉換任務〔4〕對包進行調試和運行。假設執行控制流中的單個任務，那么選中該任務，右鍵，執行任務假設運行整個包，那么選中解決方案資源管理器中的SSIS包，右鍵，執行包任務；或點擊工具欄中的綠色三角形按鈕進行調試運行，運行成功那么控制流中的任務顯綠色，失敗顯紅色，正在運行顯黃色使用SQLServer圖形設計界面來創立SSIS包3.定義和設置多任務如果包中包含兩個或更多任務，那么可以通過將它們的連接線從一項拖動到其他項而將它們連接成控制流。兩個項之間的連接器表示優先約束。優先約束定義了兩個連接項之間的關系。它指定了運行時任務的執行順序以及任務的運行條件。例如，優先約束可以指定某任務必須成功，才能運行控制流中的下一個任務。如果在前面的數據轉換任務成功完成后，需要完成另一個任務“執行T-SQL語句〞，那么在控制流界面中，可將這兩個任務連接成控制流，。使用SQLServer圖形設計界面來創立SSIS包3.定義和設置多任務雙擊兩個任務之間的連接線，翻開“優先約束編輯器〞窗口。在此窗口中可定義兩個連接項之間的關系，例如前一個任務執行成功后，可執行后一個任務。雙擊“執行T-SQL語句〞任務圖標，可編輯要執行的T-SQL語句。點擊“確定〞按鈕完成編輯。“優先約束編輯器〞窗口編輯“執行T-SQL語句〞任務窗口使用SQLServer圖形設計界面來創立SSIS包4.實驗任務要求

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

實驗結果：31內容提要一、背景概述基于Northwind數據庫實現數據倉庫構建與OLAP分析二、準備數據源三、根據業務需求設計數據倉庫四、使用IntegrationServices實現數據倉庫與ETL五、使用AnalysisService實現數據分析32Cube多維數據集源系統客戶端設計數據倉庫

導入數據倉庫 建設Cubes

查詢數據134查詢工具報表分析數據挖掘2使用AnalysisService實現數據分析33“嘿…2003年4月份我在北京賣掉了價值十萬美元的可樂〞RegionWSN維度的層次概念：

產品

地域

時間

類別國家年產品名稱省月

市日

$10萬使用AnalysisService實現數據分析使用AnalysisService實現數據分析在MicrosoftSQLServer2005AnalysisServices〔SSAS〕中，可以方便地創立復雜的聯機分析處理〔OLAP〕和數據挖掘解決方案。AnalysisServices工具提供了設計、創立和管理來自數據倉庫的多維數據集和數據挖掘模型的功能，還提供對OLAP數據和挖掘數據的客戶端訪問。35使用SQLServer圖形設計界面實現分析效勞1.創立AnalysisServices工程步驟如下：〔1〕翻開SQLServerBusinessIntelligenceDevelopmentStudio，使用菜單項文件—>新建—>工程，新建AnalysisServices工程，將名稱修改為“訂單分析〞，并選擇工程保存位置，創立同名解決方案。一、創立數據倉庫36使用SQLServer圖形設計界面實現分析效勞1.創立AnalysisServices工程步驟如下：〔2〕使用菜單視圖—>解決方案資源管理器，查看已經創立的解決方案。一、創立數據倉庫37使用SQLServer圖形設計界面實現分析效勞2.創立數據源在解決方案資源管理器中，選擇訂單分析工程下的數據源，右鍵，選擇“新建數據源〞。按數據源向導選擇數據源。在選擇如何連接數據源中，選擇“基于現有連接或新連接創立數據源〞，單擊新建按鈕，在翻開的連接管理器窗口，選擇數據庫效勞器名與數據庫〔Northwind_DW〕在下一步賬戶選擇中，選擇“使用效勞賬戶〞一、創立數據倉庫38使用SQLServer圖形設計界面實現分析效勞3.創立數據源視圖在解決方案資源管理器中，選擇訂單分析工程下的數據源視圖，右鍵，選擇“新建數據源視圖〞。按數據源視圖向導選擇相應表。

一、創立數據倉庫39使用SQLServer圖形設計界面實現分析效勞4.創立多維數據集在解決方案資源管理器中，選擇訂單分析工程下的多維數據集，右鍵，選擇“新建多維數據集〞。按向導選擇相應的事實表，事實表的度量字段，維表。得到如下所示多維數據集結構

一、創立數據倉庫40使用SQLServer圖形設計界面實現分析效勞5.創立維度、指定屬性與層次選定某一某一維度，編輯維度，包括指定維度屬性，設置維度屬性關系，構建用戶自定義層次等右鍵，新建維度，如創立時間維度表新建度量值，編輯度量值的聚集/計算方式一、創立數據倉庫41使用SQLServer圖形設計界面實現分析效勞維度屬性在〞維度結構〞選項卡中看屬性在〞瀏覽〞選項卡中看屬性成員維度屬性成員用戶自定義層次結構屬性5.創立維度、指定屬性與層次在進入某一維度的維度結構視圖后，可在“維度結構〞項查看屬性、自定義用戶層次結構，在“屬性關系〞項查看與設置屬性關系，在瀏覽器工程查看維度各層次維成員一、創立數據倉庫42使用SQLServer圖形設計界面實現分析效勞5.生成并部署所創立