A Technical Review of the Simple Demonstration

NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services and makes direct reference to a previous post containing a simple demostration of what-if analysis.

In the simple demonstration of writeback, the analyst connected to a write-enabled cube, assembled a PivotTable, write-enabled the PivotTable, entered data and recalculated the cube, and then either published or discarded his or her changes.  Each of these steps represents an interaction with Analysis Services that’s important to understand.

The connection to the write-enabled cube was established like any other connection to Analysis Services from Excel.  In fact, the OLE DB provider used by Excel to make the connection has only one parameter relevant to a writeback scenario, but it is optional and was not employed for this demonstration.

NOTE I’ll point out that parameter in a later blog entry on allocations.    

The analyst then assembled a PivotTable based on Analysis Services data.  As with establishing the connection, there is nothing special or different about this interaction.

But then the analyst selected the option to write-enable the PivotTable. That instructed Excel to unlocked the PivotTable for data entry and caused a BEGIN TRANSACTION (MDX) statement to be submitted to Analysis Services.  Now things are different.  The BEGIN TRANSACTION  statement causes Analysis Services to initialize an in-memory cache associated with the analyst’s connection.

The analyst now enters values into the PivotTable and requests for it to be recalculated.  To recalculate the data in the PivotTable based on the data entered, an UPDATE CUBE statement containing these values is submitted to Analysis Services by the application.  Analysis Services calculates the difference between its values and the assignments in the UPDATE CUBE statement and writes these to the connection-specific writeback cache.

While subsequent requests for data through this connection reflect the values in the writeback cache, the base cube remains unaltered.  Other analysts can interact with the cube without awareness of these updates, providing the analyst the freedom to explore a wide range of values without interfering with others’ work.

When the analyst is done with his or her work, he or she must then decide what to do with the values written back to the cube.  If the analyst decides to discard the changes, a ROLLBACK TRANSACTION statement is submitted which causes the cache to be reset.  (Alternatively, the analyst could simple break the connection to Analysis Services and the cache would be discarded.) 

If the analyst decides to publish the changes, a COMMIT TRANSACTION statement is submitted to Analysis Services which causes the data to be written to a writeback partition and to be reflected in data presented to all consumers of the cube. The writeback partition is established by the cube designer and houses the former contents of the writeback cache. It also associates date-time and user-identity values with the data to provide a basic audit trail.