Write-Enabled Cubes
If you
An end user's change is stored in the writeback table as a difference from the currently displayed value. For example, if an end user changes a cell value from 90 to 100, the value +10 is stored in the writeback table, along with the time of the change and information about the end user who made it. The net effect of accumulated changes is displayed to client applications. The original value in the cube is preserved, and an audit trail of changes is recorded in the writeback table.
Changes to atomic and nonatomic cube cells are handled differently. (An atomic cell represents a lowest-level member of every dimension in the cube. The value of an atomic cell cannot be reduced by drilling down or slicing.) If a cube is write-enabled, changes can be made to an atomic cell. Changes can be made to a nonatomic cell only if the client application provides a means of distributing the changes among the atomic cells that make up the nonatomic cell. Programmers of client applications can use the UPDATE CUBE statement to distribute changes made to nonatomic cells. For more information, see UPDATE CUBE Statement.
Regardless of whether a client application distributes changes made to nonatomic cells, when queries are evaluated, changes in the writeback table are applied to nonatomic as well as atomic cell values so end users can view the effects of the changes throughout the cube.
End user changes are kept in a separate writeback table that you can:
- Convert to a
partition to permanently incorporate changes into the cube. This action makes the cube read-only. You can specify a filter expression to select the changes you want to convert.Note Converting to a partition is available only if you install Analysis Services for Microsoft® SQL Server™ 2000 Enterprise Edition.
- Discard to return the cube to its original state. This action makes the cube read-only.
For more information about write-enabling a cube, converting writeback tables to partitions, and deleting writeback data, see Maintaining Write-Enabled Cubes and Writeback Data.
An end user is permitted to record changes in a cube's writeback table only if the end user belongs to a cube role with read/write access to the cube's cells. For each cube role, you can control which cube cells can and cannot be updated. For more information, see Cell Security.
Write-enabled cubes and write-enabled dimensions are different but complementary features. A write-enabled cube gives users the ability to update cube cells, whereas a write-enabled dimension gives users the ability to update members. Using these two features in combination is optional. For example, a write-enabled cube does not have to include any write-enabled dimensions. Different procedures are used to write-enable cubes and dimensions and to maintain their security. For more information about write-enabled dimensions, see Write-Enabled Dimensions.
If you want to write-enable a cube with a Microsoft Access database as a data source, do not use Microsoft OLE DB Provider for ODBC Drivers in the data source definitions for the cube, its partitions, or its dimensions. Instead, you can use Microsoft Jet 4.0 (or later) OLE DB Provider.
A cube can be write-enabled only if all of its measures use the Sum aggregate function.
Note You cannot use Microsoft SQL Server 2000 Analysis Services to process a write-enabled cube created in Microsoft SQL Server 7.0 OLAP Services if any of the measures of the write-enabled cube use an aggregate function other than Sum.
Linked cubes and local cubes cannot be write-enabled. A virtual cube cannot be write-enabled; however, if one or more of its component cubes is write-enabled, virtual cube cells derived solely from the write-enabled cubes can be updated.