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

1、Northwind 数据仓库的构建和 ETL课程设计与实验报告课程设计与实验教学目的与基本要求数据仓库与知识工程课程设计与实验是学习数据仓库与知识工程的重要环节，通过课程设计与实验， 可以使学生全面地了解和掌握数据仓库与知识工程课程的基本概念、原理及应用技术， 使学生系统科学地受到分析问题和解决问题的训练，提高运用理论知识解决实际问题的能力。使学生在后继课的学习中， 能够利用数据仓库与数据挖掘技术及实践经验， 解决相应的 实际问题， 并能在今后的学习和工作中， 结合自己的专业知识， 开发相应的数据仓库与数据 挖掘应用程序。 培养学生将已掌握的理论与实践开发相结合的能力， 以及在应用方面的思维

2、能力和实践动手能力。课程设计与实验一 数据仓库的构建和 ETL（一）目的 1理解数据库与数据仓库之间的区别与联系； 2掌握数据仓库建立的基本方法及其相关工具的使用。3掌握 ETL 实现的基本方法及其相关工具的使用。（二）内容1. 以SQLServer为系统平台，设计、建立创建数据仓库NorthwindDW （根据课程设计内容）。2. 将业务数据库 Northwind 的数据经过 ETL 导入（或加载） 到数据仓库 NorthwindDW。3. 将数据仓库 NorthwindDW 事实表的前 100 个记录导出到 Excel 中。（三）数据仓库设计要求Northwind 数据库存储了一个贸易公司

3、的订单数据、产品数据、顾客数据、员工数据、 供货商数据等， 假设贸易公司的经营者迫切的需要准确地把握贸易公司经营情况，跟踪市场趋势，更加合理地制定商品采购、营销和奖励政策。具体的分析需求是： 分析某商品在某地区的销售情况 分析某商品在某季度的销售情况 分析某年销售多少金额的产品给顾客 分析某员工的销售业绩任务：确定主题域、确定系统（或主题）的边界。设计数据模型（星型模型）的事实表 和维表。实验步骤一、主题需求分析主题域：订单主题，商品主题，员工主题;订单相关边界：Orders, Order Details， Employees；商品相关边界：Products， Order Details ；员

4、工相关边界：Employees， Orders ；实验步骤二、构建数据模型以及相应的事实表和维度表:1原始关系图2去除不需要的表和与需求分析无关的字段5.根据信息分析包以及表间关系图设计具体维度表和事实表Orders事实表结构以及主键字段：字段名称数据类型功能描述原表名原字段EmployeelDInt员工号码EmployeesEmployeeIDProductIDInt产品号码ProductsProductIDCustomerlDn char顾客号码CustomersCustomerIDOrderDatedatetime订购日期OrdersOrderDateUn itPricemoney产品单

5、价Order DetailsUn itPriceTotalmoney单项总价无Quantity * UnitPrice*DiscountQuan titysmalli nt订购数量Order DetailsQua ntityDisco untreal折扣Order DetailsDisco unt主键字段：EmployeeID, ProductID, CustomerID, OrderDateEmployee员工维度表结构以及主键字段:字段名称数据类型功能描述原表名原字段EmployeeIDInt员工号码EmployeesEmployeeIDFirstNamen varchar员工名Emplo

6、yeesFirstNameLastNamen varchar员工姓EmployeesLastName主键字段：EmployeeIDProduct产品维度表结构以及主键字段:字段名称数据类型功能描述原表名原字段ProductIDInt产品号码ProductsProductIDProductNamen varchar产品名称ProductsProductNameUn itPricemoney单价ProductsUn itPrice主键字段：ProductIDCustomer顾客维度表结构以及主键字段:字段名称数据类型功能描述原表名原字段CustomerIDn char顾客号码CustomersCu

7、stomerIDCon tactNamen varchar顾客姓名CustomersCon tactNameAddressn varchar大致地区CustomersCity+Regio n+Cou n try主键字段：CustomerIDTime时间维度表结构以及主键字段:字段名称数据类型功能描述原表名原字段OrderDatedatetime日期OrdersOrderDateYearn varchar年无OrderDate 拆分Quartern varchar季度无OrderDate拆分运算Mon thn varchar月无OrderDate 拆分主键字段：OrderDateRegion地区

