SQLServer数据仓库与数据挖掘

1、8.1 SQL Server 2005的功能构架SQL Server 2005的数据资源管理包括两大功能体系，一是关于数据库的管理，二是关于数据仓库的管理。 8.1SQLServer2005的功功能构架架SQLServer 2005在在数据仓仓库方面面提供了了三大服服务和一一个工具具来实现现系统的的整合。三大服务务是SQLServer 2005AnalysisServices(SSAS)、SQLServer 2005IntegrationServices(SSIS)SQLServer 2005Reporting Services (SSRS)，一个工具具是BusinessIntelligenc

2、eDevelopmentStudio。8.1SQLServer2005的功功能构架架8.1SQLServer2005的功功能构架架SSIS能从各各种异构构数据源源中整合合数据到到数据仓仓库中，这项功功能在以以前是通通过DTS服务务(即数数据转换换服务)来实现现的。SSAS是从数数据中产产生知识识的关键键，通过过这种服服务，可可以构建建数据立立方(Cube)，也也就是多多维数据据集，然然后进行行OLAP分析析，SSAS也也提供数数据挖掘掘的功能能。SSRS是报表表设计工工具，通通过它可可以对分分析结果果提供类类型多样样、美观观且适合合不同需需求的图图表和报报表。8.3SQLServer集集成服务

3、务8.3.1SQLServer集成成服务的的作用在 SQLServer2005IntegrationServices(SSIS)中中，可可以方便便地创建建解决方方案，来来执行提提取、转转换和加加载数据据 (ETL) 的处处理。SSIS提供了了设计、创建、部署和和管理包包的功能能，用于于处理日日常的业业务需求求。8.3.1SQLServer集成成服务的的作用建立SSIS解解决方案案时，创创建新的的项目，该项目目可以放放在一般般的方案案管理之之下，但但它只是是逻辑的的管理单单位，SSIS设计、新建、访问和和执行的的单位依依然是包包(Package)，包可可以理解解为SSIS项项目中基基本的部部署和

4、执执行单元元。8.3.2控控制流流SSIS包中的的控制流流由一组组任务组组成。上图显示示了一个个简单的的控制流流示例。它以FTP任任务开始始，该FTP任任务用于于下载已已压缩的的数据文文件。一一旦下载载完这些些文件，运行时时就会到到达Foreach循循环容器器，在该该容器内内部有三三个任务务：执行行进程任任务(该该任务调调用unzip应用程程序来解解压缩数数据文件件)、大大容量插插入任务务和检查查数据库库完整性性任务。当解压压缩完数数据文件件，数据据文件中中的数据据被加载载到SQLServer中中并检查查完数据据库的完完整性时时，循环环会停止止。如果果在循环环期间发发生了错错误，则则该控制制流

5、将执执行发送送邮件任任务：向向DBA发送电电子邮件件。如果果正确地地执行了了每个任任务，则则该控制制流将执执行SQL任务务，然后后更新统统计信息息。8.3.3数数据流流数据流是是专门处处理数据据操作的的工作流流，也称称为流水水线。在数据流流中的每每个节点点都称为为转换。数据流流通常以以源转换换开始，以目标标转换结结束。上图显示示了一个个数据流流的示例例。该数数据流以以OLE DB Source转换换开始，该转换换加载包包含上市市公司基基本情况况的事实实表。第第二个转转换是“查找”，该转转换从上上市公司司基本情情况表中中查找上上市公司司简称。这列数数据被加加入到流流水线的的数据中中。下一一个转换

6、换是“条条件性拆拆分”，该转换换将“ST公司司”与“正常公公司”分分开。由由于分析析中ST公司与与正常公公司可比比性不强强，属于于异常值值，需要要剔出，因此“ST公公司”转转换为Excel目标标表保存存即可，“正常常公司”的相关关数据则则到达“派生列列”转换换，该转转换根据据流通A股中个个人持股股数量和和总股本本数计算算“个人人持股比比例”，再经过过“聚合合”转换换，按行行业求算算“平均均个人持持股比例例”，最最后转换换为“SQLServer目标”为后期期的数据据挖掘做做准备。值得注注意的是是，在创创建包以以及数据据流之前前，需要要充分了了解在源源数据和和目标数数据中使使用的格格式。了了解了这

