Using Cube Writebacks (MDX)
You update a cube by using the UPDATE CUBE statement. This statement lets you update a tuple with a specific value. To effectively use the UPDATE CUBE statement to update a cube, you have to understand the syntax for the statement, the error conditions that can occur, and the affect that updates can have on a cube.
UPDATE CUBE Statement Syntax
The following syntax describes the UPDATE CUBE statement:
UPDATE [CUBE] <Cube_Name> SET <tuple>.VALUE = <value> [,<tuple>.VALUE = <value>...]
[ USE_EQUAL_ALLOCATION | USE_EQUAL_INCREMENT |
USE_WEIGHTED_ALLOCATION [BY <weight value_expression>] |
USE_WEIGHTED_INCREMENT [BY <weight value_expression>] ]
If a full set of coordinates is not specified for the tuple, the unspecified coordinates will use the default member of the hierarchy. The tuple identified must reference a cell that is aggregated with the Sum function, and must not use a calculated member as one of the cell's coordinates.
You can think of the UPDATE CUBE statement as a subroutine that generates a series of individual writeback operations to atomic cells. All these individual writeback operations then roll up into the specified sum. In the following example, the UPDATE CUBE statement updates the Unit Shipped measure for the Drink product family to zero (0):
UPDATE CUBE [Warehouse and Sales] SET ([Measures].[Units Shipped], [Product].[Product Family].[Product Family].&[Drink]) = 0 USE_NO_ALLOCATION
Note
In Microsoft SQL Server Analysis Services, you can update any cell.
Important
When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE. For more information, see ConnectionString.
Error Conditions
The following table describes both what can cause writebacks to fail and the result of those errors.
Error Condition |
Result |
|---|---|
Update includes members from the same dimension that do not exist with one another. |
Update will fail. The cube space will not contain the referenced cell. |
Update includes a measure sourced to a measure of an unsigned type. |
Update will fail. Increments require that the measure be able to take a negative value. |
Update includes a measure that aggregates other than sum. |
An error is raised. |
Update was tried on a subcube. |
An error is raised. |
Affect of Cube Changes
The following changes will not have an effect on a writeback:
Processing of a cube, the cube's measure groups, or the cube's dimensions.
Adding attributes to any dimension.
Adding a new dimension.
Deleting a dimension that does not include the writeback.
Adding, modifying, or removing a hierarchy.
Adding a new measure.
The following changes cannot be made without removing the writeback data:
Deleting an attribute, or its attribute hierarchy, if the attribute is included in the writeback. This includes explicitly removing the attribute, or its attribute hierarchy, or removing the attribute's parent dimension.
Deleting a measure included in the writeback.
Adding an attribute without an (All) level to a dimension included in the writeback.
Changing the dimension granularity for a dimension included in the writeback.