Bud EndressDirector of Product Management, OLAPOracle .ppt

1、,Bud Endress Director of Product Management, OLAP Oracle Corporation,Oracle OLAP Option When to use the OLAP Option to Enhance Content and Performance of Business Intelligence Applications,Topics,Deployment options Oracle business intelligence platform Enhancing application with analytic content Per

2、formance of multidimensional data types,Typical Deployment Options,Query and Analysis,Oracle Deployment Options,Oracle Business Intelligence Platform and Tools,Oracle Business Intelligence,Oracle OLAP Option,OracleBI Tools,Relational Model OracleBI Discoverer Reports, HTML Database Dimensional Model

3、 OracleBI Spreadsheet Add-in Oracle Discoverer Plus OracleBI Beans Viewing OracleBI Discoverer Viewer Oracle Portal,Logical Models,Choice of Model,Query and Analysis,Reporting,Relational Model,Dimensional Model,Promotes ad-hoc navigation and calculation definition Easily understood by end users “Sal

4、es by product and customer over time” Embedded business rules Users dont need to understand how all data is calculated Provides context for query and calculation definition Users dont need to understand the physical model,Dimensional Model,D E M O N S T R A T I O N,Dimensional Model,Implementation,C

5、hoice of Implementation,Query and Analysis,Reporting,Relational Model,Multidimensional Data Types,Enhanced calculations User-defined functions Compound aggregations Allocations Forecasts Data flows,Optimizing Performance,There is trade off between query performance and time to prepare for query In g

6、eneral, more time spent preparing data yields better query performance Pre-aggregation Pre-calculation of measures Predictable queries are easier to optimize Ad-hoc queries are more difficult to optimize,Predictable vs. Ad-Hoc,Predictable query environment Predefined reports Predefined calculations

7、Less exploration of data Ad-hoc query environment Users define reports Users access any data Users define calculations More users amplify this effect,Optimizing Static Reporting,Report sales by customer, product and month,Report profit by quarter, region and brand,Optimizing End User Query,Sales by

8、account, product class, trimester,Optimizing End User Query,Optimization becomes more difficult as queries become less predictable Many possible regions of the model Example: 8 dimensions, each with 5 levels = 32,768 potential materialized views Outer joins required for time series calculations Diff

9、icult to pre-materialize all calculations More users amplify the problem,Ad-Hoc Query Optimization,Multidimensional data types are optimized for ad-hoc query Uniform performance across entire logical model Excellent runtime calculation performance,Multidimensional Data Types,Array based measure stor

10、age Measures are prejoined to dimensions Measures share dimensions Optimizations for sparse data Summary management in multidimensional engine Computational scalability Partitioning and parallel processing,With OLAP,Without OLAP,Slower Query,Faster Query,Query Performance,Ad-Hoc Nature of Applicatio

11、n and Query Patterns,Less Ad-Hoc Predictable Queries Simple Calculations,More Ad-Hoc Unpredictable Query Patterns Sophisticated Calculations,Query Performance,With OLAP,Without OLAP,More Time,Less Time,Time To Prepare Data for Query,Ad-Hoc Nature of Application and Query Patterns,Less Ad-Hoc Predict

12、able Queries Simple Calculations,More Ad-Hoc Unpredictable Query Patterns Sophisticated Calculations,Preparation Time,With OLAP,Without OLAP,Slower Query,Faster Query,Less Time To Prepare,More Time to Prepare,Optimization of Ad-Hoc Application,Query Performance,Time To Prepare,Case Study,10 dimensio

13、nal model4,608 level combinations7.54 * 1020 cells,Case Study,Case Study,Case Study,Summary,OLAP Option provides Dimensional model that enhances data navigation and calculation definition experience Enhanced calculation capability Excellent performance for unpredictable and computationally intensive applications,