SQL-Server數(shù)據(jù)倉庫與數(shù)據(jù)挖掘

8.1SQLServer2005的功能構(gòu)架SQLServer2005的數(shù)據(jù)資源管理包括兩大功能體系，一是關(guān)于數(shù)據(jù)庫的管理，二是關(guān)于數(shù)據(jù)倉庫的管理。8.1SQLServer2005的功能構(gòu)架SQLServer2005在數(shù)據(jù)倉庫方面提供了三大效勞和一個(gè)工具來實(shí)現(xiàn)系統(tǒng)的整合。三大效勞是SQLServer2005AnalysisServices(SSAS)、SQLServer2005IntegrationServices(SSIS)SQLServer2005ReportingServices(SSRS)，一個(gè)工具是BusinessIntelligenceDevelopmentStudio。8.1SQLServer2005的功能構(gòu)架8.1SQLServer2005的功能構(gòu)架SSIS能從各種異構(gòu)數(shù)據(jù)源中整合數(shù)據(jù)到數(shù)據(jù)倉庫中，這項(xiàng)功能在以前是通過DTS效勞(即數(shù)據(jù)轉(zhuǎn)換效勞)來實(shí)現(xiàn)的。SSAS是從數(shù)據(jù)中產(chǎn)生知識(shí)的關(guān)鍵，通過這種效勞，可以構(gòu)建數(shù)據(jù)立方(Cube)，也就是多維數(shù)據(jù)集，然后進(jìn)行OLAP分析，SSAS也提供數(shù)據(jù)挖掘的功能。SSRS是報(bào)表設(shè)計(jì)工具，通過它可以對(duì)分析結(jié)果提供類型多樣、美觀且適合不同需求的圖表和報(bào)表。8.3SQL

Server集成效勞8.3.1SQL

Server集成效勞的作用在SQLServer2005IntegrationServices(SSIS)中，可以方便地創(chuàng)立解決方案，來執(zhí)行提取、轉(zhuǎn)換和加載數(shù)據(jù)(ETL)的處理。SSIS提供了設(shè)計(jì)、創(chuàng)立、部署和管理包的功能，用于處理日常的業(yè)務(wù)需求。8.3.1SQL

