Home > Querying Dimensional Objects > Exploring the OLAP Views
![]() Previous |
![]() Next |
Oracle OLAP adds power to your SQL applications by providing extensive analytic content and fast query response times. A SQL query interface enables any application to query cubes and dimensions without any knowledge of OLAP.
The OLAP option automatically generates a set of relational views on cubes, dimensions, and hierarchies. SQL applications query these views to display the information-rich contents of these objects to analysts and decision makers. You can also create custom views that follow the structure expected by your applications, using the system-generated views like base tables.
The system-generated views are created in the same schema as the analytic workspace. Oracle OLAP provides three types of views:
Cube views
Dimension views
Hierarchy views
These views are related in the same way as fact and dimension tables in a star schema. Cube views serve the same function as fact tables, and hierarchy views and dimension views serve the same function as dimension tables. Typical queries join a cube view with either a hierarchy view or a dimension view.
Each cube has a cube view that presents the data for all the measures and calculated measures in the cube. You can use a cube view like a fact table in a star or snowflake schema. However, the cube view contains all the summary data in addition to the detail level data.
The default name for a cube view is cube
_VIEW
. To find the view for UNITS_CUBE
in your schema, you might issue a query like this one:
SELECT view_name FROM user_views WHERE view_name LIKE 'UNITS_CUBE%'; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW
The next query returns the names of all the cube views in your schema from USER_CUBE_VIEWS
:
SELECT view_name FROM user_cube_views; VIEW_NAME ------------------------------ UNITS_CUBE_VIEW PRICE_CUBE_VIEW
Like a fact table, a cube view contains a column for each measure, calculated measure, and dimension in the cube. In the following example, UNITS_CUBE_VIEW
has columns for the SALES
, UNITS
, and COST
measures, for several calculated measures on SALES
, and for the TIME
, CUSTOMER
, PRODUCT
, and CHANNEL
dimensions.
DESCRIBE units_cube_view Name Null? Type ----------------------------------------- -------- ---------------------------- SALES NUMBER UNITS NUMBER COST NUMBER SALES_PP NUMBER SALES_CHG_PP NUMBER SALES_PCTCHG_PP NUMBER SALES_PROD_SHARE_PARENT NUMBER SALES_PROD_SHARE_TOTAL NUMBER SALES_PROD_RANK_PARENT_PP NUMBER TIME VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) CHANNEL VARCHAR2(100)
The USER_CUBE_VIEW_COLUMNS
data dictionary view describes the columns of a cube view, as shown by the following query.
SELECT column_name, column_type FROM user_cube_view_columns WHERE view_name = 'UNITS_CUBE_VIEW'; COLUMN_NAME COLUMN_TYPE ------------------------------ -------------- SALES MEASURE UNITS MEASURE COST MEASURE SALES_PP MEASURE SALES_CHG_PP MEASURE SALES_PCTCHG_PP MEASURE SALES_PROD_SHARE_PARENT MEASURE SALES_PROD_SHARE_TOTAL MEASURE SALES_PROD_RANK_PARENT_PP MEASURE TIME KEY CUSTOMER KEY PRODUCT KEY CHANNEL KEY 13 rows selected.
You can display the contents of a cube view quickly with a query like this one. All levels of the data are contained in the cube, from the detail level to the top.
SELECT sales, units, time, customer, product, channel FROM units_cube_view WHERE ROWNUM < 15; SALES UNITS TIME CUSTOMER PRODUCT CHANNEL ---------- ---------- ---------- ---------- ---------- -------- 1120292752 4000968 TOTAL TOTAL TOTAL TOTAL 134109248 330425 CY1999 TOTAL TOTAL TOTAL 130276514 534069 CY2003 TOTAL TOTAL TOTAL 100870877 253816 CY1998 TOTAL TOTAL TOTAL 136986572 565718 CY2005 TOTAL TOTAL TOTAL 140138317 584929 CY2006 TOTAL TOTAL TOTAL 144290686 587419 CY2004 TOTAL TOTAL TOTAL 124173522 364233 CY2000 TOTAL TOTAL TOTAL 92515295 364965 CY2002 TOTAL TOTAL TOTAL 116931722 415394 CY2001 TOTAL TOTAL TOTAL 31522409.5 88484 CY2000.Q1 TOTAL TOTAL TOTAL 27798426.6 97346 CY2001.Q2 TOTAL TOTAL TOTAL 29691668.2 105704 CY2001.Q3 TOTAL TOTAL TOTAL 32617248.6 138953 CY2005.Q3 TOTAL TOTAL TOTAL 14 rows selected.
Each dimension has one dimension view plus a hierarchy view for each hierarchy associated with the dimension. For example, a Time dimension might have these three views:
Time dimension view
Calendar hierarchy view
Fiscal hierarchy view
You can use dimension views and hierarchy views like dimension tables in a star schema.
USER_CUBE_DIM_VIEWS
identifies the dimension views for all dimensions. The default name for a dimension view is dimension
_VIEW
.
SELECT * FROM user_cube_dim_views; DIMENSION_NAME VIEW_NAME ------------------------------ ------------------------------ PRODUCT PRODUCT_VIEW CUSTOMER CUSTOMER_VIEW CHANNEL CHANNEL_VIEW TIME TIME_VIEW
USER_CUBE_HIER_VIEWS
identifies the hierarchy views for all the dimensions. For a hierarchy view, the default name is dimension_hierarchy
_VIEW
. The following query returns the dimension, hierarchy, and view names.
SELECT * FROM user_cube_hier_views ORDER BY dimension_name; DIMENSION_NAME HIERARCHY_NAME VIEW_NAME --------------- --------------- ------------------------------ CHANNEL PRIMARY CHANNEL_PRIMARY_VIEW CUSTOMER SEGMENT CUSTOMER_SEGMENT_VIEW CUSTOMER SHIPMENTS CUSTOMER_SHIPMENTS_VIEW PRODUCT PRIMARY PRODUCT_PRIMARY_VIEW TIME FISCAL TIME_FISCAL_VIEW TIME CALENDAR TIME_CALENDAR_VIEW
Like a dimension table, a dimension view contains a key column, level name, level keys for every level of every hierarchy associated with the dimension, and attribute columns. In the following example, TIME_VIEW
has a column for the dimension keys, the level name, and the dimension attributes.
DESCRIBE time_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER END_DATE DATE LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) TIME_SPAN NUMBER
USER_CUBE_DIM_VIEW_COLUMNS
describes the information in each column, as shown in this query.
SELECT column_name, column_type FROM user_cube_dim_view_columns WHERE view_name ='TIME_VIEW'; COLUMN_NAME COLUMN_TYPE ------------------------------ -------------------- DIM_KEY KEY LEVEL_NAME LEVEL_NAME DIM_ORDER DIM_ORDER END_DATE ATTRIBUTE TIME_SPAN ATTRIBUTE LONG_DESCRIPTION ATTRIBUTE SHORT_DESCRIPTION ATTRIBUTE
The following query displays the level and attributes of each dimension key.
SELECT dim_key, level_name, long_description description, time_span, end_date FROM time_view WHERE dim_key LIKE '%2005%'; DIM_KEY LEVEL_NAME DESCRIPTION TIME_SPAN END_DATE ------------ -------------------- ------------ ---------- --------- CY2005 CALENDAR_YEAR 2005 365 31-DEC-05 CY2005.Q2 CALENDAR_QUARTER Q2.05 91 30-JUN-05 CY2005.Q4 CALENDAR_QUARTER Q4.05 92 31-DEC-05 CY2005.Q3 CALENDAR_QUARTER Q3.05 92 30-SEP-05 CY2005.Q1 CALENDAR_QUARTER Q1.05 90 31-MAR-05 2005.01 MONTH JAN-05 31 31-JAN-05 2005.05 MONTH MAY-05 31 31-MAY-05 2005.07 MONTH JUL-05 31 31-JUL-05 2005.03 MONTH MAR-05 31 31-MAR-05 2005.04 MONTH APR-05 30 30-APR-05 2005.08 MONTH AUG-05 31 31-AUG-05 2005.09 MONTH SEP-05 30 30-SEP-05 2005.02 MONTH FEB-05 28 28-FEB-05 2005.11 MONTH NOV-05 30 30-NOV-05 2005.06 MONTH JUN-05 30 30-JUN-05 2005.10 MONTH OCT-05 31 31-OCT-05 2005.12 MONTH DEC-05 31 31-DEC-05 FY2005 FISCAL_YEAR FY2005 365 30-JUN-05 FY2005.Q4 FISCAL_QUARTER Q4 FY-05 91 30-JUN-05 FY2005.Q1 FISCAL_QUARTER Q1 FY-05 92 30-SEP-04 FY2005.Q2 FISCAL_QUARTER Q2 FY-05 92 31-DEC-04 FY2005.Q3 FISCAL_QUARTER Q3 FY-05 90 31-MAR-05 22 rows selected.
Like the dimension views, the hierarchy views also contain columns for the dimension key, level name, and level keys. However, all of the rows and columns are associated with the dimension keys that belong to the hierarchy.
DESCRIBE time_calendar_view Name Null? Type ----------------------------------------- -------- ---------------------------- DIM_KEY VARCHAR2(100) LEVEL_NAME VARCHAR2(30) DIM_ORDER NUMBER HIER_ORDER NUMBER LONG_DESCRIPTION VARCHAR2(100) SHORT_DESCRIPTION VARCHAR2(100) END_DATE DATE TIME_SPAN NUMBER PARENT VARCHAR2(100) TOTAL VARCHAR2(100) CALENDAR_YEAR VARCHAR2(100) CALENDAR_QUARTER VARCHAR2(100) MONTH VARCHAR2(100)
The following query displays the dimension keys, parent key, and the full ancestry for calendar year 2005.
SELECT dim_key, long_description description, parent, calendar_year year, calendar_quarter quarter, month FROM time_calendar_view WHERE calendar_year='CY2005' ORDER BY level_name, end_date; DIM_KEY DESCRIPTION PARENT YEAR QUARTER MONTH ------------ ------------ ------------ ------------ ------------ ------------ CY2005.Q1 Q1.05 CY2005 CY2005 CY2005.Q1 CY2005.Q2 Q2.05 CY2005 CY2005 CY2005.Q2 CY2005.Q3 Q3.05 CY2005 CY2005 CY2005.Q3 CY2005.Q4 Q4.05 CY2005 CY2005 CY2005.Q4 CY2005 2005 TOTAL CY2005 2005.01 JAN-05 CY2005.Q1 CY2005 CY2005.Q1 2005.01 2005.02 FEB-05 CY2005.Q1 CY2005 CY2005.Q1 2005.02 2005.03 MAR-05 CY2005.Q1 CY2005 CY2005.Q1 2005.03 2005.04 APR-05 CY2005.Q2 CY2005 CY2005.Q2 2005.04 2005.05 MAY-05 CY2005.Q2 CY2005 CY2005.Q2 2005.05 2005.06 JUN-05 CY2005.Q2 CY2005 CY2005.Q2 2005.06 2005.07 JUL-05 CY2005.Q3 CY2005 CY2005.Q3 2005.07 2005.08 AUG-05 CY2005.Q3 CY2005 CY2005.Q3 2005.08 2005.09 SEP-05 CY2005.Q3 CY2005 CY2005.Q3 2005.09 2005.10 OCT-05 CY2005.Q4 CY2005 CY2005.Q4 2005.10 2005.11 NOV-05 CY2005.Q4 CY2005 CY2005.Q4 2005.11 2005.12 DEC-05 CY2005.Q4 CY2005 CY2005.Q4 2005.12 17 rows selected.