數(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é)，通過(guò)課程設(shè)計(jì)與實(shí)驗(yàn)，可以使學(xué)生全面地了解和掌握數(shù)據(jù)倉(cāng)庫(kù)與知識(shí)工程課程的基本概念、原理及應(yīng)用技術(shù)，使學(xué)生系統(tǒng)科學(xué)地受到分析問(wèn)題和解決問(wèn)題的訓(xùn)練，提高運(yùn)用理論知識(shí)解決實(shí)際問(wèn)題的能力。使學(xué)生在后繼課的學(xué)習(xí)中，能夠利用數(shù)據(jù)倉(cāng)庫(kù)與數(shù)據(jù)挖掘技術(shù)及實(shí)踐經(jīng)驗(yàn)，解決相應(yīng)的實(shí)際問(wèn)題，并能在今后的學(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)用方面的思維能力和實(shí)踐動(dòng)手能力。課程設(shè)計(jì)

2、與實(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. 以SQL Server為系統(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)過(guò)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)易公司的訂單數(shù)據(jù)、產(chǎn)品數(shù)據(jù)、顧客數(shù)據(jù)、員工數(shù)據(jù)、供貨商數(shù)據(jù)等，假設(shè)貿(mào)易

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

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

5、lmoney單項(xiàng)總價(jià)無(wú)Quantity * UnitPrice*DiscountQuantitysmallint訂購(gòu)數(shù)量Order DetailsQuantityDiscountreal折扣Order DetailsDiscount主鍵字段：EmployeeID, ProductID, CustomerID, OrderDateEmployee 員工維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段EmployeeIDInt員工號(hào)碼EmployeesEmployeeIDFirstNamenvarchar員工名EmployeesFirstNameLastNamenvarchar員工姓E

6、mployeesLastName 主鍵字段：EmployeeIDProduct產(chǎn)品維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型 功能描述原表名原字段ProductIDInt產(chǎn)品號(hào)碼ProductsProductIDProductNamenvarchar產(chǎn)品名稱ProductsProductNameUnitPricemoney單價(jià)ProductsUnitPrice 主鍵字段：ProductIDCustomer顧客維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型 功能描述原表名原字段CustomerIDnchar 顧客號(hào)碼CustomersCustomerIDContactNamenvarchar 顧客姓名C

7、ustomersContactNameAddressnvarchar 大致地區(qū)CustomersCity+Region+Country主鍵字段：CustomerIDTime時(shí)間維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段OrderDatedatetime日期OrdersOrderDateYearnvarchar年無(wú)OrderDate拆分Quarternvarchar季度無(wú)OrderDate拆分運(yùn)算Monthnvarchar 月無(wú)OrderDate拆分主鍵字段：OrderDateRegion地區(qū)維表結(jié)構(gòu)字段名稱數(shù)據(jù)類型功能描述原表名原字段Addressnvarchar大致地區(qū)Cu

8、stomersAddressCitynvarchar城市無(wú)Address拆分Countrynvarchar國(guó)家無(wú)Address拆分Regionnvarchar地區(qū)無(wú)Address拆分主鍵字段：Address6. 下圖顯示了Northwind數(shù)據(jù)庫(kù)的星型雪花架構(gòu)結(jié)構(gòu)圖：Employees維表Orders事實(shí)表Products維表EmployeeIDemployeeIDProductIDFirstNameProductIDProductnameLastNameCustomerIDUnitPriceorderDateTotalQuantityUnitPriceDiscountCustomers維表C

9、ustomerIDContactNameCityRegionCountry實(shí)驗(yàn)步驟三、創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)并抽取轉(zhuǎn)換導(dǎo)入數(shù)據(jù)：1.首先打開SQL Server Business Intelligence Development Studio, 創(chuàng)建一個(gè)新的SQL Server Integration Services項(xiàng)目,并取名為northwind2.點(diǎn)擊確定后生成以下界面。3.接下來(lái)在菜單欄中點(diǎn)擊項(xiàng)目選擇SSIS導(dǎo)入和導(dǎo)出向?qū)?，在彈出?lái)的窗口里選擇SQL Native Client 10.0 作為數(shù)據(jù)源，數(shù)據(jù)庫(kù)下拉選擇已裝好的Northwind數(shù)據(jù)庫(kù)，點(diǎn)擊下一步。 4.進(jìn)入數(shù)據(jù)目標(biāo)的操作界面，這里需

10、要新建一個(gè)數(shù)據(jù)倉(cāng)庫(kù)，以后數(shù)據(jù)經(jīng)過(guò)ETL過(guò)程后，數(shù)據(jù)存放到這個(gè)數(shù)據(jù)倉(cāng)庫(kù)中。 5.選擇數(shù)據(jù)的復(fù)制方式，如下圖所示，有兩種方式，一種是直接將表復(fù)制過(guò)去，這種方式不是很靈活，對(duì)多表連接存在一定的局限性。這里采用的第二種方法。6.接下來(lái)輸入SQL語(yǔ)句，目前進(jìn)行提取和轉(zhuǎn)換的是事實(shí)表orders，sql語(yǔ)句必須寫正確，要不然會(huì)影響后面的導(dǎo)入數(shù)據(jù)。點(diǎn)擊分析可以查看是否有誤，沒有的話即可進(jìn)入下一步。7.接下來(lái)可以編輯表之間的映射關(guān)系。當(dāng)時(shí)我想把表名改成其他更形象具體的名字，可是不知道為什么，如果把【查詢】改了之后，后面的數(shù)據(jù)加載就會(huì)有問(wèn)題。這也是一個(gè)有待解決的問(wèn)題。8.點(diǎn)擊完成后即可以完成本次表格的導(dǎo)入。9接下

