Oracle數(shù)據(jù)倉庫解決方案

OracleDataWarehouseSolutionOracle(China)PresalesConsultantXuXin數(shù)據(jù)倉庫幾大功能Query/ReportDrillup/DrillDownCompareExceptionForcast,WhatifDataMining數(shù)據(jù)倉庫三大流程數(shù)據(jù)建模DataModelDesign數(shù)據(jù)抽取

DataExtract,Transform,Transport數(shù)據(jù)表現(xiàn)Rolap,Molap

DataDelivery,Drillup/Drilldown,DataRotation,DataMiningROLAP/MOLAP-爭論的焦點(diǎn)在哪里定義:ROLAP=基于關(guān)系的OLAPMOLAP=基于多維的OLAPROLAP供應(yīng)商說:你能用關(guān)系技術(shù)做所有的事情?MOLAPROLAP供應(yīng)商說:你用多維數(shù)據(jù)庫能做得更好ROLAP供應(yīng)商僅僅能做ROLAP....MOLAP供應(yīng)商僅僅能做MOLAP....ORACLE能提供ROLAP和MOLAPROLAP/MOLAP-爭論的焦點(diǎn)在哪里ROLAP和MOLAP-Oracle的解決方案OracleROLAPDataMartSuite--建模，數(shù)據(jù)抽取的工具Discoverer--基于關(guān)系數(shù)據(jù)庫的分析工具多維數(shù)據(jù)的視圖,挖掘和旋轉(zhuǎn)OracleMOLAPExpress系列產(chǎn)品提供MOLAP的方案多維數(shù)據(jù)的視圖,挖掘和旋轉(zhuǎn),計(jì)算和存取、預(yù)測Oracle能提供ROLAP和MOLAPExpress系列產(chǎn)品能做MOLAP和ROLAPOFA,OSAApplicationsExpressPowerAnalysisDiscovererQuery,Reporting,AnalysisDecisionsComponentsofaDataWarehouse/MartOLTPOLTPExternalDataOperationsData

StorageDataMartSuiteAddValueToTheOracleWarehouseDataMartSuite2.0-ComponentsDashing-DataMartDesigner2.1Better-DataMartBuilder2.0Debonair-Discoverer3.1Ravishing-ReportsServer3.0Compelling-CookbookDesignandManagementAnyDataAnyAccessAnySourceMetadata

ConstructionMethodology

Drag&dropvisualmetaphorData

ModelingIdentifydatasourcesIdentifysourcesubsetModelStarSchemaProcess

