《Oracle数据仓库解决方案 11207.pptx》由会员分享，可在线阅读，更多相关《Oracle数据仓库解决方案 11207.pptx（74页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle Data Warehouse SolutionOracle(China)Presales ConsultantXu Xin数据仓库几大功能数据仓库几大功能lQuery/ReportlDrill up/Drill DownlComparelExceptionlForcast,WhatiflData Mining数据仓库三大流程数据仓库三大流程l数据建模数据建模 Data Model Designl数据抽取数据抽取 Data Extract,Transform,Transportl数据表现数据表现 Rolap,Molap Data Delivery,Drill up/Drill do
2、wn,Data Rotation,Data MiningROLAP/MOLAP-ROLAP/MOLAP-争论的焦点在哪里争论的焦点在哪里争论的焦点在哪里争论的焦点在哪里定义定义:lROLAP=基于关系的基于关系的 OLAPlMOLAP=基于多维的基于多维的 OLAPlROLAP 供应商说供应商说:你能用关系技术做所有的事情你能用关系技术做所有的事情?lMOLAP ROLAP 供应商说供应商说:你用多维数据库能做得更好你用多维数据库能做得更好lROLAP 供应商仅仅能做供应商仅仅能做ROLAP.lMOLAP 供应商仅仅能做供应商仅仅能做MOLAP.lORACLE 能提供能提供 ROLAP和和MO
3、LAPROLAP/MOLAP-争论的焦点在哪里ROLAPROLAPROLAPROLAP和和和和MOLAP-OracleMOLAP-OracleMOLAP-OracleMOLAP-Oracle的解决方案的解决方案的解决方案的解决方案lOracle ROLAPData Mart Suite-建模，数据抽取的工具建模，数据抽取的工具Discoverer-基于关系数据库的分析工具基于关系数据库的分析工具多维数据的视图多维数据的视图,挖掘和旋转挖掘和旋转lOracle MOLAPExpress 系列产品提供系列产品提供 MOLAP的方案的方案多维数据的视图多维数据的视图,挖掘和旋转挖掘和旋转,计算和存取
4、、预测计算和存取、预测计算和存取、预测计算和存取、预测lOracle 能提供能提供 ROLAP 和和 MOLAPExpress 系列产品能做系列产品能做 MOLAP 和和 ROLAPOFA,OSAApplicationsExpressPower Analysis DiscovererQuery,Reporting,AnalysisDecisionsComponents of a Data Warehouse/MartOLTPOLTPExternal DataOperationsDataStorageData Mart SuiteData Mart SuiteAdd Value To The O
5、racle WarehouseData Mart Suite 2.0-ComponentsData Mart Suite 2.0-ComponentslGreat8-Oracle8,release 8.0.4lDashing-Data Mart Designer 2.1lBetter-Data Mart Builder 2.0lDebonair-Discoverer 3.1lAwesome-Web Application Server 3.0.1lRavishing-Reports Server 3.0lCompelling-CookbookDesign and ManagementAny D
6、ataAny DataAny AccessAny AccessAny SourceAny SourceMetadataConstruction Methodology Construction Methodology Drag&drop visual metaphorDrag&drop visual metaphorData ModelingIdentify data sourcesIdentify source subsetModel Star SchemaProcess ModelingBuild PlansDimension tablesTime DimensionFact tableP
7、opulate databaseBusiness ModelingDefine end-user layerOracle8Oracle8i i for Data Warehousingfor Data Warehousing Continuous InnovationContinuous InnovationOracle 7.3Oracle 7.3llHash JoinHash JoinllBitmap IndexesBitmap IndexesllParallel-Aware OptimizerParallel-Aware OptimizerllPartition ViewsPartitio
8、n ViewsllInstance Affinity:Function Instance Affinity:Function ShippingShippingllParallel Union AllParallel Union AllllAsynchronous Read-AheadAsynchronous Read-AheadllHistogramsHistogramsllAnti-JoinAnti-JoinllPartitioned Tables and IndexesPartitioned Tables and IndexesllPartition PruningPartition Pr
9、uningllParallel Index ScansParallel Index ScansllParallel Insert,Update,DeleteParallel Insert,Update,DeletellParallel Bitmap Star QueryParallel Bitmap Star QueryllParallel ANALYZEParallel ANALYZEllParallel Constraint EnablingParallel Constraint EnablingllServer Managed Backup/RecoveryServer Managed
10、Backup/RecoveryllPoint-in-Time RecoveryPoint-in-Time RecoveryOracle 8.0Oracle 8.0llSummary ManagementSummary ManagementllNew Partitioning SchemesNew Partitioning SchemesllResource ManagerResource ManagerllProgress MonitorProgress MonitorllAdaptive Parallel QueryAdaptive Parallel QueryllServer-based
11、Analytic FunctionsServer-based Analytic FunctionsllTransportable TablespacesTransportable TablespacesllDirect Loader APIDirect Loader APIllFunctional IndexesFunctional IndexesllPartition-wise JoinsPartition-wise JoinsllSecurity EnhancementsSecurity Enhancementsand more.and more.Oracle8Oracle8i iData
12、 ModelingData ModelingOracle Data Mart DesignerOracle 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_idnumber,o_entry_ddate,o_carrier_idnumber,o_ol_cntn
13、umber,o_all_localnumber)tablespace ord initrans 3 pctfree 5 storage(initial 20K next 110M pctincrease 0 freelist groups 19 freelists 9);Source:OLTPProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFac
14、t TableTarget:Data MartProcess Modeling Process Modeling Oracle Data Mart BuilderOracle Data Mart BuilderOrders-ExtractSQL QueryProductProduct_LineProduct_GroupShipShipperContactCus_ LocOrdersContact_LocProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracle Data Mart BuilderMetada
15、ta IntegrationMetadata IntegrationProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFact TableOrders-ExtractSQL QueryProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracle Data Mart
16、 BuilderOracleDiscovererOracle Data Mart DesignerMetadataMetadata决策支持系统的范围决策支持系统的范围决策支持系统的范围决策支持系统的范围IT 人员人员为用户开发为用户开发业务用户业务用户独立分析独立分析专业分析人员专业分析人员标准报表标准报表即席查询分析即席查询分析复杂分析复杂分析Oracle Oracle 决策支持工具集决策支持工具集决策支持工具集决策支持工具集标准报表标准报表即席查询分析即席查询分析复杂分析复杂分析上季度各地区销售上季度各地区销售 情况如何？情况如何？什么原因造成东南亚什么原因造成东南亚 地区的业务下降地区的
17、业务下降?如果同样的因素影响到如果同样的因素影响到 其其它地区，将会对全球的它地区，将会对全球的 业业务造成什么影响务造成什么影响?OracleExpressOracle DiscovererOracleReportsOracle Oracle 决策支持工具集决策支持工具集决策支持工具集决策支持工具集l标准报表标准报表-Oracle Reports集中式报表生成存储集中式报表生成存储输出发布高质量的报表输出发布高质量的报表 l即席查询分析即席查询分析-Discoverer即席查询即席查询,数据钻入，旋转数据钻入，旋转业界领先的易用性和性能业界领先的易用性和性能l复杂分析复杂分析-Express
18、 On-line Analytical Processing(OLAP)高级计算高级计算 财务财务,时间序列时间序列,建模建模,预测预测,回归分析，假设分析回归分析，假设分析Oracle ReportsOracle ReportslWhat is it?Formats and distributes database informationlKey advantagesUnlimited data formattingDistributes high fidelity reports via the WebApplication server based reporting engineOra
19、cleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query,AnalysisAdvanced AnalysisOracle DiscoverOracle DiscoverAdd Value To The Oracle WarehouseOracle DiscovererOracle DiscovererlWhat is it:Ad hoc query and analysis of relational datalKey advantages:Award-winning ease of useIndustry-
20、leading query performance OracleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query,AnalysisAdvanced AnalysisDiscoverer is:10 times faster than Cognos9 times faster than Business ObjectsMarket-Leading PerformanceMarket-Leading PerformanceTime to query 100,000 rows:CognosBusiness Obj
21、ectsDiscovererOracle Discoverer 3.1Oracle Discoverer 3.1User EditionUser EditionBusiness Abstraction Layer End User Layer(TM)Data Warehouse/Data Mart(or OLTP)User EditionAdministrationEditionOracle Discoverer 3.1Oracle Discoverer 3.1User EditionUser EditionSuperior Ease of UseEase of UsePerformanceW
22、arehouse ExplorationExceptional PerformanceNavigational PowerNavigational PowerOpennessOpenness Most Tested UI on the Most Tested UI on the MarketMarketResizeableWizard InterfaceIndustry Specific Quick TourDramatic Reduction of the Learning Curveand more.Ease of UseEnd User LayerEnd User LayerEase o
23、f UseHides the Complexity of the Underlying DatabaseDatabasecl_addr1|,|cl_addr2|,|cl_addr3|,|cl_twn|,|cl_zipcodesum(nvl(qty*unit_price,0)Jones Smith Name Customer Client Address Revenue Eastern Region UserBatch ReportingBatch ReportingOpenness To Do.Schedule long running queries for laterEase of Use
24、 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 tomorrow Solution to Long Running Queries Solution to Long Running QueriesReactive Query GovernorStops queries after a pre-set timeUses the resou
25、rces without showing results!PerformancePredictive Query GovernorPredicts the time a query takes before executionDoes not waste valuable resources!Managing Large Data Managing Large Data VolumesVolumesSelect Location,Period,Product,sum(videos_lent)From video_detailsGroup by Location,Period,ProductDe
26、tail RowsPerformanceSlow Table ScansMachine Intensive AggregationsSummary RedirectionSummary RedirectionSelect Location,Period,Product,sum(videos_lent)From video_detailsGroup by Location,Period,ProductDetail RowsSummary TablePerformanceSelect Location,Period,Product,SalesFrom video_summaryAutomatic!
27、Summary RedirectionDetail RowsSummary TablePerformanceServerClientNetworkResultsBase:Cubic Client CachePerformanceEfficient Storage AlgorithmMultidimensional accessRapid rotation,drillRegionRegionNorthNorthParisParisProductProductYearYearDetail RowsExternalExternalDataDataDrill EverywhereDrill Every
28、wherelDrill DownlDrill AcrosslDrill to DetaillDrill OutNavigational PowerWeb PublishingWeb PublishingNavigational PowerIntegrationIntegrationReportsReportsExpressExpressOpenness MAPIMAPIOracle Discoverer 3.1Oracle Discoverer 3.1Administration EditionAdministration EditionSuperior Ease of UseEase of
29、UseSummary ManagementServer BasedAdministrationServer Based AdministrationAdvanced Summary ManagementServer Based Abstraction Server Based Abstraction LayerLayerServer BasedAdministrationEase of UsePRODUCTIONPRODUCTIONEULEUL(RDBMS)(RDBMS)Sales.Marketing 匜匜inance匛匛ducation匔匔onsultancyUsersAdministrat
30、orRead/WriteRead OnlyEasy to Create,Maintain and SetupScaleableLeverage SecurityOpen-ODBCOpen-ODBCDB2DB2SybaseSybaseInformixInformixSQLSQLServerServerOracleOracle?.ODBCODBCEnhancements OpennessOracleOracleEnd User LayerEUL GatewayDes/2000Des/2000ETT ToolsETT ToolsApplicationsApplicationsQuery ToolsQ
31、uery ToolsBuild Summaries from QueriesBuild Summaries from QuerieslQuery performance statistics lSummary management recommendationsSummary Management38DefinitionsDefinitionsData WarehouselA multi-subject information storelDesigned specifically for decision supportData MartlA subject or application-s
32、pecific data warehouselTypes:Dependent,IndependentDW vs.Data MartDW vs.Data MartWarehouseData MartScopeScopeCorporateCorporateLine-of-BusinessLine-of-BusinessSubjectsSubjectsmulti-subjectmulti-subjectsubject-specificsubject-specificData SourcesData SourcesmanymanyfewfewSizeSize100GB-1TB+100GB-1TB+10
33、0GB100GBImplementationImplementationmonths-yearsmonths-yearsmonthsmonthsWarehouseData MartOracle ExpressOracle WarehouseOracle ExpressOracle ExpresslWhat is it:Advanced analysis of multidimensional and relational data lKey advantages:Broadest range of analytical functionsScaleable performanceIntegra
34、ted development environmentOracleExpressOracle DiscovererOracleReportsProductionReportingAd Hoc Query,AnalysisAdvanced AnalysisOracle Express Key DifferentiatorsOracle Express Key DifferentiatorslAdvanced analysisBuilt-in forecastingMulti-user what-ifAutomatic simultaneous modelsProgrammable serverl
35、Scaleable performanceAnalysis of existing relational databasesMultiple cubes,hierarchies per schemalIntegrated development environmentBroadest range of capabilities可扩展的多维数据库服务器可扩展的多维数据库服务器,内置强大的分析内置强大的分析 功功能和决策支持能力能和决策支持能力Personal Express,Express Server用于销售分析用于销售分析,市场分析市场分析,财务分析的财务分析的OLAP 应用产品应用产品Sa
36、les Analyzer,Financial Analyzer面向对象的面向对象的OLAP应用开发工具应用开发工具Express Analyzer,Express ObjectsExpress ServerExpressApplicationsExpress ToolsOracle OLAP-Express ProductsOracle OLAP-Express ProductsExpress Express 一个专门为查询和分析商业情报数一个专门为查询和分析商业情报数据而设计的服务器据而设计的服务器 ExpressExpress?对于一些分析查询和计算对于一些分析查询和计算,Express,
37、Express 比比关系型技术快关系型技术快1010倍，倍，100100倍，甚至倍，甚至10001000倍倍 Jerry HeldSenior VP,Server Technologies,Oracle Corporation查询速度如此快，使查询速度如此快，使 Express Express 具有具有很强的竞争能力很强的竞争能力.多维数据模型多维数据模型辖区经理辖区经理/行行长长财务经理财务经理 任意分析任意分析PRODTIME资金经理资金经理为数据提供快速，灵活的访问方法为数据提供快速，灵活的访问方法金融产品金融产品时间时间头寸头寸头寸头寸辖区对时间，日期的智能处理，对时间，日期的智能处理
38、，系统相关数据自行汇总。系统相关数据自行汇总。根据数据建立层次关系根据数据建立层次关系 数据自行汇总数据自行汇总在维上建立层次关系KEYS相同的数学模型相同的数学模型华东区域区域华中月份月份产品产品西南Jan胶卷镜片摄像机销售量销售量销售量销售量DIMENSIONSFebMar多维数据库服务器的特性多维数据库服务器的特性l立方体，页，片的数据l维=主键 只存放一次，重复使用强制性保证数据完整性节省磁盘空间l极容易地结合数据和公式支持共享的商业逻辑保证数据的一致性l每一个单元都被索引因此有很高的存取效率TimeproductGeography数据存放数据存放PhiladelphiaNew Yor
39、kBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales Sales数据页数据页数据存取数据存取SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales 关系数据库关系数据库 多维数据库多维数据库CostCostCostCostCostCostCostCos
40、tCostCostCostCostCostCostCostCostSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProd只要读取一页只要读取一页要读取两页要读取两页数据存取方法数据存取方法DistrictQuarterProductNew YorkBos
41、tonPhiladelphiaQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearl偏移地址存取偏移地址存取l更强有力的分析能更强有力的分析能力力l更好的执行效率更好的执行效率很多的已建好的函数很多的已建好的函数Financial FunctionsFinancial FunctionsDepreciation Depreciation 折旧率折旧率Growth Rate Growth Rate 增长率增长率Net Present Value Net Present Value 现值现值Internal Rate of ReturnInternal Rat
42、e of ReturnNumeric/Time Series FunctionsNumeric/Time Series FunctionsAverage Average 平均值平均值Cumulative Sums Cumulative Sums 累积和累积和Lag/Lead Lag/Lead 落后落后/领先领先 Variance Variance 变动率变动率Moving Average/Total Moving Average/Total 移动平均值移动平均值Smallest/Largest Smallest/Largest 最大最大/最小最小 Standard Deviation Stan
43、dard Deviation 水平偏差水平偏差Total Total 总和总和Other FunctionsForecastingRegression.你也能创建你自己的函数你也能创建你自己的函数Express LanguageExpress LanguagelApplication Programming Interfaces lBasic OperationslCharacter Manipulation FunctionslCommunicationslCounting and Testing FunctionslComputer Resource Management lData Ma
44、nipulationlDictionary OperationslDimension InformationlFinancial FunctionslForecasting and RegressionlMiscellaneous Information and Options lModelslNumeric Functions and CommandslNumeric OptionslPaging OptionslProgramslSecurity and ScopinglSystem Commands and InformationlUsing External DatalWorking
45、with WindowsOracle Confidential持续的查询过程持续的查询过程查询示例：查询示例：从费城的最近一个季度从费城的最近一个季度 的产品中选择销售额大的产品中选择销售额大 于于150K150K的产品，并且从的产品，并且从蠖钭蠖钭?大的产品。大的产品。费城费城费城费城季度季度产品产品纽约纽约纽约纽约波士顿波士顿波士顿波士顿Q1Q1Q2Q2Q3Q3Q4Q4运动鞋运动鞋运动鞋运动鞋运动衣运动衣运动衣运动衣滑板滑板滑板滑板手套手套手套手套球拍球拍球拍球拍地区地区持续的查询过程持续的查询过程查询示例：查询示例：r限制限制 季度季度 到到 Q4Q4季度季度产品产品纽约纽约纽约纽约波士
46、顿波士顿波士顿波士顿Q1Q1Q2Q2Q3Q3Q4Q4运动鞋运动鞋运动鞋运动鞋运动衣运动衣运动衣运动衣滑板滑板滑板滑板手套手套手套手套球拍球拍球拍球拍地区地区费城费城费城费城持续的查询过程持续的查询过程查询示例：查询示例：r限制限制 季度季度 到到 Q4 Q4 r限制限制 地区地区 到到 费城费城费城费城季度季度产品产品纽约纽约纽约纽约波士顿波士顿波士顿波士顿Q1Q1Q2Q2Q3Q3Q4Q4运动鞋运动鞋运动鞋运动鞋运动衣运动衣运动衣运动衣滑板滑板滑板滑板手套手套手套手套球拍球拍球拍球拍地区地区费城费城费城费城持续的查询过程持续的查询过程查询示例：查询示例：r限制限制 季度季度 到到 Q4 Q4
47、r限制限制 地区地区 到到 费城费城费城费城r限制限制 产品的销售额产品的销售额 大大于于 150,000150,000季度季度产品产品纽约纽约纽约纽约波士顿波士顿波士顿波士顿Q1Q1Q2Q2Q3Q3Q4Q4运动鞋运动鞋运动鞋运动鞋运动衣运动衣运动衣运动衣滑板滑板滑板滑板手套手套手套手套球拍球拍球拍球拍地区地区费城费城费城费城持续的查询过程持续的查询过程查询示例：查询示例：r限制限制 季度季度 到到 Q4 Q4 r限制限制 地区地区 到到 费城费城费城费城r限制限制 产品的销售额产品的销售额 大大于于 150,000 150,000r限制限制 产品的利润额产品的利润额 前前两位两位季度季度产品
48、产品纽约纽约纽约纽约波士顿波士顿波士顿波士顿Q1Q1Q2Q2Q3Q3Q4Q4运动鞋运动鞋运动鞋运动鞋运动衣运动衣运动衣运动衣滑板滑板滑板滑板手套手套手套手套球拍球拍球拍球拍地区地区费城费城费城费城许多实体许多实体/公用维公用维BUDGETBUDGETTimeMarketProductTimeFORECASTFORECASTScenarioMarketPRICEPRICETimeProductTimeSALESSALESMarketProductProduct派生的实体派生的实体v存放结果在变量中存放结果在变量中v或者由一个计算公式来决定或者由一个计算公式来决定x=价格价格价格价格销售销售销售销
49、售数量数量数量数量销售销售销售销售金额金额金额金额派生的实体派生的实体v存放结果在变量中存放结果在变量中v或者由一个计算公式来决定或者由一个计算公式来决定v这些计算公式可由其它公式得来这些计算公式可由其它公式得来.x=价格价格价格价格销售销售销售销售数量数量数量数量销售销售销售销售金额金额金额金额多个逻辑的最终用户视图多个逻辑的最终用户视图产品经理视图产品经理视图地区经理视图地区经理视图 财务经理视图财务经理视图总经理视图总经理视图TimeQ1Q1Q2Q2Q3Q3Q4Q4ProductTentsTentsCanoesCanoesRacquetsRacquetsSportswearSportsw
50、earFootwearFootwearPhiladelphiPhiladelphia aNew YorkNew YorkBostonBostonDistrict多维分析举例多维分析举例多维分析举例多维分析举例查询上季度在费城地区查询上季度在费城地区 ，营利最多的，营利最多的2 2种产品。种产品。这些产品的销售额必须这些产品的销售额必须 大于大于1515万元。万元。Philadelphia时间时间产品产品New YorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear地区地区多维数据分析多维数据分析多维数据分析多维数据分析在过去半年内，华北地