Dimension Write-Back

OLE DB Programmer's Reference

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

1998-2001 Microsoft Corporation. All rights reserved.