ALTER CUBE Statement

Analysis Services Programming

previous page next page
Analysis Services Programming

ALTER CUBE Statement

This statement allows client applications to control the structure of a cube after it has been created.

BNF
For updating the hierarchy of a calculated member

ALTER CUBE <cube>

    UPDATE DIMENSION MEMBER <member> AS '<MDX rule>'

For updating user defined default members

ALTER CUBE <cube>

    UPDATE DIMENSION <dimension_name>, DEFAULT_MEMBER = '<MDX rule>'

For updating dimensions

<alter_statement> ::= <create_statement>|<remove_statement>|<move_statement>|<update_statement>

<create_statement> ::= CREATE DIMENSION MEMBER <parent_unique_name>.<member_name> [AS '<MDX expr.>'], KEY='<key_value>' [, <property_name> = '<value>' [, <property_name> = '<value>' ... ]]

<remove_statement> ::= DROP DIMENSION MEMBER <member_unique_name> [WITH DESCENDANTS]

<move_statement> ::= MOVE DIMENSION MEMBER <member_unique_name> [, SKIPPED_LEVELS = '<value>'] [WITH DESCENDANTS] UNDER <member_unique_name>

<update_statement>::=UPDATE DIMENSION MEMBER <member_unique_name> {AS '<MDX expr.>' | ,<property_name> = '<value>'} [, <property_name> = '<value>' ...]

<member_unique_name> ::= <dimension_name>.&[[]<key>[]]

Remarks

You can change the value of a custom rollup member by using the UPDATE DIMENSION MEMBER syntax.

Use the DEFAULT_MEMBER syntax to define a new default member in each dimension.

Alternatively, cubes that have parent-child relationships between members (that is, unbalanced and ragged hierarchies) can have their structures changed by the following basic operations:

  • Creating a new dimension member

  • Dropping a member of an existing dimension

  • Moving an existing dimension member within the structure of the existing cube

  • Updating the Multidimensional Expressions (MDX) definition of a dimension member
Examples
A. Creating a New Dimension Member

This example shows how to create a new dimension member. To add a member, specify its parent member in the CREATE DIMENSION MEMBER statement, and specify a key that uniquely identifies the member.

The following code adds Idaho and Boise to the Geography dimension:

ALTER_CUBE = ALTER CUBE Sales CREATE DIMENSION MEMBER USA.IDAHO, KEY = STATE_IDAHO, CREATE DIMENSION MEMBER IDAHO.BOISE, KEY=CITY_BOISE
B. Dropping a Dimension Member

This example shows how to drop a dimension member and all its children. It drops the dimension member Idaho and all its children, including the city Boise, from the Geography dimension. If you omit the WITH DESCENDANTS phrase, the statement promotes Boise to the same level as Washington and Oregon. 

ALTER_CUBE= ALTER CUBE Sales DROP DIMENSION MEMBER STATE_IDAHO WITH DESCENDANTS
C. Moving a Dimension Member

This example shows how to move a dimension member within the structure of a cube. It specifies both the node to move and its new position. The following code moves a member of the Employees dimension from under the [Southern California] hierarchy to the [Northwest] hierarchy. If you use the WITH DESCENDANTS phrase before the UNDER statement, child nodes move along with their parent.

ALTER_CUBE = ALTER CUBE Sales MOVE DIMENSION MEMBER Salesperson.[Francisco Ramirez] UNDER Region.Northwest
D. Defining a Default Member of a Dimension Hierarchy

This example shows how to define the default member of a dimension or hierarchy using the ALTER CUBE command. You can use any valid MDX expression that evaluates to a member in the definition of a default member: 

ALTER_CUBE = ALTER CUBE Sales UPDATE DIMENSION Customers, DEFAULT_MEMBER = '[Customers].[All Customers].[USA]'
previous page start next page