CREATE CUBE Statement
  New Information - SQL Server  2000  SP3.

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

CREATE CUBE Statement (Local Cube)
BNF<create-cube-statement > ::= CREATE CUBE <cube name> <open paren> <dimensions def> <measures def> [<command expression>] <close paren>
<dimensions def> :: = DIMENSION <dimension name> [<time def>] [DIMENSION_STRUCTURE <sub_type>] [<hidden def>] [DESCRIPTION <description expression>] <options def> <comma> <hierarchy def  list> 
<time def> ::= TIME | ...

<dimension name> ::= <legal name> 
<sub_type>::= PARENT_CHILD
<hidden_def> ::= HIDDEN
<options def> ::= OPTIONS <open paren> <dim options list> <close paren>

<dim options list> ::= <dim option> [ < comma> <dim options list>]

<dim option> ::= UNIQUE_NAME | UNIQUE_KEY | NOTRELATEDTOFACTTABLE | ALLOWSIBLINGSWITHSAMENAME

<hierarchy def list> ::= <hierarchy def> [ <comma> <hierarchy def  list>

<hierarchy def> ::= [HIERARCHY <hierarchy name> [<hidden_def>] <comma>] <level def>

<hierarchy name> ::= <legal name>
<level def > ::= <parent-child level def> | <normal level def list >

<parent-child level def> ::= [<all level def> <comma>] LEVEL <Template> //only if dimension is parent-child

<normal level def list> ::= <normal level def> [ <comma> <normal level def list> ]

<all level> ::= LEVEL <level name> TYPE ALL
<level name> ::= <legal name>
<normal level def> ::= [<all level> <comma>] LEVEL <level name> [TYPE <level type>] [<level format def>] [<level options def>] [<hidden def>] [<hole def>] [<root member def>] [<custom_rollup_expr def>] [<comma> <level prop def list>]
<level type> ::= YEAR 
| QUARTER 
| MONTH 
| WEEK 
| DAY 
| DAYOFWEEK 
| DATE 
| HOUR 
| MINUTE 
| SECOND
<level format def> ::= FORMAT_NAME <expression> [FORMAT_KEY <expression>]
<level options def> ::= OPTIONS ( [<sort option> <comma>] <level option list> | [<level option list> <comma>] <sort option>)
<level option list> :: = <option> [<comma> <level option list>] 
<sort option> ::= SORTBYNAME 
| SORTBYKEY 
| SORTBYPROPERTY <property name>
<option> ::= UNIQUE 
| UNIQUE_NAME 
| UNIQUE_KEY 
| NOTRELATEDTOFACTTABLE
<hole def> ::= HIDE_MEMBER_IF <hide values>
<hide values> ::= ONLY_CHILD_AND_BLANK_NAME 
| ONLY_CHILD_AND_PARENT_NAME 
| BLANK_NAME 
| PARENT_NAME

<root member def> ::= ROOT_MEMBER_IF <root values>

<root values> ::= ROOT_IF_PARENT_IS_BLANK 
  | ROOT_IF_PARENT_IS_MISSING 
  | ROOT_IF_PARENT_IS_SELF 
  | ROOT_IF_PARENT_IS_BLANK_OR_SELF_OR_MISSING
<custom_rollup_exp> ::= CUSTOM_ROLLUP_EXPRESSION <MDX expression>
<level prop def list > ::= <level prop def > [<comma> <level prop def list>]

<level prop def> ::= PROPERTY <legal name> [<prop type def>] [<hidden def>] [<prop caption def>]

<prop type def> ::= TYPE <prop type value>

<property_type value>::= REGULAR 
  | ID 
  | RELATION_TO_PARENT  
  | ORG_TITLE 
  | CAPTION 
  | CAPTION_SHORT 
  | CAPTION_DESCRIPTION 
  | CAPTION_ABREVIATION 
  | WEB_URL 
  | WEB_HTML 
  | WEB_XML_OR_XSL 
  | WEB_MAIL_ALIAS 
  | ADDRESS 
  | ADDRESS_STREET 
  | ADDRESS_HOUSE 
  | ADDRESS_CITY 
  | ADDRESS_STATE_OR_PROVINCE 
  | ADDRESS_ZIP 
  | ADDRESS_QUARTER 
  |  ADDRESS_COUNTRY 
  | ADDRESS_BUILDING 
  | ADDRESS_ROOM 
  | ADDRESS_FLOOR 
  | ADDRESS_FAX 
  | ADDRESS_PHONE 
  | GEO_CENTROID_X 
  | GEO_CENTROID_Y 
  | GEO_CENTROID_Z 
  | GEO_BOUNDARY_TOP 
  | GEO_BOUNDARY_LEFT 
  | GEO_BOUNDARY_BOTTOM 
  | GEO_BOUNDARY_RIGHT
  | GEO_BOUNDARY_FRONT 
  | GEO_BOUNDARY_REAR 
  | GEO_BOUNDARY_POLYGON 
  | PHYSICAL_SIZE 
  | PHYSICAL_COLOR 
  | PHYSICAL_WEIGHT 
  | PHYSICAL_HEIGHT 
  | PHYSICAL_WIDTH 
  | PHYSICAL_DEPTH 
  | PHYSICAL_VOLUME 
  | PHYSICAL_DENSITY 
  | PERSON_FULL_NAME 
  | PERSON_FIRST_NAME 
  | PERSON_LAST_NAME 
  | PERSON_MIDDLE_NAME 
  | PERSON_DEMOGRAPHIC 
  | PERSON_CONTACT 
  | QTY_RANGE_LOW 
  | QTY_RANGE_HIGH 
  | FORMATTING_COLOR 
  | FORMATTING_ORDER 
  | FORMATTING_FONT 
  | FORMATTING_FONT_EFFECTS 
  | FORMATTING_FONT_SIZE 
  | FORMATTING_SUB_TOTAL 
  | DATE 
  | DATE_START 
  | DATE_ENDED 
  | DATE_CANCELED 
  | DATE_MODIFIED 
  | DATE_DURATION 
  | VERSION

<prop caption def> ::= CAPTION <any string>

<measures def> :: = MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>] [<hidden def>] [DESCRIPTION <description expression>] [<comma> <measures def>]
<measure function def> ::= FUNCTION <function name>
<function name> ::= SUM 
| MIN 
| MAX
| COUNT 
<measure format def> ::= FORMAT <expression>
<measure type def> ::= TYPE <supported OLE DB 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
<command expression> ::= COMMAND <expression> [ <comma> <command expression>]


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), including the parentheses. 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 entire expression should be surrounded by brackets. It is not recommended that quotation marks be used for this purpose because the body of the command might include quotation marks. (Microsoft® SQL Server™  2000  Analysis Services supports nested brackets but not nested quotation marks.) 

