数据仓库的构建

1、昆明理工大学信息工程与自动化学院学生实验报告（ 2015 2016 学年 第 1 学期 ）课程名称：数据仓库与数据挖掘 开课实验室：444 2015 年10 月24 日年级、专业、班计科121学号姓名成绩实验项目名称数据仓库的构建指导教师教师评语该同学是否了解实验原理：A.了解B.基本了解C.不了解该同学的实验能力：A.强 B.中等 C.差 该同学的实验是否达到要求：A.达到B.基本达到C.未达到实验报告是否规范：A.规范B.基本规范C.不规范实验过程是否详细记录：A.详细B.一般 C.没有 教师签名： 年 月 日一、上机目的及内容目的：1、理解数据库与数据仓库之间的区别与联系；2、掌握典型的

2、关系型数据库及其数据仓库系统的工作原理以及应用方法；3、掌握数据仓库建立的基本方法及其相关工具的使用。内容：以SQL Server为系统平台，设计、建立数据库，并以此为基础创建数据仓库。要求：利用实验室和指导教师提供的实验软件，认真完成规定的实验项目，真实地记录实验中遇到的各种问题和解决的方法与过程，并绘出模拟实验案例的数据仓库模型。实验完成后，应根据实验情况写出实验报告。2、 实验原理及基本技术路线图（方框原理图或程序流程图） 数据仓库 ，由数据仓库之父W.H.Inmon于1990年提出，主要功能仍是将组织透过资讯系统之联机交易处理(OLTP)经年累月所累积的大量资料，透过数据仓库理论所特有

