SQLServer数据仓库与数据挖掘

8.1SQLServer2005的功能构架SQLServer2005的数据资源管理包括两大功能体系，一是关于数据库的管理，二是关于数据仓库的管理。8.1SQLServer2005的功能构架SQLServer2005在数据仓库方面提供了三大服务和一个工具来实现系统的整合。三大服务是SQLServer2005AnalysisServices(SSAS)、SQLServer2005IntegrationServices(SSIS)SQLServer2005ReportingServices(SSRS)，一个工具是BusinessIntelligenceDevelopmentStudio。8.1SQLServer2005的功能构架8.1SQLServer2005的功能构架SSIS能从各种异构数据源中整合数据到数据仓库中，这项功能在以前是通过DTS服务(即数据转换服务)来实现的。SSAS是从数据中产生知识的关键，通过这种服务，可以构建数据立方(Cube)，也就是多维数据集，然后进行OLAP分析，SSAS也提供数据挖掘的功能。SSRS是报表设计工具，通过它可以对分析结果提供类型多样、美观且适合不同需求的图表和报表。8.3SQL

Server集成服务8.3.1SQL

Server集成服务的作用在SQLServer2005IntegrationServices(SSIS)中，可以方便地创建解决方案，来执行提取、转换和加载数据(ETL)的处理。SSIS提供了设计、创建、部署和管理包的功能，用于处理日常的业务需求。8.3.1SQL

Server集成服务的作用建立SSIS解决方案时，创建新的项目，该项目可以放在一般的方案管理之下，但它只是逻辑的管理单位，SSIS设计、新建、访问和执行的单位依然是包(Package)，包可以理解为SSIS项目中基本的部署和执行单元。8.3.2控制流SSIS包中的控制流由一组任务组成。上图显示了一个简单的控制流示例。它以FTP任务开始，该FTP任务用于下载已压缩的数据文件。一旦下载完这些文件，运行时就会到达Foreach循环容器，在该容器内部有三个任务：执行进程任务(该任务调用unzip应用程序来解压缩数据文件)、大容量插入任务和检查数据库完整性任务。当解压缩完数据文件，数据文件中的数据被加载到SQLServer中并检查完数据库的完整性时，循环会停止。如果在循环期间发生了错误，则该控制流将执行发送邮件任务：向DBA发送电子邮件。如果正确地执行了每个任务，则该控制流将执行SQL任务，然后更新统计信息。8.3.3数据流数据流是专门处理数据操作的工作流，也称为流水线。在数据流中的每个节点都称为转换。数据流通常以源转换开始，以目标转换结束。上图显示了一个数据流的示例。该数据流以OLEDBSource转换开始，该转换加载包含上市公司基本情况的事实表。第二个转换是“查找”，该转换从上市公司基本情况表中查找上市公司简称。这列数据被加入到流水线的数据中。下一个转换是“条件性拆分”，该转换将“ST公司”与“正常公司”分开。由于分析中ST公司与正常公司可比性不强，属于异常值，需要剔出，因此“ST公司”转换为Excel目标表保存即可，“正常公司”的相关数据则到达“派生列”转换，该转换根据流通A股中个人持股数量和总股本数计算“个人持股比例”，再经过“聚合”转换，按行业求算“平均个人持股比例”，最后转换为“SQLServer目标”为后期的数据挖掘做准备。值得注意的是，在创建包以及数据流之前，需要充分了解在源数据和目标数据中使用的格式。了解了这两种数据格式后，才能确定将源数据映射到目标数据所需的转换。8.3.4设设计和使使用ETL数据仓库的设设计是数据分分析和数据挖挖掘的基础工工作，良好的的数据仓库结结构设计是以以后工作能顺顺利进行的保保证。数据仓库中的的数据一般要要从原始业务务数据中获取取，经过“提提取－转换－－加载”即ETL过程，，对分析有帮帮助的数据将将保存于数据据仓库中。Step1单击“开始””菜单，选择择“所有程序序”，再指向向MicrosoftSQLServer2005，再单击击SQLServerBusinessIntelligenceDevelopmentStudio。在““文件”菜菜单上，选择择“新建”，，再单击“项项目”。Step2在“新建项目目”对话框中中，从“项目目类型”窗格格中选择“商商业智能项目目”，再在““模板”窗格格中选择“IntegrationServices项项目”，在在“名称”框框中，键入项项目名称Stock_IS。Step3在“项目”菜菜单中选择““SSIS导导入和导出向向导”命令，，在“选择数数据源”窗口口中的“数据据源”下拉列列表框中选择择Access数据源选选项，如图8.6所示。。然后在路径径选择中选择择此项目文件件夹中的stockDB.mdf文文件。8.3.4设设计计和使使用ETL8.3.4设设计计和使使用ETLStep4在选择择目标标窗口口选择择“SQLNativeClient”，，如图图8.7所所示。。设置置好服服务器器及其其登录录信息息后，，键入入目标标数据据库名名，也也可以以用系系统默默认的的数据据库名名。Step5在“指指定表表复制制或查查询””窗口口，选选择需需要复复制的的数据据源中中的表表和视视图，，或自自定义义的查查询，，这里里选择择“复复制一一个或或多个个表或或视图图的数数据””，如如图8.8所示示。Step6完成数数据导导入操操作之之前，，系统统将要要求用用户确确认操操作列列表，，并提提示将将会把把包以以“Package1.dtsx”作作为文文件名名保存存在项项目文文件夹夹下面面，而而且不不会立立即执执行，，确认认无误误后单单击““完成成”。。8.3.4设设计计和和使使用用ETL8.3.4设设计计和和使使用用ETL8.3.4设设计计和和使使用用ETL8.4SQLServer分分析析服服务务8.4.1创创建建AnalysisServices项项目目Step1单击击““开开始始””菜菜单单，，选选择择““所所有有程程序序””，，再再指指向向MicrosoftSQLServer2005，，再再单单击击SQLServerBusinessIntelligenceDevelopmentStudio。。Step2在VisualStudio的的“文文件””菜单单上，，选择择“新新建””，再再单击击“项项目””。Step3在“新新建项项目””对话话框中中，从从“项项目类类型””窗格格中选选择““商业业智能能项目目”，，再在在“模模板””窗格格中选选择““AnalysisServices项项目目”。。Step4将项目目名称称命名名为STOCK_AS，这这也将将更改改解决决方案案名称称，然然后““确定定”。。8.4.1创建建Analysis