7、这两种数数据格式式后，才才能确定定将源数数据映射射到目标标数据所所需的转转换。8.3.4设设计和和使用ETL数据仓库库的设计计是数据据分析和和数据挖挖掘的基基础工作作，良好好的数据据仓库结结构设计计是以后后工作能能顺利进进行的保保证。数据仓库库中的数数据一般般要从原原始业务务数据中中获取，经过“提取转换加载”即ETL过程程，对分分析有帮帮助的数数据将保保存于数数据仓库库中。Step1单击“开开始”菜菜单，选选择“所所有程序序”，再再指向Microsoft SQLServer2005，再再单击SQL ServerBusinessIntelligenceDevelopmentStudio。在“文件

8、件”菜单单上，选选择“新新建”，再单击击“项目目”。Step2在“新建建项目”对话框框中，从从“项目目类型”窗格中中选择“商业智智能项目目”，再再在“模模板”窗窗格中选选择“IntegrationServices项目目”，在在“名名称”框框中，键键入项目目名称Stock_IS。Step3在“项目目”菜单单中选择择“SSIS导导入和导导出向导导”命令令，在“选择数数据源”窗口中中的“数数据源”下拉列列表框中中选择Access数数据源选选项，如如图8.6所示示。然后后在路径径选择中中选择此此项目文文件夹中中的stockDB.mdf文件。8.3.4设设计和和使用ETL8.3.4设设计和和使用ETLS

9、tep4在选择目目标窗口口选择“SQL NativeClient”，如图图8.7所示。设置好好服务器器及其登登录信息息后，键键入目标标数据库库名，也也可以用用系统默默认的数数据库名名。Step5在“指定定表复制制或查询询”窗口口，选择择需要复复制的数数据源中中的表和和视图，或自定定义的查查询，这这里选择择“复制制一个或或多个表表或视图图的数据据”，如如图8.8所示示。Step6完成数据据导入操操作之前前，系统统将要求求用户确确认操作作列表，并提示示将会把把包以“Package1.dtsx”作作为文件件名保存存在项目目文件夹夹下面，而且不不会立即即执行，确认无无误后单单击“完完成”。8.3.4设

10、设计和和使用ETL8.3.4设设计和和使用ETL8.3.4设设计和和使用ETL8.4SQLServer分分析服务务8.4.1创创建AnalysisServices项目目Step1单击“开开始”菜菜单，选选择“所所有程序序”，再再指向Microsoft SQLServer2005，再再单击SQL ServerBusinessIntelligenceDevelopmentStudio。Step2在 VisualStudio的的“文文件”菜菜单上，选择“新建”，再单单击“项项目”。Step3在“新建建项目”对话框框中，从从“项目目类型”窗格中中选择“商业智智能项目目”，再再在“模模板”窗窗格中选选择

11、“AnalysisServices项项目”。Step4将项目名名称命名名为STOCK_AS，这这也将更更改解决决方案名名称，然然后“确确定”。8.4.1创创建AnalysisServices项目目8.4.2定定义数数据源Step1在解决方方案资源源管理器器中，右右键单击击“数据据源”，然后单单击“新新建数据据源”，将打开开数据源源向导，如图8.11所示。在“欢欢迎使用用数据源源向导”页上，单击“下一步步”按钮钮。Step2在“选择择如何定定义连接接”页上上，可以以基于新新连接、现有连连接或以以前定义义的数据据源对象象来定义义数据源源。以前前定义的的数据源源对象是是当前项项目中或或当前解解决方案

