數(shù)據(jù)倉(cāng)庫(kù)的構(gòu)建和ETL課程設(shè)計(jì)

1、Northwind 數(shù)據(jù)倉(cāng)庫(kù)的構(gòu)建和 ETL課程設(shè)計(jì)與實(shí)驗(yàn)報(bào)告課程設(shè)計(jì)與實(shí)驗(yàn)教學(xué)目的與基本要求數(shù)據(jù)倉(cāng)庫(kù)與知識(shí)工程課程設(shè)計(jì)與實(shí)驗(yàn)是學(xué)習(xí)數(shù)據(jù)倉(cāng)庫(kù)與知識(shí)工程的重要環(huán)節(jié)，通過課程設(shè)計(jì)與實(shí)驗(yàn)， 可以使學(xué)生全面地了解和掌握數(shù)據(jù)倉(cāng)庫(kù)與知識(shí)工程課程的基本概念、原理及應(yīng)用技術(shù)， 使學(xué)生系統(tǒng)科學(xué)地受到分析問題和解決問題的訓(xùn)練，提高運(yùn)用理論知識(shí)解決實(shí)際問題的能力。使學(xué)生在后繼課的學(xué)習(xí)中， 能夠利用數(shù)據(jù)倉(cāng)庫(kù)與數(shù)據(jù)挖掘技術(shù)及實(shí)踐經(jīng)驗(yàn)， 解決相應(yīng)的 實(shí)際問題， 并能在今后的學(xué)習(xí)和工作中， 結(jié)合自己的專業(yè)知識(shí)， 開發(fā)相應(yīng)的數(shù)據(jù)倉(cāng)庫(kù)與數(shù)據(jù) 挖掘應(yīng)用程序。 培養(yǎng)學(xué)生將已掌握的理論與實(shí)踐開發(fā)相結(jié)合的能力， 以及在應(yīng)用方面的思維

2、能力和實(shí)踐動(dòng)手能力。課程設(shè)計(jì)與實(shí)驗(yàn)一 數(shù)據(jù)倉(cāng)庫(kù)的構(gòu)建和 ETL（一）目的 1理解數(shù)據(jù)庫(kù)與數(shù)據(jù)倉(cāng)庫(kù)之間的區(qū)別與聯(lián)系； 2掌握數(shù)據(jù)倉(cāng)庫(kù)建立的基本方法及其相關(guān)工具的使用。3掌握 ETL 實(shí)現(xiàn)的基本方法及其相關(guān)工具的使用。（二）內(nèi)容1. 以SQLServer為系統(tǒng)平臺(tái)，設(shè)計(jì)、建立創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)NorthwindDW （根據(jù)課程設(shè)計(jì)內(nèi)容）。2. 將業(yè)務(wù)數(shù)據(jù)庫(kù) Northwind 的數(shù)據(jù)經(jīng)過 ETL 導(dǎo)入（或加載） 到數(shù)據(jù)倉(cāng)庫(kù) NorthwindDW。3. 將數(shù)據(jù)倉(cāng)庫(kù) NorthwindDW 事實(shí)表的前 100 個(gè)記錄導(dǎo)出到 Excel 中。（三）數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)要求Northwind 數(shù)據(jù)庫(kù)存儲(chǔ)了一個(gè)貿(mào)易公司

3、的訂單數(shù)據(jù)、產(chǎn)品數(shù)據(jù)、顧客數(shù)據(jù)、員工數(shù)據(jù)、 供貨商數(shù)據(jù)等， 假設(shè)貿(mào)易公司的經(jīng)營(yíng)者迫切的需要準(zhǔn)確地把握貿(mào)易公司經(jīng)營(yíng)情況，跟蹤市場(chǎng)趨勢(shì)，更加合理地制定商品采購(gòu)、營(yíng)銷和獎(jiǎng)勵(lì)政策。具體的分析需求是： 分析某商品在某地區(qū)的銷售情況 分析某商品在某季度的銷售情況 分析某年銷售多少金額的產(chǎn)品給顧客 分析某員工的銷售業(yè)績(jī)?nèi)蝿?wù)：確定主題域、確定系統(tǒng)（或主題）的邊界。設(shè)計(jì)數(shù)據(jù)模型（星型模型）的事實(shí)表 和維表。實(shí)驗(yàn)步驟一、主題需求分析主題域：訂單主題，商品主題，員工主題;訂單相關(guān)邊界：Orders, Order Details， Employees；商品相關(guān)邊界：Products， Order Details ；員

