Oracle数据仓库解决方案课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Oracle数据仓库解决方案,*,PPT,文档演模板,Office,PPT,Oracle数据仓库解决方案,2024/9/16,Oracle数据仓库解决方案,数据仓库几大功能,Query/Report,Drill up/ Drill Down,Compare,Exception,Forcast, Whatif,Data Mining,Oracle数据仓库解决方案,数据仓库三大流程,数据建模,Data Model Design,数据抽取,Data Extract, Transform, Transport,数据表现,Rolap, Molap,Data Delivery, Drill up/Drill down, Data Rotation, Data Mining,Oracle数据仓库解决方案,ROLAP / MOLAP -,争论的焦点在哪里,定义 :,ROLAP = 基于关系的 OLAP,MOLAP = 基于多维的 OLAP,Oracle数据仓库解决方案,ROLAP 供应商说 :,你能用关系技术做所有的事情?,MOLAP ROLAP 供应商说 :,你用多维数据库能做得更好,ROLAP 供应商仅仅能做ROLAP.,MOLAP 供应商仅仅能做MOLAP.,ORACLE 能提供 ROLAP和MOLAP,ROLAP / MOLAP -,争论的焦点在哪里,Oracle数据仓库解决方案,ROLAP和MOLAP - Oracle的解决方案,Oracle ROLAP,Data Mart Suite - 建模，数据抽取的工具,Discoverer - 基于关系数据库的分析工具,多维数据的视图, 挖掘和旋转,Oracle MOLAP,Express 系列产品提供 MOLAP的方案,多维数据的视图, 挖掘和旋转,计算和存取、预测,Oracle 能提供 ROLAP 和 MOLAP,Express 系列产品能做 MOLAP 和 ROLAP,Oracle数据仓库解决方案,OFA, OSA,Applications,Express,Power Analysis,Discoverer,Query, Reporting,Analysis,Decisions,Components of a Data Warehouse / Mart,OLTP,OLTP,External Data,Operations,DataStorage,Oracle数据仓库解决方案,Data Mart Suite,Add Value To The Oracle Warehouse,Oracle数据仓库解决方案,Data Mart Suite 2.0 - Components,Great8 - Oracle8, release 8.0.4,Dashing - Data Mart Designer 2.1,Better - Data Mart Builder 2.0,Debonair - Discoverer 3.1,Awesome - Web Application Server 3.0.1,Ravishing - Reports Server 3.0,Compelling - Cookbook,Oracle数据仓库解决方案,Design and Management,Any Data,Any Access,Any Source,Metadata,Construction Methodology,Drag & drop visual metaphor,Data,Modeling,Identify data sources,Identify source subset,Model Star Schema,Process,Modeling,Build Plans,Dimension tables,Time Dimension,Fact table,Populate database,Business Modeling,Define end-user layer,Oracle数据仓库解决方案,Oracle8,i,for Data Warehousing,Continuous Innovation,Oracle 7.3,Hash Join,Bitmap Indexes,Parallel-Aware Optimizer,Partition Views,Instance Affinity: Function Shipping,Parallel Union All,Asynchronous Read-Ahead,Histograms,Anti-Join,Partitioned Tables and Indexes,Partition Pruning,Parallel Index Scans,Parallel Insert, Update, Delete,Parallel Bitmap Star Query,Parallel ANALYZE,Parallel Constraint Enabling,Server Managed Backup/Recovery,Point-in-Time Recovery,Oracle 8.0,Summary Management,New Partitioning Schemes,Resource Manager,Progress Monitor,Adaptive Parallel Query,Server-based Analytic Functions,Transportable Tablespaces,Direct Loader API,Functional Indexes,Partition-wise Joins,Security Enhancements,and more .,Oracle8,i,Oracle数据仓库解决方案,Data Modeling,Oracle Data Mart Designer,Table 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_cntnumber,o_all_localnumber),tablespace ord initrans 3,pctfree 5 storage (initial 20K,next 110M pctincrease 0 freelist,groups 19 freelists 9);,Source: OLTP,Product,description,size,flavor,package,Promotion,description,deal,discount,media,Market,description,district,region,demographics,Time,description,weekday,holiday,fiscal,Fact Table,Target: Data Mart,Oracle数据仓库解决方案,Process Modeling,Oracle Data Mart Builder,Orders - Extract,SQL Query,Product,Product_Line,Product_Group,Ship,Shipper,Contact,Cus_ Loc,Orders,Contact_Loc,ProductLookup,MarketLookup,Promotion,Lookup,TimeLookup,Direct Path,Loader,Oracle,Data,Mart,Builder,Oracle数据仓库解决方案,Metadata Integration,Product,description,size,flavor,package,Promotion,description,deal,discount,media,Market,description,district,region,demographics,Time,description,weekday,holiday,fiscal,Fact Table,Orders - Extract,SQL Query,ProductLookup,MarketLookup,Promotion,Lookup,TimeLookup,Direct Path,Loader,Oracle,Data,Mart,Builder,Oracle,Discoverer,Oracle,Data,Mart,Designer,Metadata,Oracle数据仓库解决方案,决策支持系统的范围,IT 人员,为用户开发,业务用户,独立分析,专业分析人员,标准报表,即席查询分析,复杂分析,Oracle数据仓库解决方案,Oracle 决策支持工具集,标准报表,即席查询分析,复杂分析,上季度各地区销售,情况如何？,什么原因造成东南亚 地区的业务下降?,如果同样的因素影响到 其它地区，将会对全球的 业务造成什么影响?,Oracle,Express,Oracle,Discoverer,Oracle,Reports,Oracle数据仓库解决方案,Oracle 决策支持工具集,标准报表 -,Oracle Reports,集中式报表生成存储,输出发布高质量的报表,即席查询分析 -,Discoverer,即席查询, 数据钻入，旋转,业界领先的易用性和性能,复杂分析 -,Express,On-line Analytical Processing (OLAP),高级计算,财务, 时间序列, 建模, 预测, 回归分析，假设分析,Oracle数据仓库解决方案,Oracle Reports,What is it?,Formats and distributes database information,Key advantages,Unlimited data formatting,Distributes high fidelity reports via the Web,Application server based reporting engine,Oracle,Express,Oracle,Discoverer,Oracle,Reports,Production,Reporting,Ad Hoc Query,Analysis,Advanced Analysis,Oracle数据仓库解决方案,Oracle Discover,Add Value To The Oracle Warehouse,Oracle数据仓库解决方案,Oracle Discoverer,What is it:,Ad hoc query and analysis of relational data,Key advantages:,Award-winning ease of use,Industry-leading query performance,Oracle,Express,Oracle,Discoverer,Oracle,Reports,Production,Reporting,Ad Hoc Query,Analysis,Advanced Analysis,Oracle数据仓库解决方案,Discoverer is:,10 times faster,than Cognos,9 times faster,than Business Objects,Market-Leading Performance,Time to query 100,000 rows:,Cognos,Business Objects,Discoverer,Oracle数据仓库解决方案,Oracle Discoverer 3.1,User Edition,Business Abstraction Layer,End User Layer,(TM),Data Warehouse / Data Mart (or OLTP),User,Edition,Administration,Edition,Oracle数据仓库解决方案,Oracle Discoverer 3.1,User Edition,Superior Ease of Use,Ease of Use,Performance,Warehouse Exploration,Exceptional Performance,Navigational Power,Navigational Power,Openness,Openness,Oracle数据仓库解决方案,Most Tested UI on the Market,ResizeableWizard Interface,Industry Specific Quick Tour,Dramatic Reduction of the Learning Curve,and more.,Ease of Use,Oracle数据仓库解决方案,End User Layer,Ease of Use,Hides the Complexity of the Underlying Database,Database,cl_addr1|,|,cl_addr2|,|,cl_addr3|, |,cl_twn|, |,cl_zipcode,sum(nvl(qty*,unit_price,0),Jones,Smith,Name,Customer,Client Address,Revenue,Eastern Region,User,Oracle数据仓库解决方案,Batch Reporting,Openness,To Do.,Schedule long running queries for later,Ease 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 tomorrow,Oracle数据仓库解决方案,Solution to Long Running Queries,Reactive Query Governor,Stops queries after a pre-set time,Uses the resources without showing results!,Performance,Predictive Query Governor,Predicts the time a query takes before execution,Does not waste valuable resources!,Oracle数据仓库解决方案,Managing Large Data Volumes,Select Location, Period, Product, sum(videos_lent),From,video_details,Group by Location, Period, Product,Detail Rows,Performance,Slow Table Scans,Machine Intensive Aggregations,Oracle数据仓库解决方案,Summary Redirection,Select Location, Period, Product, sum(videos_lent),From,video_details,Group by Location, Period, Product,Detail Rows,Summary Table,Performance,Oracle数据仓库解决方案,Select Location, Period, Product, Sales,From,video_summary,Automatic!,Summary Redirection,Detail Rows,Summary Table,Performance,Oracle数据仓库解决方案,Server,Client,Network,ResultsBase: Cubic Client Cache,Performance,Efficient Storage Algorithm,Multidimensional access,Rapid rotation, drill,Oracle数据仓库解决方案,Region,North,Paris,Product,Year,Detail Rows,External,Data,Drill Everywhere,Drill Down,Drill Across,Drill to Detail,Drill Out,Navigational Power,Oracle数据仓库解决方案,Web Publishing,Navigational Power,Oracle数据仓库解决方案,Integration,Reports,Express,Openness,MAPI,Oracle数据仓库解决方案,Oracle Discoverer 3.1,Administration Edition,Superior Ease of Use,Ease of Use,Summary Management,Server Based,Administration,Server Based Administration,Advanced Summary,Management,Oracle数据仓库解决方案,Server Based Abstraction Layer,Server Based,Administration,Ease of Use,PRODUCTION,EUL,(RDBMS),Sales.Marketing 匜inance匛ducation匔onsultancy,Users,Administrator,Read/ Write,Read Only,Easy to Create, Maintain and Setup,Scaleable,Leverage Security,Oracle数据仓库解决方案,Open - ODBC,DB2,Sybase,Informix,SQL,Server,Oracle,?.,ODBC,Enhancements,Openness,Oracle,End User Layer,EUL Gateway,Des/2000,ETT Tools,Applications,Query Tools,Oracle数据仓库解决方案,Build Summaries from Queries,Query performance statistics,Summary management recommendations,Summary,Management,38,Oracle数据仓库解决方案,Definitions,Data,Warehouse,A,multi-subject,information store,Designed specifically for decision support,Data,Mart,A,subject or application-specific,data warehouse,Types: Dependent, Independent,Oracle数据仓库解决方案,DW vs. Data Mart,WarehouseData Mart,ScopeCorporateLine-of-Business,Subjectsmulti-subjectsubject-specific,Data Sourcesmanyfew,Size100GB - 1TB+100GB,Implementationmonths - yearsmonths,Warehouse,Data Mart,Oracle数据仓库解决方案,Oracle Express,Oracle Warehouse,Oracle数据仓库解决方案,Oracle Express,What is it:,Advanced analysis of multidimensional and relational data,Key advantages:,Broadest range of analytical functions,Scaleable performance,Integrated development environment,Oracle,Express,Oracle,Discoverer,Oracle,Reports,Production,Reporting,Ad Hoc Query,Analysis,Advanced Analysis,Oracle数据仓库解决方案,Oracle Express Key Differentiators,Advanced analysis,Built-in forecasting,Multi-user what-if,Automatic simultaneous models,Programmable server,Scaleable performance,Analysis of existing relational databases,Multiple cubes, hierarchies per schema,Integrated development environment,Broadest range of capabilities,Oracle数据仓库解决方案,可扩展的多维数据库服务器, 内置强大的分析,功能和决策支持能力,Personal Express, Express Server,用于销售分析, 市场分析, 财务分析的OLAP 应用产品,Sales Analyzer, Financial Analyzer,面向对象的OLAP应用开发工具,Express Analyzer, Express Objects,Express,Server,Express,Applications,Express,Tools,Oracle OLAP - Express Products,Oracle数据仓库解决方案,Express,一个专门为查询和分析商业情报数据而设计的服务器,Oracle数据仓库解决方案,Express,?,对于一些分析查询和计算, Express 比关系型技术快10倍，100倍，甚至1000倍,Jerry Held,Senior VP, Server Technologies, Oracle Corporation,查询速度如此快，使 Express 具有很强的竞争能力.,Oracle数据仓库解决方案,多维数据模型,辖区经理/行长,财务经理,任意分析,PROD,TIME,资金经理,为数据提供快速，灵活的访问方法,金融产品,时间,头寸,辖区,Oracle数据仓库解决方案,对时间，日期的智能处理，,系统相关数据自行汇总。,根据数据建立层次关系,数据自行汇总,在维上建立层次关系,Oracle数据仓库解决方案,KEYS,相同的数学模型,华东,区域,华中,月份,产品,西南,Jan,胶卷,镜片,摄像机,销售量,DIMENSIONS,Feb,Mar,Oracle数据仓库解决方案,多维数据库服务器的特性,立方体，页，片的数据,维 = 主键,只存放一次，重复使用,强制性保证数据完整性,节省磁盘空间,极容易地结合数据和公式,支持共享的商业逻辑,保证数据的一致性,每一个单元都被索引,因此有很高的存取效率,Time,p,r,o,d,u,c,t,Geography,Oracle数据仓库解决方案,数据存放,Philadelphia,New York,Boston,Q1,Q2,Q3,Q4,Tents,Canoes,Racquets,Sportswear,Footwear,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,数据页,Oracle数据仓库解决方案,数据存取,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,Sales,关系数据库 多维数据库,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Cost,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,Sales,Cost,Units,Date,Mkt,Prod,只要读取一页,要读取两页,Oracle数据仓库解决方案,数据存取方法,District,Quarter,Product,New York,Boston,Philadelphia,Q1,Q2,Q3,Q4,Tents,Canoes,Racquets,Sportswear,Footwear,偏移地址存取,更强有力的分析能力,更好的执行效率,Oracle数据仓库解决方案,很多的已建好的函数,Financial Functions,Depreciation 折旧率,Growth Rate 增长率,Net Present Value 现值,Internal Rate of Return,Numeric/Time Series Functions,Average 平均值,Cumulative Sums 累积和,Lag/Lead 落后/领先,Variance 变动率,Moving Average/Total 移动平均值,Smallest/Largest 最大/最小,Standard Deviation 水平偏差,Total 总和,Other Functions,Forecasting,Regression,. . . 你也能创建你自己的函数,Oracle数据仓库解决方案,Express Language,Application Programming Interfaces,Basic Operations,Character Manipulation Functions,Communications,Counting and Testing Functions,Computer Resource Management,Data Manipulation,Dictionary Operations,Dimension Information,Financial Functions,Forecasting and Regression,Miscellaneous Information and Options,Models,Numeric Functions and Commands,Numeric Options,Paging Options,Programs,Security and Scoping,System Commands and Information,Using External Data,Working with Windows,Oracle数据仓库解决方案,持续的查询过程,查询示例：,从费城的最近一个季度 的产品中选择销售额大 于150K的产品，并且从蠖钭?大的产品。,费城,季度,产品,纽约,波士顿,Q1,Q2,Q3,Q4,运动鞋,运动衣,滑板,手套,球拍,地区,Oracle数据仓库解决方案,持续的查询过程,查询示例：,限制,季度,到,Q4,季度,产品,纽约,波士顿,Q1,Q2,Q3,Q4,运动鞋,运动衣,滑板,手套,球拍,地区,费城,Oracle数据仓库解决方案,持续的查询过程,查询示例：,限制 季度 到 Q4,限制,地区,到,费城,季度,产品,纽约,波士顿,Q1,Q2,Q3,Q4,运动鞋,运动衣,滑板,手套,球拍,地区,费城,Oracle数据仓库解决方案,持续的查询过程,查询示例：,限制 季度 到 Q4,限制,地区 到,费城,限制,产品的销售额,大于,150,000,季度,产品,纽约,波士顿,Q1,Q2,Q3,Q4,运动鞋,运动衣,滑板,手套,球拍,地区,费城,Oracle数据仓库解决方案,持续的查询过程,查询示例：,限制 季度 到 Q4,限制,地区 到,费城,限制 产品的销售额 大于 150,000,限制,产品的利润额 前两位,季度,产品,纽约,波士顿,Q1,Q2,Q3,Q4,运动鞋,运动衣,滑板,手套,球拍,地区,费城,Oracle数据仓库解决方案,许多实体 / 公用维,BUDGET,Time,Market,Product,Time,FORECAST,Scenario,Market,PRICE,Time,Product,Time,SALES,Market,Product,Product,Oracle数据仓库解决方案,派生的实体,存放结果在变量中,或者由一个计算公式来决定,x,=,价格,销售,数量,销售,金额,Oracle数据仓库解决方案,派生的实体,存放结果在变量中,或者由一个计算公式来决定,这些计算公式可由其它公式得来,.,x,=,价格,销售,数量,销售,金额,Oracle数据仓库解决方案,多个逻辑的最终用户视图,产品经理视图,地区经理视图,财务经理视图,总经理视图,Time,Q1,Q2,Q3,Q4,Product,Tents,Canoes,Racquets,Sportswear,Footwear,Philadelphia,New York,Boston,District,Oracle数据仓库解决方案,多维分析举例,查询上季度在费城地区 ，营利最多的2种产品。这些产品的销售额必须 大于15万元。,Philadelphia,时间,产品,New York,Boston,Q1,Q2,Q3,Q4,Tents,Canoes,Racquets,Sportswear,Footwear,地区,Oracle数据仓库解决方案,多维数据分析,在过去半年内，华北地区实际销售额与预算相比较如何。,公司产品在东北地区的销售情况与华北地区相比较如何,一月份产品销售情况与去年同期相比较增长率为多少,进行比较分析,销售额,东北地区,华北地区,华南地区,产品一,产品二,产品三,一月,三月,二月,120,115,123,60,75,73,92,87,106,Oracle数据仓库解决方案,那些产品在过去半年内销售额发展趋势为负增长,那些产品的销售比预算低5% 以上，并占总销售额的10%以上,产品销售总额过去两年的总体变化情况如何,异常分析报告，趋势分析,多维数据分析,Sales,东北地区,华北地区.,华南地区,产品一,产品二,产品三,一月,三月,二月,120,115,123,60,75,73,92,87,106,Oracle数据仓库解决方案,如果产品一价格下调5%,销售量增加3%，结果将如何？,如果在东北地区增加一个销售人员，销售额将会如何？,根据今年的销售情况，预测明年上半年销售发展。,应用模型，预测,多维数据分析,Sales,东北地区,华北地区.,华南地区,产品一,产品二,产品三,一月,三月,二月,120,115,123,60,75,73,92,87,106,Oracle数据仓库解决方案,Express Objects,为 IS 专业开发人员设计,面向对象的,可视化的OLAP 开发工具,内置Data-Aware控件,Oracle数据仓库解决方案,Express Analyzer,面向最终用户的报表和分析工具,通过交互式的分析简报发布分析结果,提供Drill, pivot 和 what-if 分析能力,运行 Express Objects 开发的应用,Oracle数据仓库解决方案,Personal Express Enables Mobile OLAP,Express,Server,Unix/Mainframe,Personal,Express,Windows,RDBMS,OLAP,Client,Oracle数据仓库解决方案,Oracle DSS Product Family,Oracle Express Server,Express,Administrator,Relational Access,Manager,Other Legacy &,External Data,Relational Database Management Systems,Express,Web Agent,Express,Objects,Express,Analyzer,Express,Web Publisher,Sales,Analyzer,Financial,Analyzer,3rd Party,Tools & Apps,Oracle,Reports,Oracle,Discoverer,Oracle数据仓库解决方案,演讲完毕，谢谢听讲,!,再见，see you again,2024/9/16,Oracle数据仓库解决方案,