SQLServer2005数据挖掘与商业智能完全解决方案

1、 基于foodmart数据库的商业智能系统摘要：FoodMart数据库是SQL Server以前版本所带的示例数据库，它模拟了一家大型的食品连锁店的经营业务所产生的数据。其商业数据保存在一个Access格式的数据库中，其中包括了客户管理数据、销售数据、分销数据和库存数据等。随着业务量的增加，这个食品连锁店的老板迫切需要多方位地掌握其经营状况，而传统的报表形式和数据处理方式已经不能满足这一要求，因此在保留历史数据的基础上构建商业智能应用已经迫在眉睫。下面就描述满足这一商务需求的技术实现过程。1.设计和创建数据仓库 FoodMart数据库涉及到公司经营的各个方面，包括产品、库存、人事、客户和销售等

2、。一个真正的商业智能应用应该对这些业务需求进行全面地考虑。本章截取这些需求中的销售部分构建商业智能。2.原始业务数据分析 打开配套文件中附带的foodmart.mdb文件，可以看到如图2-1所示的24张表，虽然以前在设计这个数据库的时候加入了数据仓库的某些特点，但由于它本身源于以前的系统，也存储了全部的业务数据，因此这里作为初学可以把它理解为福马特商店的原始数据。 在这个数据库中，包含了福马特商店日常经营业务的数据，如人事管理中的员工信息存储在employee表中，员工所属部门信息存储在department表中，职务信息则存储在position表中，库存管理业务中的仓库类型存储在warehou

3、se_class表中，具体的仓库存储在warehouse中。图1福马特商店的业务数据3.设计数据仓库逻辑模型 福马特市场部的商务需求是要对1998年进行的所有销售业务数据进行多角度分析，以便市场分析人员能在查询数据库时获取快速的响应，高层管理人员也能从总体上把握影响本年度销售的因素。这需要利用存储在公司业务数据库中的数据，建立数据仓库，进而创建可用于分析的多维数据结构。如前所述，这里只着眼于销售方面的数据，因而把与销售相关的表提炼出来进行分析。在foodmart.mdb数据库中，销售业务的数据和时间、促销手段、产品和店铺等都有关系，它们的关系体现在表与表之间的逻辑关系上。要从业务数据出发设计数

4、据仓库的结构，必须明确业务数据本身的结构，而业务数据的关系一般是基于关系数据库设计的范式。这里希望用雪花形结构来构建福马特商店的销售数据仓库，逻辑结构设计图如图2所示。图2 销售数据仓库雪花形结构设计图在数据仓库的逻辑结构中，数据表可以划分为两类：一类是事实数据表；另一类是维度数据表。在实际设计的时候，通常需要根据需求情况重新建立与原始数据不同的表结构。这主要是由于传统业务的数据库是用来进行事务处理的（即OLTP），而数据仓库则是用来进行分析处理的（即OLAP），用途的不同决定了其结构的不同。这一点在以后复杂的数据仓库设计中会通过示例体现出来。4.创建foodmartsaleDW数据仓库 数据

5、仓库也是一种数据库，其管理同样是通过数据库管理系统（DBMS）来进行的。因此数据仓库可以像普通数据库一样进行创建、修改和删除。当数据仓库的逻辑结构设计完后，就可以创建物理数据仓库了。 这时可以在SQL Server Management Studio中按照一般的建立数据库的方法建立一个名为“foodmartsaleDW”的数据库，然后把这里设计的表创建好，数据类型依据原始数据库中的各个表和字段的数据类型设置。但由于这里数据仓库的表结构与原始数据库中的表结构基本一致，因此，创建foodmartsaleDW数据仓库的物理结构过程也可以在ETL阶段完成。5.设计和使用ETL 数据仓库的设计可以说是数

6、据分析和商业智能的最基础的工作。良好的数据仓库结构设计是以后工作能顺利进行的保证。而数据仓库中的数据则一般要经过“提取转换加载”的过程从原始业务数据中获取，这就是ETL过程。这里的任务就是要把数据从foodmart.mdb数据库中装载到foodmartsaleDW数据仓库中。需要使用到SQL Server Integration Services服务，即SSIS，其操作步骤如下： （1）打开Business Intelligence Development Studio，选择【文件】【新建】【项目】命令，弹出“新建项目”对话框，展开“商业智能项目”，在“模板”窗格中，单击“Integratio