12、案的其他他项目中中的现有有数据源源定义。在本例例中选择择“新建建”，如如图8.12所所示，也也就是基基于新连连接定义义新数据据源。Step3在“连接接管理器器”对话话框中，可定义义数据源源的连接接属性。首先，在“提提供程序序”列表表中，选选则“本本机OLEDB SQLNativeClient”选项项，即可可连接本本机的SQLServer数据源源。Step4在数据源源向导的的“模拟拟信息”页上，可以定定义AnalysisServices用用于连接接数据源源的安全全凭据。在本例例中，选选择AnalysisServices服服务账户户，因为为该账户户具有访访问STOCKDB 数据据库所需需的权限限。

13、选择择“使用用服务账账户”， 如图图8.14所示示，然后后单击“下一步步”按钮钮即显示示随后出出现的“完成向向导”页页。Step5在“完成成向导”页上，写出数数据源的的名称，本例中中将数据据源命名名为StockDW，单击“完成”以创建建名为StockDW的新数数据源。如图8.15所示解解决方案案资源管管理器的的“数据据源”文文件夹中中可以看看到的新新数据源源。若要要修改现现有数据据源的属属性，可可以在“数据源源”文件件夹中双双击该数数据源，并在“数据源源设计器器”中修修改数据据源属性性。8.4.3定定义数数据源视视图Step1在解决方方案资源源管理器器中，右右键单击击“数据据源视图图”，再再单

14、击“新建数数据源视视图”，如图8.16所示。此时将将打开数数据源视视图向导导。Step2在数据源源视图向向导的“选择数数据源”页，选选则StockDW 数据据源，如如图8.17所所示。单单击“下下一步”，将显显示“选选择表和和视图”页。8.4.3定定义数数据源视视图Step3在“选择择表和视视图”页页中，可可以从选选定的数数据源提提供的对对象列表表中选择择表和视视图。在在“可用用对象”列表中中，选择择需要的的表，并并将选中中的表添添加到“包含的的对象”列表中中。然后后，写出出数据源源视图的的名称StockVIEW，就可以以“完成成”数据据源视图图的定义义。Step4数据源视视图StockVIE

15、W将将在解解决方案案资源管管理器的的“数据据源视图图”文件件夹中显显示。数数据源视视图的内内容也可可以在数数据源视视图设计计器中进进行修改改。图8.18显示了了数据源源视图设设计器中中的数据据源视图图StockVIEW。8.4.3定定义数数据源视视图8.4.4用用AnalysisServices创建维维与多维维数据集集1常规规维度关关系当维度的的键列与与事实数数据表直直接联接接时，多多维数据据集维度度与度量量值组之之间便会会存在常常规维度度关系。这种直直接关系系基于基基础关系系数据库库中的主主键-外外键关系系，但是是也可以以基于数数据源视视图中定定义的逻逻辑关系系。常规规维度关关系表示示传统星

16、星型架构构设计中中维度表表与事实实数据表表之间的的关系。2引用用维度关关系当多维数数据集维维度的键键列通过过其他维维度表中中的键与与事实数数据表间间接联接接时，该该维度与与度量值值组之间间便会存存在引用用维度关关系。引引用维度度关系表表示雪花花型架构构设计中中的维度度表与事事实数据据表之间间的关系系。当雪雪花型架架构中的的各维度度表进行行连接时时，可以以使用多多个表中中的列定定义一个个维度，也可以以根据单单独的维维度表定定义单独独的维度度，然后后使用引引用维度度关系设设置定义义这些维维度之间间的链接接。3事实实维度关关系事实维度度通常称称为退化化维度，是通过过事实数数据表而而非维度度表中的的列

17、构造造的标准准维度。有用的的维度数数据有时时存储在在事实数数据表中中可以减减少重复复。4多对对多维度度关系多对多维维度可将将维度模模型扩展展了传统统的星型型架构范范围，并并在维度度不直接接与事实实数据表表相关联联的情况况下支持持复杂分分析。Step1在解决方方案资源源管理器器中，右右键单击击“多维维数据集集”，然然后单击击“新建建多维数数据集”。Step2在“欢迎迎使用多多维数据据集向导导”页上上，单击击“下一一步”按按钮，并并在“选选择生成成方法”页上，确认已已选中“使用数数据源生生成多维维数据集集”选项项，此时时，可以以启用“自动生生成”选选项，如如图8.19所所示，这这样多维维数据集集向

