MSSQL网页设计甚么是OLAP

MySQL最初的开发者的意图是用mSQL和他们自己的快速低级例程(ISAM)去连接表格。经过一些测试后，开发者得出结论：mSQL并没有他们需要的那么快和灵活。
On-LineAnalyticalProcessing(OLAP)isacategoryofsoftwaretechnologythatenablesanalysts,managersandexecutivestogaininsightintodatathroughfast,consistent,interactiveaccesstoawidevarietyofpossibleviewsofinformationthathasbeentransformedfromrawdatatoreflecttherealdimensionalityoftheenterpriseasunderstoodbytheuser.

OLAPfunctionalityischaracterizedbydynamicmulti-dimensionalanalysisofconsolidatedenterprisedatasupportingenduseranalyticalandnavigationalactivitiesincluding:calculationsandmodelingappliedacrossdimensions,throughhierarchiesand/oracrossmemberstrendanalysisoversequentialtimeperiodsslicingsubsetsforon-screenviewingdrill-downtodeeperlevelsofconsolidationreach-throughtounderlyingdetaildatarotationtonewdimensionalcomparisonsintheviewingarea


OLAPisimplementedinamulti-userclient/servermodeandoffersconsistentlyrapidresponsetoqueries,regardlessofdatabasesizeandcomplexity.OLAPhelpstheusersynthesizeenterpriseinformationthroughcomparative,personalizedviewing,aswellasthroughanalysisofhistoricalandprojecteddatainvarious"what-if"datamodelscenarios.ThisisachievedthroughuseofanOLAPServer.
OLAPSERVER
AnOLAPserverisahigh-capacity,multi-userdatamanipulationenginespecificallydesignedtosupportandoperateonmulti-dimensionaldatastructures.Amulti-dimensionalstructureisarrangedsothateverydataitemislocatedandaccessedbasedontheintersectionofthedimensionmemberswhichdefinethatitem.Thedesignoftheserverandthestructureofthedataareoptimizedforrapidad-hocinformationretrievalinanyorientation,aswellasforfast,flexiblecalculationandtransformationofrawdatabasedonformulaicrelationships.TheOLAPServermayeitherphysicallystagetheprocessedmulti-dimensionalinformationtodeliverconsistentandrapidresponsetimestoendusers,oritmaypopulateitsdatastructuresinreal-timefromrelationalorotherdatabases,orofferachoiceofboth.Giventhecurrentstateoftechnologyandtheenduserrequirementforconsistentandrapidresponsetimes,stagingthemulti-dimensionaldataintheOLAPServerisoftenthepreferredmethod.
OLAPGLOSSARYDefinedterms:AGGREGATEANALYSIS,MULTI-DIMENSIONALARRAY,MULTI-DIMENSIONALCALCULATEDMEMBERCELLCHILDRENCOLUMNDIMENSIONCONSOLIDATECUBEDENSEDERIVEDDATADERIVEDMEMBERSDETAILMEMBERDIMENSIONDRILLDOWN/UPFORMULAFORMULA,CROSS-DIMENSIONALGENERATION,HIERARCHICALHIERARCHICALRELATIONSHIPSHORIZONTALDIMENSIONHYPERCUBEINPUTMEMBERSLEVEL,HIERARCHICALMEMBER,DIMENSIONMEMBERCOMBINATIONMISSINGDATA,MISSINGVALUEMULTI-DIMENSIONALDATASTRUCTUREMULTI-DIMENSIONALQUERYLANGUAGENAVIGATIONNESTING(OFMULTI-DIMENSIONALCOLUMNSANDROWS)NON-MISSINGDATAOLAPCLIENTPAGEDIMENSIONPAGEDISPLAYPARENTPIVOTPRE-CALCULATED/PRE-CONSOLIDATEDDATAREACHTHROUGHROLL-UPROTATEROWDIMENSIONSCOPINGSELECTIONSLICESLICEANDDICESPARSEVERTICALDIMENSIONDefinitions:AGGREGATE
See:Consolidate
ANALYSIS,MULTI-DIMENSIONAL
Theobjectiveofmulti-dimensionalanalysisisforenduserstogaininsightintothemeaningcontainedindatabases.Themulti-dimensionalapproachtoanalysisalignsthedatacontentwiththeanalystsmentalmodel,hencereducingconfusionandloweringtheincidenceoferroneousinterpretations.Italsoeasesnavigatingthedatabase,screeningforaparticularsubsetofdata,askingforthedatainaparticularorientationanddefininganalyticalcalculations.Furthermore,becausethedataisphysicallystoredinamulti-dimensionalstructure,thespeedoftheseoperationsismanytimesfasterandmoreconsistentthanispossibleinotherdatabasestructures.Thiscombinationofsimplicityandspeedisoneofthekeybenefitsofmulti-dimensionalanalysis.
ARRAY,MULTI-DIMENSIONAL
Agroupofdatacellsarrangedbythedimensionsofthedata.Forexample,aspreadsheetexemplifiesatwo-dimensionalarraywiththedatacellsarrangedinrowsandcolumns,eachbeingadimension.Athree-dimensionalarraycanbevisualizedasacubewitheachdimensionformingasideofthecube,includinganysliceparallelwiththatside.Higherdimensionalarrayshavenophysicalmetaphor,buttheyorganizethedatainthewayusersthinkoftheirenterprise.Typicalenterprisedimensionsaretime,measures,products,geographicalregions,saleschannels,etc.
Synonyms:Multi-dimensionalStructure,Cube,Hypercube
CALCULATEDMEMBER
Acalculatedmemberisamemberofadimensionwhosevalueisdeterminedfromothermembersvalues(e.g.,byapplicationofamathematicalorlogicaloperation).CalculatedmembersmaybepartoftheOLAPserverdatabaseormayhavebeenspecifiedbytheuserduringaninteractivesession.Acalculatedmemberisanymemberthatisnotaninputmember.
CELL
Asingledatapointthatoccursattheintersectiondefinedbyselectingonememberfromeachdimensioninamulti-dimensionalarray.Forexample,ifthedimensionsaremeasures,time,productandgeography,thenthedimensionmembers:Sales,January1994,CandyBarsandUnitedStatesspecifyapreciseintersectionalongalldimensionsthatuniquelyidentifiesasingledatacell,whichcontainsthevalueofcandybarsalesintheUnitedStatesforthemonthofJanuary1994.
See:MemberCombination
CHILDREN
Membersofadimensionthatareincludedinacalculationtoproduceaconsolidatedtotalforaparentmember.Childrenmaythemselvesbeconsolidatedlevels,whichrequiresthattheyhavechildren.Amembermaybeachildformorethanoneparent,andachildsmultipleparentsmaynotnecessarilybeatthesamehierarchicallevel,therebyallowingcomplex,multiplehierarchicalaggregationswithinanydimension.
COLUMNDIMENSION
See:PageDisplay
CONSOLIDATE
Multi-dimensionaldatabasesgenerallyhavehierarchiesorformula-basedrelationshipsofdatawithineachdimension.Consolidationinvolvescomputingallofthesedatarelationshipsforoneormoredimensions,forexample,addingupallDepartmentstogetTotalDivisiondata.Whilesuchrelationshipsarenormallysummations,anytypeofcomputationalrelationshiporformulamightbedefined.
Synonyms:Roll-up,Aggregate
See:Formula,HierarchicalRelationships,Children,Parents
CUBE
See:Array,Multi-dimensional
DENSE
Amulti-dimensionaldatabaseisdenseifarelativelyhighpercentageofthepossiblecombinationsofitsdimensionmemberscontaindatavalues.Thisistheoppositeofsparse.
DERIVEDDATA
Deriveddataisproducedbyapplyingcalculationstoinputdataatthetimetherequestforthatdataismade,i.e.,thedatahasnotbeenpre-computedandstoredonthedatabase.Thepurposeofusingderiveddataistosavestoragespaceandcalculationtime,particularlyforcalculateddatathatmaybeinfrequentlycalledfororthatissusceptibletoahighdegreeofinteractivepersonalizationbytheuser.Thetradeoffisslowerretrievals.
See:Pre-calculatedData
DERIVEDMEMBERS
Derivedmembersarememberswhoseassociateddataisderiveddata.
DETAILMEMBER
Adetailmemberofadimensionisthelowestlevelnumberinitshierarchy.
See:Level
DIMENSION
Adimensionisastructuralattributeofacubethatisalistofmembers,allofwhichareofasimilartypeintheusersperceptionofthedata.Forexample,allmonths,quarters,years,etc.,makeupatimedimension;likewiseallcities,regions,countries,etc.,makeupageographydimension.Adimensionactsasanindexforidentifyingvalueswithinamulti-dimensionalarray.Ifonememberofthedimensionisselected,thentheremainingdimensionsinwhicharangeofmembers(orallmembers)areselecteddefinesasub-cube.Ifallbuttwodimensionshaveasinglememberselected,theremainingtwodimensionsdefineaspreadsheet(ora"slice"ora"page").Ifalldimensionshaveasinglememberselected,thenasinglecellisdefined.Dimensionsofferaveryconcise,intuitivewayoforganizingandselectingdataforretrieval,explorationandanalysis.
DRILLDOWN/UP
Drillingdownorupisaspecificanalyticaltechniquewherebytheusernavigatesamonglevelsofdatarangingfromthemostsummarized(up)tothemostdetailed(down).Thedrillingpathsmaybedefinedbythehierarchieswithindimensionsorotherrelationshipsthatmaybedynamicwithinorbetweendimensions.Forexample,whenviewingsalesdataforNorthAmerica,adrill-downoperationintheRegiondimensionwouldthendisplayCanada,theeasternUnitedStatesandtheWesternUnitedStates.Afurtherdrill-downonCanadamightdisplayToronto,Vancouver,Montreal,etc.
FORMULA
Aformulaisadatabaseobject,whichisacalculation,ruleorotherexpressionformanipulatingthedatawithinamulti-dimensionaldatabase.Formulaedefinerelationshipsamongmembers.FormulaeareusedbyOLAPdatabasebuilderstoprovidegreatrichnessofcontenttotheserverdatabase.Formulaeareusedbyenduserstomodelenterpriserelationshipsandtopersonalizethedataforgreatervisualizationandinsight.
FORMULA,CROSS-DIMENSIONAL
Formulaewithalloperandswithinadimensionarecommon,eveninnon-OLAPsystems:e.g.,Profit=Sales-Expensemightappearinasimplespreadsheetproduct.InanOLAPsystem,suchacalculationrulewouldnormallycalculateProfitforallcombinationsoftheotherdimensionsinthecube(e.g.,forallProducts,forallRegions,forallTimePeriods,etc.)usingtherespectiveRevenueandExpensedatafromthosesamedimensions.PartofthepowerofanOLAPsystemistheextensivemulti-dimensionalapplicationofsuchasimplystatedrule,whichcouldbespecifiedbytheOLAPapplicationbuilderorcreatedbytheenduserinaninteractivesession.ThetrueanalyticalpowerofanOLAPserver,however,isevidencedinitsabilitytoevaluateformulaewheretherearemembersfrommorethanonedimension.Anexampleisamulti-dimensionalallocationruleusedinbusinessunitprofitabilityapplications.If,forexample,acompanyhasaBusinessUnitdimensionandoneofthebusinessunits(XYZ)isfundingaspecialadvertisingcampaignforProductA,andtheotherbusinessunitswhichalsosellProductAarewillingtosharetheadvertisingcostsinproportiontotheirsalesoftheproduct,thentheformulawouldbe:
ADVERTISINGEXPENSE=(PRODUCTASALES/TOTALCORPORATIONPRODUCTASALES)*ADVERTISINGEXPENSEFORPRODUCTAFORBUSINESSUNITXYZ
Here,AdvertisingisfromtheMeasuresdimensionwhereveritintersectswithotherdimensions(e.g.,BusinessUnit,Product),butProductASalesismorespecific;itisSalesfromtheMeasuresdimensionrestrictedtotheProductAmemberfromtheProductdimension.TheAdvertisingExpensetobesharedistheAdvertisingExpenseforProductAspentbyBusinessUnitXYZthatthebusinessunitswhichhavenon-zerosalesofProductAagreedtoshare.ThesereferencestoseveraldimensionswithinthesamerulemakeitaCross-DimensionalFormula.

