PivotTable® Service supports transaction management for “what if” analysis and write-back to cubes. “What if” analysis enables a user to change a cached copy of a cube while browsing it in order to analyze the effects. “What if” changes are visible to only the user who makes them and are not committed until a write-back is performed. Write-back enables a user to change a cube's displayed data; however, transparently to users, the changes are recorded in a write-back table, separate from the cube's underlying source tables. After a successful write-back, all users that are synchronized with the server see the effect of the write-back change reflected in the cube.
The scope of a transaction on a Microsoft® SQL Server™ OLAP Services OLAP server is limited to a single cube. Transactions that contain updates to a single cube either commit or fail on the cube as a complete atomic operation.
Transactions that contain updates to more than one cube are not atomic. In the case when a transaction contains updates that affect more than one write-enabled cube, it is possible for the updates to commit for some cubes but fail for others. This includes the case when updates are being applied to a virtual cube that contains more than one underlying write-enabled cube - it is possible for a transaction applied to the virtual cube to commit on one or more of the underlying cubes but fail on others.
If a transaction that includes updates to multiple cubes fails due to time-out, it is safe to attempt to commit the same transaction again - the transaction will be applied only to cubes that were not updated in the previous attempt.
It is recommended that client applications limit transactions to contain updates to a single cube only.
In PivotTable Service, by default a new transaction is implicitly started when a session begins, but each transaction is explicitly completed with a commit or rollback. If a commit or rollback is not issued to complete a transaction, the transaction and all the changes it contains are automatically rolled back when the session ends. A new transaction can be begun implicitly upon the completion of the preceding transaction.
By default, automatic commits do not occur. Changes are not propagated to the cube’s write-back table or visible to other users unless an explicit commit occurs as a result of a write-back.
This mode of transaction processing supports “what if” analysis. It enables users to do any of the following:
Normally, commits occur only for updates to a cube’s write-back table. Updates to a cube’s write-back table are allowed only if the cube has been write-enabled. If a user attempts to save updates for a cube that is not write-enabled, an error results.
A transaction must include updates for only a single cube. If a transaction includes updates for multiple cubes, the updates may be successful for some cubes and unsuccessful for others.
A possible cause of commit failure is a time-out during a write-back attempt.
If a commit is not successful because a client’s attempt to update a cube’s write-back table times out (that is, the time spent attempting the commit reaches the value of the DBPROP_MSMD_WRITEBACK_TIMEOUT property), the following message is produced:
Server unable to accept transaction at this time. Transaction pending on client.
In this case, the transaction's state is the same as immediately before the commit attempt. The client can reattempt to commit, attempt to rollback, or allow more “what if” changes.
The preceding message is produced as a result of the following nonstandard return code from the ITransaction::Commit method: MSMD_E_TRANSACTION_COMMIT_TIMEOUT.
The number of future time-outs can be reduced by increasing the value of the DBPROP_MSMD_WRITEBACK_TIMEOUT property. For more information, see DBPROP_MSMD_WRITEBACK_TIMEOUT Property.
PivotTable Service supports the read-committed isolation level. The visibility of changes made concurrently by others is read-committed, meaning that only committed updates are visible.
The isolation level can be set to “isolated” by using the DBPROP_MSMD_DEFAULT_ISOLATION_MODE property. For more information, see DBPROP_MSMD_DEFAULT_ISOLATION_MODE Property.
Updates are permitted only upon cells at the lowest level of cube data. The lowest level contains atomic data, which is not aggregated. Such an “atomic cell” is represented in the dataset by a single member in the lowest level of each dimension, including measures, in the cube.
To support updates in aggregated cells, one or more methods for distributing changes among subordinate atomic cells are required. Client applications can implement such methods.
The policy for updates is “last writer wins.” Updates recorded in a cube’s write-back table are cumulative, so the cube is displayed with the net effect of all changes in the write-back table. The last user to commit an update to a cell determines the displayed values of the cell and all aggregated cells that are derived from it.
The act of updating cells is performed atomically for each cube. Each committed update is recorded separately in the write-back table.
In write-back scenarios, the frequency of client/server synchronization determines when a user sees the most recent updates to a cube. Because some queries are resolved entirely from client cache, if updates have occurred since the last synchronization, the results of such a query will not reflect the updates.
The frequency of client/server synchronization can be controlled with the DBPROP_MSMD_AUTOSYNCHPERIOD property. For more information, see DBPROP_MSMD_AUTOSYNCHPERIOD Property.
“What if” analysis is permitted on local cubes. However, local cubes cannot be write-enabled; therefore, users cannot write back to local cubes.
This interface enables creation of a rowset that represents the same data as the Dataset object. There is a limitation that the rowset can be created only for a single cell in the dataset or the entire dataset. After a cell value is updated, other cells that are read from either the dataset or the range rowset reflect the updates.
For more information about the IMDRangeRowset interface and range rowsets, see the OLE DB documentation.