資源描述：
《數(shù)據(jù)倉(cāng)庫(kù)與數(shù)據(jù)挖掘分析》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、AdventureWorks分銷商銷售數(shù)據(jù)分析---Women’sTights一、概述Women’sTights型號(hào)共三種產(chǎn)品，分別為“Women’sTights,L”、“omen’sTights,M”和“omen’sTights,S”。這三種商品在拿大、法國(guó)和英國(guó)均有銷售。在AdventureWorksDW數(shù)據(jù)庫(kù)中查詢得知三種產(chǎn)品的總銷售額如下：表1三種商品銷量對(duì)比EnglishProductNameSalesAmountWomen'sTights,L$93,554.46Women'sTights,M$17,727.64Wome
2、n'sTights,S$90,550.91總計(jì)$201,833.01查詢命令為：SELECTDimProduct.EnglishProductNameAS產(chǎn)品,SUM(FactResellerSales.SalesAmount)AS銷售額FROMDimProductINNERJOINFactResellerSalesONDimProduct.ProductKey=FactResellerSales.ProductKeyGROUPBYDimProduct.EnglishProductName,DimProduct.ModelName
3、HAVING(DimProduct.ModelName='Women''sTights')從上表可以看出，[Women'sTights,L]與[Women'sTights,S]的銷量相當(dāng)，而[Women'sTights,M]的銷量明顯低于另兩種，其銷量只占總銷量的9%。會(huì)有如此大的差距呢？下面運(yùn)用OLAP技術(shù)進(jìn)行分析。為敘述方便，下文用“M”簡(jiǎn)稱[Women'sTights,M]，用“L”表示[Women'sTights,L]，用“S”表示[Women'sTights,S]。二、多維數(shù)據(jù)集的設(shè)計(jì)1、事實(shí)表的設(shè)計(jì)：本任務(wù)主要考查銷售
4、額，因此需要的銷售額、運(yùn)費(fèi)和稅額三個(gè)主要指標(biāo)；2、維度表的設(shè)計(jì)：影響銷售的因素有很多，如銷售商、銷售團(tuán)隊(duì)、區(qū)域、時(shí)間、促銷手段等，所以此處設(shè)計(jì)了雇員、分銷商、促銷、產(chǎn)品、訂單日期、銷售區(qū)域等維度。三、數(shù)據(jù)分析1、將銷售數(shù)據(jù)按年度分割，以確認(rèn)是否與時(shí)間有關(guān)以年度為列字段，產(chǎn)品名稱為行字段，銷售額為數(shù)據(jù)字段設(shè)計(jì)立方體。表2分年度銷售額SalesAmountCalendarYearEnglishProductName20022003總計(jì)Women'sTights,L$54,257.97$39,296.49$93,554.46Women'
5、sTights,M$12,508.33$5,219.30$17,727.64Women'sTights,S$56,129.63$34,421.28$90,550.91總計(jì)$122,895.93$78,937.08$201,833.01圖1分年度銷售額直方圖從上圖來(lái)看，2002年和2003年Women’sTights,M的銷量都明顯低于另兩種商品，且2003年的銷量還不及2002年的一半，下降的幅度較另外兩種更明顯。為進(jìn)一步分析M的銷售額是否愛時(shí)間的影響，對(duì)兩年的銷售數(shù)據(jù)按周進(jìn)行鉆取，如下表所示：表3三種產(chǎn)品分周銷售額YearWee
6、kWomen'sTights,LWomen'sTights,MWomen'sTights,S總計(jì)M所占比重2002 27$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,44
7、5.66$22,795.259.67%49$7,525.82$2,249.70$7,797.52$17,573.0412.80%2003 1$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.69
8、0.00%9$5,053.52$4,805.55$9,859.060.00%總計(jì) $93,554.46$17,727.64$90,550.91$201,833.018.78%從表3看出，在有銷售的各周里，M的銷售額占總銷售額的比重均低（最高也僅12
