Revision as of 10:37, 21 April 2011 by Csalter (talk | contribs)

Writeback allows you to either permanently edit values in the Cube (if the administrator has allowed it), or to do simple what-if analysis in Excel.

XLCubed Excel Edition supports writeback in a number of ways through Grids. The data entry extensions, available as a cost option, also support Formula-based and relational writeback. In both cases the cube itself must be write enabled.

Writeback in XLCubed Grids

To enable writeback on an XLCubed Grid, select XLCubed > Properties... from the Grid right-click menu, then select the Enable Writeback option from the Writeback tab. On this tab you can also adjust a few other options, described below.

Online Entry Mode

Using Online mode, each value is submitted to the cube when typed: the value will be written and the data optionally re-retrieved (so that any aggregated data is updated straight away on the report).

By default, entry is allowed only at the leaf level of the data, as this is the most common environment.

Offline Entry Mode

In Offline mode, you can enter multiple updates at once, then submit them in one batch. To view the updates that are ready to be sent to the server, select the XLCubed > Writeback > Manage Offline Writeback right-click menu option, and to upload the changes, select XLCubed > Writeback > Submit Outstanding Writes.

Non-Leaf Level Writeback

When not entering data at leaf level, you must choose from the four Analysis Services spreading methods:

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

Data can then be entered for aggregate levels in the cube, and will be spread according to the rule specified.

Note that non-leaf Writeback in the Analysis Services environment should be used with caution since it can be time-consuming, depending on cube size and dimensionality.

Formula Writeback (available only with the data entry extensions option)

Formula writeback is available with the XL3LookupRW formula. The behaviour is identical to a standard XL3Lookup for data retrieval, but the formula can be overtyped to write data back to the cube.

The formula's syntax is identical to the XL3Lookup, and to help insert them, an option Insert XL3LookupRW can be found on the Insert / Edit XL3Lookup dialog.

Relational Writeback

In XLCubed > Options > Workbook Options, you can specify whether to use Standard (Analysis Services) or Relational writeback. Relational writeback requires certain cube customisations, and is typically a consultancy-led process to provide additional flexibility or performance over the standard writeback processing.

[Relational Writeback]