Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-01 |
|
|
View PDF |
Syntax
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:
A cube table contains a key column for each dimension and a column for each measure and calculated measure in the cube. To create a cube table, you can specify the cube with or without a cube hierarchy clause. For a dimension with multiple hierarchies, this clause limits the return values to the dimension members and levels in the specified hierarchy. Without a hierarchy clause, all dimension members and all levels are included.
A dimension table contains a key column, and a column for each level and each attribute. All dimension members and all levels are included in the table. To create a dimension table, specify the dimension without a dimension hierarchy clause.
A hierarchy table contains all the columns of a dimension table plus a column for the parent member and a column for each source level. Any dimension members and levels that are not part of the named hierarchy are excluded from the table. To create a hierarchy table, specify the dimension with a dimension hierarchy clause.
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 byCUBE_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 . . .