Oracle数据仓库解决方案

OracleDataWarehouseSolutionOracle(China)PresalesConsultantXuXin数据仓库几大功能Query/ReportDrillup/DrillDownCompareExceptionForcast,WhatifDataMining数据仓库三大流程数据建模DataModelDesign数据抽取

DataExtract,Transform,Transport数据表现Rolap,Molap

DataDelivery,Drillup/Drilldown,DataRotation,DataMiningROLAP/MOLAP-争论的焦点在哪里定义:ROLAP=基于关系的OLAPMOLAP=基于多维的OLAPROLAP供应商说:你能用关系技术做所有的事情?MOLAPROLAP供应商说:你用多维数据库能做得更好ROLAP供应商仅仅能做ROLAP....MOLAP供应商仅仅能做MOLAP....ORACLE能提供ROLAP和MOLAPROLAP/MOLAP-争论的焦点在哪里ROLAP和MOLAP-Oracle的解决方案OracleROLAPDataMartSuite--建模，数据抽取的工具Discoverer--基于关系数据库的分析工具多维数据的视图,挖掘和旋转OracleMOLAPExpress系列产品提供MOLAP的方案多维数据的视图,挖掘和旋转,计算和存取、预测Oracle能提供ROLAP和MOLAPExpress系列产品能做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决策支持系系统的范围围IT人员为用户开发业务用户独立分析专业分析人员标准报表即席查询分析复杂分析Oracle决策策支持工具具集标准报表即席查询分析复杂分析上季度各地区销售情况如何？什么原因造成东南亚地区的业务下降?如果同样的因素影响到其它地区，将会对全球的业务造成什么影响?OracleExpressOracleDiscovererOracleReportsOracle决策策支持工具具集标准报表-OracleReports集中式报表表生成存储储输出发布高高质量的报报表即席查询分分析-Discoverer即席查询,数据钻钻入，旋转转业界领先的的易用性和和性能复杂分析-ExpressOn-lineAnalyticalProcessing(OLAP)高级计算财务,时时间序列,建模,预测,回归分分析，假设设分析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可扩扩展展的的多多维维数数据据库库服服务务器器,内内置置强强大大的的分分析析功能能和和决决策策支支持持能能力力PersonalExpress,ExpressServer用于于销销售售分分析析,市市场场分分析析,财财务务分分析析的的OLAP应应用用产产品品SalesAnalyzer,FinancialAnalyzer面向向对对象象的的OLAP应应用用开开发发工工具具ExpressAnalyzer,ExpressObjectsExpressServerExpressApplicationsExpressToolsOracleOLAP-ExpressProductsExpress一个个专专门门为为查查询询和和分分析析商商业业情情报报数数据据而而设设计计的的服服务务器器Express?对于于一一些些分分析析查查询询和和计计算算,Express比比关关系系型型技技术术快快10倍倍，，100倍倍，，甚甚至至1000倍倍JerryHeldSeniorVP,ServerTechnologies,OracleCorporation查询询速速度度如如此此快快，，使使Express具具有有很很强强的的竞竞争争能能力力.多维维数数据据模模型型辖区区经经理理/行行长长财务务经经理理任意意分分析析PRODTIME资金金经经理理为数据提供快快速，灵活的的访问方法金融产品时间头寸辖区对时间，日期期的智能处理理，系统相关数据据自行汇总。。根据数据建立立层次关系数据自行汇总总在维上建立层层次关系KEYS相同的数学模模型华东区域华中月份产品西南Jan胶卷镜片摄像机销售量DIMENSIONSFebMar多维数据库服服务器的特性性立方体，页，，片的数据维=主键键只存放一次，，重复使用强制性保证数数据完整性节省磁盘空间间极容易地结合合数据和公式式支持共享的商商业逻辑保证数据的一一致性每一个单元都都被索引因此有很高的的存取效率TimeproductGeography数据存放PhiladelphiaNewYorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales数据页数据存取SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales关系数据库多多维维数据库CostCostCostCostCostCostCostCostCostCostCostCostCostCostCostCostSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProd只要读取一页页要读取两页数据存取方法法DistrictQuarterProductNewYorkBostonPhiladelphiaQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear偏移地址存取取更强有力的分分析能力更好的执行效效率很多的已建好好的函数FinancialFunctionsDepreciation折折旧率GrowthRate增增长率NetPresentValue现值InternalRateofReturnNumeric/TimeSeriesFunctionsAverage平平均值CumulativeSums累累积和Lag/Lead落落后/领先先Variance变变动率MovingAverage/Total移移动动平均值Smallest/Largest最最大/最最小StandardDeviation水水平偏差Total总总和OtherFunctionsForecastingRegression...你你也能创创建你自己的的函数ExpressLanguageApplicationProgrammingInterfacesBasicOperationsCharacterManipulationFunctionsCommunicationsCountingandTestingFunctionsComputerResourceManagementDataManipulationDictionaryOperationsDimensionInformationFinancialFunctionsForecastingandRegressionMiscellaneousInformationandOptionsModelsNumericFunctionsandCommandsNumericOptionsPagingOptionsProgramsSecurityandScopingSystemCommandsandInformationUsingExternalDataWorkingwithWindows持续的查询过过程查询示例：从费城的最近近一个季度的的产品中选选择销售额大大于150K的产品，，并且从蠖蠖钭?大的产产品。费城季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区持续的查询过过程查询示例：限制季度到Q4季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城持续的查询过过程查询示例：限制季度到到Q4限制地区到费城季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城持续的查询过过程查询示例：限制季度到到Q4限制地区到费城限制产品的销售额额大于150,000季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城持续的查询过过程查询示例：限制季度到到Q4限制地区到费城限制产品的的销售额大大于150,000限制产品的利润额额前两位季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城许多实体/公用维BUDGETTimeMarketProductTimeFORECASTScenarioMarketPRICETimeProductTimeSALESMarketProductProduct派生的实体存放结果在变变量中或者由一个计计算公式来决决定x=价格销售数量销售金额派生的实体存放结果在变变量中或者由一个计计算公式来决决定这些计算公式式可由其它公公式得来....x=价格销售数量销售金额多个逻辑的最最终用户视图图产品经理视图图地区经理视图图财务经理视图图总经理视图TimeQ1Q2Q3Q4ProductTentsCanoesRacquetsSportswearFootwearPhiladelphiaNewYorkBostonDistrict多维分析举例例查询上季度在在费城地区，，营利最多多的2种产品品。这些产品品的销售额必必须大于15万元。Philadelphia时间产品NewYorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear地区多维数据分析析在过去半年内内，华北地区区实际销售额额与预算相比比较如何。公司产品在东