昆明理工大學(xué)信息工程與自動化學(xué)院學(xué)生實驗報告（2015—2016學(xué)年第1學(xué)期）課程名稱：數(shù)據(jù)倉庫與數(shù)據(jù)挖掘 開課實驗室：444 2015年10月24日年級、專業(yè)、班計科121學(xué)號姓名成績實驗項目名稱數(shù)據(jù)倉庫的構(gòu)建指導(dǎo)教師教師評語該同學(xué)是否了解實驗原理： A.了解□ B.基本了解□C.不了解□該同學(xué)的實驗?zāi)芰Γ?A.強□ B.中等□ □差□該同學(xué)的實驗是否達到要求：A.達到□ B.基本達到□ C.未達到□實驗報告是否規(guī)范： A.規(guī)范□ B.基本規(guī)范□ C.不規(guī)范□實驗過程是否詳細記錄： A.詳細□ B.一般□ C.沒有□教師簽名：年 月 日一、上機目的及內(nèi)容目的：1、理解數(shù)據(jù)庫與數(shù)據(jù)倉庫之間的區(qū)別與聯(lián)系；2、掌握典型的關(guān)系型數(shù)據(jù)庫及其數(shù)據(jù)倉庫系統(tǒng)的工作原理以及應(yīng)用方法；3、掌握數(shù)據(jù)倉庫建立的基本方法及其相關(guān)工具的使用。內(nèi)容：以SQLServer為系統(tǒng)平臺，設(shè)計、建立數(shù)據(jù)庫，并以此為基礎(chǔ)創(chuàng)建數(shù)據(jù)倉庫。要求：利用實驗室和指導(dǎo)教師提供的實驗軟件，認真完成規(guī)定的實驗項目，真實地記錄實驗中遇到的各種問題和解決的方法與過程，并繪出模擬實驗案例的數(shù)據(jù)倉庫模型。實驗完成后，應(yīng)根據(jù)實驗情況寫出實驗報告。二、實驗原理及基本技術(shù)路線圖（方框原理圖或程序流程圖）數(shù)據(jù)倉庫，由數(shù)據(jù)倉庫之父W.H.Inmon于1990年提出，主要功能仍是將組織透過資訊系統(tǒng)之聯(lián)機交易處理（OLTP）經(jīng)年累月所累積的大量資料，透過數(shù)據(jù)倉庫理論所特有的資料儲存架構(gòu)，作一有系統(tǒng)的分析-1-整理，以利各種分析方法如線上分析處理(OLAP)、數(shù)據(jù)挖掘(DataMining)之進行，并進而支持如決策支持系統(tǒng)(DSS)、主管資訊系統(tǒng)(EIS)之創(chuàng)建，幫助決策者能快速有效的自大量資料中，分析出有價值的資訊，以利決策擬定及快速回應(yīng)外在環(huán)境變動，幫助建構(gòu)商業(yè)智能(BI)。三、所用儀器、材料(設(shè)備名稱、型號、規(guī)格等或使用軟件)臺PC及MicrosoftSQLServer套件四、實驗方法、步驟(或：程序代碼或操作過程)--建立數(shù)據(jù)USEmasterCREATEDATABASE[DW]ONPRIMARY(NAME=N'DW',FILENAME=N'E:\DW.mdf')LOGON(NAME=N'DW_log',FILENAME=N'E:\DW_log.ldf')GOUSEDW--1、建維表/*1.1訂單方式*/CREATETABLEDIM_ORDER_METHOD(ONLINEORDERFLAGINT,DSCVARCHAR(20))/*1.2銷售人員及銷售地區(qū)*/CREATETABLEDIM_SALEPERSON(SALESPERSONIDINT,DSCVARCHAR(20),SALETERRITORY_DSCVARCHAR(50))/*1.3發(fā)貨方式*/CREATETABLEDIM_SHIPMETHOD(SHIPMETHODIDINT,DSCVARCHAR(20))/*1.4訂單日期*/CREATETABLEDIM_DATE(TIME_CDVARCHAR(8),TIME_MONTHVARCHAR(6),TIME_YEARVARCHAR(6),TINE_QUAUTERVARCHAR(8),TIME_WEEKVARCHAR(6),TIME_XUNVARCHAR(4))/*1.5客戶*/CREATETABLEDIM_CUSTOMER(CUSTOMERIDINT,-2-CUSTOMER_NAMEVARCHAR(100),CUSTOMERTYPEVARCHAR(20),AGEINT,SEXVARCHAR(2),MaritalStatusVARCHAR(10),YearlyIncomeVARCHAR(50),EducationVARCHAR(50),OccupationVARCHAR(50),NumberCarsOwnedINT,TotalChildrenINT,COUNTRY_NAMEVARCHAR(100),STATEPROVINCE_NAMEVARCHAR(100),CITY_NAMEVARCHAR(100))/*1.6訂單狀態(tài)*/CREATETABLEDIM_ORDER_STATUS(STATUSINT,DSCVARCHAR(30))/*1.7客戶價值*/CREATETABLEV_SUBTOTAL_VALUES(ORDER_VALUES_IDINT,DSCVARCHAR(30),MIN_VALUEINT,MAX_VALUEINT)--二、維度表的ETLINSERTINTODIM_ORDER_METHODVALUES(0,'銷售人員')INSERTINTODIM_ORDER_METHODVALUES(1,'客戶在線')INSERTINTODIM_SHIPMETHODSELECTShipMethodID,NAMEFROMAdventureWorks.Purchasing.ShipMethodINSERTINTODIM_SALEPERSONSELECTA.SalesPersonID,'',B.NameFROMAdventureWorks.Sales.SalesPersonA,AdventureWorks.Sales.SalesTerritoryBWHEREA.TerritoryID=B.TerritoryIDINSERTINTODIM_ORDER_STATUSVALUES(1,'處理中')INSERTINTODIMORDERSTATUS-3-VALUES(2,'已批準')INSERTINTODIM_ORDER_STATUSVALUES(3,'預(yù)訂')INSERTINTODIM_ORDER_STATUSVALUES(4,'已拒絕')INSERTINTODIM_ORDER_STATUSVALUES(5,'已發(fā)貨,)INSERTINTODIM_ORDER_STATUSVALUES(6,'已取消')INSERTINTOV_SUBTOTAL_VALUESVALUES(1,'0-100',0,100)INSERTINTOV_SUBTOTAL_VALUESVALUES(2,'100-500',100,500)INSERTINTOV_SUBTOTAL_VALUESVALUES(3,'500-1000',500,1000)INSERTINTOV_SUBTOTAL_VALUESVALUES(4,'1000-2000',1000,2000)INSERTINTOV_SUBTOTAL_VALUESVALUES(5,'2000-5000',2000,5000)INSERTINTOV_SUBTOTAL_VALUESVALUES(6,'5000以上'，5000,1000000000)declare@daydateTIMESET@day='2001-01-01'while@day<'2005-01-01'BEGINinsertintoDIM_DATESELECTCONVERT(CHAR(8),@day,112),CONVERT(CHAR(6),@day,112),CONVERT(CHAR(4),@day,112)+'年'，'第'+CAST(DATEname(QUARTER,@day)ASVARCHAR(1))+'季度'，DATEname(weekday,@day),caseWHENDATEPART(DAY,@day)<11THEN'上旬'WHENDATEPART(DAY,@day)<21THEN中旬'ELSE'下旬'ENDSELECT@day=DATEADD(DAY,1,@day)ENDINSERTINTODIM_CUSTOMERSELECTA.CustomerID,G.FirstName,CASEWHENA.CustomerType='I'THEN'個人'ELSE商店'END,DATEDIFF(YEAR,Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/Individual-4-Survey";(/zhh:IndividualSurvey/zhh:BirthDate)[1]','VARCHAR(10)'),GETDATE()),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:Gender)[1]','VARCHAR(2)’),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:MaritalStatus)[1]','VARCHAR(10)’),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:YearlyIncome)[1]','VARCHAR(20)’),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:Education)[1]','VARCHAR(20)’),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:Occupation)[1]','VARCHAR(20)’),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:NumberCarsOwned)[1]','int'),Demographics.value('declarenamespacezhh="/sqlserver/2004/07/adventure-works/IndividualSurvey";(/zhh:IndividualSurvey/zhh:TotalChildren)[1]','int'),F.Name,E.Name,D.CityFROMAdventureWorks.Sales.CustomerA,AdventureWorks.Sales.IndividualB,AdventureWorks.Sales.CustomerAddressC,AdventureWorks.Person.AddressD,AdventureWorks.Person.StateProvinceE,AdventureWorks.Person.CountryRegionF,AdventureWorks.Person.ContactGWHEREA.CustomerID=B.CustomerIDANDA.CustomerID=C.CustomerIDANDC.AddressID=D.AddressIDANDD.StateProvinceID=E.StateProvinceIDANDE.CountryRegionCode=F.CountryRegionCodeANDB.ContactID=G.ContactID-5--三、建事實表CREATETABLEFACT_SALEORDER(SALEORDERIDINT,TIME_CDVARCHAR(8),STATUSINT,ONLINEORDERFLAGINT,CUSTOMERIDINT,SALESPERSONIDINT,SHIPMETHODINT,ORDER_VALUESINT,SUBTOTALDECIMAL(10,2),TAXAMTDECIMAL(10,2),FREIGHTDECIMAL(10,2))-四、事實表的ETL/*4.1FACT_SALEORDERS^ETL*/TRUNCATETABLEFACT_SALEORDERINSERTINTOFACT_SALEORDERSELECTSalesOrderID,CONVERT(CHAR(8),A.OrderDate,112),A.Status,A.OnlineOrderFlag,ISNULL(A.CustomerID,0),ISNULL(A.SalesPersonID,0),A.ShipMethodID,B.ORDER_VALUES_ID,A.SubTotal,A.TaxAmt,A.FreightFROMAdventureWorks.Sales.SalesOrderHeaderA,V_SUBTOTAL_VALUESBWHEREA.SubTotal>=B.MIN_VALUEANDA.SubTotal<B.MAX_VALUE-缺省值處理INSERTINTODIM_CUSTOMERselectDISTINCTCUSTOMERID,'未知'，'商店'，0,'N','N','未知'，'未知'，'未知'，0,0,'未知'，未知'，'未知'fromFACT_SALEORDERwhereCUSTOMERIDnotin(SELECTCUSTOMERIDFROMdbo.DIM_CUSTOMER)INSERTINTODIM_SALEPERSONselectDISTINCTSALESPERSONID,'未知'，'未知'fromFACT_SALEORDERwhereSALESPERSONIDnotin(SELECTSALESPERSONIDFROMdbo.DIM_SALEPERSON)select*FROMFACT_SALEORDERWHERECUSTOMERIDISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_CUSTOMERBWHEREA.CUSTOMERID=B.CUSTOMERID)-6-select*FROMFACT_SALEORDERWHERETIME_CDISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_DATEBWHEREA.TIME_CD=B.TIME_CD)select*FROMFACT_SALEORDERWHERESHIPMETHODISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_SHIPMETHODBWHEREA.SHIPMETHOD=B.SHIPMETHODID)select*FROMFACT_SALEORDERWHERESTATUSISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_ORDER_STATUSBWHEREA.STATUS=B.STATUS)select*FROMFACT_SALEORDERWHERESALESPERSONIDISNULLSELECTDISTINCTSALESPERSONIDFROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_SALEPERSONBWHEREA.SALESPERSONID=B.SALESPERSONID)select*FROMFACT_SALEORDERWHEREONLINEORDERFLAGISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.DIM_ORDER_METHODBWHEREA.ONLINEORDERFLAG=B.ONLINEORDERFLAG)select*FROMFACT_SALEORDERWHEREORDER_VALUESISNULLSELECT*FROMFACT_SALEORDERAWHERENOTEXISTS(SELECT1FROMdbo.V_SUBTOTAL_VALUESBWHEREA.ORDER_VALUES=B.ORDER_VALUES_ID)insertintoDIM_SALEPERSONvalues(0,'','無')(SELECTUPDATEFACT_SALEORDERSETSALESPERSONID=0WHERESALESPERSONIDNOTINSALESPERSONID(SELECTFROMDIM_SALEPERSON)select*fromDIM_CUSTOMERwherecustomeridin(selectcustomeridfromFACT_SALEORDERgroupbycustomeridhavingCOUNT(*)>1)orderbycustomerid-7-SELECT*INTOAAAAFROM(SELECTA.*,ROW_NUMBER()OVER(PARTITIONBYCUST