Services项目8.4.2定义义数据源Step1在解决方案案资源管理理器中，右右键单击““数据源””，然后单单击“新建建数据源””，将打开开数据源向向导，如图图8.11所示。在在“欢迎使使用数据源源向导”页页上，单击击“下一步步”按钮。。Step2在“选择如如何定义连连接”页上上，可以基基于新连接接、现有连连接或以前前定义的数数据源对象象来定义数数据源。以以前定义的的数据源对对象是当前前项目中或或当前解决决方案的其其他项目中中的现有数数据源定义义。在本例例中选择““新建”，，如图8.12所示示，也就是是基于新连连接定义新新数据源。。Step3在“连接管管理器”对对话框中，，可定义数数据源的连连接属性。。首先，在在“提供程程序”列表表中，选则则“本机OLEDB\SQLNativeClient”选项项，即可连连接本机的的SQLServer数据据源。Step4在数据源源向导的的“模拟拟信息””页上，，可以定定义AnalysisServices用用于连接接数据源源的安全全凭据。。在本例例中，选选择AnalysisServices服服务账户户，因为为该账户户具有访访问STOCKDB数据据库所需需的权限限。选择择“使用用服务账账户”，，如图图8.14所示示，然后后单击““下一步步”按钮钮即显示示随后出出现的““完成向向导”页页。Step5在“完成成向导””页上，，写出数数据源的的名称，，本例中中将数据据源命名名为StockDW，，单击““完成””以创建建名为StockDW的新数数据源。。如图8.15所示解解决方案案资源管管理器的的“数据据源”文文件夹中中可以看看到的新新数据源源。若要要修改现现有数据据源的属属性，可可以在““数据源源”文件件夹中双双击该数数据源，，并在““数据源源设计器器”中修修改数据据源属性性。8.4.3定定义数数据源视视图Step1在解决方方案资源源管理器器中，右右键单击击“数据据源视图图”，再再单击““新建数数据源视视图”，，如图8.16所示。。此时将将打开数数据源视视图向导导。Step2在数据源源视图向向导的““选择数数据源””页，选选则StockDW数据据源，如如图8.17所所示。单单击“下下一步””，将显显示“选选择表和和视图””页。8.4.3定定义数据据源视图Step3在“选择表和和视图”页中中，可以从选选定的数据源源提供的对象象列表中选择择表和视图。。在“可用对对象”列表中中，选择需要要的表，并将将选中的表添添加到“包含含的对象”列列表中。然后后，写出数据据源视图的名名称StockVIEW，就可以““完成”数据据源视图的定定义。Step4数据源视图StockVIEW将将在解决方方案资源管理理器的“数据据源视图”文文件夹中显示示。数据源视视图的内容也也可以在数据据源视图设计计器中进行修修改。图8.18显示了了数据源视图图设计器中的的数据源视图图StockVIEW。。8.4.3定定义数据据源视图8.4.4用用Analysis