Server集成效勞的作用建立SSIS解決方案時(shí)，創(chuàng)立新的工程，該工程可以放在一般的方案管理之下，但它只是邏輯的管理單位，SSIS設(shè)計(jì)、新建、訪問和執(zhí)行的單位依然是包(Package)，包可以理解為SSIS工程中根本的部署和執(zhí)行單元。8.3.2控制流SSIS包中的控制流由一組任務(wù)組成。上圖顯示了一個(gè)簡(jiǎn)單的控制流例如。它以FTP任務(wù)開始，該FTP任務(wù)用于下載已壓縮的數(shù)據(jù)文件。一旦下載完這些文件，運(yùn)行時(shí)就會(huì)到達(dá)Foreach循環(huán)容器，在該容器內(nèi)部有三個(gè)任務(wù)：執(zhí)行進(jìn)程任務(wù)(該任務(wù)調(diào)用unzip應(yīng)用程序來解壓縮數(shù)據(jù)文件)、大容量插入任務(wù)和檢查數(shù)據(jù)庫完整性任務(wù)。當(dāng)解壓縮完數(shù)據(jù)文件，數(shù)據(jù)文件中的數(shù)據(jù)被加載到SQLServer中并檢查完數(shù)據(jù)庫的完整性時(shí)，循環(huán)會(huì)停止。如果在循環(huán)期間發(fā)生了錯(cuò)誤，那么該控制流將執(zhí)行發(fā)送郵件任務(wù)：向DBA發(fā)送電子郵件。如果正確地執(zhí)行了每個(gè)任務(wù)，那么該控制流將執(zhí)行SQL任務(wù)，然后更新統(tǒng)計(jì)信息。8.3.3數(shù)據(jù)流數(shù)據(jù)流是專門處理數(shù)據(jù)操作的工作流，也稱為流水線。在數(shù)據(jù)流中的每個(gè)節(jié)點(diǎn)都稱為轉(zhuǎn)換。數(shù)據(jù)流通常以源轉(zhuǎn)換開始，以目標(biāo)轉(zhuǎn)換結(jié)束。上圖顯示了一個(gè)數(shù)據(jù)流的例如。該數(shù)據(jù)流以O(shè)LEDBSource轉(zhuǎn)換開始，該轉(zhuǎn)換加載包含上市公司根本情況的事實(shí)表。第二個(gè)轉(zhuǎn)換是“查找”，該轉(zhuǎn)換從上市公司根本情況表中查找上市公司簡(jiǎn)稱。這列數(shù)據(jù)被參加到流水線的數(shù)據(jù)中。下一個(gè)轉(zhuǎn)換是“條件性拆分”，該轉(zhuǎn)換將“ST公司”與“正常公司”分開。由于分析中ST公司與正常公司可比性不強(qiáng)，屬于異常值，需要剔出，因此“ST公司”轉(zhuǎn)換為Excel目標(biāo)表保存即可，“正常公司”的相關(guān)數(shù)據(jù)那么到達(dá)“派生列”轉(zhuǎn)換，該轉(zhuǎn)換根據(jù)流通A股中個(gè)人持股數(shù)量和總股本數(shù)計(jì)算“個(gè)人持股比例”，再經(jīng)過“聚合”轉(zhuǎn)換，按行業(yè)求算“平均個(gè)人持股比例”，最后轉(zhuǎn)換為“SQLServer目標(biāo)”為后期的數(shù)據(jù)挖掘做準(zhǔn)備。值得注意的是，在創(chuàng)立包以及數(shù)據(jù)流之前，需要充分了解在源數(shù)據(jù)和目標(biāo)數(shù)據(jù)中使用的格式。了解了這兩種數(shù)據(jù)格式后，才能確定將源數(shù)據(jù)映射到目標(biāo)數(shù)據(jù)所需的轉(zhuǎn)換。8.3.4設(shè)計(jì)和使用ETL數(shù)據(jù)倉庫的設(shè)計(jì)是數(shù)據(jù)分析和數(shù)據(jù)挖掘的根底工作，良好的數(shù)據(jù)倉庫結(jié)構(gòu)設(shè)計(jì)是以后工作能順利進(jìn)行的保證。數(shù)據(jù)倉庫中的數(shù)據(jù)一般要從原始業(yè)務(wù)數(shù)據(jù)中獲取，經(jīng)過“提取－轉(zhuǎn)換－加載”即ETL過程，對(duì)分析有幫助的數(shù)據(jù)將保存于數(shù)據(jù)倉庫中。Step1單擊“開始”菜單，選擇“所有程序”，再指向MicrosoftSQLServer2005，再單擊SQLServerBusinessIntelligenceDevelopmentStudio。在“文件”菜單上，選擇“新建”，再單擊“工程”。Step2在“新建工程”對(duì)話框中，從“工程類型”窗格中選擇“商業(yè)智能工程”，再在“模板”窗格中選擇“IntegrationServices工程”，在“名稱”框中，鍵入工程名稱Stock_IS。Step3在“工程”菜單中選擇“SSIS導(dǎo)入和導(dǎo)出向?qū)А泵睿凇斑x擇數(shù)據(jù)源”窗口中的“數(shù)據(jù)源”下拉列表框中選擇Access數(shù)據(jù)源選項(xiàng)，如圖8.6所示。然后在路徑選擇中選擇此工程文件夾中的stockDB.mdf文件。8.3.4設(shè)計(jì)和使用ETL8.3.4設(shè)計(jì)和使用ETLStep4在選擇目標(biāo)窗口選擇“SQLNativeClient”，如圖8.7所示。設(shè)置好效勞器及其登錄信息后，鍵入目標(biāo)數(shù)據(jù)庫名，也可以用系統(tǒng)默認(rèn)的數(shù)據(jù)庫名。Step5在“指定表復(fù)制或查詢”窗口，選擇需要復(fù)制的數(shù)據(jù)源中的表和視圖，或自定義的查詢，這里選擇“復(fù)制一個(gè)或多個(gè)表或視圖的數(shù)據(jù)”，如圖8.8所示。Step6完成數(shù)據(jù)導(dǎo)入操作之前，系統(tǒng)將要求用戶確認(rèn)操作列表，并提示將會(huì)把包以“Package1.dtsx”作為文件名保存在工程文件夾下面，而且不會(huì)立即執(zhí)行，確認(rèn)無誤后單擊“完成”。8.3.4設(shè)計(jì)和使用ETL8.3.4設(shè)計(jì)和使用ETL8.3.4設(shè)計(jì)和使用ETL8.4SQL

