Skip Headers
Previous
Previous
 
Next
Next

Exploring the OLAP Views

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:

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.

Cube Views

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.

Discovering the Names of the Cube Views

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

Discovering the Columns of a 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.

Displaying the Contents of a Cube View

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.

Dimension and Hierarchy Views

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:

You can use dimension views and hierarchy views like dimension tables in a star schema.

Discovering the Names of Dimension and Hierarchy Views

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

Discovering the Columns of a Dimension 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

Displaying the Contents of a Dimension View

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.

Discovering the Columns of a Hierarchy View

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)

Displaying the Contents of a Hierarchy View

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.