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])'
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.
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.
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.
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>']...]