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

AdventureWorks分销商销售数据分析---Women’sTights一、概述Women’sTights型号共三种产品分别为“Women’sTightsL”、“omen’sTightsM”和“omen’sTightsS”。这三种商品在拿大、法国和英国均有销售。在AdventureWorksDW数据库中查询得知三种产品的总销售额如下：表1三种商品销量对比EnglishProductNameSalesAmountWomen'sTightsL$93554.46Women'sTightsM$17727.64Women'sTightsS$90550.91总计$201833.01查询命令为：SELECTDimProduct.EnglishProductNameAS产品SUM(FactResellerSales.SalesAmount)AS销售额FROMDimProductINNERJOINFactResellerSalesONDimProduct.ProductKey=FactResellerSales.ProductKeyGROUPBYDimProduct.EnglishProductNameDimProduct.ModelNameHAVING(DimProduct.ModelName='Women''sTights')从上表可以看出[Women'sTightsL]与[Women'sTightsS]的销量相当而[Women'sTightsM]的销量明显低于另两种其销量只占总销量的9%。会有如此大的差距呢？下面运用OLAP技术进行分析。为叙述方便下文用“M”简称[Women'sTightsM]用“L”表示[Women'sTightsL]用“S”表示[Women'sTightsS]。二、多维数据集的设计1、事实表的设计：本任务主要考查销售额因此需要的销售额、运费和税额三个主要指标；2、维度表的设计：影响销售的因素有很多如销售商、销售团队、区域、时间、促销手段等所以此处设计了雇员、分销商、促销、产品、订单日期、销售区域等维度。三、数据分析1、将销售数据按年度分割以确认是否与时间有关以年度为列字段产品名称为行字段销售额为数据字段设计立方体。表2分年度销售额SalesAmountCalendarYearEnglishProductName20022003总计Women'sTightsL$54257.97$39296.49$93554.46Women'sTightsM$12508.33$5219.30$17727.64Women'sTightsS$56129.63$34421.28$90550.91总计$122895.93$78937.08$201833.01图1分年度销售额直方图从上图来看2002年和2003年Women’sTightsM的销量都明显低于另两种商品且2003年的销量还不及2002年的一半下降的幅度较另外两种更明显。为进一步分析M的销售额是否爱时间的影响对两年的销售数据按周进行钻取如下表所示：表3三种产品分周销售额YearWeekWomen'sTightsLWomen'sTightsMWomen'sTightsS总计M所占比重200227$8130.34$1529.80$8817.50$18477.648.28%31$12040.01$2609.65$12441.75$27091.419.63%36$8739.59$2429.68$9582.15$20751.4211.71%40$7677.33$1484.80$7045.04$16207.179.16%44$10144.89$2204.71$10445.66$22795.259.67%49$7525.82$2249.70$7797.52$17573.0412.80%20031$4139.45$3689.51$7828.960.00%14$6829.58$1529.80$5688.17$14047.5510.89%18$9403.75$1934.74$8129.29$19467.789.94%23$8965.86$1754.77$7159.42$17880.059.81%5$4904.35$4949.34$9853.690.00%9$5053.52$4805.55$9859.060.00%总计$93554.46$17727.64$90550.91$201833.018.78%从表3看出在