Jerry Held

1
(No Transcript)
2
Bud EndressDirector of Product Management,
OLAP Oracle Corporation
3
Oracle OLAP OptionWhen to use the OLAP Option to
Enhance Content and Performance of Business
Intelligence Applications
4
Topics

• Deployment options
• Oracle business intelligence platform
• Enhancing application with analytic content
• Performance of multidimensional data types

5
Typical Deployment Options
Query and Analysis
6
Oracle Deployment Options
7
Oracle Business IntelligencePlatform and Tools
8
Oracle Business Intelligence
9
Oracle OLAP Option
10
OracleBI Tools

• Relational Model
• OracleBI Discoverer
• Reports, HTML Database
• Dimensional Model
• OracleBI Spreadsheet Add-in
• Oracle Discoverer Plus
• OracleBI Beans
• Viewing
• OracleBI Discoverer Viewer
• Oracle Portal

11
(No Transcript)
12
(No Transcript)
13
Logical Models
14
Choice of Model
Query and Analysis
Reporting
Relational Model
15
Dimensional Model

• Promotes ad-hoc navigation and calculation
  definition
• Easily understood by end users
• Sales 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

16
Dimensional Model
17
D E M O N S T R A T I O N
Dimensional Model
18
Implementation
19
Choice of Implementation
Query and Analysis
Reporting
Relational Model
20
Multidimensional Data Types

• Enhanced calculations
• User-defined functions
• Compound aggregations
• Allocations
• Forecasts
• Data flows

21
Optimizing Performance

• There is trade off between query performance and
  time to prepare for query
• In general, 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

22
Predictable vs. Ad-Hoc

• Predictable query environment
• Predefined reports
• Predefined calculations
• Less exploration of data
• Ad-hoc query environment
• Users define reports
• Users access any data
• Users define calculations
• More users amplify this effect

23
Optimizing Static Reporting
24
Optimizing End User Query
Sales by account, product class, trimester
25
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
• Difficult to pre-materialize all calculations
• More users amplify the problem

26
Ad-Hoc Query Optimization

• Multidimensional data types are optimized for
  ad-hoc query
• Uniform performance across entire logical model
• Excellent runtime calculation performance

27
Multidimensional Data Types

• Array based measure storage
• Measures are prejoined to dimensions
• Measures share dimensions
• Optimizations for sparse data
• Summary management in multidimensional engine
• Computational scalability
• Partitioning and parallel processing

28
Query Performance
Slower Query
Without OLAP
Query Performance
With OLAP
Faster Query
Less Ad-Hoc Predictable Queries Simple
Calculations
More Ad-Hoc Unpredictable Query
Patterns Sophisticated Calculations
Ad-Hoc Nature of Application and Query Patterns
29
Time To Prepare Data for Query
More Time
Without OLAP
Preparation Time
With OLAP
Less Time
Less Ad-Hoc Predictable Queries Simple
Calculations
More Ad-Hoc Unpredictable Query
Patterns Sophisticated Calculations
Ad-Hoc Nature of Application and Query Patterns
30
Optimization of Ad-Hoc Application
Slower Query
Without OLAP
Query Performance
With OLAP
Faster Query
Less Time To Prepare
More Time to Prepare
Time To Prepare
31
Case Study

• 10 dimensional model
• 4,608 level combinations
• 7.54 1020 cells

32
Case Study
33
Case Study
34
Case Study
35
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

36
(No Transcript)