18、导将将自动定定义维度度表中各各列的属属性并自自动生成成多级层层次结构构。当然然，也可可以手动动创建属属性，然然后生成成层次结结构。8.4.4用用AnalysisServices创建维维与多维维数据集集Step3在“选择择数据源源视图”页上，确认已已选中StockVIEW 数据据源视图图。在使使用多维维数据集集向导生生成多维维数据集集时，可可以在“选择数数据源视视图”页页上单击击“完成成”按钮钮，以让让该向导导定义多多维数据据集的其其余属性性；也可可以根据据需要自自己定义义多维数数据集的的其余属属性。若若直接进进入“完完成向导导”页，可以在在该页为为多维数数据集指指定名称称并可以以查看其其结构。

19、本例中中单击“下一步步”按钮钮，以查查看并进进一步定定义多维维数据集集的属性性。Step4向导扫描描在数据据源对象象中定义义的数据据库中的的表，以以标识事事实数据据表和维维度表以以及与事事实数据据表相关关的度量量值，在在“检测测事实数数据表和和维度表表”页上上单击“下一步步”按钮钮，将显显示该向向导所标标识的事事实数据据表和维维度表。图8.20显显示了该该向导的的“标识识事实数数据表和和维度表表”页，其中为为 STOCK_AS项目选选择了事事实数据据表和维维度表。8.4.4用用AnalysisServices创建维维与多维维数据集集Step5单击“下下一步”按钮，在随即即出现的的“选择择度量值

20、值”页中中，显示示了向导导所选择择的度量量值。在在此，也也可以进进行修改改。一般般选择事事实数据据表中的的各数值值数据类类型列作作为度量量值，如如图8.21所所示。Step6在“完成成向导”页上，将多维维数据集集的名称称更改为为StockDIMS，在该该页上，也可以以查看多多维数据据集的度度量值组组、度量量值、维维度、层层次结构构和属性性，如图图8.23所示示，单击击“完成成”按钮钮以完成成向导。8.4.4用用AnalysisServices创建维维与多维维数据集集8.4.4用用AnalysisServices创建维维与多维维数据集集8.4.4用用AnalysisServices创建维维与多维

21、维数据集集8.4.5部部署AnalysisServices项项目Step1在解决方方案资源源管理器器中，右右键单击击项目名名“STOCK_AS”，然然后单击击“属性性”选项项。将出出现“STOCK_AS属属性页”对话框框，并显显示活动动配置的的属性。在此可可以定义义多个配配置，每每个配置置可以具具有不同同的属性性。Step2在“STOCK_AS 属性性页”的的树型目目录里选选择“部部署”选选项，并并在“目目标”区区域的“服务器器”文本本框里输输入Analysis Services服务器器名及实实例名，若省略略实例名名则部署署到默认认实例，然后“确定”，如图图8.25所示示，以完完成配置置属性的

22、的设置。8.4.5部部署AnalysisServices项项目8.4.5部部署AnalysisServices项项目Step3在解决方方案资源源管理器器中，右右键单击击项目名名“STOCK_AS”，再再单击“部署”选项，或者在在“生成成”菜单单上单击击“部署署STOCK_AS”，就可可以在“部署进进度”对对话框中中看到部部署情况况。Step4单击“视视图”菜菜单栏中中的“输输出”选选项，可可以查看看部署是是否成功功。部署完STOCK_AS项目目之后，启动SQLServer ManagementStudio，在“连接到到服务器器”对话话框里的的“服务务器类型型”下拉拉列表框框里选择择“Analy