11、來(lái)如下圖箭頭所示，準(zhǔn)備sql任務(wù)以結(jié)束，進(jìn)入數(shù)據(jù)流階段。按照選擇屏幕左邊的工具箱，拖出來(lái)數(shù)據(jù)流源的OLE DB源和數(shù)據(jù)流目標(biāo)的OLE DB目標(biāo)兩個(gè)元件到界面上。10. 右擊OLE DB源選擇編輯，OLE DB連接管理器那一定要看清楚是對(duì)什么進(jìn)行的編輯，如果是源，就要選source.。然后再對(duì)目標(biāo)進(jìn)行編輯，需要新建一個(gè)表來(lái)進(jìn)行映射。11.直到所有的與事實(shí)表相關(guān)的維表全部編輯完，右擊右側(cè)的你剛剛生成的包，設(shè)為啟動(dòng)項(xiàng)，再運(yùn)行程序。12.調(diào)試成功的話所有的元件底色都會(huì)變成綠色，如圖所示：13.此時(shí)回到NorthDW數(shù)據(jù)倉(cāng)庫(kù)中可以發(fā)現(xiàn)，全部的表已成功導(dǎo)入完畢，接下來(lái)回到數(shù)據(jù)庫(kù)系統(tǒng)中給所生成的各表手動(dòng)建立

12、主鍵約束以及關(guān)系設(shè)置，全部操作完成之后本次數(shù)據(jù)倉(cāng)庫(kù)抽取-轉(zhuǎn)換-導(dǎo)入數(shù)據(jù)宣告完成。Orders事實(shí)表Product維表Employee維表Customer維表實(shí)驗(yàn)步驟四、導(dǎo)出指定內(nèi)容數(shù)據(jù)至Excel表格：1. 執(zhí)行事實(shí)表Sales的數(shù)據(jù)導(dǎo)出至excel操作，首先右鍵單擊NorthwindDW數(shù)據(jù)倉(cāng)庫(kù)，在彈出的菜單下依次點(diǎn)擊任務(wù)->導(dǎo)出數(shù)據(jù)。2. 接著在需要復(fù)制的數(shù)據(jù)源處選擇SQL Native Client 10.0，數(shù)據(jù)庫(kù)選擇NorthDW，點(diǎn)擊下一步。3. 選擇目標(biāo)為Microsoft Excel，自行設(shè)置文件存放路徑，Excel版本推薦選擇97-2005，然后點(diǎn)擊下一步，選擇編寫查詢

13、以指定要傳輸?shù)臄?shù)據(jù)。4. 接著使用SQL語(yǔ)句寫出想要導(dǎo)出來(lái)的表以及相應(yīng)的內(nèi)容即可。本次目標(biāo)是導(dǎo)出Sales事實(shí)表的前100行5. 點(diǎn)擊下一步后即可生成對(duì)應(yīng)的Excel文件，導(dǎo)出任務(wù)成功，事實(shí)表前100個(gè)記錄。6. 實(shí)驗(yàn)結(jié)果：1. 確定主題域、確定系統(tǒng)（或主題）的邊界。完成了NorthDW的星型雪花結(jié)構(gòu)設(shè)計(jì)，確定全部維表和事實(shí)表。2.設(shè)計(jì)、建立創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)NorthDW。3.將業(yè)務(wù)數(shù)據(jù)庫(kù)Northwind的數(shù)據(jù)經(jīng)過(guò)ETL導(dǎo)入（或加載）到數(shù)據(jù)倉(cāng)庫(kù)NorthDW。4.將數(shù)據(jù)倉(cāng)庫(kù)NorthDW事實(shí)表的前100個(gè)記錄導(dǎo)出到Excel中。 實(shí)驗(yàn)問(wèn)題和總結(jié)： 本次實(shí)驗(yàn)花了我很多的心思和心血，主要是覺得自己基

14、礎(chǔ)太薄弱。實(shí)驗(yàn)的最終結(jié)果由于時(shí)間關(guān)系跟自己最初想做的不太一樣。主要是在開始花掉了太多的時(shí)間，但是整個(gè)流程自己還是了解得挺好了，所以說(shuō)做出一個(gè)更好的東西出來(lái)也只是時(shí)間問(wèn)題。 在做的過(guò)程中，遇到了很多小問(wèn)題。1. 創(chuàng)建數(shù)據(jù)倉(cāng)庫(kù)表的時(shí)候，到底是從原有的數(shù)據(jù)庫(kù)中復(fù)制還是用sql語(yǔ)句創(chuàng)建復(fù)制。剛開始我是用的sql語(yǔ)句復(fù)制，當(dāng)時(shí)就因?yàn)橐粋€(gè)小小的圓點(diǎn)問(wèn)題耽擱了很多時(shí)間，沒有解決出來(lái)，就嘗試用原有數(shù)據(jù)庫(kù)中的表復(fù)制，但是問(wèn)題就來(lái)了，多個(gè)表連接再映射沒法做，最后還是通過(guò)sql語(yǔ)句實(shí)現(xiàn)的。2. 表映射完后，對(duì)數(shù)據(jù)的加載也出現(xiàn)了問(wèn)題。剛開始老不成功，最后發(fā)現(xiàn)時(shí)表重復(fù)的原因，因?yàn)樽约悍磸?fù)新建了很多個(gè)項(xiàng)目和相同的表，只要把書庫(kù)倉(cāng)庫(kù)中的同名表刪掉就可以加載了。導(dǎo)出數(shù)據(jù)的時(shí)候也出現(xiàn)了問(wèn)題，后來(lái)發(fā)現(xiàn)原來(lái)我的表名和關(guān)鍵字重復(fù)了，只需要將數(shù)據(jù)倉(cāng)庫(kù)的order表重新命名就好了。3.4