7、n Services项目”，把项目命名为“foodmartsale ETL”，如图3所示。图 3创建“foodmartsale ETL”项目这时会在BI Studio环境中打开用于设计SSIS的各种工具和窗口，数据提取、转换和加载的操作都在这个界面下进行。 （2）选择【项目】【SSIS导入和导出向导】命令，这时会弹出SSIS导入和导出向导的欢迎界面，单击【下一步】按钮。 （3）在“选择数据源”窗口中的“数据源”下拉列表框中选择Access数据源选项，如图4所示。然后在路径选择中选择此项目文件夹中的foodmart 2000.mdb文件。图4 选择foodmart 2000.mdb数据源 （4）

8、在随即弹出的窗口中选择数据的导出目标，这里选择“SQL Native Client”，如图5所示。设置好服务器及其登录信息后，选择foodmartsaleDW数据仓库作为目标数据库，如果在以前的步骤中没有创建此数据库，可以在此窗口中单击【新建】按钮，在弹出的“创建数据库”窗口中创建此数据库，单击【下一步】按钮继续。图5 选择数据仓库目标 （5）此时将弹出如图6所示的窗口，通过这个窗口可以选择需要复制的是数据源的多个表和视图，还是自定义的查询，这里选择“复制一个或多个表或视图的数据”单选按钮；单击【下一步】按钮继续。图6 指定复制类型 （6）这时会让用户选择源表和源视图，如图7所示。按照前面对数

9、据仓库的设计，这里选择原始表中的time_by_day、promotion、product、product_class、customer、store和sales_fact_1998表作为需要输入的表。在图7的界面中，还可以对数据导入的目标进行定制，可以对映射方式进行编辑，甚至可以自己写“CREATE TABLE”语句作为复制的目标表。这些改变都可以体现在数据仓库的物理结构中。图7 选择源表和源视图并定制映射方式这里不对映射及其目标进行变更，保持默认的状态，使生成的数据仓库的物理模型完全符合前面对数据仓库的逻辑模型的设计。 （7）以上操作完成后，单击【下一步】按钮，系统将会把前面的操作列表并要求

10、用户确认，并提示将会把包以“Package1.dtsx”作为文件名保存在项目文件夹下面，而且不会立即执行。确认无误后单击【完成】按钮。 （8）在“解决方案资源管理器”中展开“SSIS包”文件夹，在Package1.dtsx上单击鼠标右键，在弹出的快捷菜单中选择【设为启动对象】命令，如图8所示。 （9）单击工具条上的 按钮运行这个工程，可以发现在SSIS设计界面的“控制流”和“数据流”等选项卡内都有对象在活动，这是系统正在把数据从foodmart 2000.mdb数据库中按照前面所确定的规则装载入foodmartsaleDW数据仓库中。 （10）数据装载过程完成后，切换到SQL Server M

11、anagement Studio，展开foodmartsaleDW数据仓库可以发现，已经按照设计要求建立好了数据仓库，并且仓库中已经存储了业务数据，如图9所示。 图8 设置Package1.dtsx为启动对象 图9 完成数据装载后的foodmartsaleDW数据仓库6.创建OLAP数据立方 数据立方的创建和管理需要用到SQL Server 2005中Business Intelligence Development Studio的Analysis Services组件，即SSAS。这里将创建用于福马特商店销售分析的数据立方，首先要创建一个SSAS的项目。打开Business Intellig

12、ence Development Studio，选择【文件】【新建】【项目】命令，或按【Ctrl+Shift+N】组合键以显示“新建项目”对话框。在“新建项目”对话框中，从“项目类型”选项组中选择“商业智能项目”。从“Visual Studio已安装的模板”选项组中选择“Analysis Services 项目”。在“名称”文本框中输入“foodmartsaleAS”作为项目名称，如图10所示。单击【确定】按钮进入SSAS的工作界面。图10 建立foodmartsaleAS项目打开“解决方案资源管理器”，可以看到数据源、数据源视图、多维数据集、维度和挖掘结构等8个文件夹对象，建立和管理数据立方

13、也是主要针对这8个对象进行的。7.定义数据源 这是创建数据立方的第1步。在“解决方案资源管理器”中的“数据源”文件夹上单击鼠标右键，在弹出的快捷菜单中选择【新建数据源】命令，如图2-11所示。图11 新建数据源 在弹出的“选择如何定义连接”窗口中选择“基于现有连接或新连接创建数据源”单选按钮，在“数据连接”列表框中选择foodmartsaleDW数据源，如果没有此连接，可以单击【新建】按钮，定义指向foodmartsaleDW 数据仓库的连接。设置完成后的页面如图12所示。由于向导下面的操作是没有必要的，所以这里可以直接单击【完成】按钮结束数据源向导。图12 定义数据连接8.定义数据源视图 为

