构建FoodMart数据仓库

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

2、Mart数据库涉及到公司经营的各个方面，包括产品、库存、人事、客户和销售等。一个真正的商业智能应用应该对这些业务需求进行全面地考虑。本章截取这些需求中的销售部分构建商业智能 打开配套文件中附带的foodmart.mdf文件，可以看到如图2-1所示的表,各个表的说明,人事管理三个表中：员工信息employee、职务信息position、员工所属部门department，对应二个维度employee和department维度。 库存管理业务中的仓库类型存储在warehouse_class表中,具体的仓库存储在warehouse中。,四个多维数据集,Warehouse and Sales多维数据集

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

4、表之间的逻辑关系上。 要从业务数据出发设计数据仓库的结构，必须明确业务数据本身的结构，而业务数据的关系一般是基于关系数据库设计的范式。数据仓库中表的关系不受关系数据库设计范式的约束，但也要遵循一定的结构规范，如星形结构和雪花形结构即是这种类型的规范。,这里希望用雪花形结构来构建福马特商店的销售数据仓库，逻辑结构设计图如图2-2所示,销售数据仓库雪花形结构设计图,在数据仓库的逻辑结构中，数据表可以划分为两类：一类是事实数据表（简称为“事实表”），用来存储数据仓库中的实际数据，如这里存储1998年销售数据的sales_fact_1998表即为事实表； 另一类是维度数据表（简称为“维度表”），用来存

5、储数据仓库中的维度数据，如这里的关于时间、促销手段和产品等分析要素的表均为维度表。,注意：在本例中设计的维度表和事实表与原始数据中的表名及结构都一致，这主要是由原始数据的特点和本章作为入门章节的定位决定的。在实际设计的时候，通常需要根据需求情况重新建立与原始数据不同的表结构。 这主要是由于传统业务的数据库是用来进行事务处理的（即OLTP），而数据仓库则是用来进行分析处理的（即OLAP），用途的不同决定了其结构的不同。这一点在以后复杂的数据仓库设计中会通过示例体现出来。,2.1.3 创建foodmartsale数据仓库,数据仓库也是一种数据库，其管理同样是通过数据库管理系统（DBMS）来进行的。

6、因此数据仓库可以像普通数据库一样进行创建、修改和删除。当数据仓库的逻辑结构设计完后，就可以创建物理数据仓库了,在SQL Server Management Studio建立名为“foodmartsaleDW”的数据库，然后把这里设计的表创建好，数据类型依据原始数据库中的各个表和字段的数据类型设置。 但由于这里数据仓库的表结构与原始数据库中的表结构基本一致，因此，创建foodmartsaleDW数据仓库的物理结构过程也可以在ETL阶段完成,2.2 设计和使用ETL,这里的任务就是要把数据从其他类型数据库或其他类型数据载体如access中装载到foodmartsaleDW数据仓库中。需要使用到SQ

7、L Server Integration Services服务，即SSIS,SSIS,（1）打开Business Intelligence Development Studio，选择【文件】【新建】【项目】命令，弹出“新建项目”对话框，展开“商业智能项目”，在“模板”窗格中，单击“Integration Services项目”，把项目命名为“foodmartsale ETL”，如图2-3所示。,SSIS,SSIS,这时会在BI Studio环境中打开用于设计SSIS的各种工具和窗口，数据提取、转换和加载的操作都在这个界面下进行。 （2）选择【项目】【SSIS导入和导出向导】命令，这时会弹出SS

8、IS导入和导出向导的欢迎界面，单击【下一步】按钮。 （3）在“选择数据源”窗口中的“数据源”下拉列表框中选择Access数据源选项，如图2-4所示。然后在路径选择中选择此项目文件夹中的foodmart 2000.mdb文件。,SSIS,SSIS,2-7,SSIS,SSIS,这时会让用户选择源表和源视图，如图2-7所示。按照前面对数据仓库的设计，这里选择原始表中的7个表：time_by_day、promotion、product、product_class、customer、store和sales_fact_1998表作为需要输入的表。,这里对原始表中需要导入到数据仓库的数据有很强的可定制性，对

9、这些已经选择的表中的字段还可以进行筛选和改变，对不需要的字段进行去除操作，这就是所谓的数据清洗。 当然也可以选择其他的表一起导入数据仓库中。可以看到，不一定所有的业务数据库中的数据都必须体现在数据仓库中，数据仓库中的数据也有可能是经过业务数据库中的数据运算而得到的，这都取决于具体商务活动的需求。,在图2-7的界面中，还可以对数据导入的目标进行定制，可以对映射方式进行编辑，甚至可以自己写“CREATE TABLE”语句作为复制的目标表。这些改变都可以体现在数据仓库的物理结构中。,这里不对映射及其目标进行变更，保持默认的状态，使生成的数据仓库的物理模型完全符合前面对数据仓库的逻辑模型的设计。 （7

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

11、W数据仓库中。 注意：新建的数据仓库中的表需要设置必要的主键及外键，否则无法建立多维数据集,2.3 创建OLAP数据立方,设计好了结构良好的数据仓库，并且将需要分析的业务数据装载到了数据仓库中之后，就为满足商务决策的全方位需求打下了根基，以后的操作都是基于这些拥有数据的数据仓库进行的。 但是，对数据的多维分析却并不是主要针对数据仓库，而是针对从数据仓库中提取的子集，如数据集市和多维数据集（也称为数据立方）。因此通常还需要在具体分析数据之前创建数据立方。,数据立方的创建和管理需要用到SQL Server 2005中Business Intelligence Development Studio的

