数据仓库数据挖掘与分析.doc

.页眉. .页脚. Adventure Works 分销商销售数据分析---Women ’s Tights 一、概述 Women ’s Tights 型号共三种产品, 分别为“ Women ’s Tights,L ”、“ omen ’s Tights,M ”和“ omen ’s Tights,S ”。这三种商品在拿大、法国和英国均有销售。在 Adventure WorksDW 数据库中查询得知三种产品的总销售额如下: 表1 三种商品销量对比 English Product Name Sales Amount Women's Tights, L $93, Women's Tights, M $17, Women's Tights, S $90, 总计$201, 查询命令为: SELECT DimProduct . EnglishProductName AS 产品, SUM ( FactResellerSales . SalesAmount ) AS 销售额 FROM DimProduct INNER JOIN FactResellerSales ON DimProduct . ProductKey = FactResellerSales . ProductKey GROUP BY DimProduct . EnglishProductName , DimProduct . ModelName HAVING ( DimProduct . ModelName = 'Women''s Tights' ) 从上表可以看出, [ Women's Tights, L]与[Women's Tights, S] 的销量相当,而[Women's Tights, M] 的销量明显低于另两种, 其销量只占总销量的 9%。会有如此大的差距呢?下面运用 OLAP 技术进行分析。为叙述方便,下文用“M”简称[Women's Tights, M] ,用“L”表示[ Women's Tights, L], 用“S”表示[Women's Tights, S]。二、. .页脚. 1 、事实表的设计:本任务主要考查销售额,因此需要的销售额、运费和税额三个主要指标; 2 、维度表的设计:影响销售的因素有很多,如销售商、销售团队、区域、时间、促销手段等,所以此处设计了雇员、分销商、促销、产品、订单日期、销售区域等维度。三、数据分析 1 、将销售数据按年度分割,以确认是否与时间有关以年度为列字段,产品名称为行字段,销售额为数据字段设计立方体。表2 分年度销售额 Sales Amount Calendar Year English Product Name 2002 2003 总计 Women's Tights, L $54, $39, $93, Women's Tights, M $12, $5, $17, Women's Tights, S $56, $34, $90, 总计$122, $78, $201, 图1 . .页脚. 从上图来看, 2002 年和 2