CUBE_TABLE

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'));

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
      .
      .
      .