Services创创建维与多维维数据集1．常规维度度关系当维度的键列列与事实数据据表直接联接接时，多维数数据集维度与与度量值组之之间便会存在在常规维度关关系。这种直直接关系基于于基础关系数数据库中的主主键-外键关关系，但是也也可以基于数数据源视图中中定义的逻辑辑关系。常规规维度关系表表示传统星型型架构设计中中维度表与事事实数据表之之间的关系。。2．引用维度度关系当多维数据集集维度的键列列通过其他维维度表中的键键与事实数据据表间接联接接时，该维度度与度量值组组之间便会存存在引用维度度关系。引用用维度关系表表示雪花型架架构设计中的的维度表与事事实数据表之之间的关系。。当雪花型架架构中的各维维度表进行连连接时，可以以使用多个表表中的列定义义一个维度，，也可以根据据单独的维度度表定义单独独的维度，然然后使用引用用维度关系设设置定义这些些维度之间的的链接。3．事实维度度关系事实维度通常常称为退化维维度，是通过过事实数据表表而非维度表表中的列构造造的标准维度度。有用的维维度数据有时时存储在事实实数据表中可可以减少重复复。4．多对多维维度关系多对多维度可可将维度模型型扩展了传统统的星型架构构范围，并在在维度不直接接与事实数据据表相关联的的情况下支持持复杂分析。。Step1在解决方案资资源管理器中中，右键单击击“多维数据据集”，然后后单击“新建建多维数据集集”。Step2在“欢欢迎使使用多多维数数据集集向导导”页页上，，单击击“下下一步步”按按钮，，并在在“选选择生生成方方法””页上上，确确认已已选中中“使使用数数据源源生成成多维维数据据集””选项项，此此时，，可以以启用用“自自动生生成””选项项，如如图8.19所所示，，这样样多维维数据据集向向导将将自动动定义义维度度表中中各列列的属属性并并自动动生成成多级级层次次结构构。当当然，，也可可以手手动创创建属属性，，然后后生成成层次次结构构。8.4.4用用AnalysisServices创建建维与与多维维数据据集Step3在“选选择数数据源源视图图”页页上，，确认认已选选中StockVIEW数数据据源视视图。。在使使用多多维数数据集集向导导生成成多维维数据据集时时，可可以在在“选选择数数据源源视图图”页页上单单击““完成成”按按钮，，以让让该向向导定定义多多维数数据集集的其其余属属性；；也可可以根根据需需要自自己定定义多多维数数据集集的其其余属属性。。若直直接进进入““完成成向导导”页页，可可以在在该页页为多多维数数据集集指定定名称称并可可以查查看其其结构构。本本例中中单击击“下下一步步”按按钮，，以查查看并并进一一步定定义多多维数数据集集的属属性。。Step4向导导扫扫描描在在数数据据源源对对象象中中定定义义的的数数据据库库中中的的表表，，以以标标识识事事实实数数据据表表和和维维度度表表以以及及与与事事实实数数据据表表相相关关的的度度量量值值，，在在““检检测测事事实实数数据据表表和和维维度度表表””页页上上单单击击““下下一一步步””按按钮钮，，将将显显示示该该向向导导所所标标识识的的事事实实数数据据表表和和维维度度表表。。图图8.20显显示示了了该该向向导导的的““标标识识事事实实数数据据表表和和维维度度表表””页页，，其其中中为为STOCK_AS项项目目选选择择了了事事实实数数据据表表和和维维度度表表。。8.4.4用用Analysis

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

Services创建维维与多维维数据集集8.4.4用用Analysis

Services创建维维与多维维数据集集8.4.4用用Analysis