4、工相關(guān)邊界：Employees， Orders ；實(shí)驗(yàn)步驟二、構(gòu)建數(shù)據(jù)模型以及相應(yīng)的事實(shí)表和維度表:1原始關(guān)系圖2去除不需要的表和與需求分析無關(guān)的字段5.根據(jù)信息分析包以及表間關(guān)系圖設(shè)計(jì)具體維度表和事實(shí)表Orders事實(shí)表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段EmployeelDInt員工號(hào)碼EmployeesEmployeeIDProductIDInt產(chǎn)品號(hào)碼ProductsProductIDCustomerlDn char顧客號(hào)碼CustomersCustomerIDOrderDatedatetime訂購(gòu)日期OrdersOrderDateUn itPricemoney產(chǎn)品單

5、價(jià)Order DetailsUn itPriceTotalmoney單項(xiàng)總價(jià)無Quantity * UnitPrice*DiscountQuan titysmalli nt訂購(gòu)數(shù)量Order DetailsQua ntityDisco untreal折扣Order DetailsDisco unt主鍵字段：EmployeeID, ProductID, CustomerID, OrderDateEmployee員工維度表結(jié)構(gòu)以及主鍵字段:字段名稱數(shù)據(jù)類型功能描述原表名原字段EmployeeIDInt員工號(hào)碼EmployeesEmployeeIDFirstNamen varchar員工名Emplo

6、yeesFirstNameLastNamen varchar員工姓EmployeesLastName主鍵字段：EmployeeIDProduct產(chǎn)品維度表結(jié)構(gòu)以及主鍵字段:字段名稱數(shù)據(jù)類型功能描述原表名原字段ProductIDInt產(chǎn)品號(hào)碼ProductsProductIDProductNamen varchar產(chǎn)品名稱ProductsProductNameUn itPricemoney單價(jià)ProductsUn itPrice主鍵字段：ProductIDCustomer顧客維度表結(jié)構(gòu)以及主鍵字段:字段名稱數(shù)據(jù)類型功能描述原表名原字段CustomerIDn char顧客號(hào)碼CustomersCu

7、stomerIDCon tactNamen varchar顧客姓名CustomersCon tactNameAddressn varchar大致地區(qū)CustomersCity+Regio n+Cou n try主鍵字段：CustomerIDTime時(shí)間維度表結(jié)構(gòu)以及主鍵字段:字段名稱數(shù)據(jù)類型功能描述原表名原字段OrderDatedatetime日期OrdersOrderDateYearn varchar年無OrderDate 拆分Quartern varchar季度無OrderDate拆分運(yùn)算Mon thn varchar月無OrderDate 拆分主鍵字段：OrderDateRegion地區(qū)

8、維表結(jié)構(gòu)字段名稱數(shù)據(jù)類型功能描述原表名原字段Addressn varchar大致地區(qū)CustomersAddressCityn varchar城市無Address 拆分Countryn varchar國(guó)家無Address 拆分Regi onn varchar地區(qū)無Address 拆分主鍵字段：Address6.下圖顯示了 Northwind數(shù)據(jù)庫(kù)的星型雪花架構(gòu)結(jié)構(gòu)圖:實(shí)驗(yàn)步驟三、創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)并抽取轉(zhuǎn)換導(dǎo)入數(shù)據(jù):SQL1首先打開SQL Server Busin ess In tellige neeDevelopment Studio,創(chuàng)建一個(gè)新的Server Integration Servic

9、es 項(xiàng)目，并取名為 northwind2點(diǎn)擊確定后生成以下界面。3接下來在菜單欄中點(diǎn)擊項(xiàng)目選擇SSIS導(dǎo)入和導(dǎo)出向?qū)?，在彈出來的窗口里選擇 SQL Native Client 10.0作為數(shù)據(jù)源，數(shù)據(jù)庫(kù)下拉選擇已裝好的Northwind數(shù)據(jù)庫(kù)，點(diǎn)擊下一步。ETL過程后,4進(jìn)入數(shù)據(jù)目標(biāo)的操作界面，這里需要新建一個(gè)數(shù)據(jù)倉(cāng)庫(kù)，以后數(shù)據(jù)經(jīng)過 數(shù)據(jù)存放到這個(gè)數(shù)據(jù)倉(cāng)庫(kù)中。I llllll山5山1血 址睥毘燃空科 MfliJS nr斥.ft WW-1(嗚筑H iJLJ1a珪曲.上一審J FiF ihT鋁稱但帶敢據(jù)文件芒: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數(shù)翳陣如嘟和屆性皺JS立件弋外初妬尢小()-O 不處許堀圧CAJ嗜長(zhǎng)右分比W.? KUiW 曰吿文件大小初姑人小運(yùn)):O不處許增長(zhǎng)腫_?增蛭 白分比5選擇數(shù)據(jù)的復(fù)制方式，如下圖所示，有兩種方式，一種是直接將表復(fù)制過去，這種方 式不是很靈活，對(duì)多表連接存在一定的局限性。這里采用的第二種方法。6接下來輸入SQL語句，目前進(jìn)行提

