Updating Cubes
There are three ways to update information in a cube:
- Update the fact table and reprocess the cube.
- Write back to leaf members of the cube.
- Use cell allocation on nonleaf members.
Reprocessing a Cube
This method of updating a cube's contents depends on the context of the cube itself. If the cube resides on the Analysis server, then the Decision Support Objects (DSO) Process method (of the MDStore interface) should be used to process the cube using the existing dimensions, measures, aggregations and so on. For more information about DSO, see Decision Support Objects. For more information about the Process method of the MDStore interface, see Process (MDStore Interface).
Local cubes can use the UPDATE CUBE statement for what-if analyses, but the allocations cannot be saved. That is, commit will fail if it is executed for the UPDATE CUBE statement. Because local cubes cannot have a writeback partition, writeback always fails against a local cube. Therefore, permanent changes to a local cube must be made by changing the local cube's fact table and rebuilding.
For more information on creating local cubes, see Connected to an OLE DB Provider or Building Local Cubes.
Writeback
Writebacks (that is, updates) can be accomplished on atomic cell members of write-enabled cubes. These updates result in a new aggregate value being propagated up through the cell's parent members.
Because data at higher levels is represented as a precalculation of data at lower levels, writebacks are permitted only on cells at the lowest level (that is, atomic or leaf cells) of a cube's data. The atomic cells coming from the fact table are represented in the cube by a single member in the lowest level of each dimension or measure in the cube. Updates at these levels are saved to a writeback table that stores the deltas for each value. The updated value is then propagated up through the affected aggregate members by PivotTable® Service.
If you want to update a higher-level member, use the UPDATE CUBE statement instead. This will assist you in preventing inconsistent results from being entered into the cube.
Note You cannot write back to local cubes. You can reprocess them by executing their original CREATE CUBE and INSERT INTO statements in a connection string.
Changes to a cube may not be immediately visible to other client applications that are connected to the cube, depending on their cache settings. For more information, see Managing the Client Cache and Isolation Levels.
Allocations in a Cube
When the value of a nonatomic cell is changed, the cells that contribute to that cell need to be updated to avoid inconsistent data within the cube. The UPDATE CUBE statement provides this facility. The new value of a nonatomic cell must be allocated among all of its constituent cells. The developer determines the method by which this allocation is made at design time.
The following table describes the allocation methods that are available.
| Allocation method | Description |
|---|---|
| Equal allocation | Each constituent cell is assigned an equal value |
| Equal increment | Every constituent cell will be changed according to an incremental value |
| Weighted allocation | Each constituent cell will be assigned an equal value that is weighted against a formula |
| Weighted increment | Every constituent cell is changed incrementally according to a weighting formula |
Important When supplying expressions for the allocation of a value, the client application must ensure that the expression assigns values whose aggregate value equals the originally allocated value. Additionally, the application must take into account the allocation on all dimensions concurrently.
For the purpose of transactions management, all of the operations required to make a cell update are considered to be monatomic. That is, if one atomic cell update fails, then all of them will fail, and the update itself will fail.
Note Allocations on local cubes cannot be saved by use of the transaction COMMIT method. Any allocations made on a local cube are only present while the connection to the local cube is still active (that is, for the duration of the session).