Services创建维维与多维维数据集集8.4.5部部署AnalysisServices项项目Step1在解决方方案资源源管理器器中，右右键单击击项目名名“STOCK_AS”，然然后单击击“属性性”选项项。将出出现“STOCK_AS属属性页””对话框框，并显显示活动动配置的的属性。。在此可可以定义义多个配配置，每每个配置置可以具具有不同同的属性性。Step2在“STOCK_AS属性性页”的的树型目目录里选选择“部部署”选选项，并并在“目目标”区区域的““服务器器”文本本框里输输入AnalysisServices服务器器名及实实例名，，若省略略实例名名则部署署到默认认实例，，然后““确定””，如图图8.25所示示，以完完成配置置属性的的设置。。8.4.5部部署AnalysisServices项项目8.4.5部部署AnalysisServices项项目Step3在解决方方案资源源管理器器中，右右键单击击项目名名“STOCK_AS”，再再单击““部署””选项，，或者在在“生成成”菜单单上单击击“部署署STOCK_AS””，就可可以在““部署进进度”对对话框中中看到部部署情况况。Step4单击“视视图”菜菜单栏中中的“输输出”选选项，可可以查看看部署是是否成功功。部署完STOCK_AS项目目之后，，启动SQLServerManagementStudio，在““连接到到服务器器”对话话框里的的“服务务器类型型”下拉拉列表框框里选择择“AnalysisServices”选项项并写出出部署的的服务器器名和实实例名。。连接后后，在SQLServerManagementStudio的“对对象资源源管理器器”里，，就可以以看到部部署的项项目。8.4.5部部署AnalysisServices项项目利用数数据挖挖掘向向导可可以创创建挖挖掘结结构和和挖掘掘模型型，使使用该该向导导可以以定义义结构构并制制定创创建基基于该该结构构的初初始模模型时时使用用的算算法和和定型型数据据。具具体过过程包包括：：选择择数据据源类类型，，选择择算法法，选选择数数据源源并且且指定定数据据源表表的用用法，，选择择表中中的列列并且且指定定这些些列的的用法法，对对挖掘掘模型型命名名。接接下来来，可可以对对模型型进行行处理理和分分析，，还可可以生生成数数据挖挖掘报报表。。Step1在解决决方案案资源源管理理器中中，选选择““挖掘掘结构构”，，单击击右键键，在在弹出出的菜菜单中中选择择“新新建挖挖掘结结构””命令令，弹弹出““数据据挖掘掘向导导”对对话框框，单单击““下一一步””按钮钮。Step2“选选择择定定义义方方法法””对对话话框框中中，，需需要要开开发发人人员员选选择择创创建建模模型型的的源源是是来来自自于于一一个个关关系系源源还还是是来来自自于于多多维维源源，，虽虽然然最最后后创创建建的的结结果果相相同同，，但但是是创创建建的的过过程程不不同同，，所所以以对对于于不不同同的的选选项项有有不不同同的的向向导导过过程程。。本本例例中中，，选选择择““从从现现有有关关系系数数据据库库或或数数据据仓仓库库””。。Step3在弹弹出出的的““选选择择数数据据挖挖掘掘技技术术””对对话话框框中中，，选选择择将将要要使使用用的的数数据据挖挖掘掘技技术术。。算算法法的的列列表表是是由由目目标标服服务务器器的的功功能能决决定定的的，，要要选选择择什什么么算算法法主主要要取取决决于于具具体体的的挖挖掘掘主主题题。。数数据据挖挖掘掘模模型型还还可可以以用用数数据据挖挖掘掘扩扩展展(DMX)语语言言创创建建和和处处理理。。本本例例中中选选择择了了聚聚类类分分析析和和神神经经网网络络。。Step4确定定将将要要进进行行挖挖掘掘的的数数据据，，即即选选择择数数据据源源。。本本例例中中选选择择了了前前面面已已经经创创建建好好的的数数据据源源StockDW。。Step5指定定表表类类型型，，确确定定表表中中各各列列的的用用法法。。当选选择择表表时时，，必必须须指指定定每每一一个个表表是是事事例例表表还还是是嵌嵌套套表表，，事事例例表表包包含含想想要要分分析析的的实实体体的的事事例例，，嵌嵌套套表表包包含含每每一一事事例例附附加加的的信信息息(通通常常是是事事务务信信息息)。。Step6为挖挖掘掘结结构构和和挖挖掘掘模模型型命命名名，，如如果果所所选选择择的的算算法法支支持持，，还还可可以以启启用用钻钻取取功功能能，，完完成成向向导导。。使使用用该该向向导导创创建建了了数数据据挖挖掘掘对对象象之之后后，，就就可可以以在在数数据据挖挖掘掘设设计计器器中中浏浏览览并并细细化化数数据据挖挖掘掘向向导导创创建建的的结结果果。。8.6SQLServer报表表服务务8.6.1创创建建报表表报表是是数据据挖掘掘结果果的归归纳与与呈现现，可可以使使用自自定义义的方方式来来创建建，也也可以以使用用报表表向导导来创创建。。Step1单击““开始始”菜菜单，，选择择“所所有程程序””，再再指向向MicrosoftSQLServer2005，再再单击击SQLServerBusinessIntelligenceDevelopmentStudio。在在VisualStudio的的“文文件””菜单单上，，选择择“新新建””，再再单击击“项项目””。Step2在“新新建项项目””对话话框中中，从从“项项目类类型””窗格格中选选择““商业业智能能项目目”，，再在在“模模板””窗格格中选选择““报表表服务务器项项目向向导””。将将项目目名称称命名名为STOCK_RS，然然后单单击““确定定”按按钮，，如图图8.28所示示。8.6.1创创建建报表表8.6.1创创建建报表表Step3““选择择数据据源””窗口口，为为报表表选择择或创创建所所需数数据的的来源源，输输入数数据源源名称称STOCKDW，，选择择AnalysisServices作作为数数据连连接的的类型型，再再单击击“编编辑””按钮钮，设设置此此数据据源的的连接接属性性，可可以通通过单单击““测试试连接接”按按钮确确认此此连接接的正正确性性，然然后单单击““确定定”按按钮，，如图图8.29所示示。Step4在“设计计查询””窗口中中单击““查询生生成器””，在查查询生成