3、的资料储存架构，作一有系统的分析整理，以利各种分析方法如线上分析处理(OLAP)、数据挖掘(Data Mining)之进行，并进而支持如决策支持系统(DSS)、主管资讯系统(EIS)之创建，帮助决策者能快速有效的自大量资料中，分析出有价值的资讯，以利决策拟定及快速回应外在环境变动，帮助建构商业智能(BI)。三、所用仪器、材料（设备名称、型号、规格等或使用软件）1台PC及Microsoft SQL Server套件四、实验方法、步骤（或：程序代码或操作过程）-建立数据USE master CREATE DATABASE DW ON PRIMARY ( NAME = N'DW',

4、FILENAME = N'E:DW.mdf' ) LOG ON ( NAME = N'DW_log', FILENAME = N'E:DW_log.ldf' )GOUSE DW-1、建维表/*1.1 订单方式*/CREATE TABLE DIM_ORDER_METHOD (ONLINEORDERFLAG INT,DSC VARCHAR(20)/*1.2 销售人员及销售地区*/CREATE TABLE DIM_SALEPERSON(SALESPERSONID INT,DSC VARCHAR(20),SALETERRITORY_DSC VARCHAR

5、(50)/*1.3 发货方式*/CREATE TABLE DIM_SHIPMETHOD (SHIPMETHODID INT,DSC VARCHAR(20)/*1.4 订单日期*/CREATE TABLE DIM_DATE(TIME_CD VARCHAR(8),TIME_MONTH VARCHAR(6),TIME_YEAR VARCHAR(6),TINE_QUAUTER VARCHAR(8),TIME_WEEK VARCHAR(6),TIME_XUN VARCHAR(4)/*1.5 客户*/CREATE TABLE DIM_CUSTOMER (CUSTOMERID INT,CUSTOMER_NA

6、ME VARCHAR(100),CUSTOMERTYPE VARCHAR(20),AGE INT,SEX VARCHAR(2),MaritalStatus VARCHAR(10),YearlyIncome VARCHAR(50),Education VARCHAR(50),Occupation VARCHAR(50),NumberCarsOwned INT,TotalChildren INT,COUNTRY_NAME VARCHAR(100),STATEPROVINCE_NAME VARCHAR(100),CITY_NAME VARCHAR(100)/*1.6 订单状态*/CREATE TAB

7、LE DIM_ORDER_STATUS(STATUS INT ,DSC VARCHAR(30)/*1.7 客户价值*/CREATE TABLE V_SUBTOTAL_VALUES(ORDER_VALUES_ID INT,DSC VARCHAR(30),MIN_VALUE INT,MAX_VALUE INT)-二、维度表的ETL-INSERT INTO DIM_ORDER_METHODVALUES (0,'销售人员')INSERT INTO DIM_ORDER_METHODVALUES (1,'客户在线')INSERT INTO DIM_SHIPMETHODSEL

8、ECT ShipMethodID,NAME FROM AdventureWorks.Purchasing.ShipMethodINSERT INTO DIM_SALEPERSONSELECT A.SalesPersonID,'',B.Name FROM AdventureWorks.Sales.SalesPerson A,AdventureWorks.Sales.SalesTerritory BWHERE A.TerritoryID=B.TerritoryIDINSERT INTO DIM_ORDER_STATUSVALUES(1,'处理中')INSERT IN

9、TO DIM_ORDER_STATUSVALUES(2,'已批准')INSERT INTO DIM_ORDER_STATUSVALUES(3,'预订')INSERT INTO DIM_ORDER_STATUSVALUES(4,'已拒绝')INSERT INTO DIM_ORDER_STATUSVALUES(5,'已发货')INSERT INTO DIM_ORDER_STATUSVALUES(6,'已取消')INSERT INTO V_SUBTOTAL_VALUESVALUES(1,'0-100',0

10、,100 )INSERT INTO V_SUBTOTAL_VALUESVALUES(2,'100-500',100,500 )INSERT INTO V_SUBTOTAL_VALUESVALUES(3,'500-1000',500,1000 )INSERT INTO V_SUBTOTAL_VALUESVALUES(4,'1000-2000',1000,2000 )INSERT INTO V_SUBTOTAL_VALUESVALUES(5,'2000-5000',2000,5000 )INSERT INTO V_SUBTOTAL_V

11、ALUESVALUES(6,'5000以上',5000,1000000000 )declare day dateTIMESET day='2001-01-01'while day<'2005-01-01'BEGINinsert into DIM_DATESELECT CONVERT(CHAR(8),day,112),CONVERT(CHAR(6),day,112),CONVERT(CHAR(4),day,112)+'年','第'+CAST(DATEname(QUARTER , day) AS VARCHAR(

12、1)+'季度',DATEname(weekday , day),case WHEN DATEPART(DAY,day)<11 THEN '上旬' WHEN DATEPART(DAY,day)<21 THEN '中旬' ELSE '下旬' END SELECT day=DATEADD(DAY,1,day)END INSERT INTO DIM_CUSTOMERSELECT A.CustomerID,G.FirstName,CASE WHEN A.CustomerType='I' THEN '个人&

13、#39; ELSE '商店' END,DATEDIFF(YEAR,Demographics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:BirthDate)1','VARCHAR(10)'),GETDATE() ,Demographics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:Gender)1','VARCHAR(2)') ,Demographics.

14、value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:MaritalStatus)1','VARCHAR(10)') ,Demographics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:YearlyIncome)1','VARCHAR(20)') ,Demographics.value('declare namespace zhh="(/zhh:Individu

15、alSurvey/zhh:Education)1','VARCHAR(20)') ,Demographics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:Occupation)1','VARCHAR(20)') ,Demographics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:NumberCarsOwned)1','int') ,Demogra

16、phics.value('declare namespace zhh="(/zhh:IndividualSurvey/zhh:TotalChildren)1','int') ,F.Name,E.Name,D.City FROM AdventureWorks.Sales.Customer A,AdventureWorks.Sales.Individual B,AdventureWorks.Sales.CustomerAddress C,AdventureWorks.Person.Address D,AdventureWorks.Person.StateP

17、rovince E,AdventureWorks.Person.CountryRegion F,AdventureWorks.Person.Contact GWHERE A.CustomerID=B.CustomerIDAND A.CustomerID=C.CustomerID AND C.AddressID=D.AddressIDAND D.StateProvinceID=E.StateProvinceIDAND E.CountryRegionCode=F.CountryRegionCodeAND B.ContactID=G.ContactID-三、建事实表CREATE TABLE FACT

18、_SALEORDER(SALEORDERID INT,TIME_CD VARCHAR(8),STATUS INT,ONLINEORDERFLAG INT,CUSTOMERID INT,SALESPERSONID INT,SHIPMETHOD INT,ORDER_VALUES INT,SUBTOTAL DECIMAL(10,2),TAXAMT DECIMAL(10,2),FREIGHT DECIMAL(10,2) -四、事实表的ETL/*4.1 FACT_SALEORDER的ETL*/TRUNCATE TABLE FACT_SALEORDERINSERT INTO FACT_SALEORDERS

19、ELECT SalesOrderID,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.FreightFROM AdventureWorks.Sales.SalesOrderHeader A,V_SUBTOTAL_VALUES BWHERE A.SubTotal>=B.MIN_VALUE AND A.SubTot

20、al<B.MAX_VALUE-缺省值处理INSERT INTO DIM_CUSTOMERselect DISTINCT CUSTOMERID,'未知','商店',0,'N','N','未知','未知','未知',0,0,'未知','未知','未知' from FACT_SALEORDERwhere CUSTOMERID not in (SELECT CUSTOMERID FROM dbo.DIM_CUSTOMER )INSERT

21、INTO DIM_SALEPERSONselect DISTINCT SALESPERSONID,'未知','未知' from FACT_SALEORDERwhere SALESPERSONID not in (SELECT SALESPERSONID FROM dbo.DIM_SALEPERSON )select * FROM FACT_SALEORDER WHERE CUSTOMERID IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.DIM_CUSTOMER

22、BWHERE A.CUSTOMERID=B.CUSTOMERID) select * FROM FACT_SALEORDER WHERE TIME_CD IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.DIM_DATE BWHERE A.TIME_CD=B.TIME_CD) select * FROM FACT_SALEORDER WHERE SHIPMETHOD IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FRO

23、M dbo.DIM_SHIPMETHOD BWHERE A.SHIPMETHOD=B.SHIPMETHODID) select * FROM FACT_SALEORDER WHERE STATUS IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.DIM_ORDER_STATUS BWHERE A.STATUS =B.STATUS ) select * FROM FACT_SALEORDER WHERE SALESPERSONID IS NULLSELECT DISTINCT SALESPERSON

24、ID FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.DIM_SALEPERSON BWHERE A.SALESPERSONID =B.SALESPERSONID ) select * FROM FACT_SALEORDER WHERE ONLINEORDERFLAG IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.DIM_ORDER_METHOD BWHERE A.ONLINEORDERFLAG =B.ONLINEORDERFLAG ) select * FROM FACT_SALEORDER WHERE ORDER_VALUES IS NULLSELECT * FROM FACT_SALEORDER AWHERE NOT EXISTS (SELECT 1 FROM dbo.V_SUBTOTAL_VALUES BWHERE A.ORDER_VALUES =B.ORDER_VALUES_ID ) insert into DIM_SALEPERSONv