外文翻译 - 构建基于 Web 的分析系统

0外文原文1BuildingaWeb-BasedAnalysisSystemPart1Areal-worldlookatusingtheAnalysisServicesThinWebClientBrowserMarkScott,JohnLynnUsingOWCtoDeployOfficeontheWebWhenworkingwithanalyticaldatabases,analystsorganizedataintocommongroupsandtrytodeterminewhatwouldhappenifthingsweredifferent.Forexample,wouldincreasingaproductspricewhichwouldincreaseprofitperunitbutprobablyreducenumberofunitssoldyieldahigherorloweroverallprofit?Orhowwouldadropinthefederaldiscountrateaffecttheyieldofrealestateloans?Tohelpanalystsmakeeducatedprojectionsbasedonhistoricaltrends,MicrosoftprovidesAnalysisServicesinSQLServer2000andOLAPServicesinSQLServer7.0.TheseservicesprovideOLAPcapabilityandcanprocessdatastoredinSQLServer(oranyotherOLEDBcompatibledatasource)intomultidimensionaldatastructurescalledcubes.Datacubessimplifytheprocessofanalyzingtrendsandcorrelatingthewayentitiesinteractwithoneanother.Forexample,realestateinvestorsusecash-flowmodelingtoisolateagroupofloansthathavecommoncharacteristics(e.g.,typesofproperties,geographicarea,rangeofinterestrates)andprojecttheeffectsofdifferentkindsofevents.Whatwillhappenifloansmaturemorerapidlythanexpectedoriftheborrowersdefault?Andhowmightsuchunpredictableeventsaffecttheyieldofbondsthattheloanssecure?Selectingfromliststhatcanincludehundredsofloansandisolatingtheloansthathavethecharacteristicthatyoureanalyzingcanbetricky.AnalysisServicesandOLAPServicescanhelpcorrelatethesegroupsofloanssothatanalystscanmodelloanassumptions.Tohelpaclientsrealestateanalystsprojecttheperformanceofcommercialmortgage-backedsecurities,ourdevelopmentteamneededtodeviseasystemthatsimplifiedthegroupingofloansindifferentwayssuchasbytheirinterestrate,termtomaturity,orpropertylocation.Theinterfaceneededtobeeasytolearnanduse.Andthesystemwedevelopedneededtobe1securelydeployedthroughtheInternet.Tomeetthesecriteria,thedevelopmentteamchoseAnalysisServices.Havingsettledonaback-endtechnology,thedevelopmentteambeganworkingonaplanforimplementingthefront-endinterface.MostfinancialanalystsuseMicrosoftExcelandarefamiliarandcomfortablewithitsinterface.ExcelincludesPivotTableService,whichletsanalystsconnecttoAnalysisServicesdatabases.Excelsdrag-and-dropinterfaceprovidessimple,intuitiveaccesstomultidimensionaldatawithoutrequiringuserstohaveextensivetraining.AndbyusingExcelsgraphingcapabilities,userscanpresentdataingraphsandcharts.Soforthefront-endinterface,theteamsfirstchoicewasExcel2002,whichispartofMicrosoftOfficeXP.Figure1showsExcelsPivotTableServiceexploringanAnalysisServicesOLAPcube.ExcelwouldhavebeenafinechoiceifalltheclientsusersworkedtogetherinthesamebuildingandcouldaccesstheAnalysisserverthroughthesameLAN.Butbecausetheusersneededtosharetheapplicationfromavarietyoforganizationswhoseofficesarescatteredaroundtheworld,theteamneededacomponentsimilartoExcelthatuserscouldaccessthroughtheInternet.TheteamfoundthesolutiontothischallengeinOfficeWebComponents.OWCisasetofActiveXcontrolsthatyoucanuseonWebpagestoprovideOfficefunctionality.TheOWCPivotTablecomponentisaWebversionofExcelsPivotTableService;PivotTableusesPivotTableServiceandrequiresthatPivotTableServicebeinstalledbeforeitwillrun.ButtheOWCPivotTableworkswithoutExcel.PivotTablecanretrievemultidimensionaldatafromanAnalysisserverandpresentthedatainaninteractive,drag-and-dropinterface.UserswhohaveMicrosoftInternetExplorer(IE)4.01orlatercanuseOWCtoanalyzeAnalysisServicesdatawithoutinstallingadditionalcomponentsoftware.Figure2showstheOWCPivotTableclientinterface,whichlooksandworkslikethefamiliarExcelinterface.TheOWCPivotTablealsoprovidesintelligentcaching,whichimprovesperformancebyreducingthenumberoftripsPivotTablemakesthroughthenetworktotheserver.SobyactivelyworkingwithAnalysisServices,PivotTablecanreducedatatransferandworkfaster.2AlthoughOWCprovidedeverythingourdevelopmentteamsprojectneeded,weencounteredproblemswhenwetriedtodeployOWCacrosstheInternet.ThefirstproblemwastheplatformthatOWCrunson.TheOfficeXPversionofOWCrequiresMicrosoftDataAccessComponents(MDAC)2.6orlater.ManyoftheservicesubscribersuseWindowsNTWorkstation4.0astheirOS,andtoinstallMDAC2.6,theyalsohadtoinstallServicePack6(SP6).OneoftheprimaryattractionsofusingOWCwasthatwethoughtdeploymentwouldbeseamless.Wediscoveredthatalthoughwecouldautomatetheprocessofinstallingservicepacks,theprocessrequiresrebootsandisintrusive.MicrosoftlaterprovidedarevisedversionoftheOWCcomponentthatworkswithSP4,butatthetimeweweredevelopingourapplication,thedeploymentofservicepacksinthetightlycontrolledclientnetworkofafinancialinstitutionwasasignificantbarrier.Thus,asolutionthatrequiredaspecificservicepackfortheOSwasntaviableoption.Thesecondproblemthatourteamencounteredwasconnectivity.OWCrequiresadirectconnectiontotheAnalysisServicesdatasource.OWCcommunicatesdirectlywiththeAnalysisserverthroughthedefaultport,2725,whichisaproblemfororganizationswithfirewalls.(FordetailsaboutconnectingtoAnalysisServices,seetheMicrosoftarticleINF:TCPPortsUsedbyOLAPServiceswhenConnectingThroughaFirewallat/default.aspx?scid=kb;en-us;q301901.)First,wetriedtosolvetheconnectivityproblembyusinganHTTPconnectiontoconnecttotheserverthroughport80.ThisconnectionprovidesaccessthroughthesameportthattheWebbrowseruses.Foraddedsecurity,AnalysisServicescanalternativelyconnectthroughport443byusingSecureSocketsLayer(SSL).Mostorganizationsopenbothport80andport443toletusersaccesstheInternet.(FormoreinformationaboutusingHTTP,seetheMicrosoftarticleINF:HowtoConnecttoAnalysisServices2000ByUsingHTTPConnectionat/default.aspx?scid=kb;en-us;q279489.)Figure3illustratesthechallengesinvolvedinusingOWCtoconnecttotheAnalysisserver.However,theimplementationofHTTPconnectivitycreatedsomeinsurmountableobstacles.Connectionsthroughport80weresignificantlyslowerthandirectconnectionsin3ourtests.Becausethemultidimensionalcubehastoprovidelargeamountsofdatatotheclient,theperformancedegradationmadeusingOWCimpractical.LookingforAlternativesNext,ourteamconsideredusingADO-MDandMDXqueriestocreateacustominterface.YoucanquerytheAnalysisServicescubedirectlythroughtheOPENROWSETcommand.(ForinformationaboutqueryingAnalysisServices,seetheMicrosoftarticleHOWTO:SQLServer7DistributedQuerywithOLAPServerat/default.aspx?scid=kb;en-us;q218592.)OPENROWSETletsyouquerydatafromanyOLEDBsource,includingAnalysisServices.ThisflexibilitywouldletususeADOtoqueryAnalysisServices.TheOLEDBproviderforAnalysisServices,MSOLAP,convertsthemultidimensionaldataintoastandardrowsetthatADOcanusetocommunicatethedatatothefront-endapplication.Theproblemwiththiscustomsolutionwasthecomplexityofcreatinganinterfacethatprovidedtheintuitive,interactivelookandfeelofOWCandExcel.Althoughtheteamwascapableofcreatingsuchaninterface,thetime,expense,andongoingmaintenanceofsuchasolutionmadeitprohibitive.Theteamalsoinvestigatedseveralthird-partysolutions.Manythird-partysolutionshelpusersbuildaqueryandexecuteittoseetheresults.SuchanapproachusessystemresourcesefficientlybutisntasinteractiveastheExcelandOWCdrag-and-dropinterfaces.Soalthoughthesesolutionsoffereduniqueadvantages,nonemettheexactrequirementsoftheproject.Whenourteamaddedcustomdevelopmentcoststotheoverallcostofthethird-partysoftware,wedecidedtolookelsewhere.4中文译文1构建基于Web的分析系统第一部分使用分析服务Web客户端浏览器的现实看法MarkScott,JohnLynn使用OWC在Web上部署Office在选定了后端技术后，开发小组开始制订实现前端界面的计划。多数金融分析人员使用MicrosoftExcel，他们对其界面比较熟悉，感觉也很舒服。Excel包括数据透视表服务，能够允许分析人员连接到分析服务数据库。Excel的拖放界面提供了对多维数据的简单和直观的访问，并不要求用户进行深入的培训。而且，通过使用Excel的制图功能，用户能够以图和表的形式表示数据。所以，对于前端界面，小组的首选是MicrosoftOfficeXP中的Excel2002。图1显示了Excel数据透视表服务浏览一个分析服务OLAP多维数据集的情形。如果所有的客户端用户在同一幢大楼内一起工作，并通过同一个局域网访问分析服务器，Excel会是不错的选择。但用户需要和办公地点散布于世界各地的不同组织共享应用程序，因此开发小组需要一个用户可以通过Internet访问且类似于Excel的组件。该小组发现OfficeWeb组件(OWC)能够满足这一需要。OWC是一组能够在Web页上使用并提供Office功能的ActiveX控件。OWC数据透视表组件是Excel中数据透视表服务的Web版本；数据透视表使用数据透视表服务，并要求在运行前安装数据透视表服务。但没有Excel，OWC数据透视表也能工作。数据透视表能够从分析服务器上检索多维数据并将这些数据显示在一个交互的拖放界面上。已安装MicrosoftInternetExplorer(IE)4.01或以上版本的用户可以使用OWC对分析服务数据进行分析，而不必安装额外的组件软件。图2显示了外观和操作都类似于熟悉的Excel界面的OWC数据透视表客户端界面。OWC数据透视表也提供了智能缓存，通过减少数据透视表从网络到服务器的往返行程次数而提高性能。所以，通过有效使用分析服务，数据透视表能够减少数据传输并提高效率。虽然OWC提供了我们开发小组的项目需要的全部东西，但当我们试图在Internet上部署OWC时，我们遇到了难题。首先是OWC的运行平台问题。OfficeXP版的OWC要求使用MicrosoftDataAccessComponents(MDAC)2.6或以上版本。而许多服务订户使用WindowsNTWorkstation4.0作为其操作系统，如果要安装MDAC2.6，还必须安装ServicePack6(SP6)。使用OWC的一个主要吸引力在于我们认为它能够实现无缝的部署。我们发现虽然能够自动处理安装ServicePack，但该过程需要重新启动，非常麻烦。以后，Microsoft提供了一个使用SP4的OWC组件修订版本，但我们同时也在开发自己的应用程序，在金融机构严格控制的客户端网络上部署ServicePack是一个很大的困难。因此，需要在操作系统上使用特定ServicePack的解决方案是不可行的。其次，我们小组遇到了连接问题。OWC要求直接连接分析服务数据源。OWC使用默认的2725端口直接和分析服务器通信，对于使用防火墙的机构来说，这是个问题。（有关连接分析服务的详细信息，参见位于/default.aspx?scid=kb;en-us;q301901的Microsoft文章INF:TCPPortsUsedbyOLAPServiceswhenConnectingThroughaFirewall。）5首先，我们试图使用HTTP连接和服务器通过80端口进行连接以解决连接问题。该连接通过Web浏览器使用的同一个端口来提供访问。对于额外的安全性，分析服务还能够使用安全套接字层(SSL)，通过443端口进行连接。大多数组织同时打开80端口和443端口以便用户访问Internet。（有关使用HTTP的更多信息，参见位于/default.aspx?scid=kb;en-us;q279489的Microsoft文章INF:HowtoConnecttoAnalysisService2000ByUsingHTTPConection。）图3说明了使用OWC连接到分析服务器涉及的问题。然而，HTTP连接的执行产生了一些难以克服的困难。我们测试显示，通过80端口进行连接要明显慢于直接连接。因为多维数据集需要向客户端提供大量的数据，性能的降低使OWC的使用很不现实。寻找替代方案接下来，我们的小组考虑使用ADO-MD和MDX查询创建自定义界面。您可使用OPENROWSET命令直接查询分析服务多维数据集。（有关查询分析服务的信息，参见位于/default.aspx?scid=kb;en-us;q218592的Microsoft文章HOWTO:SQLServer7DistributedQuerywithOLAPServer。）OPENROWSET允许您从包括分析服务在内的任何OLEDB源上查询数据。这种灵活性能够让我们使用ADO查询分析服务。分析服务使用的OLEDB提供程序MSOLAP将多维数据转换成ADO能够用来同前端的应用程序进行数据通信的标准行集。这种自定义解决方案的问题在于创建具有OWC和Excel外观的直观而且互动的界面是一件非常复杂的工作。虽然开发小组能够创建这样的界面，但所花时间长、费用高，且需要不断进行维护，因此该解决方案不具备可行性。开发小组也研究了几个第三方的解决方案。很多第三方解决方案是帮助用户生成一个查询，然后执行它来查看结果。这种方式虽然有效地利用了系统资源，但达不到Excel和OWC的拖放界面同样的交互式效果。所以，虽然这些解决方案各具优势，但没有一个能够完全满足本项目的要求。在我们小组将自定义开发成本加入第三方软件的总成本考虑时，我们决定重新寻找替代方案。6外文原文2BuildingaWeb-BasedAnalysisSystemPart2UsingtheThinWebClienttoAccessMultidimensionalDataThedevelopmentteammembersfinallywenttotheMicrosoftSQLServerResourceKitandfoundthesolutiontoourdilemma:theAnalysisServicesThinWebClientBrowser.(ToaccesstheThinWebClientontheresourcekitCD-ROM,seethereferenceinchapter39oftheresourcekitat/resources/documentation/sql/2000/all/reskit/en-us/part11/c3961.mspx.)TheThinWebClientusesActiveServerPages(ASP)toconnecttotheAnalysisserver,convertsthemultidimensionaldatatoHTML,andpassesthedatatotheclient.Figure4showstheThinWebClientdisplayingasubsetofloandata.TheThinWebClientrequiresIE5.0orlater.BecausetheclientdoesntconnectdirectlytotheAnalysisServicescomputer,theclientdoesntneedMDAC2.6.MostofourclientssubscribershaveIE5.0,sowedidnthavetodeployservicepackstothesubscribersPCs.Figure5showsthearchitecturethattheThinWebClientuses.TheThinWebClientusesASPtoquerythecubefromtheMicrosoftIISserverratherthanfromtheclient.TheThinWebClientcomeswithASPpages,whichyoucandeployonanIISserver.OnlytheIISserverthatsrunningASPcanconnecttotheAnalysisServicesdatabase.Thus,youcanuseoneconnectiontosecurecubeaccessthroughthefirewall,andyoucanlimitthatconnectiontojusttheWebserversandthedatabaseserver.Thearrangementcreatesahigh-performance,easilysecuredconnection.TheonlyconnectiontotheclientisastandardHTMLconnection,whichalleviatesthefirewallconcerns.TheThinWebClientputsthequerieddatainagridlikeHTMLtablestructureandsendsthedatatothebrowser.TheuserinteractswiththedatathroughJavaScript.Byusingtransparentlayers,userscandragdimensionsintothecubetomanipulatethedata,drilldownthroughdimensions,anddisplayandanalyzethedatatheyneed.UsingtheThinWebClientrequiressomecompromises,asmostdesigndecisionsdo.BecauseatrustrelationshipcantexistbetweentheWebserverdomainandtheback-enddomain,youcantextendActiveDirectory(AD)asyourauthenticationservicethroughthe7system.Suchatrustrelationshipcouldprovideameansforanintrudertoviolatesecurityontheback-enddomain.SothesystemmustsetuponeanonymoususertoaccessthedataontheAnalysisserver.Ifaccesstothedatadependsontheuser,youcantuseAnalysisServicesrole-basedsecuritytocontrolaccesstothedata.Inasystemthatletsallusersaccessthedatathroughonesecuritycontext,however,thisarrangementusingtheThinWebClientissecureandeasytodeploy.TheThinWebClientalsohasotherdrawbacks.OWCusesPivotTablesintelligentcachingtominimizetripsfromtheWebbrowsertotheOLAPWebservertogetmoredata.ButtheThinWebClientdrawsdatafromtheserverside,andbecausedataisntcachedattheWebbrowser,eachchangetothedatarequiresthebrowsertomakeanewrequestfromtheWebserver.Whenyoureworkingwithlargeamountsofdata,thisprocesscanbesluggish.Also,OWChasarichobjectmodelagainstwhichyoucanprogramcustomcode.TheThinWebClientusesJavaScriptandcanbemoredifficulttocustomize.BecausetheThinWebClientisfreecodeincludedintheresourcekit,MicrosoftdoesntprovidethesamelevelofsupportfortheThinWebClientasitdoesforExcelorOWC.AndtheThinWebClientrequiresclient-sidescripting,whichcanproduceerrorsiftheWebbrowserisupdatedorchanges.Inaddition,theThinWebClientdoesntdirectlysupportwrite-backtotheserver.Fortunately,ourteamsapplicationdidntrequirewrite-back,sothismissingelementdidntdetertheteamfromusingtheThinWebClient.AfterevaluatingtheadvantagesanddrawbacksoftheThinWebClient,wedecidedtodeployit.Thesolutionworkswellintheproductionenvironmentandhasbeenwellreceivedbysubscribers.ChoosingtheRightClientfortheTaskThelessonsourdevelopmentteamlearnedfromthisprojectcanprovideavaluableguidefordeployingOLAPsolutionsinanenterprise.Excelprovidesacapable,familiarclientthatyoucandeployinaLANbutrequiresrealtimeconnectivitytotheOLAPserver.OWCworkswellfordeployinganAnalysisServicesclientinanintranetbecauseyoucaneasilycontroltheclientplatformandopenportssecurelyinanintranet.TheThinWebClientprovidesagoodInternetsolutionwhenfirewallsareinplaceandyouwantminimalimpactontheuserOS.Foranydevelopmentproject,youneedtounderstandthebusiness8requirementsandneedsofthepeoplewhowillusetheproductsyoudevelop.Byoutliningrequirementsandweighingalltheoptions,youcandiscovertherightsolutiontosatisfyyourclientsrequirements.9中文译文2构建基于Web的分析系统第二部分使用Web客户端访问多维数据开发小组成员最后选用MicrosoftSQLServerResourceKit，为我们的难题找到了解决方案：分析服务Web客户端浏览器。（要在资源工具箱CD-ROM上访问Web瘦客户端，参见资源工具箱第39章的参考信息，该信息位于/technet/treeview/default.asp?url=/technet/pro