数据仓库的构建

昆明理工大学信息工程与自动化学院学生实验报告（2015—2016学年第1学期）课程名称：数据仓库与数据挖掘 开课实验室：444 2015年10月24日年级、专业、班计科121学号姓名成绩实验项目名称数据仓库的构建指导教师教师评语该同学是否了解实验原理： A.了解□ B.基本了解□C.不了解□该同学的实验能力： A.强□ B.中等□ □差□该同学的实验是否达到要求：A.达到□ B.基本达到□ C.未达到□实验报告是否规范： A.规范□ B.基本规范□ C.不规范□实验过程是否详细记录： A.详细□ B.一般□ C.没有□教师签名：年 月 日一、上机目的及内容目的：1、理解数据库与数据仓库之间的区别与联系；2、掌握典型的关系型数据库及其数据仓库系统的工作原理以及应用方法；3、掌握数据仓库建立的基本方法及其相关工具的使用。内容：以SQLServer为系统平台，设计、建立数据库，并以此为基础创建数据仓库。要求：利用实验室和指导教师提供的实验软件，认真完成规定的实验项目，真实地记录实验中遇到的各种问题和解决的方法与过程，并绘出模拟实验案例的数据仓库模型。实验完成后，应根据实验情况写出实验报告。二、实验原理及基本技术路线图（方框原理图或程序流程图）数据仓库，由数据仓库之父W.H.Inmon于1990年提出，主要功能仍是将组织透过资讯系统之联机交易处理（OLTP）经年累月所累积的大量资料，透过数据仓库理论所特有的资料储存架构，作一有系统的分析-1-整理，以利各种分析方法如线上分析处理(OLAP)、数据挖掘(DataMining)之进行，并进而支持如决策支持系统(DSS)、主管资讯系统(EIS)之创建，帮助决策者能快速有效的自大量资料中，分析出有价值的资讯，以利决策拟定及快速回应外在环境变动，帮助建构商业智能(BI)。三、所用仪器、材料(设备名称、型号、规格等或使用软件)台PC及MicrosoftSQLServer套件四、实验方法、步骤(或：程序代码或操作过程)--建立数据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销售人员及销售地区*/CREATETABLEDIM_SALEPERSON(SALESPERSONIDINT,DSCVARCHAR(20),SALETERRITORY_DSCVARCHAR(50))/*1.3发货方式*/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订单状态*/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,'预订')INSERTINTODIM_ORDER_STATUSVALUES(4,'已拒绝')INSERTINTODIM_ORDER_STATUSVALUES(5,'已发货,)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