Server分析效勞8.4.1創(chuàng)立Analysis

Services工程Step1單擊“開始”菜單，選擇“所有程序”，再指向MicrosoftSQLServer2005，再單擊SQLServerBusinessIntelligenceDevelopmentStudio。Step2在VisualStudio的“文件”菜單上，選擇“新建”，再單擊“工程”。Step3在“新建工程”對(duì)話框中，從“工程類型”窗格中選擇“商業(yè)智能工程”，再在“模板”窗格中選擇“AnalysisServices工程”。Step4將工程名稱命名為STOCK_AS，這也將更改解決方案名稱，然后“確定”。8.4.1創(chuàng)立Analysis

Services工程8.4.2定義數(shù)據(jù)源Step1在解決方案資源管理器中，右鍵單擊“數(shù)據(jù)源”，然后單擊“新建數(shù)據(jù)源”，將翻開數(shù)據(jù)源向?qū)?，如圖8.11所示。在“歡送使用數(shù)據(jù)源向?qū)А表撋?，單擊“下一步”按鈕。Step2在“選擇如何定義連接”頁上，可以基于新連接、現(xiàn)有連接或以前定義的數(shù)據(jù)源對(duì)象來定義數(shù)據(jù)源。以前定義的數(shù)據(jù)源對(duì)象是當(dāng)前工程中或當(dāng)前解決方案的其他工程中的現(xiàn)有數(shù)據(jù)源定義。在本例中選擇“新建”，如圖8.12所示，也就是基于新連接定義新數(shù)據(jù)源。Step3在“連接管理器”對(duì)話框中，可定義數(shù)據(jù)源的連接屬性。首先，在“提供程序”列表中，選那么“本機(jī)OLEDB\SQLNativeClient”選項(xiàng)，即可連接本機(jī)的SQLServer數(shù)據(jù)源。Step4在數(shù)據(jù)源向?qū)У摹澳M信息”頁上，可以定義AnalysisServices用于連接數(shù)據(jù)源的平安憑據(jù)。在本例中，選擇AnalysisServices效勞賬戶，因?yàn)樵撡~戶具有訪問STOCKDB數(shù)據(jù)庫所需的權(quán)限。選擇“使用效勞賬戶”，如圖8.14所示，然后單擊“下一步”按鈕即顯示隨后出現(xiàn)的“完成向?qū)А表摗tep5在“完成向?qū)А表撋?，寫出?shù)據(jù)源的名稱，本例中將數(shù)據(jù)源命名為StockDW，單擊“完成”以創(chuàng)立名為StockDW的新數(shù)據(jù)源。如圖8.15所示解決方案資源管理器的“數(shù)據(jù)源”文件夾中可以看到的新數(shù)據(jù)源。假設(shè)要修改現(xiàn)有數(shù)據(jù)源的屬性，可以在“數(shù)據(jù)源”文件夾中雙擊該數(shù)據(jù)源，并在“數(shù)據(jù)源設(shè)計(jì)器”中修改數(shù)據(jù)源屬性。8.4.3定義數(shù)據(jù)源視圖Step1在解決方案資源管理器中，右鍵單擊“數(shù)據(jù)源視圖”，再單擊“新建數(shù)據(jù)源視圖”，如圖8.16所示。此時(shí)將翻開數(shù)據(jù)源視圖向?qū)?。Step2在數(shù)據(jù)源視圖向?qū)У摹斑x擇數(shù)據(jù)源”頁，選那么StockDW數(shù)據(jù)源，如圖8.17所示。單擊“下一步”，將顯示“選擇表和視圖”頁。8.4.3定義數(shù)據(jù)源視圖Step3在“選擇表和視圖”頁中，可以從選定的數(shù)據(jù)源提供的對(duì)象列表中選擇表和視圖。在“可用對(duì)象”列表中，選擇需要的表，并將選中的表添加到“包含的對(duì)象”列表中。然后，寫出數(shù)據(jù)源視圖的名稱StockVIEW，就可以“完成”數(shù)據(jù)源視圖的定義。Step4數(shù)據(jù)源視圖StockVIEW將在解決方案資源管理器的“數(shù)據(jù)源視圖”文件夾中顯示。數(shù)據(jù)源視圖的內(nèi)容也可以在數(shù)據(jù)源視圖設(shè)計(jì)器中進(jìn)行修改。圖8.18顯示了數(shù)據(jù)源視圖設(shè)計(jì)器中的數(shù)據(jù)源視圖StockVIEW。8.4.3定義數(shù)據(jù)源視圖8.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集1．常規(guī)維度關(guān)系當(dāng)維度的鍵列與事實(shí)數(shù)據(jù)表直接聯(lián)接時(shí)，多維數(shù)據(jù)集維度與度量值組之間便會(huì)存在常規(guī)維度關(guān)系。這種直接關(guān)系基于根底關(guān)系數(shù)據(jù)庫中的主鍵-外鍵關(guān)系，但是也可以基于數(shù)據(jù)源視圖中定義的邏輯關(guān)系。常規(guī)維度關(guān)系表示傳統(tǒng)星型架構(gòu)設(shè)計(jì)中維度表與事實(shí)數(shù)據(jù)表之間的關(guān)系。

