CREATE CUBE

The CREATE CUBE statement defines the structure of a new local cube. 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 cubes.

The cube is not populated when the CREATE CUBE 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.

For more information about cubes, see Administrator’s Guide.

BNF

<create-cube-statement > ::= CREATE CUBE <cube name> <open paren>

   DIMENSION <dimension name> [TYPE TIME],

   <hierarchy def> [<hierarchy def>...]

      [{, DIMENSION <dimension name> [TYPE TIME],

      <hierarchy def> [<hierarchy def>...]}...] ,

   MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>]

      [{, MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>] }...]

   [,COMMAND <expression>]

      [,COMMAND <expression>...]

   <close paren>

<dimension name> ::= <legal name>

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

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

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

<level format def> ::= FORMAT_NAME <expression> [FORMAT_KEY <expression>]

<level options def> ::= OPTIONS (<option_list>)

<option_list> :: = <option> [,<option_list>]

<option> ::= UNIQUE | SORTBYNAME | SORTBYKEY

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

<measure format def> ::= FORMAT <expression>

<function name> ::= SUM | MIN | MAX | COUNT

<measure type def> ::= TYPE <supported OLEDB numeric types>

<supported OLEDB numeric types> :: = DBTYPE_I1 | DBTYPE_I2 | DBTYPE_I4 | DBTYPE_I8 | DBTYPE_UI1 | DBTYPE_UI2 | DBTYPE_UI4 | DBTYPE_UI8 | DBTYPE_R4 | DBTYPE_R8 | DBTYPE_CY | DBTYPE_DECIMAL | DBTYPE_NUMERIC | DBTYPE_DATE

Remarks

• In the DIMENSION clause of the CREATE CUBE statement, the name given to a level of TYPE ALL applies the specified name to the ALL member rather than the ALL level; the ALL level always has the name (All). For example, the clause LEVEL [All Customers] TYPE ALL creates a level named (All) containing a single member named [All Customers]. There is no [All Customers] level.
• If the <expression> value of the COMMAND clause has spaces, the whole expression should be surrounded by brackets. It is not recommended to use quotation marks for this purpose because the body of the command might include quotation marks. (OLAP Services supports nested brackets but not nested quotation marks.)

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 OPTIONS (SORTBYKEY, UNIQUE),

   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

   TYPE DBTYPE_UI4

)