Oracle数据仓库解决方案

1、Oracle Data Warehouse SolutionOracle ( China )Presales ConsultantXu Xin数据仓库几大功能Query/ReportDrill up/ Drill DownCompareExceptionForcast, WhatifData Mining数据仓库三大流程数据建模 Data Model Design数据抽取 Data Extract, Transform, Transport数据表现 Rolap, Molap Data Delivery, Drill up/Drill down, Data Rotation, Data Mini

2、ngROLAP / MOLAP - 争论的焦点在哪里定义 :ROLAP = 基于关系的 OLAPMOLAP = 基于多维的 OLAPROLAP 供应商说 : 你能用关系技术做所有的事情?MOLAP ROLAP 供应商说 : 你用多维数据库能做得更好ROLAP 供应商仅仅能做ROLAP. MOLAP 供应商仅仅能做MOLAP. ORACLE 能提供 ROLAP和MOLAPROLAP / MOLAP - 争论的焦点在哪里ROLAP和MOLAP - Oracle的解决方案Oracle ROLAPData Mart Suite - 建模，数据抽取的工具Discoverer - 基于关系数据库的分析工具

3、多维数据的视图, 挖掘和旋转Oracle MOLAPExpress 系列产品提供 MOLAP的方案多维数据的视图, 挖掘和旋转, 计算和存取、预测Oracle 能提供 ROLAP 和 MOLAPExpress 系列产品能做 MOLAP 和 ROLAPOFA, OSAApplicationsExpressPower Analysis DiscovererQuery, Reporting,AnalysisDecisionsComponents of a Data Warehouse / MartOLTPOLTPExternal DataOperationsDataStorageData Mart

4、SuiteAdd Value To The Oracle WarehouseData Mart Suite 2.0 - ComponentsGreat8 - Oracle8, release 8.0.4Dashing - Data Mart Designer 2.1Better - Data Mart Builder 2.0Debonair - Discoverer 3.1Awesome - Web Application Server 3.0.1Ravishing - Reports Server 3.0Compelling - CookbookDesign and ManagementAn

5、y DataAny AccessAny SourceMetadataConstruction Methodology Drag & drop visual metaphorData ModelingIdentify data sourcesIdentify source subsetModel Star SchemaProcess ModelingBuild PlansDimension tablesTime DimensionFact tablePopulate databaseBusiness ModelingDefine end-user layerOracle8i for Data W

6、arehousing Continuous InnovationOracle 7.3Hash JoinBitmap IndexesParallel-Aware OptimizerPartition ViewsInstance Affinity: Function ShippingParallel Union AllAsynchronous Read-AheadHistogramsAnti-JoinPartitioned Tables and IndexesPartition PruningParallel Index ScansParallel Insert, Update, DeletePa

7、rallel Bitmap Star QueryParallel ANALYZEParallel Constraint EnablingServer Managed Backup/RecoveryPoint-in-Time RecoveryOracle 8.0Summary ManagementNew Partitioning SchemesResource ManagerProgress MonitorAdaptive Parallel QueryServer-based Analytic FunctionsTransportable TablespacesDirect Loader API

