Writing a Value Back to a Cell

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.CommitTrans

UPDATE CUBE [Budget Cube] 
   SET
    ([1999], [Marketing], [Budget], [All Departments]) = 1000   

    USE_WEIGHTED_ALLOCATION BY 
([1998], [Sales], [Actual])/
([1999], [Sales], [Actual], [All Departments])

UPDATE CUBE [Budget Cube] 
   SET
    ([1999], [Sales], [Budget]) = 1000   

    USE_WEIGHTED_ALLOCATION BY 
      
([Sales], [Budget])/
1 + (Rank([1999].Children, Time.CurrentMember) * 0.1
 /
Sum(Rank([1999].Children,
   1 + (Rank([1999].Children, Time.CurrentMember) * 0.1)