GENERATION,HIERARCHICAL
Twomembersofahierarchyhavethesamegenerationiftheyhavethesamenumberofancestorsleadingtothetop.Forexample,thetopmemberofadimensionisfromGeneration1.TheremaybetwoormoremembersinGeneration1iftherearemultiplehierarchiesinthedimension.
NOTE:Thetermsgenerationandlevelarebothnecessarytodescribesub-groupsofdimensionmembers,since,forexample,althoughtwosiblingssharethesameparentandarethereforeofthesamegeneration,theywontbefromthesamelevelifoneofthesiblingshasachildandtheotherdoesnt.
Synonyms:Peer,Sibling
See:Level,HierarchicalRelationships,Parent,Children
HIERARCHICALRELATIONSHIPS
Anydimensionsmembersmaybeorganizedbasedonparent-childrelationships,typicallywhereaparentmemberrepresentstheconsolidationofthememberswhichareitschildren.Theresultisahierarchy,andtheparent/childrelationshipsarehierarchicalrelationships.
HORIZONTALDIMENSION
See:PageDisplay
HYPERCUBE
See:Cube,Array,Multi-dimensional
INPUTMEMBERS
Inputmembershavevaluesthatareloadeddirectlyfromeithermanualentryorbyaccessinganothercomputer-baseddatasource,asopposedtobeingcalculatedfromtherawdata.
LEVEL,HIERARCHICAL
Membersofadimensionwithhierarchiesareatthesamelevelif,withintheirhierarchy,theyhavethesamemaximumnumberofdescendantsinanysinglepathbelow.Forexample,inanAccountsdimensionwhichconsistsofgeneralledgeraccounts,allofthedetailaccountsareLevel0members.TheaccountsonelevelhigherareLevel1,theirparentsareLevel2,etc.Itcanhappenthataparenthastwoormorechildrenwhicharedifferentlevels,inwhichcasetheparentslevelisdefinedasonehigherthanthelevelofthechildwiththehighestlevel.
See:Generation,Hierarchical
MEMBER,DIMENSION
Adimensionmemberisadiscretenameoridentifierusedtoidentifyadataitemspositionanddescriptionwithinadimension.Forexample,January1989or1Qtr93aretypicalexamplesofmembersofaTimedimension.Wholesale,Retail,etc.,aretypicalexamplesofmembersofaDistributionChanneldimension.
Synonyms:Position,Item,Attribute
MEMBERCOMBINATION
Amembercombinationisanexactdescriptionofauniquecellinamulti-dimensionalarray,consistingofaspecificmemberselectionineachdimensionofthearray.
See:Cell
MISSINGDATA,MISSINGVALUE
Aspecialdataitemwhichindicatesthatthedatainthiscelldoesnotexist.Thismaybebecausethemembercombinationisnotmeaningful(e.g.,snowmobilesmaynotbesoldinMiami)orhasneverbeenentered.MissingdataissimilartoanullvalueorN/A,butisnotthesameasazerovalue.
MULTI-DIMENSIONALDATASTRUCTURE
See:Array,Multi-dimensional
MULTI-DIMENSIONALQUERYLANGUAGE
Acomputerlanguagethatallowsonetospecifywhichdatatoretrieveoutofacube.Theuserprocessforthistypeofqueryisusuallycalledslicinganddicing.Theresultofamulti-dimensionalqueryiseitheracell,atwo-dimensionalslice,oramulti-dimensionalsub-cube.
NAVIGATION
Navigationisatermusedtodescribetheprocessesemployedbyuserstoexploreacubeinteractivelybydrilling,rotatingandscreening,usuallyusingagraphicalOLAPclientconnectedtoanOLAPserver.
NESTING(OFMULTI-DIMENSIONALCOLUMNSANDROWS)
Nestingisadisplaytechniqueusedtoshowtheresultsofamulti-dimensionalquerythatreturnsasub-cube,i.e.,morethanatwo-dimensionalsliceorpage.Thecolumn/rowlabelswilldisplaytheextradimensionalityoftheoutputbynestingthelabelsdescribingthemembersofeachdimension.Forexample,thedisplayscolumnsmaybe:
JanuaryFebruaryMarchActualBudgetActualBudgetActualBudgetProdAProdBProdAProdBProdAProdBProdAProdBProdAProdBProdAProdB
Thesecolumnscontainthreedimensions,nestedintheuserspreferredarrangement.Likewise,areportsrowsmaycontainnesteddimensions:
ChocolateBarsUnitSalesxxxxxxxxxxxxRevenuexxxxxxxxxxxxMarginxxxxxxxxxxxxFruitBarsUnitSalesxxxxxxxxxxxxRevenuexxxxxxxxxxxxMarginxxxxxxxxxxxx
NON-MISSINGDATA
Datawhichexistsandhasvalues,asopposedtonullormissingdata.
OLAPCLIENT
EnduserapplicationsthatcanrequestslicesfromOLAPserversandprovidetwo-dimensionalormulti-dimensionaldisplays,usermodifications,selections,ranking,calculations,etc.,forvisualizationandnavigationpurposes.OLAPclientsmaybeassimpleasaspreadsheetprogramretrievingasliceforfurtherworkbyaspreadsheet-literateuserorashigh-functionedasafinancialmodelingorsalesanalysisapplication.
PAGEDIMENSION
Apagedimensionisgenerallyusedtodescribeadimensionwhichisnotoneofthetwodimensionsofthepagebeingdisplayed,butforwhichamemberhasbeenselectedtodefinethespecificpagerequestedfordisplay.Allpagedimensionsmusthaveaspecificmemberchoseninordertodefinetheappropriatepagefordisplay.
PAGEDISPLAY
Thepagedisplayisthecurrentorientationforviewingamulti-dimensionalslice.Thehorizontaldimension(s)runacrossthedisplay,definingthecolumndimension(s).Theverticaldimension(s)rundownthedisplay,definingthecontentsoftherowdimension(s).Thepagedimension-memberselectionsdefinewhichpageiscurrentlydisplayed.Apageismuchlikeaspreadsheet,andmayinfacthavebeendeliveredtoaspreadsheetproductwhereeachcellcanbefurthermodifiedbytheuser.
PARENT
Thememberthatisonelevelupinahierarchyfromanothermember.Theparentvalueisusuallyaconsolidationofallofitschildrensvalues.
See:Children
PIVOT
See:Rotate
PRE-CALCULATED/PRE-CONSOLIDATEDDATA
Pre-calculateddataisdatainoutputmembercellsthatarecomputedpriorto,andinanticipationof,ad-hocrequests.Pre-calculationusuallyresultsinfasterresponsetoqueriesattheexpenseofstorage.Datathatisnotpre-calculatedmustbecalculatedatquerytime.
See:DerivedData/Members,OutputData
REACHTHROUGH
ReachthroughisameansofextendingthedataaccessibletotheenduserbeyondthatwhichisstoredintheOLAPserver.AreachthroughisperformedwhentheOLAPserverrecognizesthatitneedsadditionaldataandautomaticallyqueriesandretrievesthedatafromadatawarehouseorOLTPsystem.
ROLL-UP
See:Consolidate
ROTATE
Tochangethedimensionalorientationofareportorpagedisplay.Forexample,rotatingmayconsistofswappingtherowsandcolumns,ormovingoneoftherowdimensionsintothecolumndimension,orswappinganoff-spreadsheetdimensionwithoneofthedimensionsinthepagedisplay(eithertobecomeoneofthenewrowsorcolumns),etc.AspecificexampleofthefirstcasewouldbetakingareportthathasTimeacross(thecolumns)andProductsdown(therows)androtatingitintoareportthathasProductacrossandTimedown.AnexampleofthesecondcasewouldbetochangeareportwhichhasMeasuresandProductsdownandTimeacrossintoareportwithMeasuresdownandTimeoverProductsacross.AnexampleofthethirdcasewouldbetakingareportthathasTimeacrossandProductdownandchangingitintoareportthathasTimeacrossandGeographydown.
Synonym:Pivot
ROWDIMENSION