8、维表结构字段名称数据类型功能描述原表名原字段Addressn varchar大致地区CustomersAddressCityn varchar城市无Address 拆分Countryn varchar国家无Address 拆分Regi onn varchar地区无Address 拆分主键字段：Address6.下图显示了 Northwind数据库的星型雪花架构结构图:实验步骤三、创建数据仓库并抽取转换导入数据:SQL1首先打开SQL Server Busin ess In tellige neeDevelopment Studio,创建一个新的Server Integration Servic

9、es 项目，并取名为 northwind2点击确定后生成以下界面。3接下来在菜单栏中点击项目选择SSIS导入和导出向导，在弹出来的窗口里选择 SQL Native Client 10.0作为数据源，数据库下拉选择已装好的Northwind数据库，点击下一步。ETL过程后,4进入数据目标的操作界面，这里需要新建一个数据仓库，以后数据经过 数据存放到这个数据仓库中。I llllll山5山1血 址睥毘燃空科 MfliJS nr斥.ft WW-1(嗚筑H iJLJ1a珪曲.上一审J FiF ihT铝称但带敢据文件芒:Frogram Fi 1 e5 1Hi croft SQL. v MbL%!UU. M

10、L；i；ULL；KEKk MliULBATAtictLldMHaiLa.nidiC： KFrgram Filc=Mi cr& = of t SQLS ei-vr BSSQL4 Q. IMSSQLSERERs MSSQLDATthDW :L 聲.ld抠定SUL i 417VC1數翳陣如嘟和届性皺JS立件弋外初妬尢小()-O 不处许堀圧CAJ嗜长右分比W.? KUiW 曰吿文件大小初姑人小运):O不处许增长肿_?增蛭 白分比5选择数据的复制方式，如下图所示，有两种方式，一种是直接将表复制过去，这种方 式不是很灵活，对多表连接存在一定的局限性。这里采用的第二种方法。6接下来输入SQL语句，目前进行提

11、取和转换的是事实表orders, sql语句必须写正确,要不然会影响后面的导入数据。点击分析可以查看是否有误，没有的话即可进入下一步。53L运:包=粤弓 qjie sue t m*ri011 & orOKdw. cdittjrH urd spr I at,d. r i c b- a d. d i . co-uilL k x T a IL sJ_fr g_Qz- -ke 3. Pj. E ax* ur *!. Xa J o d,p-, wnpl. x *cua, 4 on u 3. =VsKiV#c ordflirEI( d. cr d&rlD arid d . EnpLaU= -*. ?npL

12、 oy* qI D uid p Fr - djc tl D= d Fr dactl D uid.E g 弋山IDs 孔 C u Iwii ID耳柿 迂JIiSUfe J. b -N i_；皆 O AI 7接下来可以编辑表之间的映射关系。当时我想把表名改成其他更形象具体的名字，可 是不知道为什么，如果把【查询】改了之后，后面的数据加载就会有问题。这也是一个有待 解决的问题。更询】ntPKniMft Cxpate ThIiIe SQL 语句RR肘辿:圖寫誡韶關誥翻山语创 但乳 丄菲乂辭帖的命咗语NIJ,扛罐sriployetlDPTodqeiU)Piston*r13SSL语旬order Jl-at