23、sis Services”选项项并写出出部署的的服务器器名和实实例名。连接后后，在SQLServer ManagementStudio的“对对象资源源管理器器”里，就可以以看到部部署的项项目。8.4.5部部署AnalysisServices项项目利用数据据挖掘向向导可以以创建挖挖掘结构构和挖掘掘模型，使用该该向导可可以定义义结构并并制定创创建基于于该结构构的初始始模型时时使用的的算法和和定型数数据。具具体过程程包括：选择数数据源类类型，选选择算法法，选择择数据源源并且指指定数据据源表的的用法，选择表表中的列列并且指指定这些些列的用用法，对对挖掘模模型命名名。接下下来，可可以对模模型进行行处理和

24、和分析，还可以以生成数数据挖掘掘报表。Step1在解决方方案资源源管理器器中，选选择“挖挖掘结构构”，单单击右键键，在弹弹出的菜菜单中选选择“新新建挖掘掘结构”命令，弹出“数据挖挖掘向导导”对话话框，单单击“下下一步”按钮。Step2“选择定定义方法法”对话话框中，需要开开发人员员选择创创建模型型的源是是来自于于一个关关系源还还是来自自于多维维源，虽虽然最后后创建的的结果相相同，但但是创建建的过程程不同，所以对对于不同同的选项项有不同同的向导导过程。本例中中，选择择“从现现有关系系数据库库或数据据仓库”。Step3在弹出的的“选择择数据挖挖掘技术术”对话话框中，选择将将要使用用的数据据挖掘技技

25、术。算算法的列列表是由由目标服服务器的的功能决决定的，要选择择什么算算法主要要取决于于具体的的挖掘主主题。数数据挖掘掘模型还还可以用用数据挖挖掘扩展展(DMX)语语言创建建和处理理。本例例中选择择了聚类类分析和和神经网网络。Step4确定将要要进行挖挖掘的数数据，即即选择数数据源。本例中中选择了了前面已已经创建建好的数数据源StockDW。Step5指定表类类型，确确定表中中各列的的用法。当选择表表时，必必须指定定每一个个表是事事例表还还是嵌套套表，事事例表包包含想要要分析的的实体的的事例，嵌套表表包含每每一事例例附加的的信息(通常是是事务信信息)。Step6为挖掘结结构和挖挖掘模型型命名，如

26、果所所选择的的算法支支持，还还可以启启用钻取取功能，完成向向导。使使用该向向导创建建了数据据挖掘对对象之后后，就可可以在数数据挖掘掘设计器器中浏览览并细化化数据挖挖掘向导导创建的的结果。8.6SQLServer报报表服务务8.6.1创创建报报表报表是数数据挖掘掘结果的的归纳与与呈现，可以使使用自定定义的方方式来创创建，也也可以使使用报表表向导来来创建。Step1单击“开开始”菜菜单，选选择“所所有程序序”，再再指向Microsoft SQLServer2005，再再单击SQL ServerBusinessIntelligenceDevelopmentStudio。在Visual Studio的

27、的“文件件”菜单单上，选选择“新新建”，再单击击“项目目”。Step2在“新建建项目”对话框框中，从从“项目目类型”窗格中中选择“商业智智能项目目”，再再在“模模板”窗窗格中选选择“报报表服务务器项目目向导”。将项项目名称称命名为为 STOCK_RS，然后后单击“确定”按钮，如图8.28所示。8.6.1创创建报报表8.6.1创创建报报表Step3“选择数数据源”窗口，为报表表选择或或创建所所需数据据的来源源，输入入数据源源名称STOCKDW，选择择AnalysisServices作作为数据据连接的的类型，再单击击“编辑辑”按钮钮，设置置此数据据源的连连接属性性，可以以通过单单击“测测试连接接”按钮钮确认此此连接的的正确性性，然后后单击“确定”按钮，如图8.29所示。Step4在“设计计查询”窗口中中单击“查询生生成器”，在查查询生成成器窗口口设计希希望显示示在报表表中的数数据内容容。如图图8.30所示示。Step5设