CREATE CUBE Statement

The purpose of the CREATE CUBE statement is to define a new cube structure. This statement shares much of the syntax and semantics of SQL-92 syntax and shares the semantics of the CREATE TABLE statement. However, the CREATE CUBE statement contains syntax specific to data cubes.

The CREATE CUBE statement defines the structure of a new cube. However, the cube is not populated when this statement is executed. The cube is populated using the INSERT INTO statement in a manner similar to the SQL-92 approach for creating and populating tables.

A cube is defined as a collection of measures, each having an aggregate function, and a set of dimensions, each containing one or more levels. There may be special dimensions such as time dimensions and special levels (such as ALL and various time units).

Optionally, dimensions may include multiple hierarchies. Each hierarchy contains levels. The example below shows a time dimension that contains two hierarchies. The remainder of the dimensions each contain a single NULL hierarchy; therefore the hierarchy is not specified in the dimension definition.

BNF

CREATE CUBE <cube name> <open paren>
  DIMENSION <dimension name> [TYPE <dimension type>],
  <hierarchy def> [<hierarchy def>...]
     [{, DIMENSION <dimension name> [TYPE <dimension type>],
     <hierarchy def> [<hierarchy def>...]}...] ,
  MEASURE <measure name> <function def> [<member-properties>]
     [{, MEASURE <measure name> <function def> [<member-properties>]}...]
  <close paren>

<cube name> ::= [[<datasource>.]<database name>.]<legal name>

<dimension name> ::= <legal name>

<dimension type> ::= TIME

<hierarchy def> ::= [HIERARCHY <hierarchy name>,] <level def> [,<level def>...]

<level def> ::= LEVEL <level name> [TYPE <level type>]

<level type> ::= ALL | YEAR | HALFYEAR | QUARTER | MONTH | WEEK | DAY |
  HOUR | MINUTE | SECOND

<function def> ::= FUNCTION <function name>

<function name> ::= SUM | MIN | MAX | COUNT | AVG | STD | VAR

Note   Only SUM, MIN, MAX, and COUNT are supported in this release.

EXAMPLE

CREATE CUBE Sales (
DIMENSION Time TYPE TIME,
   HIERARCHY [Fiscal],
      LEVEL [Fiscal Year] TYPE YEAR,
      LEVEL [Fiscal Qtr] TYPE QUARTER,
      LEVEL [Fiscal Month] TYPE MONTH,
   HIERARCHY [Calendar],
      LEVEL [Calendar Year] TYPE YEAR,
      LEVEL [Calendar Month] TYPE MONTH,
DIMENSION Products,
      LEVEL [All Products] TYPE ALL,
      LEVEL Category,
      LEVEL [Sub Category],
      LEVEL [Product Name],
DIMENSION Geography,
      LEVEL [Whole World] TYPE ALL,
      LEVEL Region,
      LEVEL Country,
      LEVEL City,
MEASURE  [$ Sales]
   FUNCTION SUM 
   FORMAT 'Currency'
MEASURE [Units Sold]
   FUNCTION SUM
)