Revision as of 11:44, 14 August 2012 by Csalter (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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, 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

There are two XLCubed formulae which handle Analysis Services writeback:XL3DoWriteback formula and XL3LookupRW formula.

XL3DoWriteback allows a value held in another cell to be written back to the cube whereas with XL3LookupRW the formula itself is overwritten directly.

In most cases XL3DoWriteback offers more flexibility and control.

There is also XL3RunSQL formula which is available for relational SQL updates.

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.

Please see the main article for more information: Relational Writeback