使用SQLServerAnalysisServer2012創(chuàng)建多維數(shù)據(jù)庫

1、魚躍家庭吸氧機、先決條件若要完成本教程，需要使用下列組件、示例和工具:SQLServer數(shù)據(jù)庫引擎（SQLServer2012自帶）AnalysisServices（SQLServer2012自帶）SQLServerDataTools（SQLServer2012自帶）HealthDW數(shù)據(jù)庫（微軟webcast提供，這里也提供一個下載）Excel2010二、設(shè)定場景Health公司是一家保健公司，積累了員工信息，產(chǎn)品信息，產(chǎn)品的單價和產(chǎn)品的銷售息產(chǎn)品信息產(chǎn)品的單價和產(chǎn)品的銷售量。該公司希望建立多維數(shù)據(jù)集了解不同部門員工的銷售業(yè)績。部門員工的銷售業(yè)績。例如，想要了解2008年網(wǎng)售部門銷售八組石雷的

2、銷售業(yè)績。三、實現(xiàn)步驟1、新建并還原HealthDW數(shù)據(jù)庫到本機實例中，略去。2、打開SQLServer2012自帶的SSDT,新建一個AnalysisServicesMultidimensionalandDataMiningProject，命名為SSASLearnO1,然后建立數(shù)據(jù)源、數(shù)據(jù)源視圖，這個略去。魚躍家庭吸氧機DMProductKeyDimDatE卑|TimeKe?1ProductNameProductstyleFroductOassUnitPriceDimProductDimEmpEmployeeKeyEmployNameGroupNameDeptTJame數(shù)據(jù)源視圖包含一個事實

3、表，三個維度表J一一丿JFactSalesProductKeyEmployeeKeyTimeKeyQuantityAmount3、新建cube,并引用前面剛新建的數(shù)據(jù)源視圖。魚躍家庭吸氧機魚躍家庭吸氧機魚躍家庭吸氧機魚躍家庭吸氧機已DimEmp:EmployeeKeyEmployrJameGroupMameDeptf-Jame4、新建三個維度，選擇三個維度表，其他全部默認(rèn)。完成后界面如圖：DimDat&.dimDesignx魚躍家庭吸氧機SolutionExplor&rQI如SolutionSSASL&arnOl1project)呂S&A&LearnOLj區(qū)Dim亡仃sianStructure

4、閣Attribut亡R亡方tionshieTBnslBtkHs-slE-xlaaiiaQ.黑”DataSources*HeaIthDW.diJ_vD白t自SourceViews*J3H&aIthDW.divCubes.HeaIthDW.cub&j_j-Dirnensians匕DimDate.dim1ZDimEmp.dimtZDimProduct.dimMiningStrjdures/Rol&sAaiernbliets_j-MiscellaneousAttributesNDimDate愛TimeKey5、我們首先創(chuàng)建日期維度，產(chǎn)品維度和員工維度與些相似。Hierardii亡百Tocreatean

5、ewhierarchyrdraganattributehere.Da-DimDate.dirnD&sign*x魚躍家庭吸氧機XolutionExplorer才SolutionSSASL&arnOl(1project)匕&ALearnOLj丄DataSources*：*H&althDW.ds_fuimensionst/DimDate.dirnNDimEmp.dimt/DimProduett,dimMiningStructures二Roles_fAssemblies二MiscellaneousDataSourceViews嶇HealthDW.dsvINarreYNameColumnDimDat&.

