數(shù)據(jù)倉庫的構(gòu)建和ETL課程設(shè)計.doc

Northwind數(shù)據(jù)倉庫的構(gòu)建和ETL課程設(shè)計與實(shí)驗(yàn)報告課程設(shè)計與實(shí)驗(yàn)教學(xué)目的與基本要求數(shù)據(jù)倉庫與知識工程課程設(shè)計與實(shí)驗(yàn)是學(xué)習(xí)數(shù)據(jù)倉庫與知識工程的重要環(huán)節(jié)，通過課程設(shè)計與實(shí)驗(yàn)，可以使學(xué)生全面地了解和掌握數(shù)據(jù)倉庫與知識工程課程的基本概念、原理及應(yīng)用技術(shù)，使學(xué)生系統(tǒng)科學(xué)地受到分析問題和解決問題的訓(xùn)練，提高運(yùn)用理論知識解決實(shí)際問題的能力。使學(xué)生在后繼課的學(xué)習(xí)中，能夠利用數(shù)據(jù)倉庫與數(shù)據(jù)挖掘技術(shù)及實(shí)踐經(jīng)驗(yàn)，解決相應(yīng)的實(shí)際問題，并能在今后的學(xué)習(xí)和工作中，結(jié)合自己的專業(yè)知識，開發(fā)相應(yīng)的數(shù)據(jù)倉庫與數(shù)據(jù)挖掘應(yīng)用程序。培養(yǎng)學(xué)生將已掌握的理論與實(shí)踐開發(fā)相結(jié)合的能力，以及在應(yīng)用方面的思維能力和實(shí)踐動手能力。課程設(shè)計與實(shí)驗(yàn)一 數(shù)據(jù)倉庫的構(gòu)建和ETL（一）目的1理解數(shù)據(jù)庫與數(shù)據(jù)倉庫之間的區(qū)別與聯(lián)系；2掌握數(shù)據(jù)倉庫建立的基本方法及其相關(guān)工具的使用。3掌握ETL實(shí)現(xiàn)的基本方法及其相關(guān)工具的使用。（二）內(nèi)容1. 以SQL Server為系統(tǒng)平臺，設(shè)計、建立創(chuàng)建數(shù)據(jù)倉庫NorthwindDW（根據(jù)課程設(shè)計內(nèi)容）。2. 將業(yè)務(wù)數(shù)據(jù)庫Northwind的數(shù)據(jù)經(jīng)過ETL導(dǎo)入（或加載）到數(shù)據(jù)倉庫NorthwindDW。3. 將數(shù)據(jù)倉庫NorthwindDW事實(shí)表的前100個記錄導(dǎo)出到Excel中。（三）數(shù)據(jù)倉庫設(shè)計要求Northwind數(shù)據(jù)庫存儲了一個貿(mào)易公司的訂單數(shù)據(jù)、產(chǎn)品數(shù)據(jù)、顧客數(shù)據(jù)、員工數(shù)據(jù)、供貨商數(shù)據(jù)等，假設(shè)貿(mào)易公司的經(jīng)營者迫切的需要準(zhǔn)確地把握貿(mào)易公司經(jīng)營情況，跟蹤市場趨勢，更加合理地制定商品采購、營銷和獎勵政策。具體的分析需求是： l 分析某商品在某地區(qū)的銷售情況 l 分析某商品在某季度的銷售情況 l 分析某年銷售多少金額的產(chǎn)品給顧客 l 分析某員工的銷售業(yè)績 任務(wù)：確定主題域、確定系統(tǒng)（或主題）的邊界。設(shè)計數(shù)據(jù)模型（星型模型）的事實(shí)表和維表。實(shí)驗(yàn)步驟一、主題需求分析:主題域：訂單主題，商品主題，員工主題；訂單相關(guān)邊界：Orders，Order Details，Employees；商品相關(guān)邊界：Products， Order Details；員工相關(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è)計具體維度表和事實(shí)表:Orders事實(shí)表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段EmployeeIDInt員工號碼EmployeesEmployeeIDProductIDInt產(chǎn)品號碼ProductsProductIDCustomerIDnchar顧客號碼CustomersCustomerIDOrderDatedatetime訂購日期OrdersOrderDateUnitPricemoney產(chǎn)品單價Order DetailsUnitPriceTotalmoney單項(xiàng)總價無Quantity * UnitPrice*DiscountQuantitysmallint訂購數(shù)量Order DetailsQuantityDiscountreal折扣Order DetailsDiscount主鍵字段：EmployeeID, ProductID, CustomerID, OrderDateEmployee 員工維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段EmployeeIDInt員工號碼EmployeesEmployeeIDFirstNamenvarchar員工名EmployeesFirstNameLastNamenvarchar員工姓EmployeesLastName 主鍵字段：EmployeeIDProduct產(chǎn)品維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型 功能描述原表名原字段ProductIDInt產(chǎn)品號碼ProductsProductIDProductNamenvarchar產(chǎn)品名稱ProductsProductNameUnitPricemoney單價ProductsUnitPrice 主鍵字段：ProductIDCustomer顧客維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型 功能描述原表名原字段CustomerIDnchar 顧客號碼CustomersCustomerIDContactNamenvarchar 顧客姓名CustomersContactNameAddressnvarchar 大致地區(qū)CustomersCity+Region+Country主鍵字段：CustomerIDTime時間維度表結(jié)構(gòu)以及主鍵字段：字段名稱數(shù)據(jù)類型功能描述原表名原字段OrderDatedatetime日期OrdersOrderDateYearnvarchar年無OrderDate拆分Quarternvarchar季度無OrderDate拆分運(yùn)算Monthnvarchar 月無OrderDate拆分主鍵字段：OrderDateRegion地區(qū)維表結(jié)構(gòu)字段名稱數(shù)據(jù)類型功能描述原表名原字段Addressnvarchar大致地區(qū)CustomersAddressCitynvarchar城市無Address拆分Countrynvarchar國家無Address拆分Regionnvarchar地區(qū)無Address拆分主鍵字段：Address6. 下圖顯示了Northwind數(shù)據(jù)庫的星型雪花架構(gòu)結(jié)構(gòu)圖：Employees維表Orders事實(shí)表Products維表EmployeeIDemployeeIDProductIDFirstNameProductIDProductnameLastNameCustomerIDUnitPriceorderDateTotalQuantityUnitPriceDiscountCustomers維表CustomerIDContactNameCityRegionCountry實(shí)驗(yàn)步驟三、創(chuàng)建數(shù)據(jù)倉庫并抽取轉(zhuǎn)換導(dǎo)入數(shù)據(jù)：1.首先打開SQL Server Business Intelligence Development Studio, 創(chuàng)建一個新的SQL Server Integration Services項(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ù)庫下拉選擇已裝好的Northwind數(shù)據(jù)庫，點(diǎn)擊下一步。 4.進(jìn)入數(shù)據(jù)目標(biāo)的操作界面，這里需要新建一個數(shù)據(jù)倉庫，以后數(shù)據(jù)經(jīng)過ETL過程后，數(shù)據(jù)存放到這個數(shù)據(jù)倉庫中。 5.選擇數(shù)據(jù)的復(fù)制方式，如下圖所示，有兩種方式，一種是直接將表復(fù)制過去，這種方式不是很靈活，對多表連接存在一定的局限性。這里采用的第二種方法。6.接下來輸入SQL語句，目前進(jìn)行提取和轉(zhuǎn)換的是事實(shí)表orders，sql語句必須寫正確，要不然會影響后面的導(dǎo)入數(shù)據(jù)。點(diǎn)擊分析可以查看是否有誤，沒有的話即可進(jìn)入下一步。7.接下來可以編輯表之間的映射關(guān)系。當(dāng)時我想把表名改成其他更形象具體的名字，可是不知道為什么，如果把【查詢】改了之后，后面的數(shù)據(jù)加載就會有問題。這也是一個有待解決的問題。8.點(diǎn)擊完成后即可以完成本次表格的導(dǎo)入。9接下來如下圖箭頭所示，準(zhǔn)備sql任務(wù)以結(jié)束，進(jìn)入數(shù)據(jù)流階段。按照選擇屏幕左邊的工具箱，拖出來數(shù)據(jù)流源的OLE DB源和數(shù)據(jù)流目標(biāo)的OLE DB目標(biāo)兩個元件到界面上。10. 右擊OLE DB源選擇編輯，OLE DB連接管理器那一定要看清楚是對什么進(jìn)行的編輯，如果是源，就要選source.。然后再對目標(biāo)進(jìn)行編輯，需要新建一個表來進(jìn)行映射。11.直到所有的與事實(shí)表相關(guān)的維表全部編輯完，右擊右側(cè)的你剛剛生成的包，設(shè)為啟動項(xiàng)，再運(yùn)行程序。12.調(diào)試成功的話所有的元件底色都會變成綠色，如圖所示：13.此時回到NorthDW數(shù)據(jù)倉庫中可以發(fā)現(xiàn)，全部的表已成功導(dǎo)入完畢，接下來回到數(shù)據(jù)庫系統(tǒng)中給所生成的各表手動建立主鍵約束以及關(guān)系設(shè)置，全部操作完成之后本次數(shù)據(jù)倉庫抽取-轉(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ù)倉庫，在彈出的菜單下依次點(diǎn)擊任務(wù)->導(dǎo)出數(shù)據(jù)。2. 接著在需要復(fù)制的數(shù)據(jù)源處選擇SQL Native Client 10.0，數(shù)據(jù)庫選擇NorthDW，點(diǎn)擊下一步。3. 選擇目標(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行5. 點(diǎn)擊下一步后即可生成對應(yīng)的Excel文件，導(dǎo)出任務(wù)成功，事實(shí)表前100個記錄。6. 實(shí)驗(yàn)結(jié)果：1. 確定主題域、確定系統(tǒng)（或主題）的邊界。完成了NorthDW的星型雪花結(jié)構(gòu)設(shè)計，確定全部維表和事實(shí)表。2.設(shè)計、建立創(chuàng)建數(shù)據(jù)倉庫NorthDW。3.將業(yè)務(wù)數(shù)據(jù)庫Northwind的數(shù)據(jù)經(jīng)過ETL導(dǎo)入（或加載）到數(shù)據(jù)倉庫NorthDW。4.將數(shù)據(jù)倉庫NorthDW事實(shí)表的前100個記錄導(dǎo)出到Excel中。 實(shí)驗(yàn)問題和總結(jié)： 本次實(shí)驗(yàn)花了我很多的心思和心血，主要是覺得自己基礎(chǔ)太薄弱。實(shí)驗(yàn)的最終結(jié)果由于時間關(guān)系跟自己最初想做的不太一樣。主要是在開始花掉了太多的時間，但是整個流程自己還是了解得挺好了，所以說做出一個更好的東西出來也只是時間問題。 在做的過程中，遇到了很多小問題。1. 創(chuàng)建數(shù)據(jù)倉庫表的時候，到底是從原有的數(shù)據(jù)庫中復(fù)制還是用sql語句創(chuàng)建復(fù)制。剛開始我是用的sql語句復(fù)制，當(dāng)時就因?yàn)橐粋€小小的圓點(diǎn)問題耽擱了很多時間，沒有解決出來，就嘗試用原有數(shù)據(jù)庫中的表復(fù)制，但是問題就來了，多個表連接再映射沒法做，最后還是通過sql語句實(shí)現(xiàn)的。2. 表映射完后，對數(shù)據(jù)的加載也出現(xiàn)了問題。剛開始老不成功，最后發(fā)現(xiàn)時表重復(fù)的原因，因?yàn)樽约悍磸?fù)新建了很多個項(xiàng)目和相同的表，只要把書庫倉庫中的同名表刪掉就可以加載了。導(dǎo)出數(shù)據(jù)的時候也出現(xiàn)了問題，后來發(fā)現(xiàn)原來我的表名和關(guān)鍵字重復(fù)了，只需要將數(shù)據(jù)倉庫的order表重新命名就好了。3.4 生成數(shù)據(jù)倉庫表的關(guān)系圖的時候，由于兩張表的customerID數(shù)據(jù)類型不同，則不能添加主外鍵關(guān)系，本來想直接在數(shù)據(jù)庫里改的，但是不允許修改，就只能借助數(shù)據(jù)轉(zhuǎn)換工具，但是數(shù)據(jù)轉(zhuǎn)換現(xiàn)在還沒有研究成功。5. 其實(shí)還想做更多的任務(wù)，比如字段的拼接，由于時間關(guān)系這次沒有做，但是下來會花時間來做做。遇到的問題肯定不止這一點(diǎn)點(diǎn)，但都是些很小的問題，通過自己的研究和同學(xué)的幫助最終做出一個勉強(qiáng)的東西，但是下來我會接著研究的，因?yàn)橥ㄟ^學(xué)習(xí)，感覺數(shù)據(jù)倉庫還是挺有趣的。