14、了对多维数据集进行这些高级操作，这一步定义数据源视图。可以在“数据源视图”文件夹对象上单击鼠标右键，在弹出的快捷菜单中选择【新建数据源视图】命令，然后在弹出的“数据源视图向导”窗口中选择foodmartsaleDW选项作为关系数据源，单击【下一步】按钮。这时会弹出如图13所示的“名称匹配”窗口，其原因是在前面SSIS的数据装载操作中没有为数据仓库中的表设置主键及其关系，SSAS试图在匹配的列上创建逻辑关系，可以选择“与主键同名”单选按钮，单击【下一步】按钮继续。图13 设置名称匹配进入“选择表和视图”窗口，如图14所示。可以从选定的数据源提供的对象列表中选择表和视图。以上操作完成后，单击【下一

15、步】按钮，为此数据源视图命名为Vfoodmartsale，再单击【完成】按钮结束此向导。图14 选择表和视图同样也是由于数据源中的表没有设置主键的原因，现在打开的数据源视图上的表都是独立的，相互之间没有关系，还需要我们设置各个表的主键及其关系才能成为可用的视图。一般来说，事实表是没有主键的，而维度表都有主键，且每一个维度表的主键都是事实表的外键，因而，需要为每一个维度表设置主键，如图15所示，在维度表中选择应该为主键的字段，然后单击鼠标右键，在弹出的快捷菜单中选择【设置逻辑主键】命令，即可设置维度表的主键。图15 设置维度表的主键对每一个维度表设置好主键后，应该设置维度表和事实表之间的关系，方

16、法是把事实表中的外键作为源，拖动到维度表中的相关字段，这时会弹出“创建关系”窗口，如图16所示。需要注意的是一定要把外键表作为源，主键表作为目标，如果方向错了，可以单击图16的【反向】按钮以保证其关系符合业务数据中的逻辑关系。图2-16 “创建关系”对话框按照以上的步骤，设置好维度表和事实表之间的关系后，数据源视图将会如图17所示。图17 设置好表间关系后的数据源视图8. 生成多维数据集这一步在上面创建的数据源视图的基础上生成多维数据集，方法如下：（1）在“解决方案资源管理器”中用鼠标右键单击“多维数据集”文件夹对象，在弹出的快捷菜单中选择【新建多维数据集】命令。（2）在弹出的“多维数据集向导

17、”欢迎界面中单击【下一步】按钮进入“选择生成方法”窗口，如图18所示。在此窗口中选择“使用数据源生成多维数据集”单选按钮，并且确认选中了“自动生成”复选框，并在下拉列表框中选择“创建属性和层次结构”选项，以便向导能为维度表中的大多数列创建属性，并尝试建立包含多级的层次结构。图18 “选择生成方法”窗口（3）单击【下一步】按钮，选择前面创建的数据源视图来为多维数据集提供数据。再单击【下一步】按钮，向导将扫描关系架构，以识别事实表和维度表。识别完成后，单击【下一步】按钮将会弹出如图19所示的“标识事实数据表和维度表”窗口。选择“time_by_day”作为时间维度表，并且按照图中所示来分别设置各表

18、为事实表或是维度表。设置好后单击【下一步】按钮继续。图19 标识事实数据表和维度表（4）由于上一步选择了“time_by_day”作为时间维度表，这一步需要设置时间维度的层次结构。如图20所示，依据时间表中的具体情况，为年月日等时间属性指定时间表列。设置好后单击【下一步】按钮继续。图20 指定时间维度层次结构（5）这时系统将会让用户选择度量值，如图21所示。度量是来源于事实表中的数据，在图中选择“Store Sales”、“Store Cost”和“Unit Sales”为度量。单击【下一步】按钮继续。图21 选择度量值（6）这时系统将会依据前面对维度表和事实表的设置来检测层次结构。如果前面的

19、设置都是正确的，则会成功检测其层次结构，检测完毕，单击【下一步】按钮继续。（7）由于前面系统自动检测了维度之间的层次关系，因此，产生了一些新的维度，这一步向导将提供一个窗口来查看新建维度的结构并根据需要进行更改。此例中的新建维度结构关系如图22所示。设置完成后单击【下一步】按钮，为多维数据集指定一个名称，再单击【完成】按钮，结束多维数据集的创建向导。图22 维度层次结构关系（8）完成向导后，可以查看建立的多维数据集结构，如图23所示为多维数据集的数据源视图，这里的视图表达的是多维数据集的表间关系，而且用黄色标记了事实表，蓝色标记了维度表。图23 多维数据集的数据源视图（9）双击刚才创建的多维数

