Export (0) Print
Expand All

CREATE SUBCUBE Statement (MDX)

Updated: 17 July 2006

Redefines the cube space of a specified cube or subcube to a specified subcube. This statement changes the apparent cube space for subsequent operations.


CREATE SUBCUBE Cube_Name AS Select_Statement

Cube_Name

The valid string expression that provides the name of the cube or perspective that is being restricted, which becomes the name of the subcube.

Select_Statement

A valid Multidimensional Expressions (MDX) SELECT expression that does not contain WITH, NON EMPTY, or HAVING clauses, and does not request dimension or cell properties.

When default members are excluded in the definition of a subcube, coordinates will correspondingly change. For attributes that can be aggregated, the default member is moved to the [All] member. For attributes that cannot be aggregated, the default member is moved to a member that exists in the subcube. The following table contains example subcube and default member combinations.

Original default member Can be aggregated Subselect Revised default member

Time.Year.All

Yes

{Time.Year.2003}

No change

Time.Year.[1997]

Yes

{Time.Year.2003}

Time.Year.All

Time.Year.[1997]

No

{Time.Year.2003}

Time.Year.[2003]

Time.Year.[1997]

Yes

{Time.Year.2003, Time.Year.2004}

Time.Year.All

Time.Year.[1997]

No

{Time.Year.2003, Time.Year.2004}

Either Time.Year.[2003] or

Time.Year.[2004]

[All] members will always exist in a subcube.

Session objects created in the context of a subcube are dropped when the subcube is dropped.

For more information about subcubes, see Building Subcubes in MDX (MDX).

Example

The following example creates a subcube that restricts the apparent cube space to members that exist with the country of Canada. It then uses the MEMBERS function to return all members of the Country level of the Geography user-defined hierarchy - returning only the country of Canada.

CREATE SUBCUBE [Adventure Works] AS
   SELECT [Geography].[Country].&[Canada] ON 0
   FROM [Adventure Works]

SELECT [Geography].[Country].[Country].MEMBERS ON 0
   FROM [Adventure Works]

Release History

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.

Community Additions

ADD
Show:
© 2014 Microsoft