The ability to write information to a write-enabled cube in Multidimensional Expressions (MDX) is called a writeback. Writebacks are supported by two different methods, depending upon the level depth of the member to be changed. Writebacks are supported on server cubes through PivotTable® Service, as described later in this topic. Writebacks to local cubes are not supported.
Lowest-Level Member Writebacks
A lowest-level member is a member in a dimension associated with the lowest defined level of that dimension. For example, in the following diagram, the Products dimension is defined with three levels (not counting the (All) level).
Any writeback to a member at the [Product Name] level is considered a lowest-level writeback, because there are no defined levels below the [Product Name] level.
A separate table is maintained by Microsoft® SQL Server™ 2000 Analysis Services to store data changed by writebacks, and PivotTable Service propagates the data through the affected aggregate members.
Lowest-level writebacks are most commonly used to modify individual lowest-level member data for speculative analysis. If all of the members of a given aggregate are to be modified, it is often easier to use an aggregate-level member writeback.
Writing a Value Back to a Cell
There are different methods for updating the value of a cell directly in the cube, depending on the level of that value. The first method, called writeback, sets the value of a leaf level member directly, using the transaction methods of the Connection object. The second method, cell allocation, sets the value of a nonleaf member and specifies how the change should be distributed among the children of the member. For this method, it is not necessary to use the transaction methods of the Connection object. You can indirectly update a value in a cube by modifying its fact table and reprocessing it.
Writing to a Local Cube
Because local cubes do not use individual partitions to store data, changes to their data cannot be stored permanently. All changes made to the data of a local cube are stored only for the duration of the session scope. If you need to change the contents of a local cube permanently, you must make changes to the source data, not the local cube, and then rebuild the local cube.
Writing Back to Leaf Cells
For cells that are at the lowest level of the hierarchy, you can use the writeback method. The choice of method for updating the values of aggregate members within a server cube depends on the level depth of the member. For members that are at the lowest level of a hierarchy (and are therefore nonaggregated and atomic), update the cell's value property in a matching set of connection transaction methods.
Writing Back to Nonleaf Cells
For cells that are not at the lowest level of a hierarchy, use the UPDATE CUBE to execute a cell allocation. Using this method, an application can make a change to a nonleaf member and describe how that change should be allocated to the children of that member. It may be helpful to think of the UPDATE CUBE statement as a subroutine that automatically generates a series of individual writeback operations to atomic cells that roll up into a specified sum.
Examples
A. Updating a Leaf Cell Using Transactions
The following transaction updates a cell in a Cellset object, using cell writeback and transactions:
'Assume the existence of an open ADO Connection object (cn) and a Cellset object (cs).
'Also assume that ix and iy are integers pointing to an updatable cell.
'txtNewValue is assumed to be a string containing a new value for the cell.
cn.BeginTrans ' Start a new transaction.
cs(ix, iy).Value = Val(txtnewValue.Text) ' Write the new value to the cell
cn.CommitTransAggregate-Level Member Writebacks
An aggregate-level member is any member in a dimension whose value depends upon the value of members related to levels below the aggregate level. For example, in the previous diagram, the [Brand Name] level is an aggregate level because the values for its members depend upon aggregations performed on the [Product Name] level. The [Product Category], too, is an aggregate level, because the values for its members depend upon aggregations created from the [Brand Name] level members.
Aggregate-level writebacks are more difficult to process, because in order to modify an aggregate level, all of the members that are used to construct the values for that aggregate level must be modified. You could individually modify each lowest-level member so that the aggregate level represents the desired value, but for cubes representing thousands, tens of thousands, or more values, this is not a recommended option.
Instead, the UPDATE CUBE statement can be employed, using an allocation. Using one of four different allocation formulas, MDX can distribute the desired aggregate value across all of the lowest level members, in effect handling all of the individual lowest-level writebacks for you. Aggregate-level writebacks can be used only when the values are aggregated using the Sum aggregate function.
Aggregate-level writebacks are best used when a correction to an aggregate figure is required affecting all lowest-level members of a particular aggregation. Although lowest-level writebacks can also be used to accomplish this task, the aggregate-level writeback is faster and, because it is treated as a single atomic transaction, ensures that security or formula validation issues will not leave a cube in an inconsistent state.
Note Aggregate-level writebacks may produce imprecise results when integer values are allocated, due to incremental rounding variations.
UPDATE CUBE Statement
This statement portions out, according to a specified formula, the delta of an updated cell value to all of the children of that member. This method of updating the contents of a cube is called allocation, and is only supported on measures using the SUM aggregation type.
SET
[ USE_EQUAL_ALLOCATION |
USE_EQUAL_INCREMENT |
USE_WEIGHTED_ALLOCATION [BY <>]
USE_WEIGHTED_INCREMENT [BY
