商业智能系列讲座(4) - OLAP 高级

BIBI11BusinessIntelligenceBusinessIntelligenceOLAPOLAPOLAPOLAP––ActionsActions––NamedSetsNamedSets––DrilltoDetailDrilltoDetail––CalculatedCellsCalculatedCellsBIBI33HTMLHTMLActionActionBIBI44NamedSetsNamedSetsNamedSets=NamedSets=––Alistofthetop10productsAlistofthetop10products––ThisyearThisyear’’smostatsmostat--riskcustomersriskcustomers––(({A,B,C}){A,B,C})––((1010))BIBI55BIBI66––––BIBI77CalculatedCellsCalculatedCellsCalculatedMembersCalculatedMembers––CreatenewmeasuresCreatenewmeasuresCalculatedCellsCalculatedCells––unlikecalculatedmembers,areusedtochangetheunlikecalculatedmembers,areusedtochangethevalueofvalueof““realreal””cubecells.Aswithcalculatedmembers,cubecells.Aswithcalculatedmembers,thecalculationisperformedatruntimethecalculationisperformedatruntime----onlywhenonlywhencellsarequeriedaretheformulasresolved.Forcellsarequeriedaretheformulasresolved.Fordatabasesrequiringcomplexcalculationlogic,databasesrequiringcomplexcalculationlogic,calculatedcellsareaninvaluable,ifnotrequisite,partcalculatedcellsareaninvaluable,ifnotrequisite,partoftheequation.oftheequation.OLAPOLAPCubeCubeCubeCubeT3T3BIBI99FactTableFactTableHighestlevelofaggregation(1,1,1,1,Highestlevelofaggregation(1,1,1,1,……))MostdetailedMostdetailedAggregationsAggregations((m,m,mm,m,m,,……))Showmeallsalesforallproductsforall...Showmeallsalesforallproductsforall...AggregationsAggregationsBIBI1010CubeCubeMOLAPMOLAPOLAPOLAPROLAPROLAPOLAPOLAPHOLAPHOLAPOLAPOLAPBIBI1111MDMDRDBRDBMDMDRDBRDBRDBRDBMDMDCubeCubeRDBRDBHOLAPHOLAPROLAPROLAPMOLAPMOLAPBIBI1212TT33ProjectGoalsProjectGoalsDemonstratethescalabilityofAnalysisServicesDemonstratethescalabilityofAnalysisServices––Buildcubefrom1TB+ofsourcedataBuildcubefrom1TB+ofsourcedata––DescribethetechniquesusedtooperateatthatscaleDescribethetechniquesusedtooperateatthatscaleUsethecubetodemonstratehighspeedqueriesUsethecubetodemonstratehighspeedqueriesProofProof--ofof--conceptsystemconceptsystemAddressrealbusinessissues:Addressrealbusinessissues:schema,data,goalsschema,data,goalsBIBI1313HTTPWebBrowserWebServerOLEDBforOLAPTT33DataFlowDataFlowMOLAPCubeOLEDBTerminalServerRDPPCClientOLEDBforOLAPDataWarehouseTapesfromdataproviderBIBI1414UnisysES7000e-@ctionEnterpriseServersT3HardwareConfigurationOLAPServer16CPUDataWarehouse8CPUWebServer8CPUTerminalServer4CPUcLANBackboneInternetClientSystemsEMC2EnterpriseStorage3Symmetrix3830-36EnterpriseStorageNetworkEMCConnectrix3.4TB3.4TB3.4TBEMCControlCenterEDMBackupServerOLAPOLAPMM--NNBIBI1616vs.4vs.4--44--55Time.[445]Time.StandardBIBI1717––ParentDimension=MeasureParentDimension=Measure––ParentDimension=ParentDimension=YTDYTD––YTD?YTD?BIBI1818––––––––––BIBI1919MM--NNCubeCube––NN--11NN--11MM--NNOLAPOLAPOLAPOLAP––ArchitectureReviewArchitectureReview––AggregationDesignAggregationDesign––DataandDimensionStorageDataandDimensionStorage––ProcessingProcessing––QueryingQuerying––ServerMemoryManagementServerMemoryManagementBIBI2121ArchitectureArchitecture––SingleServerSingleServerOLAPOLAPStoreStoreApplicationApplicationADOMDADOMDPivotTableServicePivotTableServiceOLEDBforOLAPOLEDBforOLAPAnalysisAnalysisServerServerProcessingProcessingQueryingQueryingAnalysisAnalysisManagerManagerDSODSOSQLServerSQLServerDataDataWarehouseWarehouseOtherOtherOLEDBOLEDBProvidersProvidersOLEDBOLEDBBIBI2222FactTableFactTableShowmeallsalesforallproductsforall...Showmeallsalesforallproductsforall...MostdetailedMostdetailedAggregationsAggregationsHighestlevelofaggregationHighestlevelofaggregationPartialAggregationPartialAggregation