Examples
Creating a Local CubeThe following code shows how to define a local cube's dimensions and measures:

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_KEY),
   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
)


CREATE CUBE Statement (Virtual Cube)
This statement facilitates the construction of complex data mining queries by client applications. Virtual cubes can be created that incorporate dimensions based on data mining models. Such dimensions are not related to fact tables.

BNF<create vcube> ::= CREATE {SESSION} CUBE <cube name>
FROM <cube list>

<cube list> ::= <cube> [,<cube list>]
<param list> ::= <param> ,<param list> | <param>
<param> ::= <measures list> | <dims list>

<measures list>    ::= <measure> [,<measures list>]
<measure>    ::= MEASURE <cube name>.<measure name> [<visibility qualifier>] [AS <measure name>]
<visibility qualifier>    ::= HIDDEN

<dims list> ::= <dim def> [, <dims list>]
<dim def> ::= <derived dim def> | <regular dim def>
<regular dim def> ::= DIMENSION <cube>.<dimension name>
<derived dim def> ::= DIMENSION <dim name>
<flags> [<visibility qual>]

FROM <from clause> 
<dim content def>
<from clause> ::= <DM from clause> | <reg from clause>
<DM from clause> ::= <dm model name>
<reg from clause> ::= <dim name>

COLUMN <column name>


Remarks
This feature allows client applications to create virtual cubes on the fly in order to conduct more sophisticated analysis. The virtual cubes that are created are of session scope, and they cannot be saved on the client computer.

To delete a virtual cube, use the DROP CUBE statement.

Examples
Creating a Session Virtual CubeThe following example creates a virtual cube that contains portions of an existing cube named Children and the results of a data mining model named My DM Model:

CREATE SESSION CUBE [Student DMM]
  FROM [Children]
  (
        MEASURE [Children].[Count],
        MEASURE [Children].[Avg Age]
        DIMENSION [Children].[Population],
        DIMENSION [Children].[Area]
        DIMENSION [Decision Tree] NOT_RELATED_TO_FACTS 
             FROM [My DM Model] COLUMN [Measures.Sales]
  )