12、Analysis Services组件，即SSAS。,这里将创建用于福马特商店销售分析的数据立方，首先要创建一个SSAS的项目。 打开Business Intelligence Development Studio，选择【文件】【新建】【项目】命令，或按【Ctrl+Shift+N】组合键以显示“新建项目”对话框。在“新建项目”对话框中，从“项目类型”选项组中选择“商业智能项目”。从“Visual Studio已安装的模板”选项组中选择“Analysis Services 项目”。在“名称”文本框中输入“foodmartsaleAS”作为项目名称，如图2-10所示。单击【确定】按钮进入SSAS的

13、工作界面。,建立foodmartsaleAS项目,打开“解决方案资源管理器”，可以看到数据源、数据源视图、多维数据集、维度和挖掘结构等8个文件夹对象，建立和管理数据立方也是主要针对这8个对象进行的。下面介绍创建数据立方的步骤,2.3.1 定义数据源,这是创建数据立方的第1步。在“解决方案资源管理器”中的“数据源”文件夹上单击鼠标右键，在弹出的快捷菜单中选择【新建数据源】命令，如图2-11所示。,新建数据源,在弹出的“选择如何定义连接”窗口中选择“基于现有连接或新连接创建数据源”单选按钮，在“数据连接”列表框中选择foodmartsaleDW数据源，如果没有此连接，可以单击【新建】按钮，定义指向

14、foodmartsaleDW 数据仓库的连接。设置完成后的页面如图2-12所示。由于向导下面的操作是没有必要的，所以这里可以直接单击【完成】按钮结束数据源向导。,定义数据连接,2.3.2 定义数据源视图,数据源提供与数据库的简单连接，但更多高级功能，如缓存元数据、添加关系、创建计算和设置逻辑键等还需要使用数据源视图来完成。为了对多维数据集进行这些高级操作，这一步定义数据源视图。,可以在“数据源视图”文件夹对象上单击鼠标右键，在弹出的快捷菜单中选择【新建数据源视图】命令，然后在弹出的“数据源视图向导”窗口中选择foodmartsaleDW选项作为关系数据源，单击【下一步】按钮。这时会弹出如图2-

15、13所示的“名称匹配”窗口，其原因是在前面SSIS的数据装载操作中没有为数据仓库中的表设置主键及其关系，SSAS试图在匹配的列上创建逻辑关系，可以选择“与主键同名”单选按钮(SSAS2008没有这个功能) ，单击【下一步】按钮继续 。,设置名称匹配,这时进入“选择表和视图”窗口，如图2-14所示。可以从选定的数据源提供的对象列表中选择表和视图。这里主要的商务需求是对销售数据进行分析，所以可以把相关的数据表都选入数据源视图，对数据源中与分析需求关联不大的要素也可以不选入此分析视图，例如此处的region表可以不用选入数据源视图。,以上操作完成后，单击【下一步】按钮，为此数据源视图命名为Vfood

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

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

18、向导”欢迎界面中单击【下一步】按钮进入“选择创建方法”窗口，如图2-18所示。并在下拉列表框中选择“使用现有表”选项，以便向导能为维度表中的大多数列创建属性，并尝试建立包含多级的层次结构,“选择创建方法”窗口,（3）单击【下一步】按钮，选择前面创建的数据源视图来为多维数据集提供数据。再单击【下一步】按钮，向导将扫描关系架构，以识别事实表和维度表。识别完成后，单击【下一步】按钮将会弹出如图2-19所示的“选择度量值组表”窗口。选择“1998年销售事实数据”含有度量值的事实表，并且按照图中所示来设置度量值：销售额、成本和数量。设置好后单击【下一步】按钮继续。,选择度量值,选择维度表,（3）单击【下

19、一步】按钮，选择前面创建的数据源视图来为选择维度表 。再单击【下一步】按钮，向导将扫描关系架构，单击【下一步】按钮将会弹出如图2-19所示的“选择维度表 ”窗口。这里不选择“time_by_day”作为时间维度表，其余的全选中，并且按照图中所示来分别设置各表为维度表。设置好后单击【下一步】按钮继续。 时间维度需要特殊设置。,选择维度表,（4）由于上一步选择了“time_by_day”作为时间维度表，这一步需要设置时间维度的层次结构。在所有维度的层次结构中，只有时间维度最为特殊。其他维度系统可以根据数据之间的关系检测其层次结构，而时间维度则需要指定其时间上的层次。如图2-20所示，依据时间表中的

20、具体情况，为年月日等时间属性指定时间表列。 设置好后单击【下一步】按钮继续。,指定时间维度层次结构,主表选择：“按天计时”,指定时间维度层次结构,维度属性中：月份、年度和季节属性修改为日历的相应属性,指定时间维度层次结构,4个维度属性,（6）这时系统将会依据前面对维度表和事实表的设置来检测层次结构。如果前面的设置都是正确的，则会成功检测其层次结构，检测完毕，单击【下一步】按钮继续。 （7）由于前面系统自动检测了维度之间的层次关系，因此，产生了一些新的维度，这一步向导将提供一个窗口来查看新建维度的结构并根据需要进行更改。此例中的新建维度结构关系如图2-22所示。可见，由程序自动分析出来的层次结构和用户自己设置的时间层次结构都是和业务数据中的逻辑结构相符合的。如果不符合，则可以在这一步进行修改。设置完成后单击【下一步】按钮，为多维数据集指