Transactions in Analysis Services

PivotTable® Service supports transaction management for allocations and writebacks to cubes on the Analysis server. An allocation or a writeback to a cube changes a cached copy of that cube in order to analyze the effects of the change. This transaction process enables users to:

• Perform what-if analysis on cubes that are not write-enabled (that is, that do not support writeback).
  
  
• Perform what-if analysis on local cubes, which cannot be write-enabled.
  
  
• Perform what-if analysis on cubes to which they have only read permission.
  
  
• Perform what-if analysis without committing the updates.
  
  
• Make multiple what-if changes and reverse or alter some before committing all changes at once.

PivotTable Service supports these transactions by supporting the use of the Microsoft® ActiveX® Data Objects (ADO) Connection objects transaction methods.

Changes made during what-if analysis are visible only to the user who makes them; they are not committed to a shared cube until a Commit transaction is performed. Therefore a user may make a change to a cube's displayed data transparently without affecting other users. The changes are recorded in a writeback partition (that is, a table), separate from the cube's underlying source tables. After a successful writeback, all users who are synchronized with the server see the effect of the writeback change reflected in the cube.

In PivotTable Service, a new transaction is implicitly started whenever a session begins. Each transaction must either be explicitly completed by executing the ADO Commit transaction method, or be rolled back using the rollback transaction method. If a transaction is not completed properly, then the transaction and all the changes it contains are automatically rolled back when the session ends. A new transaction begins implicitly when the preceding transaction is completed.

Automatic commits do not occur. Changes are not propagated to the cube's writeback table and will not be visible to other users unless the Commit transaction method is used.