Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CUBE_TABLE

Syntax

Description of cube_table.gif follows
Description of the illustration cube_table.gif

Purpose

CUBE_TABLE extracts data from a cube or dimension and returns it in the two-dimensional format of a relational table, which can be used by SQL-based applications.

The function takes a single VARCHAR2 argument. The optional hierarchy clause enables you to specify a dimension hierarchy. A cube can have multiple hierarchy clauses, one for each dimension.

You can generate these different types of tables:

CUBE_TABLE is a table function and is always used in the context of a SELECT statement with this syntax:

SELECT ... FROM TABLE(CUBE_TABLE('arg'));

See Also:

Oracle OLAP User's Guide for information about dimensional objects and about the tables generated by CUBE_TABLE.

Examples

The following SELECT statement generates a dimension table of CHANNEL in the GLOBAL schema.

SELECT *
  FROM TABLE(CUBE_TABLE('global.channel'));
 
DIM_KEY  LEVEL_NAME      LONG_DESCRIP SHORT_DESCRI TOTAL_CHANNEL_ID CHANNEL_ID
-------- --------------- ------------ ------------ ---------------- ----------
1        TOTAL_CHANNEL   All Channels All Channels                1
2        CHANNEL         Direct Sales Direct Sales                1          2
3        CHANNEL         Catalog      Catalog                     1          3
4        CHANNEL         Internet     Internet                    1          4

The next statement generates a cube table of UNITS_CUBE. It restricts the table to the MARKET_ROLLUP and CALENDAR hierarchies.

SELECT *
  FROM TABLE(CUBE_TABLE(
  'global.units_cube HIERARCHY customer market_rollup HIERARCHY time calendar'));
 
     SALES      UNITS       COST TIME     CUSTOMER PRODUCT  CHANNEL
---------- ---------- ---------- -------- -------- -------- --------
 134109248     330425  124918967 2        7        1        1
32275009.5      77425   30255208 10       7        1        1
10768750.7      25780 10058324.5 36       7        1        1
 109261.64        278  101798.32 36       5        1        1
  22371.47         53   20887.54 36       36       1        1
. . .