Download
1 / 37

Enhancing the Performance and Analytic Content of the Data Warehouse Using Oracle OLAP Option - PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on

Enhancing the Performance and Analytic Content of the Data Warehouse Using Oracle OLAP Option. Bud Endress, Director of Product Management - OLAP September 5, 2008.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Enhancing the Performance and Analytic Content of the Data Warehouse Using Oracle OLAP Option' - jenette-mclaughlin


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

1


Enhancing the performance and analytic content of the data warehouse using oracle olap option

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
OLAP in the Data Warehouse 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.

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 warehouse1
OLAP in the Data Warehouse 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.

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 warehouse2
OLAP in the Data Warehouse 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.

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 warehouse3
OLAP in the Data Warehouse 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.

  • 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
Oracle OLAP Option 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.

  • 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
OLAP Option 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.

  • 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 option1
OLAP Option 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.

  • 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 option2
OLAP Option 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.

  • An open solution

    • Oracle cubes and dimensions are queried using

      • SQL

      • PL / SQL

      • Oracle OLAP API

    • Transparent access as cube-organized materialized view

      • SQL

SELECT

time,

product,

customer,

sales_ytd

FROM

sales_cube

11


Olap option3
OLAP Option 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.

  • A content rich solution

    • Rich aggregations

    • Time series

    • Indices and market shares

    • Rankings

    • Forecasting

    • Allocations

    • Statistics

  • Calculations are embeddedin the database

    • Centrally managed for consistency

    • Accessible by any application

12


Olap option4

Predictable query environment 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.

Predefined reports

Predefined calculations

Less exploration of data

Exploratory query environment

Users define reports

Users access any data

Users define calculations

More users amplify this effect

OLAP Option

  • OLAP cubes are optimized for ad-hoc, exploratory usage patterns

Static Reporting

Self Service Reportingand Analysis

13


Olap option5

OLAP cubes offer excellent performance for unpredictable query patterns

Appropriate for bothstatic and exploratoryreporting

Advantages increaseas reporting becomesmore exploratory

OLAP Option

14


Olap option6

OLAP Cubes offer fast, incremental updates of data sets query patterns

Manage all summaries in a single database object

Fast, incrementalmaterialized view refresh

Incremental / fastaggregation

Cost-basedaggregation

OLAP Option

15


Olap option7

OLAP Cubes offer fast, incremental updates of data sets query patterns

Manage all summaries in a single database object

Fast, incrementalmaterialized view refresh

Incremental / fastaggregation

Cost-basedaggregation

OLAP Option

16


Olap option8
OLAP Option query patterns

  • 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
SQL Query of OLAP Cubes query patterns

BI Application

BI Application

SQL

SQL

CubeMaterializedViews

Cube Views

Automatic Query Rewrite

Oracle Cube

18


One cube dimensional or sql tools single version of the truth

Metadata query patterns

Data

Business Rules

One Cube, Dimensional or SQL ToolsSingle version of the truth

OLAP Query

Extract, Load

& Transform (ELT)

SQL Query

Centrally managed data, meta data and business rules

19


Cube organized materialized views
Cube Organized Materialized Views query patterns

  • 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
Materialized Views query patternsTypical MV Architecture Today

BI Application

  • Users expect excellent query response for all summary queries

    • Might require 10’s, 100’s or even 1,000’s of materialized views

    • Difficult to manage

    • Longer build and update times

Automatic Query Rewrite

SELECT SUM(sales)GROUP BY quarter, brand,region, channel

Summary Data: Collections of Materialized Views

Fact Table: Sales by Day, Item, Customer and Channel

21


Cube organized materialized views automatic query rewrite
Cube-Organized Materialized Views query patternsAutomatic Query Rewrite

BI Application

  • 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

Automatic Query Rewrite

SELECT SUM(sales)GROUP BY quarter, brand,region, channel

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
Cube-Organized Materialized Views Enterprise Edition. Fast, Incremental MV Refresh

BI Application

  • 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

SELECT SUM(sales)GROUP BY quarter, brand,region, channel

MV Refresh

Fact Table: Sales by Day, Item, Customer and Channel

24


Cube organized materialized views1
Cube Organized Materialized Views Enterprise Edition.

  • 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
Cube Organized Materialized Views Enterprise Edition. 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 study1
Cube Organized Materialized Views Enterprise Edition. 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 study2
Cube Organized Materialized Views Enterprise Edition. 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
Cube Organized Materialized Views Enterprise Edition. Case Study Results

Time in minutes to

29


Olap cubes views sql query of oracle cubes
OLAP Cubes Views Enterprise Edition. 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
Empowering Any SQL-Based Tool Enterprise Edition. 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 t.level_name = 'CALENDAR_YEAR' AND t.calendar_year = 'CY2006' AND p.dim_key = 'TOTAL' AND cu.parent = 'TOTAL' AND ch.dim_key = 'TOTAL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND 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
Oracle OLAP Option dimensions.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
For More Information dimensions.

  • 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


Q dimensions.

&

A

38


39 dimensions.


ad