See:PageDisplay
SCOPING
Restrictingtheviewofdatabaseobjectstoaspecifiedsubset.Furtheroperations,suchasupdateorretrieve,willaffectonlythecellsinthespecifiedsubset.Forexample,scopingallowsuserstoretrieveorupdateonlythesalesdatavaluesforthefirstquarterintheeastregion,ifthatistheonlydatatheywishtoreceive.
SELECTION
Aselectionisaprocesswherebyacriterionisevaluatedagainstthedataormembersofadimensioninordertorestrictthesetofdataretrieved.Examplesofselectionsincludethetoptensalespersonsbyrevenue,datafromtheeastregiononlyandallproductswithmarginsgreaterthan20percent.
Synonyms:Condition,Screen,Filter
SLICE
Asliceisasubsetofamulti-dimensionalarraycorrespondingtoasinglevalueforoneormoremembersofthedimensionsnotinthesubset.Forexample,ifthememberActualsisselectedfromtheScenariodimension,thenthesub-cubeofalltheremainingdimensionsistheslicethatisspecified.Thedataomittedfromthisslicewouldbeanydataassociatedwiththenon-selectedmembersoftheScenariodimension,forexampleBudget,Variance,Forecast,etc.Fromanenduserperspective,thetermslicemostoftenreferstoatwo-dimensionalpageselectedfromthecube.
SLICEANDDICE
Theuser-initiatedprocessofnavigatingbycallingforpagedisplaysinteractively,throughthespecificationofslicesviarotationsanddrilldown/up.
SPARSE
Amulti-dimensionaldatasetissparseifarelativelyhighpercentageofthepossiblecombinations(intersections)ofthemembersfromthedatasetsdimensionscontainmissingdata.Thetotalpossiblenumberofintersectionscanbecomputedbymultiplyingtogetherthenumberofmembersineachdimension.Datasetscontainingonepercent,.01percent,orevensmallerpercentagesofthepossibledataexistandarequitecommon.
See:Dense
VERTICALDIMENSIONMysql的存储引擎接口定义良好。有兴趣的开发者可以通过阅读文档编写自己的存储引擎。

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.

所以你总能得到相应的升级版本，来满足你的需求。

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。