构建基于FoodMart数据库的数据仓库建立与OLAP(实验一)

构建基于FoodMart数据库的福马特商业智能系统 按照学习理论 大处着眼 小处着手 是快速学习的好方法 它既能把握全局 又能掌握细节内容安排上属于实践中的 务实 理论上的 务虚 只着眼于操作过程而忽略其中的深奥理论 目的在于明确一个商业智能应用的全貌 基于FoodMart数据库的福马特商业智能系统 FoodMart数据库是SQLServer以前版本所带的示例数据库 它模拟了一家大型的食品连锁店的经营业务所产生的数据 其商业数据保存在一个数据库中 其中包括了客户管理数据 销售数据 分销数据和库存数据等 随着业务量的增加 这个食品连锁店的老板迫切需要多方位地掌握其经营状况 而传统的报表形式和数据处理方式已经不能满足这一要求 因此在保留历史数据的基础上构建商业智能应用已经迫在眉睫 下面就描述满足这一商务需求的技术实现过程 FoodMart数据库涉及到公司经营的各个方面 包括产品 库存 人事 客户和销售等 一个真正的商业智能应用应该对这些业务需求进行全面地考虑 本章截取这些需求中的销售部分构建商业智能 打开配套文件中附带的foodmart mdf文件 可以看到如图2 1所示的表 人事管理中的员工信息存储在employee表中员工所属部门信息存储在department表中职务信息则存储在position表中库存管理业务中的仓库类型存储在warehouse class表中具体的仓库存储在warehouse中 2 1 2设计数据仓库逻辑模型 福马特市场部的商务需求是要对1998年进行的所有销售业务数据进行多角度分析 以便市场分析人员能在查询数据库时获取快速的响应 高层管理人员也能从总体上把握影响本年度销售的因素 这需要利用存储在公司业务数据库中的数据 建立数据仓库 进而创建可用于分析的多维数据结构 如前所述 这里只着眼于销售方面的数据 因而把与销售相关的表提炼出来进行分析 在foodmart数据库中 销售业务的数据和时间 促销手段 产品和店铺等都有关系 它们的关系体现在表与表之间的逻辑关系上 要从业务数据出发设计数据仓库的结构 必须明确业务数据本身的结构 而业务数据的关系一般是基于关系数据库设计的范式 数据仓库中表的关系不受关系数据库设计范式的约束 但也要遵循一定的结构规范 如星形结构和雪花形结构即是这种类型的规范 同时这也是数据仓库逻辑结构的两种类型 这里希望用雪花形结构来构建福马特商店的销售数据仓库 逻辑结构设计图如图2 2所示 销售数据仓库雪花形结构设计图 在数据仓库的逻辑结构中 数据表可以划分为两类 一类是事实数据表 简称为 事实表 用来存储数据仓库中的实际数据 如这里存储1998年销售数据的sales fact 1998表即为事实表 另一类是维度数据表 简称为 维度表 用来存储数据仓库中的维度数据 如这里的关于时间 促销手段和产品等分析要素的表均为维度表 注意 在本例中设计的维度表和事实表与原始数据中的表名及结构都一致 这主要是由原始数据的特点和本章作为入门章节的定位决定的 在实际设计的时候 通常需要根据需求情况重新建立与原始数据不同的表结构 这主要是由于传统业务的数据库是用来进行事务处理的 即OLTP 而数据仓库则是用来进行分析处理的 即OLAP 用途的不同决定了其结构的不同 这一点在以后复杂的数据仓库设计中会通过示例体现出来 2 1 3创建foodmartsaleDW数据仓库 数据仓库也是一种数据库 其管理同样是通过数据库管理系统 DBMS 来进行的 因此数据仓库可以像普通数据库一样进行创建 修改和删除 当数据仓库的逻辑结构设计完后 就可以创建物理数据仓库了 这时可以在SQLServerManagementStudio中按照一般的建立数据库的方法建立一个名为 foodmartsaleDW 的数据库 然后把这里设计的表创建好 数据类型依据原始数据库中的各个表和字段的数据类型设置但由于这里数据仓库的表结构与原始数据库中的表结构基本一致 因此 创建foodmartsaleDW数据仓库的物理结构过程也可以在ETL阶段完成 2 2设计和使用ETL 这里的任务就是要把数据从其他类型数据库或其他类型数据载体中装载到foodmartsaleDW数据仓库中 需要使用到SQLServerIntegrationServices服务 即SSIS 1 打开BusinessIntelligenceDevelopmentStudio 选择 文件 新建 项目 命令 弹出 新建项目 对话框 展开 商业智能项目 在 模板 窗格中 单击 IntegrationServices项目 把项目命名为 foodmartsaleETL 如图2 3所示 这时会在BIStudio环境中打开用于设计SSIS的各种工具和窗口 数据提取 转换和加载的操作都在这个界面下进行 2 选择 项目 SSIS导入和导出向导 命令 这时会弹出SSIS导入和导出向导的欢迎界面 单击 下一步 按钮 3 在 选择数据源 窗口中的 数据源 下拉列表框中选择Access数据源选项 如图2 4所示 然后在路径选择中选择此项目文件夹中的foodmart2000 mdb文件 2 7 这时会让用户选择源表和源视图 如图2 7所示 按照前面对数据仓库的设计 这里选择原始表中的time by day promotion product product class customer store和sales fact 1998表作为需要输入的表 这里对原始表中需要导入到数据仓库的数据有很强的可定制性 对这些已经选择的表中的字段还可以进行筛选和改变 对不需要的字段进行去除操作 这就是所谓的数据清洗 当然也可以选择其他的表一起导入数据仓库中 可以看到 不一定所有的业务数据库中的数据都必须体现在数据仓库中 数据仓库中的数据也有可能是经过业务数据库中的数据运算而得到的 这都取决于具体商务活动的需求 在图2 7的界面中 还可以对数据导入的目标进行定制 可以对映射方式进行编辑 甚至可以自己写 CREATETABLE 语句作为复制的目标表 这些改变都可以体现在数据仓库的物理结构中 这里不对映射及其目标进行变更 保持默认的状态 使生成的数据仓库的物理模型完全符合前面对数据仓库的逻辑模型的设计 7 以上操作完成后 单击 下一步 按钮 系统将会把前面的操作列表并要求用户确认 并提示将会把包以 Package1 dtsx 作为文件名保存在项目文件夹下面 而且不会立即执行 确认无误后单击 完成 按钮 8 在 解决方案资源管理器 中展开 SSIS包 文件夹 在Package1 dtsx上单击鼠标右键 在弹出的快捷菜单中选择 设为启动对象 命令 如图2 8所示 9 单击工具条上的运行按钮运行这个工程 可以发现在SSIS设计界面的 控制流 和 数据流 等选项卡内都有对象在活动 这是系统正在把数据从foodmart2000 mdb数据库中按照前面所确定的规则装载入foodmartsaleDW数据仓库中 新建的数据仓库中的表需要设置必要的主键及外键 否则无法建立多维数据集SSIS的进一步使用请参照朱德利书第四章案例见4 3 2 3创建OLAP数据立方 设计好了结构良好的数据仓库 并且将需要分析的业务数据装载到了数据仓库中之后 就为满足商务决策的全方位需求打下了根基 以后的操作都是基于这些拥有数据的数据仓库进行的 但是 对数据的多维分析却并不是主要针对数据仓库 而是针对从数据仓库中提取的子集 如数据集市和多维数据集 也称为数据立方 因此通常还需要在具体分析数据之前创建数据立方 数据立方的创建和管理需要用到SQLServer2005中BusinessIntelligenceDevelopmentStudio的AnalysisServices组件 即SSAS 这里将创建用于福马特商店销售分析的数据立方 首先要创建一个SSAS的项目 打开BusinessIntelligenceDevelopmentStudio 选择 文件 新建 项目 命令 或按 Ctrl Shift N 组合键以显示 新建项目 对话框 在 新建项目 对话框中 从 项目类型 选项组中选择 商业智能项目 从 VisualStudio已安装的模板 选项组中选择 AnalysisServices项目 在 名称 文本框中输入 foodmartsaleAS 作为项目名称 如图2 10所示 单击 确定 按钮进入SSAS的工作界面 建立foodmartsaleAS项目 打开 解决方案资源管理器 可以看到数据源 数据源视图 多维数据集 维度和挖掘结构等8个文件夹对象 建立和管理数据立方也是主要针对这8个对象进行的 下面介绍创建数据立方的步骤 2 3 1定义数据源 这是创建数据立方的第1步 在 解决方案资源管理器 中的 数据源 文件夹上单击鼠标右键 在弹出的快捷菜单中选择 新建数据源 命令 如图2 11所示 新建数据源 在弹出的 选择如何定义连接 窗口中选择 基于现有连接或新连接创建数据源 单选按钮 在 数据连接 列表框中选择foodmartsaleDW数据源 如果没有此连接 可以单击 新建 按钮 定义指向foodmartsaleDW数据仓库的连接 设置完成后的页面如图2 12所示 由于向导下面的操作是没有必要的 所以这里可以直接单击 完成 按钮结束数据源向导 定义数据连接 2 3 2定义数据源视图 数据源提供与数据库的简单连接 但更多高级功能 如缓存元数据 添加关系 创建计算和设置逻辑键等还需要使用数据源视图来完成 为了对多维数据集进行这些高级操作 这一步定义数据源视图 可以在 数据源视图 文件夹对象上单击鼠标右键 在弹出的快捷菜单中选择 新建数据源视图 命令 然后在弹出的 数据源视图向导 窗口中选择foodmartsaleDW选项作为关系数据源 单击 下一步 按钮 这时会弹出如图2 13所示的 名称匹配 窗口 其原因是在前面SSIS的数据装载操作中没有为数据仓库中的表设置主键及其关系 SSAS试图在匹配的列上创建逻辑关系 可以选择 与主键同名 单选按钮 单击 下一步 按钮继续 设置名称匹配 这时进入 选择表和视图 窗口 如图2 14所示 可以从选定的数据源提供的对象列表中选择表和视图 这里主要的商务需求是对销售数据进行分析 所以可以把相关的数据表都选入数据源视图 对数据源中与分析需求关联不大的要素也可以不选入此分析视图 例如此处的region表可以不用选入数据源视图 以上操作完成后 单击 下一步 按钮 为此数据源视图命名为Vfoodmartsale 再单击 完成 按钮结束此向导 选择表和视图 同样也是由于数据源中的表没有设置主键的原因 现在打开的数据源视图上的表都是独立的 相互之间没有关系 还需要我们设置各个表的主键及其关系才能成为可用的视图 一般来说 事实表是没有主键的 而维度表都有主键 且每一个维度表的主键都是事实表的外键 因而 需要为每一个维度表设置主键 如图2 15所示 在维度表中选择应该为主键的字段 然后单击鼠标右键 在弹出的快捷菜单中选择 设置逻辑主键 命令 即可设置维度表的主键 设置维度表的主键 对每一个维度表设置好主键后 应该设置维度表和事实表之间的关系 方法是把事实表中的外键作为源 拖动到维度表中的相关字段 这时会弹出 创建关系 窗口 如图2 16所示 需要注意的是一定要把外键表作为源 主键表作为目标 如果方向错了 可以单击图2 16的 反向 按钮以保证其关系符合业务数据中的逻辑关系 创建关系 对话框 按照以上的步骤 设置好维度表和事实表之间的关系后 数据源视图将会如图2 17所示 设置好表间关系后的数据源视图 2 3 3生成多维数据集 这一步在上面创建的数据源视图的基础上生成多维数据集 方法如下 1 在 解决方案资源管理器 中用鼠标右键单击 多维数据集 文件夹对象 在弹出的快捷菜单中选择 新建多维数据集 命令 2 在弹出的 多维数据集向导 欢迎界面中单击 下一步 按钮进入 选择生成方法 窗口 如图2 18所示 在此窗口中选择 使用数据源生成多维数据集 单选按钮 并且确认选中了 自动生成 复选框 并在下拉列表框中选择 创建属性和层次结构 选项 以便向导能为维度表中的大多数列创建属性 并尝试建立包含多级的层次结构 选择生成方法 窗口 3 单击 下一步 按钮 选择前面创建的数据源视图来为多维数据集提供数据 再单击 下一步 按钮 向导将扫描关系架构 以识别事实表和维度表 识别完成后 单击 下一步 按钮将会弹出如图2 19所示的 标识事实数据表和维度表 窗口 选择 time by day 作为时间维度表 并且按照图中所示来分别设置各表为事实表或是维度表 设置好后单击 下一步 按钮继续 标识事实数据表和维度表 4 由于上一步选择了 time by day 作为时间维度表 这一步需要设置时间维度的层次结构 在所有维度的层次结构中 只有时间维度最为特殊 其他维度系统可以根据数据之间的关系检测其层次结构 而时间维度则需要指定其时间上的层次 如图2 20所示 依据时间表中的具体情况 为年月日等时间属性指定时间表列 设置好后单击 下一步 按钮继续 指定时间维度层次结构 5 这时系统将会让用户选择度量值 如图2 21所示 度量是来源于事实表中的数据 在图中选择 StoreSales StoreCost 和 UnitSales 为度量 单击 下一步 按钮继续 选择度量值 6 这时系统将会依据前面对维度表和事实表的设置来检测层次结构 如果前面的设置都是正确的 则会成功检测其层次结构 检测完毕 单击 下一步 按钮继续 7 由于前面系统自动检测了维度之间的层次关系 因此 产生了一些新的维度 这一步向导将提供一个窗口来查看新建维度的结构并根据需要进行更改 此例中的新建维度结构关系如图2 22所示 可见 由程序自动分析出来的层次结构和用户自己设置的时间层次结构都是和业务数据中的逻辑结构相符合的 如果不符合 则可以在这一步进行修改 设置完成后单击 下一步 按钮 为多维数据集指定一个名称