Using Writebacks

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.

For more information about lowest-level writebacks, see Writing a Value Back to a Cell.

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.

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

For more information about aggregate-level member writebacks, see UPDATE CUBE Statement.