数据仓库与数据挖掘试验一数据仓库的构建

1、一、实验内容和目的目的：1. 理解数据库与数据仓库之间的区别与联系;2. 掌握典型的关系型数据库及其数据仓库系统的工作原理以及应用方法；3. 掌握数据仓库建立的基本方法及其相关工具的使用。内容：以SQL Server为系统平台，设计、建立数据库，并以此为基础创建数据仓库。二、所用仪器、材料(设备名称、型号、规格等)操作系统平台：Windows 7数据库平台：SQL Server 2008 SP2三、实验原理数据仓库是在原有关系型数据库基础上发展形成的，但不同丁数据库系统的组织结构形 式，它从原有的业务数据库中获得的数据形成当前基本数据层， 经过综合后形成轻度综合数 据层，轻度综合数据再经过综合

2、后形成高度综合数据层。数据仓库结构包括当前基本数据(current detail data)、历史基本数据(older detail data)、轻度珠合数据(lightly summarized data)、高度综合数据(highly summarized data)和元数据(meta data。数据仓库系统由数据仓库、仓库管理和分析工具3部分组成，结构形式如下图所示：数据仓库的逻辑数据模型是多维结构的数据视图，也称多维数据模型。对于逻辑数据模 型，可以使用不同的存储机制和表示模式来实现多维数据模型。目前使用的多维数据模型主要有星型模型、雪花模型、星网模型、第三范式等。ETL过程在开发数据仓

3、库时，占去 70%的工作量。ETL过程的主要步骤概括为：(1) 决定数据仓库中需要的所有的目标数据；(2) 决定所有的数据源，包括内部和外部的数据源；(3) 准备从源数据到目标数据的数据映射关系；(4) 建立全面的数据抽取规则；(5) 决定数据转换和活洗规则;(6) 为综合表制定计划；(7) 组织数据缓冲区域和检测工具；(8) 为所有的数据装载编写规程；(9) 维度表的抽取、转换和装载；(10) 事实表的抽取、转换和装载。四、实验方法、步骤要求：利用实验室和指导教师提供的实验软件，认真完成规定的实验项目，真实地记录实验中遇到的各种问题和解决的方法与过程，并绘出模拟实验案例的数据仓库模型。实验完

4、 成后，应根据实验情况写出实验报告。五、实验过程原始记录(数据、图表、计算等)本次实验使用Microsoft SQL Server的示例数据库Adventure Works,用其用户订单模型 相关数据建立数据仓库。AdventureWorks 由来：Adventure Works Cycles , AdventureWorks 示例数据库所基于的虚构公司，是一家大型跨国生产公司。公司生产金届和复合材料的自行 车，产品远销北美、欧洲和业洲I市场。公司总部设在华盛顿州的伯瑟尔市，拥有 290名雇员，而且拥有多个活跃在世界各地的地区性销售团队。首先安装Adventure Works示例数据库查看安装

5、好的数据库信息，了解相关表结构-I |J AdventureWorks*归欲据库关系图闩_J表1+i+ dbcAWBuildVersion1+ J dbo.DatabeLogI j.1E rw-xnrl C分析订单业务模型，设计数据仓库相关表结构，如下:DIM_ORDER_METHOD :下订单方式维表列名数据类型长度精度是否为空说明ONLINEORDERFLAGint410是idDSCvarchar200是含义DIM SALEPERSON :销彳害人员维表列名数据类型:长度精度是否为空说明SALESPERSONIDint410是销售人员ID二DSCvarchar200是销售人员名称SALET

6、ERRITORY DSCvarchar500是所属区域DIM_SHIPMETHOD :发货方式维表列名数据类型长度精度是否为空说明SHIPMETHODIDint410是发货方法IDDSCvarchar200是发货方法DIM_DATE :订单日期维表列名数据类型长度精度是否为空说明TIME CDvarchar80是日期TIME_MONTHvarchar60是年月TIME_YEARvarchar60是年TINE_QUAUTERvarchar80是季度TIME_WEEKvarchar601是星期TIME_XUNvarchar40是旬DIM_CUSTOMER :客户维表列名数据类型长度精度是否为空说明

7、CUSTOMERIDint410是客户IDCUSTOMER_NAMEvarchar1000是客户名CUSTOMERTYPEvarchar00是客户类型AGEint410是年龄SEXvarchar20是性别MaritalStatusvarchar100是婚姻状况YearlyIncomevarchar500是年收入Educationvarchar500是教育程度Occupationvarchar500是职称NumberCarsOwnedint410是有车数重TotalChildrenint410是孩子数量COUNTRY_NAMEvarchar1000是国家STATEPROVINCE NAMEvar

8、char1000是省CITY_NAMEvarchar1000是城市DIM_ORDER_STATUS :订单状态维表列名数据类型长度精度是否为空说明STATUSint410是订单状态IDDSCvarchar300是订单状态V_SUBTOTAL_V ALUES :订单价值段列名数据类型长度精度是否为空说明ORDER_VALUES_IDint410是订单价值段IDDSCvarchar300是价值段MIN V ALUEint410是最小价值MAX_V ALUEint410是最大价值FACT_SALEORDER :订单分析事实表列名数据类型长度精度是否为空说明SALEORDERIDint410是订单号T

9、IME_CDvarchar80是订单时间STATUSint410是订单状态ONLINEORDERFLAGint410是下订单方式CUSTOMERIDint410是客户IDSALESPERSONIDint410是销售人IDSHIPMETHODint410是发货方式ORDER VALUESint410是订单价值段SUBTOTALdecimal910是销售额TAXAMTdecimal910是税FREIGHTdecimal910是E费根据以上设计，建立数据库，并进行 ETL,代码如下:-建立数据USE masterCREATE DATABASE DW ON PRIMARY(NAME = N'D

10、W , FILENAME = N'C:DWDW.mdf' )LOG ON(NAME = N'DW_log' , FILENAME = N'C:DWDW_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), SALETERRIT

11、ORY_DSC VARCHAR( 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_CUSTOME

12、R ( CUSTOMERID INT ,CUSTOMER_NAME 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 V

13、ARCHAR( 100 ), CITY_NAME VARCHAR( 100 )/*1.6订单状态*/CREATE TABLE 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 )INSERTINSERTINTOINTODIM_ORDER_METHODDIM_ORDER_METHODVALUES (0,'销售人员1)VALU

14、ES ( 1 ,'客户在线1INSERTSELECTINTO DIM_SHIPMETHODShipMethodID , NAME FROM AdventureWorks. Purchasing . ShipMethodINSERT INTO DIM_SALEPERSONSELECT A. SalesPersonID, '' , B. NameFROM AdventureWorks. Sales . SalesPersonA,AdventureWorks. Sales . SalesTerritoryBWHERE A. TerritoryID=B. TerritoryID

15、INSERT INTO DIM_ORDER_STATUS VALUES ( 1,'处理中')INSERT INTO DIM_ORDER_STATUS VALUES ( 2,'已批准')INSERT INTO DIM_ORDER_STATUS VALUES ( 3,'预订)INSERT INTO DIM_ORDER_STATUS VALUES ( 4,'已拒绝')INSERT INTO DIM_ORDER_STATUS VALUES ( 5,'已发货')INSERT INTO DIM_ORDER_STATUS VALUES

16、( 6,'已取消')INSERT INTO V_SUBTOTAL_VALUES VALUES ( 1 , '0-100',INSERT INTO V_SUBTOTAL_VALUES VALUES ( 2, '100-500'0, 100 ), 100 , 500 )INSERTINSERTINTOINTOV_SUBTOTAL_VALUESV_SUBTOTAL_VALUESVALUES(3,VALUES(4,'500-1000''1000-2000', 500 , 1000 ), 1000 , 2000 )INSER

17、TINTOV_SUBTOTAL_VALUESVALUES(5,'2000-5000', 2000 , 5000 )INSERTINTOV_SUBTOTAL_VALUESVALUES(6,'5000 以上',5000 , 1000000000)ETLdeclare day dateTIMESET day='2001-01-01'while day<'2005-01-01'BEGINinsert into DIM_DATESELECT CONVERT( CHAR( 8), day, 112 ),CONVERT( CHAR( 6)

18、, day, 112 ),CONVERT( CHAR( 4), day, 112 )+ '年','第'+CAST( DATEname ( QUARTER , day) AS VARCHAR( 1)+ '季度',DATEname (weekday , day),case WHEN DATEPART ( DAY, day)< 11 THEN '上旬' WHEN DATEPART( DAY, day)< 21 THEN 中旬'ELSE '下旬'ENDSELECT day = DATEADD( DAY,

19、 1 , day)ENDINSERT INTO DIM_CUSTOMERSELECT A. CustomerID , G. FirstName , CASE WHEN A. CustomerType ='I' THEN '个人'ELSE 商店'END,DATEDIFF ( YEAR, Demographics . value ( 'declare namespace zhh="Survey"(/zhh:IndividualSurvey/zhh:BirthDate)1', 'VARCHAR(10)' ),

20、 GETDATE()Demographics . value ( 'declare namespace zhh=" Survey"(/zhh:IndividualSurvey/zhh:Gender)1', 'VARCHAR(2)'),Demographics . value ( 'declare namespace zhh="Survey"(/zhh:IndividualSurvey/zhh:MaritalStatus)1', 'VARCHAR(10)' ),Demographics . v

21、alue ( 'declare namespacezhh=" Survey"(/zhh:IndividualSurvey/zhh:YearlyIncome)1', 'VARCHAR(20)'Demographics . value ( 'declare namespace zhh=" Survey"(/zhh:IndividualSurvey/zhh:Education)1', 'VARCHAR(20)' )Demographics . value ( 'declare namesp

22、ace zhh=" Survey"(/zhh:IndividualSurvey/zhh:Occupation)1', 'VARCHAR(20)' )Demographics . value ( 'declare namespace zhh=" Survey”;(/zhh:IndividualSurvey/zhh:NumberCarsOwned)1', 'int'Demographics . value ( 'declare namespace zhh=" Survey"(/zhh:

23、IndividualSurvey/zhh:TotalChildren)1' F. Name, E. Name, D. City FROM AdventureWorks AdventureWorks AdventureWorks AdventureWorks. Sales . Customer. Sales . Individual B,. Sales . CustomerAddress. Person . Address D,A,C,), 'int'),),AdventureWorks.Person.StateProvinceE,AdventureWorks.Perso

24、n.CountryRegionF,AdventureWorks.Person.Contact GWHERE A. CustomerID =B. CustomerIDAND A. CustomerID =C. CustomerIDAND C. AddressID=D. AddressIDAND D. StateProvinceID = E. StateProvinceIDAND E. CountryRegionCode = F. CountryRegionCodeAND B. ContactID=G. ContactID-三、建事实表CREATE TABLE FACT_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