使用SQLServerAnalysisServer2012创建多维数据库

1、鱼跃家庭吸氧机、先决条件若要完成本教程，需要使用下列组件、示例和工具:SQLServer数据库引擎（SQLServer2012自带）AnalysisServices（SQLServer2012自带）SQLServerDataTools（SQLServer2012自带）HealthDW数据库（微软webcast提供，这里也提供一个下载）Excel2010二、设定场景Health公司是一家保健公司，积累了员工信息，产品信息，产品的单价和产品的销售息产品信息产品的单价和产品的销售量。该公司希望建立多维数据集了解不同部门员工的销售业绩。部门员工的销售业绩。例如，想要了解2008年网售部门销售八组石雷的

2、销售业绩。三、实现步骤1、新建并还原HealthDW数据库到本机实例中，略去。2、打开SQLServer2012自带的SSDT,新建一个AnalysisServicesMultidimensionalandDataMiningProject，命名为SSASLearnO1,然后建立数据源、数据源视图，这个略去。鱼跃家庭吸氧机DMProductKeyDimDatE卑|TimeKe?1ProductNameProductstyleFroductOassUnitPriceDimProductDimEmpEmployeeKeyEmployNameGroupNameDeptTJame数据源视图包含一个事实

3、表，三个维度表J一一丿JFactSalesProductKeyEmployeeKeyTimeKeyQuantityAmount3、新建cube,并引用前面刚新建的数据源视图。鱼跃家庭吸氧机鱼跃家庭吸氧机鱼跃家庭吸氧机鱼跃家庭吸氧机已DimEmp:EmployeeKeyEmployrJameGroupMameDeptf-Jame4、新建三个维度，选择三个维度表，其他全部默认。完成后界面如图：DimDat&.dimDesignx鱼跃家庭吸氧机SolutionExplor&rQI如SolutionSSASL&arnOl1project)吕S&A&LearnOLj区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、我们首先创建日期维度，产品维度和员工维度与些相似。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:日JAll1JiJ1d1010d11d11d12d垃J2部署完成后，我们可以浏览下日期维度的浏览效果：浏览一下效果，注意这里的1代表的

10、是|不鬥年色J注意上图中的月1有多个，但代表的是不同年份。这里有个小小的bug,就是月份排列按字符排列，不是按实际月份数字排列，修正一下即可OrderEyKeyQrderByAttributeKeyProteksingGtateNameRootMemberlfAttributeKeyTyp&AttributeM白me此时对该维度，Process,并Reconnection,得到如下效果:鱼跃家庭吸氧机排序妥了，我们希望的得到一个日期的层次结构钻取，再做如下修改即可。MMDimDale.dimDesignt/DimDt亡1-AttributesHierariesDataSourceViewt.期

11、砚加newlevTimeKey：!DimDateTim亡KeDPlTimeKey塔典DimensionStructure埠AttributeRelationshipsIranslations|LBrowser至超丨育”耗丨裁|EX巳”您依次将左边的三个维度属性拖过来，注意顺序J一一丿对Cube,Process，并Reconnection,得到如下效果:|g|山ActianEIPartitiansAggregations|爲IPerepe鱼跃家庭吸氧机HealihDWxubeDesignxDimDate.dimdesignLanguage;defaultHu日thDW0肩HierarchyDim

12、EmpEmployeeKeyDimProductProductKeyI為CalojlaticnsCubeStruct.1STDimension注意SSDT中已经废除了多维浏览功能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已经废除了在该开发界面中对多维数据集的浏览,我们只能转到Excel界面完成鱼跃家庭吸氧机A5A3匚0nhi5

14、330735B4851-!i十6+78因T工材且兀+5OfB910111213H15ffl010咙.-)工fFJSl-Micro&qREwcel开妁RSI韦是式觌搖审闽观罔ffigTSLoadTestPowerPlvut主蜀曲?ataMiningTeamII丨IJAmount目甘豆改*縛厂碍翻表换曽远项哗捋祈睁辣爼1十2007sr.oifio4-2008252D32谨择要添加封振喪的辜段: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行标査Hieraret推迟有目更轩在臥下区握冃拖功宇尿:7抿杏Fii云專选挥”修無运4膻SgOLAPZs丫的圏芳折”鱼跃家庭吸氧机鱼跃家庭吸氧机6、我们继续完成产品维度和员工维度的设计。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担且已mtidrimhipm|垢Trsnmlm