数据仓库与数据挖掘实验一(数据仓库的构建)

-1-一、实验内容和目的目的：1．理解数据库与数据仓库之间的区别与联系；2．掌握典型的关系型数据库及其数据仓库系统的工作原理以及应用方法；3．掌握数据仓库建立的基本方法及其相关工具的使用。内容：以SQLServer为系统平台，设计、建立数据库，并以此为基础创建数据仓库。二、所用仪器、材料（设备名称、型号、规格等）操作系统平台：Windows7数据库平台：SQLServer2008SP2三、实验原理数据仓库是在原有关系型数据库基础上发展形成的，但不同于数据库系统的组织结构形式，它从原有的业务数据库中获得的数据形成当前基本数据层，经过综合后形成轻度综合数据层，轻度综合数据再经过综合后形成高度综合数据层。数据仓库结构包括当前基本数据（currentdetaildata）、历史基本数据（olderdetaildata）、轻度综合数据（lightlysummarizeddata）、高度综合数据（highlysummarizeddata）和元数据（metadata）。数据仓库系统由数据仓库、仓库管理和分析工具3部分组成，结构形式如下图所示：关系数据库数据文件其他数据数据建模抽取、转换装载（ETL）元数据系统管理综合数据当前数据历史数据查询工具OLAP工具DM工具C/S工具-2-数据仓库的逻辑数据模型是多维结构的数据视图，也称多维数据模型。对于逻辑数据模型，可以使用不同的存储机制和表示模式来实现多维数据模型。目前使用的多维数据模型主要有星型模型、雪花模型、星网模型、第三范式等。ETL过程在开发数据仓库时，占去70%的工作量。ETL过程的主要步骤概括为：（1）决定数据仓库中需要的所有的目标数据；（2）决定所有的数据源，包括内部和外部的数据源；（3）准备从源数据到目标数据的数据映射关系；（4）建立全面的数据抽取规则；（5）决定数据转换和清洗规则；（6）为综合表制定计划；（7）组织数据缓冲区域和检测工具；（8）为所有的数据装载编写规程；（9）维度表的抽取、转换和装载；（10）事实表的抽取、转换和装载。四、实验方法、步骤要求：利用实验室和指导教师提供的实验软件，认真完成规定的实验项目，真实地记录实验中遇到的各种问题和解决的方法与过程，并绘出模拟实验案例的数据仓库模型。实验完成后，应根据实验情况写出实验报告。五、实验过程原始记录(数据、图表、计算等)本次实验使用MicrosoftSQLServer的示例数据库AdventureWorks，用其用户订单模型相关数据建立数据仓库。AdventureWorks由来：AdventureWorksCycles，AdventureWorks示例数据库所基于的虚构公司，是一家大型跨国生产公司。公司生产金属和复合材料的自行车，产品远销北美、欧洲和亚洲市场。公司总部设在华盛顿州的伯瑟尔市，拥有290名雇-3-员，而且拥有多个活跃在世界各地的地区性销售团队。首先安装AdventureWorks示例数据库查看安装好的数据库信息，了解相关表结构分析订单业务模型，设计数据仓库相关表结构，如下：DIM_ORDER_METHOD：下订单方式维表列名数据类型长度精度是否为空说明ONLINEORDERFLAGint410是idDSCvarchar200是含义DIM_SALEPERSON：销售人员维表列名数据类型长度精度是否为空说明SALESPERSONIDint410是销售人员IDDSCvarchar200是销售人员名称SALETERRITORY_DSCvarchar500是所属区域DIM_SHIPMETHOD：发货方式维表列名数据类型长度精度是否为空说明SHIPMETHODIDint410是发货方法IDDSCvarchar200是发货方法DIM_DATE：订单日期维表列名数据类型长度精度是否为空说明TIME_CDvarchar80是日期TIME_MONTHvarchar60是年月-4-TIME_YEARvarchar60是年TINE_QUAUTERvarchar80是季度TIME_WEEKvarchar60是星期TIME_XUNvarchar40是旬DIM_CUSTOMER：客户维表列名数据类型长度精度是否为空说明CUSTOMERIDint410是客户IDCUSTOMER_NAMEvarchar1000是客户名CUSTOMERTYPEvarchar200是客户类型AGEint410是年龄SEXvarchar20是性别MaritalStatusvarchar100是婚姻状况YearlyIncomevarchar500是年收入Educationvarchar500是教育程度Occupationvarchar500是职称NumberCarsOwnedint410是有车数量TotalChildrenint410是孩子数量COUNTRY_NAMEvarchar1000是国家STATEPROVINCE_NAMEvarchar1000是省CITY_NAMEvarchar1000是城市DIM_ORDER_STATUS：订单状态维表列名数据类型长度精度是否为空说明STATUSint410是订单状态IDDSCvarchar300是订单状态V_SUBTOTAL_VALUES：订单价值段列名数据类型长度精度是否为空说明ORDER_VALUES_IDint410是订单价值段IDDSCvarchar300是价值段MIN_VALUEint410是最小价值MAX_VALUEint410是最大价值FACT_SALEORDER：订单分析事实表列名数据类型长度精度是否为空说明SALEORDERIDint410是订单号TIME_CDvarchar80是订单时间STATUSint410是订单状态ONLINEORDERFLAGint410是下订单方式CUSTOMERIDint410是客户IDSALESPERSONIDint410是销售人IDSHIPMETHODint410是发货方式ORDER_VALUESint410是订单价值段SUBTOTALdecimal910是销售额TAXAMTdecimal910是税FREIGHTdecimal910是运费根据以上设计，建立数据库，并进行ETL，代码如下：-5---建立数据USEmasterCREATEDATABASE[DW]ONPRIMARY(NAME=N'DW',FILENAME=N'C:\DW\DW.mdf')LOGON(NAME=N'DW_log',FILENAME=N'C:\DW\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,CUSTOMER_NAMEVARCHAR(100),CUSTOMERTYPEVARCHAR(20),AGEINT,SEXVARCHAR(2),MaritalStatusVARCHAR(10),YearlyIncomeVARCHAR(50),EducationVARCHAR(50),OccupationVARCHAR(50),NumberCarsOwnedINT,-6-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)---------------------------------------二、维度表的ETL-------------------------------------INSERTINTODIM_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,'处理中')INSERTINTODIM_ORDER_STATUSVALUES(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@daydateTIME-7-SET@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'中旬