20、据集，系统将会切换到多维数据集设计窗口，如图24所示的是该窗口的主要标签，它们标识了可以针对多维数据集进行的各种操作。图24 多维数据集设计界面的主要标签（10）这一步将把创建好的多维数据集部署到Analysis Services数据库中，如图25所示，在多维数据集上单击鼠标右键，在弹出的快捷菜单中选择【处理】命令，在“是否生成和部署项目”的对话框中选择“是”选项。待部署结束，系统将会弹出“处理多维数据集”对话框，单击【运行】按钮，程序将会对多维数据集进行处理，处理完毕后，在“处理进度”对话框中单击【关闭】按钮结束处理过程。以上操作都顺利进行，则此多维数据集已经顺利地部署到了Analysis

21、Services数据库。打开SQL Server Management Studio，在对象资源管理器中的连接下拉菜单中选择【Analysis Services】命令，如图26所示。输入相应的认证信息，即可进入Analysis Services，部署成功，则会有如图27所示的界面。 图25 处理多维数据集 图26 连接Analysis Services图27 成功部署多维数据集后Analysis Services的界面9. 创建和使用报表创建基于多维数据集的报表可以对数据立方进行全方位地钻取，按照第1章对商业智能的认识可知道，这是商业智能的重要表现。按照BI解决方案的体系结构，如果说SSIS整

22、合了不同源的数据，实现了ETL的功能；SSAS对数据进行了相应的分析，创建了Cube和OLAP服务所需的数据库；那么报表应该是属于BI体系中的前端展现部分，需要使用SSRS报表服务来实现。10. 创建报表报表可以使用完全自定义的方式来创建，也可以使用报表向导来创建，其操作步骤如下：（1）打开Business Intelligence Development Studio，选择【文件】【新建】【项目】命令，弹出“新建项目”对话框。在“项目类型”选项组中，选择“商业智能项目”选项，在“模板”选项组中，选择“报表服务器项目向导”选项。将报表服务器项目取名为foodmartsaleRPT，单击【确定】

23、按钮，如图28所示。图28 新建报表项目向导（2）系统将弹出“报表向导”窗口，并告诉我们此向导将完成选择从中获取数据的数据源、设计对数据源执行的查询、选择要创建的报表类型、指定报表的基本布局、指定报表的格式设置等步骤，单击【下一步】按钮继续。（3）此时系统弹出“选择数据源”窗口，为报表选择或创建所需数据的来源，输入“foodmartsale”作为名称，选择Analysis Services作为数据连接的类型，如图29所示。再单击【编辑】按钮，设置此数据源的连接属性，部署到“Analysis Services”的数据库，通过单击【测试连接】按钮确认此连接的正确性。设置好后，单击【确定】按钮返回“

24、选择数据源”窗口，单击【下一步】按钮继续操作。（4）在弹出的“设计查询”窗口中单击【查询生成器】按钮，得到如图30所示的查询生成器窗口。该窗口主要有4个部分组成，左上角为元数据，左下角为计算成员，右上角为维度筛选，右下角是相应维度和量度组成的查询结果。选择“Time By Day”维度的“the_month”字段和“Product”维度中具有层次结构的“Product_Category”、“Product_Class”和“Product”3个字段，而量度选择“Store Cost”和“Store Sales”2个字段，最后结果如图29所示。图29 选择数据源窗口图30 查询生成器窗口（5）由于

25、这里只需要分析1998年的销售数据，可以把Time By Day维度从“元数据”窗格拖曳到维度筛选窗格中，进行如图31所示的设置。完成后单击【确定】按钮回到“设计查询”对话框，可以看到在“查询字符串”中已经有刚才设计的查询的代码。单击【下一步】按钮继续操作。图31 进行维度筛选（6）这时系统将要让用户选择要创建的表报类型，选择“表格格式”， 单击【下一步】按钮进入如图32所示的“设计表”窗口。把“the_month”字段作为分页所用的字段，Product维度的3个层次的字段依次作为分组字段，而2个量度则作为详细信息字段，其结果如图32所示。单击【下一步】按钮继续。图32 “设计表”窗口（7）在弹出的“选择表布局”窗口中选择“递进”选项，同时为了能对相关的数据自动汇总，体现报表的智能特性，选中“包括小计”和“启用明细”2个选项，如图33所示，设置完后单击【下一步】按钮继续操作。图33 “选择表布局”窗口（8）这一步先在“选择表样式”窗口中选择一个窗口样式选项，单击【下一步】按钮将会弹出“选择部署位置”窗口，这是设置以B/S（浏览器/服务器