Transcript Oracle OLAP 11g

1
<Insert Picture Here>
Enhancing the Performance and Analytic Content
of the Data Warehouse Using Oracle OLAP Option
Bud Endress, Director of Product Management - OLAP
September 5, 2008
The following is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
3
OLAP in the Data Warehouse
Use Oracle OLAP to enhance your data warehouse
• Simplified summary management
• ‘Speed of thought’ query performance
• Advanced time series analysis and analytic content
• Centralized management of data, meta data, calculations and
security
4
OLAP in the Data Warehouse
Every data warehouse can benefit from Oracle OLAP
• Every business intelligence tool accesses summary data
• Every business user wants excellent query performance in both
static and exploratory BI applications
• Every business user will benefit from rich analytic content
5
OLAP in the Data Warehouse
Embedded Oracle OLAP is preferred by IT to external
solutions
• Use the database you already own
• Use the BI tools they already own
• Use Oracle skills you already have
• Embedded Oracle OLAP is secure and enterprise ready
6
OLAP in the Data Warehouse
• Ask yourself the following questions
• Do you use business intelligence tools?
• Oracle BI EE, Business Objects, Cognos,
MicroStrategy, etc.?
• Would business users benefit from
• Significantly improved query performance?
• Rich analytic content?
• Would IT benefit from
• Fast, efficient updates of data sets?
• Fewer servers to manage?
• Consolidating stand alone OLAP servers into the
database?
7
Oracle OLAP Option
• A summary management solution for SQL
based business intelligence applications
• An alternative to table-based materialized
views, offering improved query performance
and fast, incremental update
• A full featured multidimensional OLAP
server
• Excellent query performance for ad-hoc /
unpredictable query
• Enhances the analytic content of Business
intelligence application
• Fast, incremental updates of data sets
8
OLAP Option
• An embedded OLAP solution
• Runs within Oracle Database
Enterprise Edition
• Data are stored in Oracle data
files
• Meta data in the Oracle Data
Dictionary
• Fully compatible with RAC / Grid
computing
9
OLAP Option
• A secure solution
• Oracle users are OLAP users
• SQL GRANT / REVOKE on
OLAP cubes and dimensions
• Compatible with Virtual Private
Database
• Fine Grained Cube Security
Oracle Authentication
SQL Cube Access Control
Virtual Private Database
Fine Grained Cube Security
10
OLAP Option
• An open solution
• Oracle cubes and dimensions
are queried using
• SQL
• PL / SQL
• Oracle OLAP API
• Transparent access as cubeorganized materialized view
• SQL
SELECT
time,
product,
customer,
sales_ytd
FROM
sales_cube
11
OLAP Option
• A content rich solution
•
•
•
•
•
•
•
Rich aggregations
Time series
Indices and market shares
Rankings
Forecasting
Allocations
Statistics
• Calculations are embedded
in the database
• Centrally managed for consistency
• Accessible by any application
12
OLAP Option
• OLAP cubes are optimized for ad-hoc, exploratory
usage patterns
Predictable query environment
• Predefined reports
• Predefined calculations
• Less exploration of data
Static Reporting
Exploratory query environment
•
•
•
•
Users define reports
Users access any data
Users define calculations
More users amplify this effect
Self Service Reporting
and Analysis
13
OLAP Option
• OLAP cubes offer excellent
performance for unpredictable
query patterns
• Appropriate for both
static and exploratory
reporting
• Advantages increase
as reporting becomes
more exploratory
14
OLAP Option
• OLAP Cubes offer fast,
incremental updates of data
sets
• Manage all summaries in a
single database object
• Fast, incremental
materialized view refresh
• Incremental / fast
aggregation
• Cost-based
aggregation
15
OLAP Option
• OLAP Cubes offer fast,
incremental updates of data
sets
• Manage all summaries in a
single database object
• Fast, incremental
materialized view refresh
• Incremental / fast
aggregation
• Cost-based
aggregation
16
OLAP Option
• One cube can be used as
• A summary management solution to SQL-based business
intelligence applications as cube-organized materialized
views
• A analytically rich data source to SQL-based business
intelligence applications as SQL cube-views
• A full-featured multidimensional cube, servicing dimensionally
oriented business intelligence applications
17
SQL Query of OLAP Cubes
BI Application
BI Application
SQL
SQL
Cube
Materialized
Views
Automatic
Query
Rewrite
Cube Views
Oracle Cube
18
One Cube, Dimensional or SQL Tools
Single version of the truth
Metadata
Data
Business Rules
OLAP Query
Extract, Load
& Transform (ELT)
SQL Query
Centrally managed data, meta data and business rules
19
Cube Organized Materialized Views
• Transparently enhance the query performance of BI
applications
• Data is managed in an Oracle cube
• Fast query
• Fast refresh
• Manage a single cube instead of 10’s, 100’s or 1,000’s of
table-based materialized views
• Applications query base / detail relational tables
• Oracle automatically rewrites SQL queries to OLAP cubes
• Access to summary data in the cube is fully transparent
20
Materialized Views
Typical MV Architecture Today
BI
Application
• Users expect excellent
query response
for all
Automatic
Query
SELECT
summary queries
Rewrite
SUM(sales)
GROUP BY
• Might require 10’s, 100’s or
quarter,
brand,even 1,000’s of materialized
region,
views
channel
Summary Data:
• Difficult to manage
• Longer build and update times
Collections of Materialized Views
Fact Table: Sales by Day, Item, Customer and Channel
21
Cube-Organized Materialized Views
Automatic Query Rewrite
BI
Application
SELECT
SUM(sales)
GROUP BY
quarter,
brand,
region,
channel
•
Automatic
Query Rewrite
•
•
•
A single cube manages
summaries for all
groupings in the model
A cube can be represented
as a cube-organized
materialized view
Oracle automatically
rewrites summary queries
to the cube
A singe cube can replace
10’s, 100’s or 1,000’s of
materialized views
Fact Table: Sales by Day, Item, Customer and Channel
22
Typical query issued by
Oracle Business
Intelligence Enterprise
Edition.
Query is automatically
rewritten by Oracle to
access summary data in the
cube-organized materialized
view.
23
Cube-Organized Materialized Views
Fast, Incremental MV Refresh
BI
Application
•
SELECT
SUM(sales)
GROUP BY
quarter,
brand,
region,
channel
MV Refresh
A single cube is refreshed
using MV refresh system
• Fast, incremental
update from MV logs.
• Fast, incremental
aggregation within the
cube.
• Efficient management
of sparse data sets.
• Replaces 10’s, 100’s
or even 1,000’s of
table-based MVs
Fact Table: Sales by Day, Item, Customer and Channel
24
Cube Organized Materialized Views
• An excellent summary management solution for
business intelligence tools such as BI EE,
MicroStrategy, Cognos and Business Objects
• Cube organized materialized views are similar to
materialized views on pre-built tables
• Cube organized materialized views are meta data only – they
do not store data; data comes from the cube
• A common implementation will be to leave detail data
in tables and create the cube at aggregate levels
• E.g. tables with day, customer and cube with month, zip code
25
Cube Organized Materialized Views
Case Study
• Compares performance of table-based materialized
views with cube-organized materialized views with
goals of:
• Improving query performance of SQL-based BI tools
• Reducing build/update times
• Source data
• Fast moving consumer goods company data
• 7 dimensions
• 20 million fact rows
26
Cube Organized Materialized Views
Case Study
• Methodology
• Indexes and materialized views were created as per Oracle
SQL Access Advisor recommendations.
• 124 materialized views
• 198 indexes
• Oracle cube and cube-organized materialized views were
created by DBA.
• 1 compressed cube
• Pre-aggregated to 20%
• 1469 test queries
27
Cube Organized Materialized Views
Case Study
• Measurements
• Time to load data and prepare it for query
• MVs: Create MVs, create indexes and compute statistics
• Cube: Load data and aggregate.
• Query performance
• Run the same 1469 queries against MVs and cube.
28
Cube Organized Materialized Views
Case Study Results
180
160
140
120
Time in minutes to
100
Load and Aggregate
Query
80
60
40
20
0
Table MVs
Cube MV
29
OLAP Cubes Views
SQL Query of Oracle Cubes
Cube is represented as star
schema of relational views
• Dimension and fact views
• Detail and summary fact rows
• Rich analytic fact columns
OLAP Cube Includes
• All levels of summarization
• Rich analytical calculations
31
Empowering Any SQL-Based Tool
Simple SQL Queries Advanced Cube Content
Application Express on Oracle OLAP
SELECT cu.long_description customer,
f.profit_rank_cust_sh_parent,
f.profit_share_cust_sh_parent,
f.profit_rank_cust_sh_level,
f.profit,
f.gross_margin
FROM time_calendar_view t,
product_primary_view p,
customer_shipments_view cu,
channel_primary_view ch,
units_cube_view f
WHERE
AND
AND
AND
AND
AND
AND
AND
AND
t.level_name = 'CALENDAR_YEAR'
t.calendar_year = 'CY2006'
p.dim_key = 'TOTAL'
cu.parent = 'TOTAL'
ch.dim_key = 'TOTAL'
t.dim_key = f.TIME
p.dim_key = f.product
cu.dim_key = f.customer
ch.dim_key = f.channel;
32
Oracle Business
Intelligence Enterprise
Edition querying time series
calculations directly from
an Oracle cube using SQL.
Oracle cubes can make any
BI tool smarter and faster.
33
SQL issued by Oracle BI EE
against views of Oracle
cube and dimensions.
New Joined Cube Scan row
source pushes joins into
the cube and accesses
summary data and
calculations.
34
Oracle OLAP Option
Summary
• Enhances the performance and analytic content of
SQL-based business intelligence applications.
• May be used as:
• A summary management solution with cube-organized
materialized views.
• A full-featured multidimensional cube and calculation engine
queried directly with SQL
• Embedded in the Oracle database instance and
storage.
• Safe, secure and manageable.
• Fully compatible with Grid Computing/Real Application
Clusters.
36
For More Information
• Oracle.com
• http://www.oracle.com/solutions/business_intelligence/
olap.html
• Oracle Technology Network:
• http://www.oracle.com/technology/products/bi/olap/index.html
• Product Discussion Forum:
• http://forums.oracle.com/forums/forum.jspa?forumID=16
37
38
39