6、Y(WChar)AttributesHierarchiesDataSi電TimeKeyTocreatEanewhierardny,drmanattributehere.t/DimDateMHTim亡忙亡”AttributeRelationships|垢TranslatiansDimensionStructureKeyColumnsDimDate.TimeKey(Intteger)Me-mberNamesUniqueFakeM&mbersWithDataNonLeaiDataVlilWe-Me-mbersWithDataCapticNameTimeKeyNameColumnDimDate.D(W

7、Char)|PropertiesTx|DimDate.dimDesign*x魚躍家庭吸氧機QKMDimen5ionAttribute寓KeyColumis(none)sourcetable:DinDateDimDate.M(WChaiJNameCanceb；DimensionStrLctire0None0送AttributeRelctionships|逐Iranslanons|反BrowserEncourageGiroupingMNoneTru&(Collection)FalseNorLeafDataVisibleUnproLmssmdPar&ntlsBlankSelfOrMi站R&gnlor

8、(none)Regular(none)Qj5tomRollupPropertiesDefaultMemberDescriptionDiscretizazionBucketCourDistr&tiza:inM&tbodEstimat&dCountF&rmaistrlngtroupingB&haviorIDInstanceselectionkAggr&gataol?KeyColumnsMeinbfrrNameiUnlqueMemb&rsWithDataMembersWithDataCaptcNametNameColurniNamingTemplateOrderEyCrderEyAttribuzeP

9、rocessingStateRootMemberlfTypeUnaryOperatorCourmiusageValueColumniI*r*rr*AvailableColurrnsKeyColumns.國TimeKey國M/肉為月屬于年.所以xeyCol-imnst佢顯示名稱為乩即月Z-1此H注意月維度必須包含年，因為獨立的月毫無意義。好了，此時，部署整個項目，第一次需要這樣，后面只需要單獨處理維度或cube即可完成部署。魚躍家庭吸氧機Currentlevel:日J(rèn)All1JiJ1d1010d11d11d12d垃J2部署完成后，我們可以瀏覽下日期維度的瀏覽效果：瀏覽一下效果，注意這里的1代表的

10、是|不鬥年色J注意上圖中的月1有多個，但代表的是不同年份。這里有個小小的bug,就是月份排列按字符排列，不是按實際月份數(shù)字排列，修正一下即可OrderEyKeyQrderByAttributeKeyProteksingGtateNameRootMemberlfAttributeKeyTyp&AttributeM白me此時對該維度，Process,并Reconnection,得到如下效果:魚躍家庭吸氧機排序妥了，我們希望的得到一個日期的層次結(jié)構(gòu)鉆取，再做如下修改即可。MMDimDale.dimDesignt/DimDt亡1-AttributesHierariesDataSourceViewt.期

11、硯加newlevTimeKey：!DimDateTim亡KeDPlTimeKey塔典DimensionStructure埠AttributeRelationshipsIranslations|LBrowser至超丨育”耗丨裁|EX巳”您依次將左邊的三個維度屬性拖過來，注意順序J一一丿對Cube,Process，并Reconnection,得到如下效果:|g|山ActianEIPartitiansAggregations|爲(wèi)IPerepe魚躍家庭吸氧機HealihDWxubeDesignxDimDate.dimdesignLanguage;defaultHu日thDW0肩HierarchyDim

12、EmpEmployeeKeyDimProductProductKeyI為CalojlaticnsCubeStruct.1STDimension注意SSDT中已經(jīng)廢除了多維瀏覽功能U我們需要BLJ1AmountBLJ1QuanfltyKPIsDimSKElISsE0YPlTimeKeyAmountQuantity2C062005-01-0300:00:00,0002-4622C0S1200S-01-0500:00:00.000軸22100512006-01-0700:00:00.00011212J0D&22CCI&-C2-0200:00:00.00045412C0S22005-02-0600:0

13、0:00.0002522100522C05-02-070Q:0Q:0Q.00022JOOS22006-02-0800:00:00.000尿1ZOOS22005-02-0900:00:00,0005112C0G32006-03-0300:00!00.00025212C0532005-03-0500:00:00,000252121006321006-03-0600:00:00.0002A62ZllUbZLlUb-u-u己lu：uu:uu.uuuz注意，SQLServer2012自帶的SSDT已經(jīng)廢除了在該開發(fā)界面中對多維數(shù)據(jù)集的瀏覽,我們只能轉(zhuǎn)到Excel界面完成魚躍家庭吸氧機A5A3匚0nhi5

14、330735B4851-!i十6+78因T工材且兀+5OfB910111213H15ffl010嚨.-)工fFJSl-Micro&qREwcel開妁RSI韋是式覿搖審閩觀罔ffigTSLoadTestPowerPlvut主蜀曲?ataMiningTeamII丨IJAmount目甘豆改*縛廠礙翻表換曽遠(yuǎn)項嘩捋祈睜辣爼1十2007sr.oifio4-2008252D32謹(jǐn)擇要添加封振喪的辜段:1G總計1n*fh|mp呂CFB/匸”活動孚帳:1行駆簽；+2006”AinountQuant1ty1882785-J-JHierarciiyT亙他拿啟2695739321914.22003-01-2600:

15、00:00.0002OS43S2172003-01-0500:00:00.0002008-D1-L600:00:00.000二FctEJ1-i/liTioun.tH7Quantity行標(biāo)査Hieraret推遲有目更軒在臥下區(qū)握冃拖功宇尿:7抿杏Fii云專選揮”修無運4膻SgOLAPZs丫的圏芳折”魚躍家庭吸氧機魚躍家庭吸氧機6、我們繼續(xù)完成產(chǎn)品維度和員工維度的設(shè)計。DimProductdimiDesign*xHealthDW.cubeDesign*DimDate.dimDesign魚躍家庭吸氧機DimensionStructure逼AtbibutER亡btjcinshim|城Tensbtici

16、nw|園Eiitws亡雖金Inixl因|ElQk為TAttributesHierarchiestzDimProduct1-f7777777ProductClassn_-yProductKeyJProductstyle1朗“ProductClass宀Productstyle土ProductKeyTocreateanewhieraryjdraganattributehere,魚躍家庭吸氧機魚躍家庭吸氧機ProductName魚躍家庭吸氧機HealthDW.cube舊岀DimEmp.dimDesign*xDimProducttGlimDesign*魚躍家庭吸氧機jIpsDirengionStuctijr&國人旳血11擔(dān)且已mtidrimhipm|垢Trsnmlm