2．引用維度關(guān)系當(dāng)多維數(shù)據(jù)集維度的鍵列通過其他維度表中的鍵與事實(shí)數(shù)據(jù)表間接聯(lián)接時(shí)，該維度與度量值組之間便會(huì)存在引用維度關(guān)系。引用維度關(guān)系表示雪花型架構(gòu)設(shè)計(jì)中的維度表與事實(shí)數(shù)據(jù)表之間的關(guān)系。當(dāng)雪花型架構(gòu)中的各維度表進(jìn)行連接時(shí)，可以使用多個(gè)表中的列定義一個(gè)維度，也可以根據(jù)單獨(dú)的維度表定義單獨(dú)的維度，然后使用引用維度關(guān)系設(shè)置定義這些維度之間的鏈接。3．事實(shí)維度關(guān)系事實(shí)維度通常稱為退化維度，是通過事實(shí)數(shù)據(jù)表而非維度表中的列構(gòu)造的標(biāo)準(zhǔn)維度。有用的維度數(shù)據(jù)有時(shí)存儲(chǔ)在事實(shí)數(shù)據(jù)表中可以減少重復(fù)。4．多對(duì)多維度關(guān)系多對(duì)多維度可將維度模型擴(kuò)展了傳統(tǒng)的星型架構(gòu)范圍，并在維度不直接與事實(shí)數(shù)據(jù)表相關(guān)聯(lián)的情況下支持復(fù)雜分析。Step1在解決方案資源管理器中，右鍵單擊“多維數(shù)據(jù)集”，然后單擊“新建多維數(shù)據(jù)集”。Step2在“歡送使用多維數(shù)據(jù)集向?qū)А表撋?，單擊“下一步”按鈕，并在“選擇生成方法”頁上，確認(rèn)已選中“使用數(shù)據(jù)源生成多維數(shù)據(jù)集”選項(xiàng)，此時(shí)，可以啟用“自動(dòng)生成”選項(xiàng)，如圖8.19所示，這樣多維數(shù)據(jù)集向?qū)⒆詣?dòng)定義維度表中各列的屬性并自動(dòng)生成多級(jí)層次結(jié)構(gòu)。當(dāng)然，也可以手動(dòng)創(chuàng)立屬性，然后生成層次結(jié)構(gòu)。8.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集Step3在“選擇數(shù)據(jù)源視圖”頁上，確認(rèn)已選中StockVIEW數(shù)據(jù)源視圖。在使用多維數(shù)據(jù)集向?qū)啥嗑S數(shù)據(jù)集時(shí)，可以在“選擇數(shù)據(jù)源視圖”頁上單擊“完成”按鈕，以讓該向?qū)Фx多維數(shù)據(jù)集的其余屬性；也可以根據(jù)需要自己定義多維數(shù)據(jù)集的其余屬性。假設(shè)直接進(jìn)入“完成向?qū)А表?，可以在該頁為多維數(shù)據(jù)集指定名稱并可以查看其結(jié)構(gòu)。本例中單擊“下一步”按鈕，以查看并進(jìn)一步定義多維數(shù)據(jù)集的屬性。Step4向?qū)呙柙跀?shù)據(jù)源對(duì)象中定義的數(shù)據(jù)庫中的表，以標(biāo)識(shí)事實(shí)數(shù)據(jù)表和維度表以及與事實(shí)數(shù)據(jù)表相關(guān)的度量值，在“檢測(cè)事實(shí)數(shù)據(jù)表和維度表”頁上單擊“下一步”按鈕，將顯示該向?qū)鶚?biāo)識(shí)的事實(shí)數(shù)據(jù)表和維度表。圖8.20顯示了該向?qū)У摹皹?biāo)識(shí)事實(shí)數(shù)據(jù)表和維度表”頁，其中為STOCK_AS工程選擇了事實(shí)數(shù)據(jù)表和維度表。8.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集Step5單擊“下一步”按鈕，在隨即出現(xiàn)的“選擇度量值”頁中，顯示了向?qū)x擇的度量值。在此，也可以進(jìn)行修改。一般選擇事實(shí)數(shù)據(jù)表中的各數(shù)值數(shù)據(jù)類型列作為度量值，如圖8.21所示。Step6在“完成向?qū)А表撋?，將多維數(shù)據(jù)集的名稱更改為StockDIMS，在該頁上，也可以查看多維數(shù)據(jù)集的度量值組、度量值、維度、層次結(jié)構(gòu)和屬性，如圖8.23所示，單擊“完成”按鈕以完成向?qū)А?.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集8.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集8.4.4用Analysis

