Writeback to a Parent-Child Dimension

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Writeback to a parent-child dimension is handled using the ALTER CUBE MDX statement. This statement identifies the cube along with the operation to be performed. While there are many operations that can be performed through the ALTER CUBE statement, we are interested in those allowing us to add, move and drop a dimension member.

NOTE There full array of operations supported by the ALTER CUBE statement is something you may wish to explore if you are building an application against Analysis Services. For a more complete survey of these operations, please check out this Books Online entry.

Adding a Member

To add a member to a dimension, the CREATE DIMENSION MEMBER clause is used to identify the new member by name and by key (should the key and the name not be the same) and to specify the value of any additional properties associated with the new member. In this sample, a new member, Objective A.X which sits under the parent member Objective A, is added to the Objective dimension’s Objectives parent-child hierarchy. The member’s lone property, Owner, is assigned a value in the statement as well:

ALTER CUBE [Project Scorecard]
CREATE DIMENSION MEMBER
[Objective].[Objectives].[Objective A].[Objective A.X],
[Owner] = "Owner 01";

As the key and the name for the Objective A.X member are the same, the key is not identified in the previous statement. However, the following statement, employing the Key keyword, could be issued to the same effect:

ALTER CUBE [Project Scorecard]
CREATE DIMENSION MEMBER
[Objective].[Objectives].[Objective A].[Objective A.X],
Key = "Objective A.X",
[Owner] = "Owner 01";

Moving a Member

To move a member to a new position within a parent-child hierarchy, the MOVE DIMENSION MEMBER clause is employed to identify the member and its new parent. In the following sample statement, the member Objective A.X is moved to a position immediately under member Objective B:

ALTER CUBE [Project Scorecard]
MOVE DIMENSION MEMBER
[Objective].[Objectives].[Objective A.X]
UNDER [Objective].[Objectives].[Objective B];

If you wish to skip levels between the new parent and the moved member, the SKIPPED_LEVELS keyword can be employed along with a valid positive integer value. If not specified, it is assumed that the new member resides just under its newly assigned parent.

Please note, should the member being moved have descendants, those descendents are moved with it to the newly specified position.

Dropping a Member

To drop a member, the DROP DIMENSION MEMBER clause is used, identifying the member to be removed. The WITH DESCENDANTS keyword phrase can be used to drop any descendant members of this member. Without that subclause, descendants of the dropped member are moved under the it's parent (retaining the hierarchical structure of the descendants). 

In this sample, the Objective A.X member is dropped along with any potential descendants:

ALTER CUBE [Project Scorecard]
DROP DIMENSION MEMBER
[Objective].[Objectives].[Objective A.X]
WITH DESCENDANTS;