8、Functional IndexesPartition-wise JoinsSecurity Enhancementsand more .Oracle8iData ModelingOracle Data Mart DesignerTable orders (constraint c_o_date check (O_entry_d between to_date (?1-jan-1997? 慸d-mon-yyyy?and to_date (?1-dec-1997? 慸d-mon-yyyy?) disable,o_idnumber,o_d_idnumber,o_w_idnumber,o_c_idn

9、umber,o_entry_ddate,o_carrier_idnumber,o_ol_cntnumber,o_all_localnumber)tablespace ord initrans 3 pctfree 5 storage (initial 20K next 110M pctincrease 0 freelist groups 19 freelists 9);Source: OLTPProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregion

10、demographicsTimedescriptionweekdayholidayfiscalFact TableTarget: Data MartProcess Modeling Oracle Data Mart BuilderOrders - ExtractSQL QueryProductProduct_LineProduct_GroupShipShipperContactCus_ LocOrdersContact_LocProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracle Data Mart B

11、uilderMetadata IntegrationProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFact TableOrders - ExtractSQL QueryProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracle Data Mart Build

12、erOracleDiscovererOracle Data Mart DesignerMetadata决策支持系统的范围IT 人员为用户开发业务用户独立分析专业分析人员标准报表即席查询分析复杂分析Oracle 决策支持工具集标准报表即席查询分析复杂分析上季度各地区销售 情况如何？什么原因造成东南亚 地区的业务下降?如果同样的因素影响到 其它地区，将会对全球的 业务造成什么影响?OracleExpressOracle DiscovererOracleReportsOracle 决策支持工具集标准报表 - Oracle Reports集中式报表生成存储输出发布高质量的报表 即席查询分析 - Dis

13、coverer即席查询, 数据钻入，旋转业界领先的易用性和性能复杂分析 - Express On-line Analytical Processing (OLAP)高级计算 财务, 时间序列, 建模, 预测, 回归分析，假设分析Oracle ReportsWhat is it? Formats and distributes database informationKey advantagesUnlimited data formattingDistributes high fidelity reports via the WebApplication server based reporti

14、ng engineOracleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query, AnalysisAdvanced AnalysisOracle DiscoverAdd Value To The Oracle WarehouseOracle DiscovererWhat is it: Ad hoc query and analysis of relational dataKey advantages:Award-winning ease of useIndustry-leading query perfor

15、mance OracleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query, AnalysisAdvanced AnalysisDiscoverer is:10 times faster than Cognos9 times faster than Business ObjectsMarket-Leading PerformanceTime to query 100,000 rows:CognosBusiness ObjectsDiscovererOracle Discoverer 3.1User Editi

16、onBusiness Abstraction Layer End User Layer(TM) Data Warehouse / Data Mart (or OLTP)User EditionAdministrationEditionOracle Discoverer 3.1User EditionSuperior Ease of UseEase of UsePerformanceWarehouse ExplorationExceptional PerformanceNavigational PowerNavigational PowerOpennessOpenness Most Tested

17、 UI on the MarketResizeableWizard InterfaceIndustry Specific Quick TourDramatic Reduction of the Learning Curveand more.Ease of UseEnd User LayerEase of UseHides the Complexity of the Underlying DatabaseDatabasecl_addr1|,|cl_addr2|,|cl_addr3|, |cl_twn|, |cl_zipcodesum(nvl(qty*unit_price,0)Jones Smit

18、h Name Customer Client Address Revenue Eastern Region UserBatch ReportingOpenness To Do.Schedule long running queries for laterEase of Use 9:00 Open workbooks 12 :00 Refresh workbooks 3:00 Schedule Monthly reports 5:00 Schedule Quarterly reports 5:30 Generate new report for management meeting tomorr

19、ow Solution to Long Running QueriesReactive Query GovernorStops queries after a pre-set timeUses the resources without showing results!PerformancePredictive Query GovernorPredicts the time a query takes before executionDoes not waste valuable resources!Managing Large Data VolumesSelect Location, Per

20、iod, Product, sum(videos_lent)From video_detailsGroup by Location, Period, ProductDetail RowsPerformanceSlow Table ScansMachine Intensive AggregationsSummary RedirectionSelect Location, Period, Product, sum(videos_lent)From video_detailsGroup by Location, Period, ProductDetail RowsSummary TablePerfo

21、rmanceSelect Location, Period, Product, SalesFrom video_summaryAutomatic!Summary RedirectionDetail RowsSummary TablePerformanceServerClientNetworkResultsBase: Cubic Client CachePerformanceEfficient Storage AlgorithmMultidimensional accessRapid rotation, drillRegionNorthParisProductYearDetail RowsExt

22、ernalDataDrill EverywhereDrill DownDrill AcrossDrill to DetailDrill OutNavigational PowerWeb PublishingNavigational PowerIntegrationReportsExpressOpenness MAPIOracle Discoverer 3.1Administration EditionSuperior Ease of UseEase of UseSummary ManagementServer BasedAdministrationServer Based Administra

23、tionAdvanced Summary ManagementServer Based Abstraction LayerServer BasedAdministrationEase of UsePRODUCTIONEUL(RDBMS)Sales.Marketing 匜inance匛ducation匔onsultancyUsersAdministratorRead/ WriteRead OnlyEasy to Create, Maintain and SetupScaleableLeverage SecurityOpen - ODBCDB2SybaseInformixSQLServerOrac

24、le?.ODBCEnhancements OpennessOracleEnd User LayerEUL GatewayDes/2000ETT ToolsApplicationsQuery ToolsBuild Summaries from QueriesQuery performance statistics Summary management recommendationsSummary Management38DefinitionsData WarehouseA multi-subject information storeDesigned specifically for decis

25、ion supportData MartA subject or application-specific data warehouseTypes: Dependent, IndependentDW vs. Data MartWarehouseData MartScopeCorporateLine-of-BusinessSubjectsmulti-subjectsubject-specificData SourcesmanyfewSize100GB - 1TB+100GBImplementationmonths - yearsmonthsWarehouseData MartOracle Exp

26、ressOracle WarehouseOracle ExpressWhat is it: Advanced analysis of multidimensional and relational data Key advantages:Broadest range of analytical functionsScaleable performanceIntegrated development environmentOracleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query, AnalysisAdva

27、nced AnalysisOracle Express Key DifferentiatorsAdvanced analysisBuilt-in forecastingMulti-user what-ifAutomatic simultaneous modelsProgrammable serverScaleable performanceAnalysis of existing relational databasesMultiple cubes, hierarchies per schemaIntegrated development environmentBroadest range o

28、f capabilities可扩展的多维数据库服务器, 内置强大的分析 功能和决策支持能力Personal Express, Express Server用于销售分析, 市场分析, 财务分析的OLAP 应用产品Sales Analyzer, Financial Analyzer面向对象的OLAP应用开发工具Express Analyzer, Express ObjectsExpress ServerExpressApplicationsExpress ToolsOracle OLAP - Express ProductsExpress 一个专门为查询和分析商业情报数据而设计的服务器 Expre

29、ss?对于一些分析查询和计算, Express 比关系型技术快10倍，100倍，甚至1000倍 Jerry HeldSenior VP, Server Technologies, Oracle Corporation查询速度如此快，使 Express 具有很强的竞争能力.多维数据模型辖区经理/行长财务经理 任意分析PRODTIME资金经理为数据提供快速，灵活的访问方法金融产品时间头寸辖区对时间，日期的智能处理， 系统相关数据自行汇总。根据数据建立层次关系 数据自行汇总在维上建立层次关系KEYS相同的数学模型华东区域华中月份产品西南Jan胶卷镜片摄像机销售量DIMENSIONSFebMar多维数

30、据库服务器的特性立方体，页，片的数据维 = 主键 只存放一次，重复使用强制性保证数据完整性节省磁盘空间极容易地结合数据和公式支持共享的商业逻辑保证数据的一致性每一个单元都被索引因此有很高的存取效率TimeproductGeography数据存放PhiladelphiaNew YorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales数据页数据存取

31、SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales 关系数据库 多维数据库CostCostCostCostCostCostCostCostCostCostCostCostCostCostCostCostSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateM

32、ktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProd只要读取一页要读取两页数据存取方法DistrictQuarterProductNew YorkBostonPhiladelphiaQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear偏移地址存取更强有力的分析能力更好的执行效率很多的已建好的函数Financial FunctionsDepreciation 折旧率Growth Rate 增长率Net Present Value 现值Internal Rate of ReturnNumeric/Ti

33、me Series FunctionsAverage 平均值Cumulative Sums 累积和Lag/Lead 落后/领先 Variance 变动率Moving Average/Total 移动平均值Smallest/Largest 最大/最小 Standard Deviation 水平偏差Total 总和Other FunctionsForecastingRegression. . . 你也能创建你自己的函数Express LanguageApplication Programming Interfaces Basic OperationsCharacter Manipulation F

34、unctionsCommunicationsCounting and Testing FunctionsComputer Resource Management Data ManipulationDictionary OperationsDimension InformationFinancial FunctionsForecasting and RegressionMiscellaneous Information and Options ModelsNumeric Functions and CommandsNumeric OptionsPaging OptionsProgramsSecu

35、rity and ScopingSystem Commands and InformationUsing External DataWorking with WindowsOracle Confidential持续的查询过程查询示例：从费城的最近一个季度 的产品中选择销售额大 于150K的产品，并且从蠖钭?大的产品。费城季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区持续的查询过程查询示例：限制 季度 到 Q4季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城持续的查询过程查询示例：限制 季度 到 Q4 限制 地区 到 费城季度产品纽约波士顿Q1Q2Q3Q4运动鞋

36、运动衣滑板手套球拍地区费城持续的查询过程查询示例：限制 季度 到 Q4 限制 地区 到 费城限制 产品的销售额 大于 150,000季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城持续的查询过程查询示例：限制 季度 到 Q4 限制 地区 到 费城限制 产品的销售额 大于 150,000限制 产品的利润额 前两位季度产品纽约波士顿Q1Q2Q3Q4运动鞋运动衣滑板手套球拍地区费城许多实体 / 公用维BUDGETTimeMarketProductTimeFORECASTScenarioMarketPRICETimeProductTimeSALESMarketProductProduct派生的实体存放结果在变量中或者由一个计算公式来决定x=价格销售数量销售金额派生的实体存放结果在变量中或者由一个计算公式来决定这些计算公式可由其它公式得来.x=价格销售数量销售金额多个逻辑的最终用户视图产品经理视图地区经理视图 财务经理视图总经理视图TimeQ1Q2Q3Q4P