Dimension Write-Back

It is often useful for a client application to be able to add or remove members from a dimension. A common example occurs in "what-if" scenarios, in which a "Hypothetical Unit Sales" member is added to a Measures dimension.

Updating Member Properties

Dimension write-back is supported in MDX by the ALTER CUBE statement. For instance, the following statement updates the VALUE property:

ALTER CUBE Sales UPDATE DIMENSION MEMBER [Forecast Sales]
   Name='Forecase Sales ? Qtr1'

To modify a calculated member, use the "AS '<mdx_expression>'" clause:

ALTER CUBE Sales UPDATE DIMENSION MEMBER [Profit]
   AS '[Unit Sales] * ([Unit Price] ? [Unit Cost])'

Deleting Members

The ALTER CUBE statement can also be used to delete a dimension member or an entire subtree:

ALTER CUBE Sales DROP DIMENSION MEMBER
   [Measures].[Forecast].[Forecast Sales]

ALTER CUBE Sales DROP DIMENSION MEMBER
   [Measures].[Forecast] WITH DESCENDANTS

The first of the examples above shows the deletion of a single dimension member, in this case the member [Forecast Sales], which is a child of the member [Forecast]. The second example deletes the member [Forecast] along with its entire subtree.

If a parent member is deleted without using the WITH DESCENDANTS clause, the subtree is attached to its parent. For example, the statement

ALTER CUBE Sales DROP DIMENSION MEMBER [Measures].[Forecast]

would delete the [Forecast] member. The current children of [Forecast] would be attached to the [Measures] member, producing [Measures].[Forecast Sales] and [Measures].[Forecast Profit], for example.

Adding Members

To add a member to a dimension, use the CREATE DIMENSION MEMBER clause:

ALTER CUBE Sales CREATE DIMENSION MEMBER [Joe's QuickMart]
   KEY = [Geography].[USA].[WA].[Seattle],
   [Store Number]='12'

Here, a new store is added under Seattle in the Geography dimension. The member property [Store Number] is also set.

Moving Members

Dimension members can be moved within the hierarchy, either alone or with their entire subtrees. The following statement moves the [Beverages] member, with all its associated children, to reside under the [Food Products] member.

ALTER CUBE Sales MOVE DIMENSION MEMBER [Products].[Beverages]
   WITH DESCENDANTS
   UNDER [Products].[Food Products]

The move operation is equivalent to a deletion-and-addition operation performed in sequence. See the above section on deletion for more information about the semantics of deleting a member.

Syntax

ALTER CUBE <cube> <alter_statement> [[, <alter_statement>]...]

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

<create_statement> ::= CREATE DIMENSION MEMBER <member_spec>,
                        KEY='<key_value>'
                        [[, <property_name>='<value>']
                         [, <property_name>='<value>']...]

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

<move_statement> ::= MOVE DIMENSION MEMBER <member_spec> 
                     [WITH DESCENDANTS]
                     UNDER <member_spec>

<update_statement> ::= UPDATE DIMENSION MEMBER <member_spec>
                        [AS '<mdx_expression>', ] |
                        <property_name>='<value>'
                        [[, <property_name>='<value>']...]