数据仓库的构建和ETL课程设计

Northwind数据仓库的构建和ETL课程设计与实验报告课程设计与实验教学目的与基本要求数据仓库与知识工程课程设计与实验是学习数据仓库与知识工程的重要环节，通过课程设计与实验，可以使学生全面地了解和掌握数据仓库与知识工程课程的基本概念、原理及应用技术，使学生系统科学地受到分析问题和解决问题的训练，提高运用理论知识解决实际问题的能力。使学生在后继课的学习中，能够利用数据仓库与数据挖掘技术及实践经验，解决相应的实际问题，并能在今后的学习和工作中，结合自己的专业知识，开发相应的数据仓库与数据挖掘应用程序。培养学生将已掌握的理论与实践开发相结合的能力，以及在应用方面的思维能力和实践动手能力。课程设计与实验一 数据仓库的构建和ETL（一）目的1理解数据库与数据仓库之间的区别与联系；2掌握数据仓库建立的基本方法及其相关工具的使用。3掌握ETL实现的基本方法及其相关工具的使用。（二）内容1. 以SQL Server为系统平台，设计、建立创建数据仓库NorthwindDW（根据课程设计内容）。2. 将业务数据库Northwind的数据经过ETL导入（或加载）到数据仓库NorthwindDW。3. 将数据仓库NorthwindDW事实表的前100个记录导出到Excel中。（三）数据仓库设计要求Northwind数据库存储了一个贸易公司的订单数据、产品数据、顾客数据、员工数据、供货商数据等，假设贸易公司的经营者迫切的需要准确地把握贸易公司经营情况，跟踪市场趋势，更加合理地制定商品采购、营销和奖励政策。具体的分析需求是： l 分析某商品在某地区的销售情况 l 分析某商品在某季度的销售情况 l 分析某年销售多少金额的产品给顾客 l 分析某员工的销售业绩 任务：确定主题域、确定系统（或主题）的边界。设计数据模型（星型模型）的事实表和维表。实验步骤一、主题需求分析:主题域：订单主题，商品主题，员工主题；订单相关边界：Orders，Order Details，Employees；商品相关边界：Products， Order Details；员工相关边界：Employees，Orders；实验步骤二、构建数据模型以及相应的事实表和维度表：1.原始关系图:2.去除不需要的表和与需求分析无关的字段:5.根据信息分析包以及表间关系图设计具体维度表和事实表:Orders事实表结构以及主键字段：字段名称数据类型功能描述原表名原字段EmployeeIDInt员工号码EmployeesEmployeeIDProductIDInt产品号码ProductsProductIDCustomerIDnchar顾客号码CustomersCustomerIDOrderDatedatetime订购日期OrdersOrderDateUnitPricemoney产品单价Order DetailsUnitPriceTotalmoney单项总价无Quantity * UnitPrice*DiscountQuantitysmallint订购数量Order DetailsQuantityDiscountreal折扣Order DetailsDiscount主键字段：EmployeeID, ProductID, CustomerID, OrderDateEmployee 员工维度表结构以及主键字段：字段名称数据类型功能描述原表名原字段EmployeeIDInt员工号码EmployeesEmployeeIDFirstNamenvarchar员工名EmployeesFirstNameLastNamenvarchar员工姓EmployeesLastName 主键字段：EmployeeIDProduct产品维度表结构以及主键字段：字段名称数据类型 功能描述原表名原字段ProductIDInt产品号码ProductsProductIDProductNamenvarchar产品名称ProductsProductNameUnitPricemoney单价ProductsUnitPrice 主键字段：ProductIDCustomer顾客维度表结构以及主键字段：字段名称数据类型 功能描述原表名原字段CustomerIDnchar 顾客号码CustomersCustomerIDContactNamenvarchar 顾客姓名CustomersContactNameAddressnvarchar 大致地区CustomersCity+Region+Country主键字段：CustomerIDTime时间维度表结构以及主键字段：字段名称数据类型功能描述原表名原字段OrderDatedatetime日期OrdersOrderDateYearnvarchar年无OrderDate拆分Quarternvarchar季度无OrderDate拆分运算Monthnvarchar 月无OrderDate拆分主键字段：OrderDateRegion地区维表结构字段名称数据类型功能描述原表名原字段Addressnvarchar大致地区CustomersAddressCitynvarchar城市无Address拆分Countrynvarchar国家无Address拆分Regionnvarchar地区无Address拆分主键字段：Address6. 下图显示了Northwind数据库的星型雪花架构结构图：Employees维表Orders事实表Products维表EmployeeIDemployeeIDProductIDFirstNameProductIDProductnameLastNameCustomerIDUnitPriceorderDateTotalQuantityUnitPriceDiscountCustomers维表CustomerIDContactNameCityRegionCountry实验步骤三、创建数据仓库并抽取转换导入数据：1.首先打开SQL Server Business Intelligence Development Studio, 创建一个新的SQL Server Integration Services项目,并取名为northwind2.点击确定后生成以下界面。3.接下来在菜单栏中点击项目选择SSIS导入和导出向导，在弹出来的窗口里选择SQL Native Client 10.0 作为数据源，数据库下拉选择已装好的Northwind数据库，点击下一步。 4.进入数据目标的操作界面，这里需要新建一个数据仓库，以后数据经过ETL过程后，数据存放到这个数据仓库中。 5.选择数据的复制方式，如下图所示，有两种方式，一种是直接将表复制过去，这种方式不是很灵活，对多表连接存在一定的局限性。这里采用的第二种方法。6.接下来输入SQL语句，目前进行提取和转换的是事实表orders，sql语句必须写正确，要不然会影响后面的导入数据。点击分析可以查看是否有误，没有的话即可进入下一步。7.接下来可以编辑表之间的映射关系。当时我想把表名改成其他更形象具体的名字，可是不知道为什么，如果把【查询】改了之后，后面的数据加载就会有问题。这也是一个有待解决的问题。8.点击完成后即可以完成本次表格的导入。9接下来如下图箭头所示，准备sql任务以结束，进入数据流阶段。按照选择屏幕左边的工具箱，拖出来数据流源的OLE DB源和数据流目标的OLE DB目标两个元件到界面上。10. 右击OLE DB源选择编辑，OLE DB连接管理器那一定要看清楚是对什么进行的编辑，如果是源，就要选source.。然后再对目标进行编辑，需要新建一个表来进行映射。11.直到所有的与事实表相关的维表全部编辑完，右击右侧的你刚刚生成的包，设为启动项，再运行程序。12.调试成功的话所有的元件底色都会变成绿色，如图所示：13.此时回到NorthDW数据仓库中可以发现，全部的表已成功导入完毕，接下来回到数据库系统中给所生成的各表手动建立主键约束以及关系设置，全部操作完成之后本次数据仓库抽取-转换-导入数据宣告完成。Orders事实表Product维表Employee维表Customer维表实验步骤四、导出指定内容数据至Excel表格：1. 执行事实表Sales的数据导出至excel操作，首先右键单击NorthwindDW数据仓库，在弹出的菜单下依次点击任务->导出数据。2. 接着在需要复制的数据源处选择SQL Native Client 10.0，数据库选择NorthDW，点击下一步。3. 选择目标为Microsoft Excel，自行设置文件存放路径，Excel版本推荐选择97-2005，然后点击下一步，选择编写查询以指定要传输的数据。4. 接着使用SQL语句写出想要导出来的表以及相应的内容即可。本次目标是导出Sales事实表的前100行5. 点击下一步后即可生成对应的Excel文件，导出任务成功，事实表前100个记录。6. 实验结果：1. 确定主题域、确定系统（或主题）的边界。完成了NorthDW的星型雪花结构设计，确定全部维表和事实表。2.设计、建立创建数据仓库NorthDW。3.将业务数据库Northwind的数据经过ETL导入（或加载）到数据仓库NorthDW。4.将数据仓库NorthDW事实表的前100个记录导出到Excel中。 实验问题和总结： 本次实验花了我很多的心思和心血，主要是觉得自己基础太薄弱。实验的最终结果由于时间关系跟自己最初想做的不太一样。主要是在开始花掉了太多的时间，但是整个流程自己还是了解得挺好了，所以说做出一个更好的东西出来也只是时间问题。 在做的过程中，遇到了很多小问题。1. 创建数据仓库表的时候，到底是从原有的数据库中复制还是用sql语句创建复制。刚开始我是用的sql语句复制，当时就因为一个小小的圆点问题耽搁了很多时间，没有解决出来，就尝试用原有数据库中的表复制，但是问题就来了，多个表连接再映射没法做，最后还是通过sql语句实现的。2. 表映射完后，对数据的加载也出现了问题。刚开始老不成功，最后发现时表重复的原因，因为自己反复新建了很多个项目和相同的表，只要把书库仓库中的同名表删掉就可以加载了。导出数据的时候也出现了问题，后来发现原来我的表名和关键字重复了，只需要将数据仓库的order表重新命名就好了。3.4 生成数据仓库表的关系图的时候，由于两张表的customerID数据类型不同，则不能添加主外键关系，本来想直接在数据库里改的，但是不允许修改，就只能借助数据转换工具，但是数据转换现在还没有研究成功。5. 其实还想做更多的任务，比如字段的拼接，由于时间关系这次没有做，但是下来会花时间来做做。遇到的问题肯定不止这一点点，但都是些很小的问题，通过自己的研究和同学的帮助最终做出一个勉强的东西，但是下来我会接着研究的，因为通过学习，感觉数据仓库还是挺有趣的。