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