Services創(chuàng)立維與多維數(shù)據(jù)集8.4.5部署AnalysisServices工程Step1在解決方案資源管理器中，右鍵單擊工程名“STOCK_AS”，然后單擊“屬性”選項(xiàng)。將出現(xiàn)“STOCK_AS屬性頁”對(duì)話框，并顯示活動(dòng)配置的屬性。在此可以定義多個(gè)配置，每個(gè)配置可以具有不同的屬性。Step2在“STOCK_AS屬性頁”的樹型目錄里選擇“部署”選項(xiàng)，并在“目標(biāo)”區(qū)域的“效勞器”文本框里輸入AnalysisServices效勞器名及實(shí)例名，假設(shè)省略實(shí)例名那么部署到默認(rèn)實(shí)例，然后“確定”，如圖8.25所示，以完成配置屬性的設(shè)置。8.4.5部署AnalysisServices工程8.4.5部署AnalysisServices工程Step3在解決方案資源管理器中，右鍵單擊工程名“STOCK_AS”，再單擊“部署”選項(xiàng)，或者在“生成”菜單上單擊“部署STOCK_AS”，就可以在“部署進(jìn)度”對(duì)話框中看到部署情況。Step4單擊“視圖”菜單欄中的“輸出”選項(xiàng)，可以查看部署是否成功。部署完STOCK_AS工程之后，啟動(dòng)SQLServerManagementStudio，在“連接到效勞器”對(duì)話框里的“效勞器類型”下拉列表框里選擇“AnalysisServices”選項(xiàng)并寫出部署的效勞器名和實(shí)例名。連接后，在SQLServerManagementStudio的“對(duì)象資源管理器”里，就可以看到部署的工程。8.4.5部署AnalysisServices工程利用數(shù)據(jù)挖掘向?qū)Э梢詣?chuàng)立挖掘結(jié)構(gòu)和挖掘模型，使用該向?qū)Э梢远x結(jié)構(gòu)并制定創(chuàng)立基于該結(jié)構(gòu)的初始模型時(shí)使用的算法和定型數(shù)據(jù)。具體過程包括：選擇數(shù)據(jù)源類型，選擇算法，選擇數(shù)據(jù)源并且指定數(shù)據(jù)源表的用法，選擇表中的列并且指定這些列的用法，對(duì)挖掘模型命名。接下來，可以對(duì)模型進(jìn)行處理和分析，還可以生成數(shù)據(jù)挖掘報(bào)表。Step1在解決方案資源管理器中，選擇“挖掘結(jié)構(gòu)”，單擊右鍵，在彈出的菜單中選擇“新建挖掘結(jié)構(gòu)”命令，彈出“數(shù)據(jù)挖掘向?qū)А睂?duì)話框，單擊“下一步”按鈕。Step2“選擇定義方法”對(duì)話框中，需要開發(fā)人員選擇創(chuàng)立模型的源是來自于一個(gè)關(guān)系源還是來自于多維源，雖然最后創(chuàng)立的結(jié)果相同，但是創(chuàng)立的過程不同，所以對(duì)于不同的選項(xiàng)有不同的向?qū)н^程。本例中，選擇“從現(xiàn)有關(guān)系數(shù)據(jù)庫或數(shù)據(jù)倉庫”。Step3在彈出的“選擇數(shù)據(jù)挖掘技術(shù)”對(duì)話框中，選擇將要使用的數(shù)據(jù)挖掘技術(shù)。算法的列表是由目標(biāo)效勞器的功能決定的，要選擇什么算法主要取決于具體的挖掘主題。數(shù)據(jù)挖掘模型還可以用數(shù)據(jù)挖掘擴(kuò)展(DMX)語言創(chuàng)立和處理。本例中選擇了聚類分析和神經(jīng)網(wǎng)絡(luò)。Step4確定將要進(jìn)行挖掘的數(shù)據(jù)，即選擇數(shù)據(jù)源。本例中選擇了前面已經(jīng)創(chuàng)立好的數(shù)據(jù)源StockDW。Step5指定表類型，確定表中各列的用法。中選擇表時(shí)，必須指定每一個(gè)表是事例表還是嵌套表，事例表包含想要分析的實(shí)體的事例，嵌套表包含每一事例附加的信息(通常是事務(wù)信息)。Step6為挖掘結(jié)構(gòu)和挖掘模型命名，如果所選擇的算法支持，還可以啟用鉆取功能，完成向?qū)?。使用該向?qū)?chuàng)立了數(shù)據(jù)挖掘?qū)ο笾?，就可以在?shù)據(jù)挖掘設(shè)計(jì)器中瀏覽并細(xì)化數(shù)據(jù)挖掘向?qū)?chuàng)立的結(jié)果。8.6SQL

