If the cube supports editing (the dimension group has a ssas writeback partition) it is possible to use the Pivot Grid table in the editing mode to make changes to the cube data directly.
The table section accessible for editing is highlighted with yellow background color. It is formed depending on the cube security settings, types of indicators (calculated indicators are not editable), etc. It is permitted to write an arithmetical expression using the syntax and functions of the MDX language. It gives users the possibility to calculate an indicator using the current context (the server does it when recalculating). The table cells modified by the user are highlighted with blue background color and dark blue bold text in the cell). Display of the cell depends on the current server data synchronization mode.
There are two server data synchronization modes:
- Automatic update: any modification of the cell results in sending the changes to the server and recalculation of the data. The recalculated result is returned to the client.
- Working with the cache memory: the modifications are stored in the cache memory on the local workstation; the modifications are sent to the server upon the user's command Save changes (Recalculate, Recalculate table data with current changes), in order to reduce traffic and ensure a fluent response.
Thus, the modified cells that are not yet updated to the server are highlighted with blue background color, the cells updated with the server are shown in dark blue bold text.
When using cache memory, the user can send the data to the server for recalculation as often as necessary. Only the modifications stored in the cache memory since the last save operation (recalculation) or since the beginning of editing, can be rolled back by using Undo in the pivot grid table. In order to undo the recalculated changes, it is necessary to roll back the whole transaction.
All changes are isolated within the user session, they are not accessible to other users until the transaction is fixed.
The data can be entered both to list type members and to aggregates. When editing aggregates, allocation mechanisms are applied automatically based on the number of subordinated list members. Generally, there is a large number of members in the cube dimensions, therefore automatic allocation should be used with caution, as it can generate millions of records, which would substantially slow down the application or turn it inoperative.
For this reason, the number of updated cells shall be controlled and limited, and the developer of the MDX query should determine the updating rules in the UPDATE CUBE command.
To obtain the coordinates of the cell being modified due to a specific change, it is necessary to address the change hierarchy in the UPDATE CUBE command (the change hierarchy is enclosed between symbols <% %> , <%[Change].[Hierarchy]%> ), parameter <%newValue%> returns the new value, parameter <%oldValue%> returns the old value that was stored in the server. Thus, it is possible to correctly calculate the delta when editing an aggregate. It is recommended to include the whole cortege in the update command, in order to prevent uncontrolled generation of individual write back operations.
In order to ensure the offline work mode, it is possible to save the result of the original query and the changes in an XML file on the local machine and read them from the XML file later.