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

AdventureWorks分销商销售数据分析---Women’sTights一、概述Women’sTights型号共三种产品，分别为“Women’sTights,L”、“omen’sTights,M”和“omen’sTights,S”。这三种商品在拿大、法国和英国均有销售。在AdventureWorksDW数据库中查询得知三种产品的总销售额如下：表1三种商品销量对比EnglishProductNameSalesAmountWomen'sTights,L$93,554.46Women'sTights,M$17,727.64Women'sTights,S$90,550.91总计$201,833.01查询命令为：SELECTDimProduct.EnglishProductNameAS产品,SUM(FactResellerSales.SalesAmount)AS销售额FROMDimProductINNERJOINFactResellerSalesONDimProduct.ProductKey=FactResellerSales.ProductKeyGROUPBYDimProduct.EnglishProductName,DimProduct.ModelNameHAVING(DimProduct.ModelName='Women''sTights')从上表可以看出，[Women'sTights,L]与[Women'sTights,S]的销量相当，而[Women'sTights,M]的销量明显低于另两种，其销量只占总销量的9%。会有如此大的差距呢？下面运用OLAP技术进行分析。为叙述方便，下文用“M”简称[Women'sTights,M]，用“L”表示[Women'sTights,L]，用“S”表示[Women'sTights,S]。二、多维数据集的设计1、事实表的设计：本任务主要考查销售额，因此需要的销售额、运费和税额三个主要指标；2、维度表的设计：影响销售的因素有很多，如销售商、销售团队、区域、时间、促销手段等，所以此处设计了雇员、分销商、促销、产品、订单日期、销售区域等维度。三、数据分析1、将销售数据按年度分割，以确认是否与时间有关以年度为列字段，产品名称为行字段，销售额为数据字段设计立方体。表2分年度销售额SalesAmountCalendarYearEnglishProductName20022003总计Women'sTights,L$54,257.97$39,296.49$93,554.46Women'sTights,M$12,508.33$5,219.30$17,727.64Women'sTights,S$56,129.63$34,421.28$90,550.91总计$122,895.93$78,937.08$201,833.01图1分年度销售额直方图从上图来看，2002年和2003年Women’sTights,M的销量都明显低于另两种商品，且2003年的销量还不及2002年的一半，下降的幅度较另外两种更明显。为进一步分析M的销售额是否爱时间的影响，对两年的销售数据按周进行钻取，如下表所示：表3三种产品分周销售额YearWeekWomen'sTights,LWomen'sTights,MWomen'sTights,S总计M所占比重200227$8,130.34$1,529.80$8,817.50$18,477.648.28%31$12,040.01$2,609.65$12,441.75$27,091.419.63%36$8,739.59$2,429.68$9,582.15$20,751.4211.71%40$7,677.33$1,484.80$7,045.04$16,207.179.16%44$10,144.89$2,204.71$10,445.66$22,795.259.67%49$7,525.82$2,249.70$7,797.52$17,573.0412.80%20031$4,139.45$3,689.51$7,828.960.00%14$6,829.58$1,529.80$5,688.17$14,047.5510.89%18$9,403.75$1,934.74$8,129.29$19,467.789.94%23$8,965.86$1,754.77$7,159.42$17,880.059.81%5$4,904.35$4,949.34$9,853.690.00%9$5,053.52$4,805.55$9,859.060.00%总计$93,554.46$17,727.64$90,550.91$201,833.018.78%从表3看出，在有销售的各周里，M的销售额占总销售额的比重均低（最高也仅12.8%）。这说明M的销售