13、egant 1 yoAilpiricala EcsuttIctalREtTE TfcELZ 曲订査询( gglayEtlDini K)f NULL, ant WOT FILL.custoiieiXD ruclm IR7 WLX, or J car dat & ti me2 (3 qiiaiiti JIOT FILLni tpria白my ND7 MJI1Hdi scq. jit J r.EacHfl* Lll士:1落F期DlLHLLE-ILL-UL.1Z1J.jSu CTCoioetl I uiAUDI!李任it日也IN.a*mtL g mr pB匸S4-c OLE rs FUfJ SQL签耐

14、他if C QApM t目桁Z SL Strv*r 目空二层理SS度偲录Jte自林厂 毀制逛 卜屮I 3CT町.创 爭件理理辰呼W 甘峠49常审（1*.|灯塑用瀝届位边7曰上宾L屮 H工注rl *tfcWT% V i vti4 iE F12. 调试成功的话所有的元件底色都会变成绿色，如图所示:rrfckfti. 1IU Iffrtj rvk.gy 虫如 rifit |” *厂 弗tie 站I费出西HAit輛审 此 HflMF加帶* 和屋Mu*画换in* 1*13. 此时回到NorthDW数据仓库中可以发现，全部的表已成功导入完毕，接下来回到数 据库系统中给所生成的各表手动建立主键约束以及关系设

15、置，全部操作完成之后本次数据仓库抽取-转换-导入数据宣告完成。Orders事实表e-npo/e*.：Li匚G： -曰匸1q.fesnacriin-ce EME1l宀s-vzrrrP口11Z口0D43ViMCTiCkfitftd 二VWET1*994-0?-04 0-.afTEMA-CJ-Ofi Q口dy.NCida.acE a-4Z -Ol=-*43-i：CTEma-Qz-as fiWW诒i21存gga严斗*SUPR.OIMb-a,7-a-9 oa：-,40 4,SigQ,O5L.4ra-T3 3-sufep-lMfli-QT-a?-OOi2 5E OCTJO0.0?Z &4kMBQ7.3Mb

16、O心333h-v-jahiQQa-QT-ia ca-.30ia.oLaoao339H屮平LW-O-jQOO .2ZYOO0Q3*H4MAA44liA.QOClDQQ3-HfJI HL 斗”= j L CO.,.J kJlB.3iOD&. IS3.七QOC2 斗BAIMA-rST.! L 電CI H* QWQO02RrO5Ui193-0-12 GO ,2015.3000OOIBh.EC5UiLa-d7-3 3 ?. “朗L3.-SOODDoMIM 睾 piJHQQi2bfiQ90p卫REdSLi!XH-ONtN Qd -r30-b.ZsQOOa03-S3Vi&LLiot斗XH：LAA14-37-

17、OO:JS3S. MZOQ口*LS畔中-U4 1凸卯QieEr JOaO4TfHCL-iM!-l#B4-a7 iti- 40 ,W4涉oProduct 维表ArociucttJrtmeUnitPriceChei10.00002Cmc 口虫 ooaoAniseed Syru口lo.aoooChef Antons Ca.22.00005Chef Antons Qu.35QO号Grandmas Boyis s-25-0000Llnd 目口10 OrgHi *30.0000-bkhtlh 內Odd 百Cr.-W.0CMsh*%Ini Kobe hlikki7.00010Ikura31,000011Ql

18、jc*：&亡茂性自1巴古31,000012Qlj uwccKeg 38,000013Konbu6.000Q1 ；Tofu耳 50015ienen ShomyuIS. 50001IX-450017U亡合 Mutton35.00001J3BlnnBir vorn Fgw! st若工soaoWTeribmc Choicotri.-9. 200030Srr Rodrie ys Mor,.,81,000021Sf Rodancys Seo.10,000022GuetaFg Kn ?dertKng8LuraCallahanAnneDcds/orthALUCustomer 维表实验步骤四、导出指疋内谷数据至

19、 Excel表格:1. 执行事实表Sales的数据导出至excel操作，首先右键单击NorthwindDW 数据仓库，在弹出的菜单下依次点击任务 - 导出数据。i:L=JnrLLJ二扁青 b #牒p二弓寻si艺5 *喜笋苕【“ Tr=邑2is J J J 1 J I I I I I i -I I I I I I I np I a I; - - M 蓉！冒sssl-sss-si_i. o. iaoc 一戸racdua c fcXOT3T 占 p-d.ECTrcrrfljpw unWfMH fW-i* TseI .r.rLiJPPaf-jjA PM*MQIMOIl-ICDIyIC&ICS=EllW

20、ELL2. 接着在需要复制的数据源处选择SQL Native Client 10.0，数据库选择 NorthDW，点击下止zjo3. 选择目标为 Microsoft Excel，自行设置文件存放路径，Excel版本推荐选择 97-2005,然后点击下一步，选择编写查询以指定要传输的数据。4.接着使用SQL语句写出想要导出来的表以及相应的内容即可。本次目标是导出Sales事实表的前100行 L c t. -i, op I QO EkLiH Wv上一歩Kk6.5.点击下一步后即可生成对应的Excel文件，导出任务成功，事实表前100个记录。H SQL Server导人4导出向导11蠢计11成Q惜谟

21、0粤甘详細洁启u，1換ft/|+电舶船佶卅汁事翊正斟确叱直搖涉主莅设畳SQL命令7正在逹鱼豳接韓正在後宜m悯庫按色匸甬號证勒哇缺行成功执疔之前0正曲帀翊血正在复制到询尸怯轴1DC席曲抽jj之治收吉 TQU ICE19M-00-OS OOsOOsODl 000QtirCKI BO B-O5 ilQ 同Q; Q0 QQOQU ICEW9C-0B-95 OT;flQ;OQQQICEL96-0B-C: :0:0t：00. )(1CVIM*注94口406 M:00D.0MjVIME7:dj-C-Ch )(j：UO:OD. 0U3畑:話lSW-QB-OT M:00:Ci(X QKM詁L99&-Q3-&7

22、(K;0Q;Q QOQWTO19W-OB-OB 00：ggQ(RTeminICkGK仙n旧 M：cd：oa doo tgigc-cs 0i：c0iOCL ooJtCiRGKiyWrOB-_9 JU：(J0：W JtJ .BEXG3LSW-Ofl-12 00:00:001000EElJS199S-03-12 MeOOeC OOD昭程 BEJlGSLBtllSEElG.ft gg口 m-1 a io ： oo：oa oao 1990-12 OG:CO:：CiCi. OOO L9g6-Q3-13 的涣OEM Q叹 19B6-08-14 00:00:011 Q?BEG：L99S-0B-14 00:00

23、:00. 000E!lGS1996-0 B-14 00 sOOsOD. 000BCIJE?HQHYRam199=O4T 4 (K: 00 ： OS 000I6-QB14 iX;00;CCi. 0W iQ3-U 叫；Q0;仇tOM肚业丫1355-t： 0-15 M:00:00i000V KEY199f 0B-15 00:00:00.000LIUS19吟Oft-iE U:0O:C0lOKLILAS19fl6-oe-16 g;0Q;gQ0CLILAS00Q曲JOjtiOiCO.tiOOLEDI2:加： C3-19000LE3UE14-06-19 W :00:0t. Otf)LEXUS1996-06-

24、19 iKi：0：00. 000LmsT 阴ElCi B-19 K;0g00. QOQDICE19516-0 3-2D JO : 0Q MH QOO5D D 3 D 3 D O 3 D 5 5S3OO OR-CU 5 .1 O 5 Jr_t-*E r2 叫 气/一匕p1rlfr FlFll-rs r2plrl 匸；- FTFF.电 r*t; 匚：匚,一 *3 rl -zri.ru %10r2T it 4 17. 2 7. d *3. 6 1 24- E： 4. S势.)&0.2BtX 36 IX 25 O-EIT31.6x5se429.6C001F酣匕一 3sC0EFG1cnu丄.VcsLDR

25、lScLlclLDJd teunjQtyLizd ItititEdl jQ JljtT*U13Lr5Fll?ihit1996-07-04 00:00! 00.000r12Y*&:5437IIITBT199&-OV-04 00：(10： DC OX)wkeFo4F&也7IHT156-O7-C4 OOjQOsOC.OTO号*34. 3*0!11Txrrs?1996-U7-05 00:09:00.00018.6l6|fbTL-I5?L9?5-O7-CLi OaiOOiOC. O3D4D4Z.电&F07441H胡皑iMa-07-fle oa：!0b：M. ooo*10Tt304a血俪L9W-OY-0B

26、 JOiOPiW.OOO42, 4t, 1 沖.咽亡.3. ： Q. jL94衍EUTU199OT-08 10:00:04.000isr16. 8I, iSOOOCK.SjOCOOl&Sr22TICTS19W-O7-.!ir jO&CE. 10DOC04Z(J5F55CEJf 51996-07-11 0a：U5:M. OTDr2Ll釵Zt. lSOQOM&a *80003215crops1996-07-11 OOiOOM.OOO*21PB抚&RICtJ19W-OV-12 00:00:(X .： i0Is, 2bro23aRICS7199&-O7-12 00:00 DO. 000*35i玄9H2

27、4gRTCSl1n?-l2 00:0&:bfl.MOF15. 2*0Z5?RICSVL?9B-O?-L2 QikOD; ML 0004426s*53TILL：19&S-O7-15 00:d&:OC.OM*15*25. 2飞827g%VELLI13?&-O7-15 Qn；O);D0令ro30g*77HILULBK-QT-16 00:003 00.00015r10. 43:-liEBffSH19&6-O7-17 OOrO&sM.OOOsori5. 2Vzr152331*5arsa1?&-Q7-17 QQRQIhMLQK)65ItVz2：LE3jEEVSH工:-(7-Z7 if H l： if . n ji：E*25. ab, 2勺 a. Tjoaoi344r2tCSHTC196-07-18 O ;(H)：IXI,ODOWFe*u35-ciin 71%-O7-L9 00 00:00. 000r20 . 83实验结果：NorthD