Server報(bào)表效勞8.6.1創(chuàng)立報(bào)表報(bào)表是數(shù)據(jù)挖掘結(jié)果的歸納與呈現(xiàn)，可以使用自定義的方式來創(chuàng)立，也可以使用報(bào)表向?qū)韯?chuàng)立。Step1單擊“開始”菜單，選擇“所有程序”，再指向MicrosoftSQLServer2005，再單擊SQLServerBusinessIntelligenceDevelopmentStudio。在VisualStudio的“文件”菜單上，選擇“新建”，再單擊“工程”。Step2在“新建工程”對(duì)話框中，從“工程類型”窗格中選擇“商業(yè)智能工程”，再在“模板”窗格中選擇“報(bào)表效勞器工程向?qū)А?。將工程名稱命名為STOCK_RS，然后單擊“確定”按鈕，如圖8.28所示。8.6.1創(chuàng)立報(bào)表8.6.1創(chuàng)立報(bào)表Step3“選擇數(shù)據(jù)源”窗口，為報(bào)表選擇或創(chuàng)立所需數(shù)據(jù)的來源，輸入數(shù)據(jù)源名稱STOCKDW，選擇AnalysisServices作為數(shù)據(jù)連接的類型，再單擊“編輯”按鈕，設(shè)置此數(shù)據(jù)源的連接屬性，可以通過單擊“測(cè)試連接”按鈕確認(rèn)此連接的正確性，然后單擊“確定”按鈕，如圖8.29所示。Step4在“設(shè)計(jì)查詢”窗口中單擊“查詢生成器”，在查詢生成器窗口設(shè)計(jì)希