1 / 44

Using SQL to Query Oracle OLAP Cubes Bud Endress Director of Product Management, OLAP

Using SQL to Query Oracle OLAP Cubes Bud Endress Director of Product Management, OLAP. Powerful OLAP for SQL Applications. Power your SQL based applications with Oracle OLAP performance and analytic content OLAP cubes provide fast refresh, fast query and rich analytic content

yuma
Download Presentation

Using SQL to Query Oracle OLAP Cubes Bud Endress Director of Product Management, OLAP

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using SQL to Query Oracle OLAP CubesBud EndressDirector of Product Management, OLAP

  2. Powerful OLAP for SQL Applications • Power your SQL based applications with Oracle OLAP performance and analytic content • OLAP cubes provide fast refresh, fast query and rich analytic content • SQL query interface allows any application query cubes and dimensions without having to learn and use OLAP languages

  3. Powerful OLAP for SQL Applications • Example: Oracle Application Express • A general purpose database application development tool • Queries OLAP cubes using SQL • Has no specific knowledge of OLAP • Reporting via tables and graphs • OLAP content in Application Express • Time Series • Rankings • Product share • Interactive drilling to children in a hierarchy • Etc.

  4. OLAP Cube Views • The OLAP Option automatically creates a collection of views that allow SQL applications to easily query cubes and dimensions • OLAP cube and dimension views are similar to a star schema • Cube views play the role of fact tables • Dimension and hierarchy views play the role of dimension tables

  5. Finding Cube and Dimension Views • Finding cube and dimension views • Naming conventions • Fact views: cube_VIEW • Dimension views: dimension_VIEW • Hierarchy views: dimension_hierarchy_VIEW • Dictionary queries • user_cube_views • user_cube_dimension_views • user_cube_hierarchy_views

  6. Finding Cube Views SELECT view_name FROM user_views WHERE view_name LIKE 'UNITS_CUBE%'; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW  Cube fact view

  7. Cube Fact View DESC units_cube_view; Name Null Type ------------------------- -------- -------------- TIME VARCHAR2(100) CHANNEL VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) UNITS NUMBER SALES NUMBER COST NUMBER SALES_PRIOR_PERIOD NUMBER SALES_DIFF_PRIOR_PERIOD NUMBER SALES_PCT_DIFF_PRIOR_PERIOD NUMBER Keys (by default, named afterdimensions) Facts

  8. Finding Dimension and Hierarchy Views SELECT view_name FROM user_views WHERE view_name LIKE 'CUSTOMER%' VIEW_NAME ------------------------------ CUSTOMER_VIEW  Dimension view Customer dimension CUSTOMER_SEGMENT_VIEW  Hierarchy view for Segment hierarchy CUSTOMER_SHIPMENTS_VIEW  Hierarchy view for Shipments hierarchy

  9. Cube Fact View • The cube fact view returns all data of the cube • All facts (measures) of the cube, both stored and calculated • All rows, both detailed and summary • All the calculation rules are embedded in the cube • Calculation rules do not need to be expressed in SQL query of the cube

  10. Cube Fact View DESC units_cube_view; Name Null Type ------------------------- -------- -------------- TIME VARCHAR2(100) CHANNEL VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) UNITS NUMBER SALES NUMBER COST NUMBER SALES_PRIOR_PERIOD NUMBER SALES_DIFF_PRIOR_PERIOD NUMBER SALES_PCT_DIFF_PRIOR_PERIOD NUMBER Keys Stored facts Calculated facts

  11. Cube Fact View SELECT time, product, customer, channel, units FROM units_cube_view WHERE rownum < 15; TIME PRODUCT CUSTOMER CHANNEL UNITS --------- ------- -------- ------- ---------- TOTAL TOTAL TOTAL TOTAL 4000968 CY1999 TOTAL TOTAL TOTAL 330425 CY2003 TOTAL TOTAL TOTAL 534069 CY1998 TOTAL TOTAL TOTAL 253816 CY2005 TOTAL TOTAL TOTAL 565718 CY2006 TOTAL TOTAL TOTAL 584929 CY2004 TOTAL TOTAL TOTAL 587419 CY2000 TOTAL TOTAL TOTAL 364233 CY2002 TOTAL TOTAL TOTAL 364965 CY2001 TOTAL TOTAL TOTAL 415394 CY2000.Q1 TOTAL TOTAL TOTAL 88484 CY2001.Q2 TOTAL TOTAL TOTAL 97346 CY2001.Q3 TOTAL TOTAL TOTAL 105704 CY2005.Q3 TOTAL TOTAL TOTAL 138953 Calendar Year aggregates Calendar Quarter aggregates Totals for Product, Customerand Channel dimensions

  12. Dimension Views • Dimension views contain • Rows for all keys of a dimension • A union of all hierarchies • Detail and aggregate level keys • Columns for • The primary key • Attributes of the primary key. E.g, • Short and Long Description • The level of the key • User defined attributes (color, size, etc.)

  13. Dimension Views desc customer_view; Name Null Type ------------------- -------- -------------- DIM_KEY VARCHAR2(100)  Primary Key LEVEL_NAME VARCHAR2(30) LONG_DESCRIPTION VARCHAR2(100) Attributes SHORT_DESCRIPTION VARCHAR2(100)

  14. Dimension Views SELECT * FROM customer_view WHERE rownum < 15; DIM_KEY LEVEL_NAME LONG_DESCRIPTION SHORT_DESCRIPTION --------------------------- ----------------- ----------------- TOTAL TOTAL_CUSTOMER All Customers All Customers EMEA REGION Europe Europe AMER REGION North America North America APAC REGION Asia Pacific Asia Pacific JPN WAREHOUSE Japan Japan CAN WAREHOUSE Canada Canada ITA WAREHOUSE Italy Italy UK WAREHOUSE United Kingdom United Kingdom AUS WAREHOUSE Australia Australia SPA WAREHOUSE Spain Spain FRA WAREHOUSE France France US WAREHOUSE United States United States SIN WAREHOUSE Singapore Singapore GER WAREHOUSE Germany Germany Detail and aggregate keys Level Attributes

  15. Hierarchy Views • Hierarchy views contain • Rows for all keys of a hierarchy • Detail and aggregate level keys • Columns for • The primary key • Hierarchical attributes • The parent of the key • The level of the key • Ancestors of the key • User defined attributes (color, size, etc.)

  16. Hierarchy Views desc time_calendar_view; Name Null Type ------------------ -------- -------------- DIM_KEY VARCHAR2(100)  Primary Key PARENT VARCHAR2(100)  Parent of primary key MONTH VARCHAR2(100) CALENDAR_QUARTE VARCHAR2(100) Ancestors CALENDAR_YEAR VARCHAR2(100) ALL_TIMES VARCHAR2(100) LEVEL_NAME VARCHAR2(30) LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) END_DATE DATE Attributes TIME_SPAN NUMBER DIM_ORDER NUMBER HIER_ORDER NUMBER

  17. Hierarchy Views SELECT dim_key, level_name, parent, month, calendar_quarter, calendar_year FROM time_calendar_view WHERE calendar_year = 'CY2007';

  18. Hierarchy Views DIM_KEY LEVEL_NAME PARENT MONTH CALENDAR_QUARTER CALENDAR_YEAR ----------------------------- ---------- -------- ---------------- ------------- CY2007 CALENDAR_YEAR 9999 CY2007 CY2007.Q3 CALENDAR_QUARTER CY2007 CY2007.Q3 CY2007 CY2007.Q2 CALENDAR_QUARTER CY2007 CY2007.Q2 CY2007 CY2007.Q1 CALENDAR_QUARTER CY2007 CY2007.Q1 CY2007 CY2007.Q4 CALENDAR_QUARTER CY2007 CY2007.Q4 CY2007 2007.03 MONTH CY2007.Q1 2007.03 CY2007.Q1 CY2007 2007.01 MONTH CY2007.Q1 2007.01 CY2007.Q1 CY2007 2007.05 MONTH CY2007.Q2 2007.05 CY2007.Q2 CY2007 2007.06 MONTH CY2007.Q2 2007.06 CY2007.Q2 CY2007 2007.08 MONTH CY2007.Q3 2007.08 CY2007.Q3 CY2007 2007.10 MONTH CY2007.Q4 2007.10 CY2007.Q4 CY2007 2007.09 MONTH CY2007.Q3 2007.09 CY2007.Q3 CY2007 2007.12 MONTH CY2007.Q4 2007.12 CY2007.Q4 CY2007 2007.02 MONTH CY2007.Q1 2007.02 CY2007.Q1 CY2007 2007.07 MONTH CY2007.Q3 2007.07 CY2007.Q3 CY2007 2007.04 MONTH CY2007.Q2 2007.04 CY2007.Q2 CY2007 2007.11 MONTH CY2007.Q4 2007.11 CY2007.Q4 CY2007 Detail andaggregatekeys Level Parent Ancestors

  19. Querying the Cube Fact View • Query the cube view fact like a fact table of a star schema: • Cube fact views are typically joined to dimension or hierarchy views • But, GROUP BY is typically not needed because the cube fact view returns aggregate data computed in the cube

  20. Querying the Cube Fact View • A typical query of the cube fact view SELECT t.long_description time, p.long_description product, cu.long_description customer, ch.long_description channel, f.sales FROM time_view t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL_PRODUCT' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel; SELECT list (like a star query) FROM clause (like a star query) Level filters (instead of GROUP BY) Joins (like a star query)

  21. Querying the Cube Fact View • Guidelines for successful queries • Apply a filter to each dimension • Filters should be compatible with the cube • Allow the cube to aggregate the data

  22. APPLICATION Returns data through OCI or JDBC SELECT Statement SQL ‘ENGINE’ SQL functions, filters and joins to tables SQL will finish the query as needed Data (as rows) SELECT Statement CUBE_TABLE Return the minimum number of rows as required by the query Data Filter Translation CUBE Let the cube do as muchwork as possible Filters, cube joins, aggregation and calculation SQL Processing

  23. Dimension Filters • Typically, there should be a filter on every dimension when querying the cube fact view • Remember, the cube fact view contains both detail and aggregate level data • The cube fact view can return very large numbers of rows • If a filter is not applied to a dimension, rows for all keys – detailed and summary – are returned by the query

  24. Dimension Filter Examples SELECT t.long_description TIME, p.long_description product, cu.long_description customer, ch.long_description channel, sales, sales_pct_diff_prior_peri FROM time_view t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE t.long_description in ('2005','2006') AND p.buyer = 'MONTURIO' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel; Filters using attributes Level filters

  25. Dimension Filters • Example of why dimension filters they are important: • In this small example, the Units Cube is dimensioned by • Time (222 values) • Product (48 values) • Customer (106 values) • Channel (4 values) • Units Cube can potentially return 4,518,144 rows (222 * 48 * 106 * 4) • Every filter dramatically reduces the number of rows returned by the cube WHERE t.long_description in ('2005','2006')  20,352 rows remaining after this filter AND p.buyer = 'MONTURIO'  8,480 rows remaining after this filter AND cu.level_name = 'TOTAL_CUSTOMER'  80 rows remaining after this filter AND ch.level_name = 'TOTAL_CHANNEL‘  20 rows remaining after this filter

  26. Aggregation • The cube will aggregate data for all dimension members • There is no need for GROUP BY on keys

  27. Examples of Dimensional Queries • Leveraging elements of the dimensional model for multidimensional queries • Level based queries • Drilling • Ancestor and descendant based queries

  28. Level Based Queries SELECT t.long_description TIME, p.long_description product, cu.long_description customer, ch.long_description channel, round(f.sales,0) sales FROM time_view t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE t.level_name = 'CALENDAR_YEAR' AND p.level_name = 'TOTAL_PRODUCT' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time;

  29. Drilling Down SELECT t.long_description TIME, p.long_description product, cu.long_description customer, ch.long_description channel, f.sales FROM time_calendar_view t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE p.level_name = 'TOTAL_PRODUCT' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' AND t.parent = 'CY2006' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel ORDER BY time;

  30. Drilling Up SELECT t.long_description TIME, p.long_description product, cu.long_description customer, ch.long_description channel, ROUND(f.sales, 0) sales FROM time_calendar_view_2 t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE t.dim_key = (SELECT DISTINCT calendar_year FROM time_calendar_view WHERE MONTH = '2006.01') AND p.level_name = 'TOTAL_PRODUCT' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.dim_key = f.customer AND ch.dim_key = f.channel;

  31. Descendants Of Member SELECT t.long_description TIME, p.long_description product, cu.long_description customer, ch.long_description channel, ROUND(f.sales, 0) sales FROM time_calendar_view_2 t, product_view p, customer_view cu, channel_view ch, units_cube_view f WHERE t.level_name = 'MONTH' AND t.calendar_year = 'CY2006' AND p.level_name = 'TOTAL_PRODUCT' AND cu.level_name = 'TOTAL_CUSTOMER' AND ch.level_name = 'TOTAL_CHANNEL' 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. Explain Plan Examples

  33. Explain Plans for Cubes

  34. Oracle OLAPin Oracle Database 11g • Improves business intelligence applications • Optimized for fast refresh and ad-hoc query • Database-managed summary management • Embedded BI calculations and metadata • Accessible by any application • SQL or OLAP API based

  35. For More Information http://search.oracle.com oracle olap or oracle.com/database

More Related