Updates the value of a specified leaf or nonleaf cell in a cube, optionally allocating the value for a specified non-leaf cell across dependent leaf cells.
Syntax
UPDATE [ CUBE ] Cube_Name
SET
<update clause> [, <update clause> ...n ]
Tuple_Expression[.VALUE]= New_Value [ NO_ALLOCATION | USE_EQUAL_ALLOCATION | USE_EQUAL_INCREMENT | USE_WEIGHTED_ALLOCATION [ BY Weight_Expression] | USE_WEIGHTED_INCREMENT [ BY Weight_Expression] ]
Arguments
Cube_NameA valid string that provides the name of a cube.
Tuple_Expression
A valid Multidimensional Expressions (MDX) expression that returns a tuple.
New_Value
A valid numeric expression.
Weight_Expression
A valid Multidimensional Expressions (MDX) numeric expression that returns a decimal value between 0 and 1.
Remarks
The cell specified by the tuple expression can be any valid cell in the multidimensional space (that is, the cell does not have to be a leaf cell). However, the cell must be aggregated with the Sum aggregate function and must not include a calculated member in the tuple that is used to identify the cell.
It may be helpful to think of the UPDATE CUBE statement as a subroutine that will
automatically generate a series of individual cell writeback operations to leaf and non-leaf cells that will roll up into a specified sum.
The following table describes the methods of allocation.
Allocation method Description
USE_EQUAL_ALLOCATION Every leaf cell that contributes to the
updated cell will be assigned an equal value based on the following expression:
Allocation method Description
are contained in <tuple>)
USE_EQUAL_INCREMENT Every leaf cell that contributes to the updated cell will be changed according to the following expression:
<leaf cell value> = <leaf cell value> +
(<New Value > - <existing value>) /
Count(leaf cells contained in
<tuple>)
USE_WEIGHTED_ALLOCATION Every leaf cell that contributes to the
updated cell will be assigned an equal value that is based on the following expression:
<leaf cell value> = < New Value> * Weight_Expression
USE_WEIGHTED_INCREMENT Every leaf cell that contributes to the updated cell will be changed according to the following expression:
<leaf cell value> = <leaf cell value> +
(<New Value> - <existing value>) * Weight_Expression
If a weight expression is not specified, the UPDATE CUBE statement implicitly uses the following expression:
Weight_Expression = <leaf cell value> / <existing value>
A weight expression should be expressed as a decimal value between zero (0) and 1. This value specifies the ratio of the allocated value that you want to assign to the leaf cells that are affected by the allocation. The client application programmer's has the responsibility of creating
expressions whose rollup aggregate values will equal the allocated value of the expression. The client application must consider the allocation of all dimensions concurrently to avoid possible unexpected results, including incorrect rollup values or inconsistent data. Each UPDATE CUBE allocation should be considered to be atomic for transactional purposes. This means, that if any one of the allocation operations fails for any reason, such as an error in a formula or a security violation, the whole UPDATE CUBE operation will fail. Before the
calculations of the individual allocation operations are processed, a snapshot of the data is taken to ensure that the resulting calculations are correct.
When used on a measure that contains integers, the USE_WEIGHTED_ALLOCATION method can return imprecise results caused by incremental rounding changes. When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE.
See Also
MDX Data Manipulation Statements (MDX)
P:Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectionString
MDX Operator Reference
The Multidimensional Expressions (MDX) language supports arithmetic, logical, comparison, set, string, and unary operators. The following table lists the supported operators and their
descriptions.
In This Section
Topic Description
-- (Comment) Indicates comment text that is provided by the user.
- (Except) Performs a set operation that returns the difference between two sets, removing duplicate members.
- (Negative) (MDX) Performs a unary operation that returns the negative value of a numeric expression.
- (Subtract) (MDX) Performs an arithmetic operation that subtracts one number from another number.
* (Crossjoin) Performs a set operation that returns the cross product of two sets.
* (Multiply) (MDX) Performs an arithmetic operation that Caution
Topic Description
/ (Divide) (MDX) Performs an arithmetic operation that divides one number by another number.
^ (Power) Performs an arithmetic operation that raises one number by another number.
/*...*/ (Comment) Indicates comment text that is provided by the user.
// (Comment) Indicates user-provided text.
: (Range) Performs a set operation that returns a naturally ordered set, with the two specified members as endpoints, and all members between the two specified members included as members of the set.
+ (Add) (MDX) Performs an arithmetic operation that adds two numbers.
+ (Positive) (MDX) Performs a unary operation that returns the positive value of a numeric expression.
+ (String Concatenation) (MDX) Performs a string operation that
concatenates two or more character strings, tuples, or a combination of strings and tuples.
+ (Union) Performs a set operation that returns a union of two sets, removing duplicates.
< (Less Than) (MDX) Performs a comparison operation that determines whether the value of one MDX expression is less than the value of another MDX expression.
<= (Less Than or Equal To) (MDX) Performs a comparison operation that determines whether the value of one MDX expression is less than or equal to the value of another MDX expression.
<> (Not Equal To) (MDX) Performs a comparison operation that determines whether the value of one MDX expression is not equal to the value of another MDX expression.
Topic Description
determines whether the value of one MDX expression is equal to the value of another MDX expression.
> (Greater Than) (MDX) Performs a comparison operation that determines whether the value of one MDX expression is greater than the value of another MDX expression.
>= (Greater Than or Equal To) (MDX) Performs a comparison operation that determines whether the value of one MDX expression is greater than or equal to the value of another MDX expression.
AND (MDX) Performs a logical conjunction on two numeric expressions.
IS Performs a logical comparison on two object expressions.
NOT (MDX) Performs a logical negation on a numeric expression.
OR (MDX) Performs a logical disjunction on two numeric expressions.
XOR Performs a logical exclusion on two numeric expressions.
See Also
MDX Language Reference
-- (Comment)
Indicates comment text that is provided by the user.
Syntax
Parameters
Parameter Description
Comment_Text The string that contains the text of the comment.
Remarks
Comments can be inserted on a separate line, nested at the end of a Multidimensional
Expressions (MDX) script line, or nested within an MDX statement. The server does not evaluate the comment.
Use this operator for single-line or nested comments. Comments inserted with -- are delimited by the newline character.
There is no maximum length for comments.
Examples
The following example demonstrates the use of this operator.
-- This member returns the gross profit margin for product types -- and reseller types crossjoined by year.
SELECT
[Date].[Calendar].[Calendar Year].Members * [Reseller].[Reseller Type].Children ON 0, [Product].[Category].[Category].Members ON 1 FROM -- Select from the Adventure Works cube. [Adventure Works]
WHERE
[Measures].[Gross Profit Margin]
See Also
/*...*/ (Comment) (MDX) // (Comment) (MDX)
- (Except)
Performs a set operation that returns the difference between two sets, removing duplicate members.
Syntax
Set_Expression - Set_Expression
Parameters
Parameter Description
Set_Expression A valid Multidimensional Expressions (MDX) expression that returns a set.