11、取和轉(zhuǎn)換的是事實(shí)表orders, sql語句必須寫正確,要不然會(huì)影響后面的導(dǎo)入數(shù)據(jù)。點(diǎn)擊分析可以查看是否有誤，沒有的話即可進(jìn)入下一步。53L運(yùn):包=粵弓 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接下來可以編輯表之間的映射關(guān)系。當(dāng)時(shí)我想把表名改成其他更形象具體的名字，可 是不知道為什么，如果把【查詢】改了之后，后面的數(shù)據(jù)加載就會(huì)有問題。這也是一個(gè)有待 解決的問題。更詢】ntPKniMft Cxpate ThIiIe SQL 語句RR肘辿:圖寫誡韶關(guān)誥翻山語創(chuàng) 但乳 丄菲乂辭帖的命咗語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簽?zāi)?/p>

14、他if C QApM t目桁Z SL Strv*r 目空二層理SS度偲錄Jte自林廠 毀制逛 卜屮I 3CT町.創(chuàng) 爭(zhēng)件理理辰呼W 甘峠49常審（1*.|燈塑用瀝屆位邊7曰上賓L屮 H工注rl *tfcWT% V i vti4 iE F12. 調(diào)試成功的話所有的元件底色都會(huì)變成綠色，如圖所示:rrfckfti. 1IU Iffrtj rvk.gy 蟲如 rifit |” *廠 弗tie 站I費(fèi)出西HAit輛審 此 HflMF加帶* 和屋Mu*畫換in* 1*13. 此時(shí)回到NorthDW數(shù)據(jù)倉(cāng)庫(kù)中可以發(fā)現(xiàn)，全部的表已成功導(dǎo)入完畢，接下來回到數(shù) 據(jù)庫(kù)系統(tǒng)中給所生成的各表手動(dòng)建立主鍵約束以及關(guān)系設(shè)

15、置，全部操作完成之后本次數(shù)據(jù)倉(cāng)庫(kù)抽取-轉(zhuǎn)換-導(dǎo)入數(shù)據(jù)宣告完成。Orders事實(shí)表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嚴(yán)斗*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衛(wèi)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號(hào)Grandmas Boyis s-25-0000Llnd 目口10 OrgHi *30.0000-bkhtlh 內(nèi)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 維表實(shí)驗(yàn)步驟四、導(dǎo)出指疋內(nèi)谷數(shù)據(jù)至

19、 Excel表格:1. 執(zhí)行事實(shí)表Sales的數(shù)據(jù)導(dǎo)出至excel操作，首先右鍵單擊NorthwindDW 數(shù)據(jù)倉(cāng)庫(kù)，在彈出的菜單下依次點(diǎn)擊任務(wù) - 導(dǎo)出數(shù)據(jù)。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. 接著在需要復(fù)制的數(shù)據(jù)源處選擇SQL Native Client 10.0，數(shù)據(jù)庫(kù)選擇 NorthDW，點(diǎn)擊下止zjo3. 選擇目標(biāo)為 Microsoft Excel，自行設(shè)置文件存放路徑，Excel版本推薦選擇 97-2005,然后點(diǎn)擊下一步，選擇編寫查詢以指定要傳輸?shù)臄?shù)據(jù)。4.接著使用SQL語句寫出想要導(dǎo)出來的表以及相應(yīng)的內(nèi)容即可。本次目標(biāo)是導(dǎo)出Sales事實(shí)表的前100行 L c t. -i, op I QO EkLiH Wv上一歩Kk6.5.點(diǎn)擊下一步后即可生成對(duì)應(yīng)的Excel文件，導(dǎo)出任務(wù)成功，事實(shí)表前100個(gè)記錄。H SQL Server導(dǎo)人4導(dǎo)出向?qū)?1蠢計(jì)11成Q惜謨

21、0粵甘詳細(xì)潔啟u，1換ft/|+電舶船佶卅汁事翊正斟確叱直搖涉主蒞設(shè)畳SQL命令7正在逹魚豳接韓正在後宜m憫庫(kù)按色匸甬號(hào)證勒哇缺行成功執(zhí)疔之前0正曲帀翊血正在復(fù)制到詢尸怯軸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肚業(yè)丫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勢(shì).)&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號(hào)*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實(shí)驗(yàn)結(jié)果：NorthD