ModelingBuildPlansDimensiontablesTimeDimensionFacttablePopulatedatabaseBusinessModelingDefineend-userlayerOracle8iforDataWarehousingContinuousInnovationOracle7.3HashJoinBitmapIndexesParallel-AwareOptimizerPartitionViewsInstanceAffinity:FunctionShippingParallelUnionAllAsynchronousRead-AheadHistogramsAnti-JoinPartitionedTablesandIndexesPartitionPruningParallelIndexScansParallelInsert,Update,DeleteParallelBitmapStarQueryParallelANALYZEParallelConstraintEnablingServerManagedBackup/RecoveryPoint-in-TimeRecoveryOracle8.0SummaryManagementNewPartitioningSchemesResourceManagerProgressMonitorAdaptiveParallelQueryServer-basedAnalyticFunctionsTransportableTablespacesDirectLoaderAPIFunctionalIndexesPartition-wiseJoinsSecurityEnhancementsandmore...Oracle8iDataModelingOracleDataMartDesignerTableorders(constraintc_o_datecheck(O_entry_dbetweento_date(?1-jan-1997?慸慸d-mon-yyyy?andto_date(?1-dec-1997?慸d-mon-yyyy?)disable,o_idnumber,o_d_idnumber,o_w_idnumber,o_c_idnumber,o_entry_ddate,o_carrier_idnumber,o_ol_cntnumber,o_all_localnumber)tablespaceordinitrans3pctfree5storage(initial20Knext110Mpctincrease0freelistgroups19freelists9);Source:OLTPProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFactTableTarget:DataMartProcessModelingOracleDataMartBuilderOrders-ExtractSQLQueryProductProduct_LineProduct_GroupShipShipperContactCus_LocOrdersContact_LocProduct

LookupMarket

LookupPromotionLookupTime

LookupDirectPathLoader

Oracle

Data

Mart

BuilderMetadataIntegrationProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFactTableOrders-ExtractSQLQueryProduct

LookupMarket

LookupPromotionLookupTime

LookupDirectPathLoader

OracleDataMartBuilderOracleDiscovererOracleDataMartDesignerMetadata決策支持系系統(tǒng)的范圍圍IT人員為用戶開發(fā)業(yè)務(wù)用戶獨(dú)立分析專業(yè)分析人員標(biāo)準(zhǔn)報(bào)表即席查詢分析復(fù)雜分析Oracle決策策支持工具具集標(biāo)準(zhǔn)報(bào)表即席查詢分析復(fù)雜分析上季度各地區(qū)銷售情況如何？什么原因造成東南亞地區(qū)的業(yè)務(wù)下降?如果同樣的因素影響到其它地區(qū)，將會對全球的業(yè)務(wù)造成什么影響?OracleExpressOracleDiscovererOracleReportsOracle決策策支持工具具集標(biāo)準(zhǔn)報(bào)表-OracleReports集中式報(bào)表表生成存儲儲輸出發(fā)布高高質(zhì)量的報(bào)報(bào)表即席查詢分分析-Discoverer即席查詢,數(shù)據(jù)鉆鉆入，旋轉(zhuǎn)轉(zhuǎn)業(yè)界領(lǐng)先的的易用性和和性能復(fù)雜分析-ExpressOn-lineAnalyticalProcessing(OLAP)高級計(jì)算財(cái)務(wù),時(shí)時(shí)間序列,建模,預(yù)測,回歸分分析，假設(shè)設(shè)分析OracleReportsWhatisit?FormatsanddistributesdatabaseinformationKeyadvantagesUnlimiteddataformattingDistributeshighfidelityreportsviatheWebApplicationserverbasedreportingengineOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAnalysisOracleDiscoverAddValueToTheOracleWarehouseOracleDiscovererWhatisit:AdhocqueryandanalysisofrelationaldataKeyadvantages:Award-winningeaseofuseIndustry-leadingqueryperformanceOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAnalysisDiscovereris:10timesfasterthanCognos9timesfasterthanBusinessObjectsMarket-LeadingPerformanceTimetoquery100,000rows:CognosBusinessObjectsDiscovererOracleDiscoverer3.1UserEditionBusinessAbstractionLayerEndUserLayer(TM)DataWarehouse/DataMart(orOLTP)UserEditionAdministrationEditionOracleDiscoverer3.1UserEditionSuperiorEaseofUseEaseofUsePerformanceWarehouseExplorationExceptionalPerformanceNavigationalPower NavigationalPowerOpennessOpennessMostTestedUIontheMarketResizeable

WizardInterfaceIndustrySpecificQuickTourDramaticReductionoftheLearningCurveandmore...EaseofUseEndUserLayerEaseofUseHidestheComplexityoftheUnderlyingDatabaseDatabasecl_addr1||','||cl_addr2|','||cl_addr3||','||cl_twn||','||cl_zipcodesum(nvl(qty*unit_price,0))JonesSmithNameCustomerClientAddressRevenueEasternRegionUserBatchReportingOpennessToDo...SchedulelongrunningqueriesforlaterEaseofUse9:00Openworkbooks12:00Refreshworkbooks3:00ScheduleMonthlyreports5:00ScheduleQuarterlyreports5:30GeneratenewreportformanagementmeetingtomorrowSolutiontoLongRunningQueriesReactiveQueryGovernorStopsqueriesafterapre-settime

Usestheresourceswithoutshowingresults!PerformancePredictiveQueryGovernorPredictsthetimeaquerytakesbeforeexecutionDoesnotwastevaluableresources!ManagingLargeDataVolumesSelectLocation,Period,Product,sum(videos_lent)Fromvideo_detailsGroupbyLocation,Period,ProductDetailRowsPerformanceSlowTableScansMachineIntensiveAggregationsSummaryRedirectionSelectLocation,Period,Product,sum(videos_lent)Fromvideo_detailsGroupbyLocation,Period,ProductDetailRowsSummaryTablePerformanceSelectLocation,Period,Product,SalesFromvideo_summary Automatic!!SummaryRedirectionDetailRowsSummaryTablePerformanceServerClientNetworkResultsBase:CubicClientCachePerformanceEfficientStorageAlgorithmMultidimensionalaccessRapidrotation,drillRegionNorthParisProductYearDetailRowsExternalDataDrillEverywhereDrillDownDrillAcrossDrilltoDetailDrillOut NavigationalPowerWebPublishing NavigationalPowerIntegrationReportsExpressOpennessMAPIOracleDiscoverer3.1AdministrationEditionSuperiorEaseofUseEaseofUse SummaryManagementServerBasedAdministrationServerBasedAdministrationAdvancedSummaryManagementServerBasedAbstractionLayerServerBasedAdministrationEaseofUsePRODUCTIONEUL(RDBMS)Sales...Marketing匜inance匛ducation匔onsultancyUsersAdministratorRead/WriteReadOnlyEasytoCreate,MaintainandSetupScaleableLeverageSecurityOpen-ODBCDB2SybaseInformixSQLServerOracle?.ODBCEnhancementsOpennessOracleEndUserLayerEULGatewayDes/2000ETTToolsApplicationsQueryToolsBuildSummariesfromQueriesQueryperformancestatisticsSummarymanagementrecommendationsSummaryManagement38DefinitionsDataWarehouseAmulti-subjectinformationstoreDesignedspecificallyfordecisionsupportDataMartAsubjectorapplication-specificdatawarehouseTypes:Dependent,IndependentDWvs.DataMartWarehouse DataMartScope CorporateLine-of-BusinessSubjectsmulti-subject subject-specificDataSourcesmany fewSize100GB-1TB+ <100GBImplementation months-years monthsWarehouseDataMartOracleExpressOracleWarehouseOracleExpressWhatisit:AdvancedanalysisofmultidimensionalandrelationaldataKeyadvantages:BroadestrangeofanalyticalfunctionsScaleableperformanceIntegrateddevelopmentenvironmentOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAnalysisOracleExpressKeyDifferentiatorsAdvancedanalysisBuilt-inforecastingMulti-userwhat-ifAutomaticsimultaneousmodelsProgrammableserverScaleableperformanceAnalysisofexistingrelationaldatabasesMultiplecubes,hierarchiesperschemaIntegrateddevelopmentenvironmentBroadestrangeofcapabilities可擴(kuò)擴(kuò)展展的的多多維維數(shù)數(shù)據(jù)據(jù)庫庫服服務(wù)務(wù)器器,內(nèi)內(nèi)置置強(qiáng)強(qiáng)大大的的分分析析功能能和和決決策策支支持持能能力力PersonalExpress,ExpressServer用于于銷銷售售分分析析,市市場場分分析析,財(cái)財(cái)務(wù)務(wù)分分析析的的OLAP應(yīng)應(yīng)用用產(chǎn)產(chǎn)品品SalesAnalyzer,FinancialAnalyzer面向向?qū)ο笙蟮牡腛LAP應(yīng)應(yīng)用用開開發(fā)發(fā)工工具具ExpressAnalyzer,ExpressObjectsExpressServerExpressApplicationsExpressToolsOracleOLAP-ExpressProductsExpress一個(gè)個(gè)專專門門為為查查詢詢和和分分析析商商業(yè)業(yè)情情報(bào)報(bào)數(shù)數(shù)據(jù)據(jù)而而設(shè)設(shè)計(jì)計(jì)的的服服務(wù)務(wù)器器Express?對于于一一些些分分析析查查詢詢和和計(jì)計(jì)算算,Express比比關(guān)關(guān)系系型型技技術(shù)術(shù)快快10倍倍，，100倍倍，，甚甚至至1000倍倍JerryHeldSeniorVP,ServerTechnologies,OracleCorporation查詢詢速速度度如如此此快快，，使使Express具具有有很很強(qiáng)強(qiáng)的的競競爭爭能能力力.多維維數(shù)數(shù)據(jù)據(jù)模模型型轄區(qū)區(qū)經(jīng)經(jīng)理理/行行長長財(cái)務(wù)務(wù)經(jīng)經(jīng)理理任意意分分析析PRODTIME資金金經(jīng)經(jīng)理理為數(shù)據(jù)提供快快速，靈活的的訪問方法金融產(chǎn)品時(shí)間頭寸轄區(qū)對時(shí)間，日期期的智能處理理，系統(tǒng)相關(guān)數(shù)據(jù)據(jù)自行匯總。。根據(jù)數(shù)據(jù)建立立層次關(guān)系數(shù)據(jù)自行匯總總在維上建立層層次關(guān)系KEYS相同的數(shù)學(xué)模模型華東區(qū)域華中月份產(chǎn)品西南Jan膠卷鏡片攝像機(jī)銷售量DIMENSIONSFebMar多維數(shù)據(jù)庫服服務(wù)器的特性性立方體，頁，，片的數(shù)據(jù)維=主鍵鍵只存放一次，，重復(fù)使用強(qiáng)制性保證數(shù)數(shù)據(jù)完整性節(jié)省磁盤空間間極容易地結(jié)合合數(shù)據(jù)和公式式支持共享的商商業(yè)邏輯保證數(shù)據(jù)的一一致性每一個(gè)單元都都被索引因此有很高的的存取效率TimeproductGeography數(shù)據(jù)存放PhiladelphiaNewYorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales數(shù)據(jù)頁數(shù)據(jù)存取SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales關(guān)系數(shù)據(jù)庫多多維維數(shù)據(jù)庫CostCostCostCostCostCostCostCostCostCostCostCostCostCostCostCostSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProd只要讀取一頁頁要讀取兩頁數(shù)據(jù)存取方法法DistrictQuarterProductNewYorkBostonPhiladelphiaQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear偏移地址存取取更強(qiáng)有力的分分析能力更好的執(zhí)行效效率很多的已建好好的函數(shù)FinancialFunctionsDepreciation折折舊率GrowthRate增增長率NetPresentValue現(xiàn)值InternalRateofReturnNumeric/TimeSeriesFunctionsAverage平平均值CumulativeSums累累積和Lag/Lead落落后/領(lǐng)先先Variance變變動率MovingAverage/Total移移動動平均值Smallest/Largest最最大/最最小StandardDeviation水水平偏差Total總總和OtherFunctionsForecastingRegression...你你也能創(chuàng)創(chuàng)建你自己的的函數(shù)ExpressLanguageApplicationProgrammingInterfacesBasicOperationsCharacterManipulationFunctionsCommunicationsCountingandTestingFunctionsComputerResourceManagementDataManipulationDictionaryOperationsDimensionInformationFinancialFunctionsForecastingandRegressionMiscellaneousInformationandOptionsModelsNumericFunctionsandCommandsNumericOptionsPagingOptionsProgramsSecurityandScopingSystemCommandsandInformationUsingExternalDataWorkingwithWindows持續(xù)的查詢過過程查詢示例：從費(fèi)城的最近近一個(gè)季度的的產(chǎn)品中選選擇銷售額大大于150K的產(chǎn)品，，并且從蠖蠖鈄?大的產(chǎn)產(chǎn)品。費(fèi)城季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運(yùn)動鞋運(yùn)動衣滑板手套球拍地區(qū)持續(xù)的查詢過過程查詢示例：限制季度到Q4季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運(yùn)動鞋運(yùn)動衣滑板手套球拍地區(qū)費(fèi)城持續(xù)的查詢過過程查詢示例：限制季度到到Q4限制地區(qū)到費(fèi)城季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運(yùn)動鞋運(yùn)動衣滑板手套球拍地區(qū)費(fèi)城持續(xù)的查詢過過程查詢示例：限制季度到到Q4限制地區(qū)到費(fèi)城限制產(chǎn)品的銷售額額大于150,000季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運(yùn)動鞋運(yùn)動衣滑板手套球拍地區(qū)費(fèi)城持續(xù)的查詢過過程查詢示例：限制季度到到Q4限制地區(qū)到費(fèi)城限制產(chǎn)品的的銷售額大大于150,000限制產(chǎn)品的利潤額額前兩位季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運(yùn)動鞋運(yùn)動衣滑板手套球拍地區(qū)費(fèi)城許多實(shí)體/公用維BUDGETTimeMarketProductTimeFORECASTScenarioMarketPRICETimeProductTimeSALESMarketProductProduct派生的實(shí)體存放結(jié)果在變變量中或者由一個(gè)計(jì)計(jì)算公式來決決定x=價(jià)格銷售數(shù)量銷售金額派生的實(shí)體存放結(jié)果在變變量中或者由一個(gè)計(jì)計(jì)算公式來決決定這些計(jì)算公式式可由其它公公式得來....x=價(jià)格銷售數(shù)量銷售金額多個(gè)邏輯的最最終用戶視圖圖產(chǎn)品經(jīng)理視圖圖地區(qū)經(jīng)理視圖圖財(cái)務(wù)經(jīng)理視圖圖總經(jīng)理視圖TimeQ1Q2Q3Q4ProductTentsCanoesRacquetsSportswearFootwearPhiladelphiaNewYorkBostonDistrict多維分析舉例例查詢上季度在在費(fèi)城地區(qū)，，營利最多多的2種產(chǎn)品品。這些產(chǎn)品品的銷售額必必須大于15萬元。Philadelphia時(shí)間產(chǎn)品NewYorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear地區(qū)多維數(shù)據(jù)分析析在過去半年內(nèi)內(nèi)，華北地區(qū)區(qū)實(shí)際銷售額額與預(yù)算相比比較如何。公司產(chǎn)品在東