Querying the Cube Fact View
Download
Report
Transcript Querying the Cube Fact View
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
• SQL query interface allows any application query cubes and
dimensions without having to learn and use OLAP languages
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.
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
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
Finding Cube Views
SELECT view_name
FROM user_views
WHERE view_name LIKE 'UNITS_CUBE%';
VIEW_NAME
-----------------------------UNITS_CUBE_VIEW
Cube fact view
Cube Fact View
DESC units_cube_view;
Name
Null
------------------------- -------TIME
CHANNEL
CUSTOMER
PRODUCT
UNITS
SALES
COST
SALES_PRIOR_PERIOD
SALES_DIFF_PRIOR_PERIOD
SALES_PCT_DIFF_PRIOR_PERIOD
Type
-------------VARCHAR2(100)
VARCHAR2(100)
VARCHAR2(100)
VARCHAR2(100)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
Keys (by default, named after
dimensions)
Facts
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
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
Cube Fact View
DESC units_cube_view;
Name
Null
------------------------- -------TIME
CHANNEL
CUSTOMER
PRODUCT
UNITS
SALES
COST
SALES_PRIOR_PERIOD
SALES_DIFF_PRIOR_PERIOD
SALES_PCT_DIFF_PRIOR_PERIOD
Type
-------------VARCHAR2(100)
VARCHAR2(100)
VARCHAR2(100)
VARCHAR2(100)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
Keys
Stored facts
Calculated facts
Cube Fact View
SELECT time,
product,
customer,
channel,
units
FROM units_cube_view
WHERE rownum < 15;
TIME
--------TOTAL
CY1999
CY2003
CY1998
CY2005
CY2006
CY2004
CY2000
CY2002
CY2001
CY2000.Q1
CY2001.Q2
CY2001.Q3
CY2005.Q3
PRODUCT
------TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
CUSTOMER
-------TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
TOTAL
CHANNEL
UNITS
------- ---------TOTAL
4000968
TOTAL
330425
TOTAL
534069
TOTAL
253816
TOTAL
565718
TOTAL
584929
TOTAL
587419
TOTAL
364233
TOTAL
364965
TOTAL
415394
TOTAL
88484
TOTAL
97346
TOTAL
105704
TOTAL
138953
Calendar Year aggregates
Calendar Quarter aggregates
Totals for Product, Customer
and Channel dimensions
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.)
Dimension Views
desc customer_view;
Name
------------------DIM_KEY
LEVEL_NAME
LONG_DESCRIPTION
SHORT_DESCRIPTION
Null
Type
-------- -------------VARCHAR2(100)
VARCHAR2(30)
VARCHAR2(100)
VARCHAR2(100)
Primary Key
Attributes
Dimension Views
SELECT *
FROM customer_view
WHERE rownum < 15;
Detail and
aggregate keys
DIM_KEY
LEVEL_NAME
--------------------------TOTAL
TOTAL_CUSTOMER
EMEA
REGION
AMER
REGION
APAC
REGION
JPN
WAREHOUSE
CAN
WAREHOUSE
ITA
WAREHOUSE
UK
WAREHOUSE
AUS
WAREHOUSE
SPA
WAREHOUSE
FRA
WAREHOUSE
US
WAREHOUSE
SIN
WAREHOUSE
GER
WAREHOUSE
Level
LONG_DESCRIPTION
----------------All Customers
Europe
North America
Asia Pacific
Japan
Canada
Italy
United Kingdom
Australia
Spain
France
United States
Singapore
Germany
SHORT_DESCRIPTION
----------------All Customers
Europe
North America
Asia Pacific
Japan
Canada
Italy
United Kingdom
Australia
Spain
France
United States
Singapore
Germany
Attributes
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.)
Hierarchy Views
desc time_calendar_view;
Name
Null
Type
------------------ -------- -------------DIM_KEY
VARCHAR2(100)
PARENT
VARCHAR2(100)
MONTH
VARCHAR2(100)
CALENDAR_QUARTE
VARCHAR2(100)
CALENDAR_YEAR
VARCHAR2(100)
ALL_TIMES
VARCHAR2(100)
LEVEL_NAME
VARCHAR2(30)
LONG_DESCRIPTION
VARCHAR2(100)
SHORT_DESCRIPTION
VARCHAR2(100)
END_DATE
DATE
TIME_SPAN
NUMBER
DIM_ORDER
NUMBER
HIER_ORDER
NUMBER
Primary Key
Parent of primary key
Ancestors
Attributes
Hierarchy Views
SELECT dim_key,
level_name,
parent,
month,
calendar_quarter,
calendar_year
FROM time_calendar_view
WHERE calendar_year = 'CY2007';
Hierarchy Views
DIM_KEY
LEVEL_NAME
----------------------------CY2007
CALENDAR_YEAR
CY2007.Q3
CALENDAR_QUARTER
CY2007.Q2
CALENDAR_QUARTER
CY2007.Q1
CALENDAR_QUARTER
CY2007.Q4
CALENDAR_QUARTER
2007.03
MONTH
2007.01
MONTH
2007.05
MONTH
2007.06
MONTH
2007.08
MONTH
2007.10
MONTH
2007.09
MONTH
2007.12
MONTH
2007.02
MONTH
2007.07
MONTH
2007.04
MONTH
2007.11
MONTH
Detail and
aggregate
keys
Level
PARENT
---------9999
CY2007
CY2007
CY2007
CY2007
CY2007.Q1
CY2007.Q1
CY2007.Q2
CY2007.Q2
CY2007.Q3
CY2007.Q4
CY2007.Q3
CY2007.Q4
CY2007.Q1
CY2007.Q3
CY2007.Q2
CY2007.Q4
Parent
MONTH
CALENDAR_QUARTER CALENDAR_YEAR
-------- ---------------- ------------CY2007
CY2007.Q3
CY2007
CY2007.Q2
CY2007
CY2007.Q1
CY2007
CY2007.Q4
CY2007
2007.03 CY2007.Q1
CY2007
2007.01 CY2007.Q1
CY2007
2007.05 CY2007.Q2
CY2007
2007.06 CY2007.Q2
CY2007
2007.08 CY2007.Q3
CY2007
2007.10 CY2007.Q4
CY2007
2007.09 CY2007.Q3
CY2007
2007.12 CY2007.Q4
CY2007
2007.02 CY2007.Q1
CY2007
2007.07 CY2007.Q3
CY2007
2007.04 CY2007.Q2
CY2007
2007.11 CY2007.Q4
CY2007
Ancestors
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
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)
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
SQL Processing
APPLICATION
Returns data through
OCI or JDBC
SELECT Statement
SQL functions, filters and
joins to tables
SQL ‘ENGINE’
SQL will finish the query as
needed
Data (as rows)
SELECT Statement
CUBE_TABLE
Data
Filter Translation
CUBE
Filters, cube joins,
aggregation and calculation
Return the minimum
number of rows as required
by the query
Let the cube do as much
work as possible
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
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
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')
AND p.buyer = 'MONTURIO'
AND cu.level_name = 'TOTAL_CUSTOMER'
AND ch.level_name = 'TOTAL_CHANNEL‘
20,352 rows remaining after this filter
8,480 rows remaining after this filter
80 rows remaining after this filter
20 rows remaining after this filter
Aggregation
• The cube will aggregate data for all dimension
members
• There is no need for GROUP BY on keys
Examples of Dimensional Queries
• Leveraging elements of the dimensional model for
multidimensional queries
• Level based queries
• Drilling
• Ancestor and descendant based queries
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;
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;
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;
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;
Explain Plan Examples
Explain Plans for Cubes
Oracle OLAP
in 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
For More Information
http://search.oracle.com
oracle olap
or
oracle.com/database