SSAS write-back is a very under-used feature of SSAS in my view. It can be very powerful when we have a good understanding of its capabilities. A good first step is to explore how it works through some MDX.
For the purpose of this article, I am using a very simple cube, which has two dimensions and one measure. I have enabled MOLAP write-back for its only partition and I have processed it, thus creating its Write Table. I will explore what happens when we issue different transaction management and UPDATE CUBE statements in varying order.
The first thing we should understand is that SSAS utilises transaction management for the write-back operations. There are three statements which we can use for this:
BEGIN TRAN[SACTION]
COMMIT TRAN[SACTION]
ROLLBACK TRAN[SACTION]
The [SACTION] part is optional and we can freely omit it.
To perform the actual updates, we use an UPDATE statement. You can find its definition here:
http://technet.microsoft.com/en-us/library/ms145488.aspx
It is worth to note that the BEGIN TRAN statement is not necessary since UPDATE issues a BEGIN TRAN statement implicitly. However, to complete a transaction we must either execute a COMMIT or a ROLLBACK statement. Also, important note here is that the transaction scope is within a session. An uncommitted transaction is visible only in its session. Once a COMMIT is called, the transaction gets written to the Write Table, and becomes visible for all cube statements (i.e. to all sessions).
So, let’s work with an MDX sample statement:
BEGIN TRANSACTION
UPDATE CUBE [Service Request] SET
([Service Start Date].[FiscalYear].[Fiscal Period].&[2011]&[2011-Q4]&[2011-13],
[Service End Date].[FiscalYear].[Fiscal Period].&[2011]&[2011-Q4]&[2011-13],
[Measures].[Measure 1 Sum])=10
USE_EQUAL_ALLOCATION
SELECT
{
([Service Start Date].[FiscalYear].[Fiscal Period].&[2011]&[2011-Q4]&[2011-13],
[Service End Date].[FiscalYear].[Fiscal Period].&[2011]&[2011-Q4]&[2011-13],
[Measures].[Measure 1 Sum])
} ON 0
FROM [Service Request]
ROLLBACK TRAN
COMMIT TRAN
Note that I am running this in an SSMS query window and I cannot execute multiple statements. Instead, we can select each statement and then execute it. Since we are in the same session, we can do this without losing the effect of the previously executed one.
First, I run the BEGIN TRANSACTION statement. It executes successfully:
Then, I run the UPDATE CUBE statement. It also executes successfully. Now, when I verify its success with the SELECT, I get 10 as the value for the measure. After this, we can check if anything got to the Write Table:
It is empty, because the transaction has not yet been committed. Also, if we browse the cube in BIDS we will not see the value:
This shows us that the value is only there in the current session where out transaction is active.
If we then call the COMMIT TRAN statement, we get a slight delay and we are notified that the statement has been executed successfully. At the same time, I run SQL Server Profiler and I captured the trace of what happened. As we can see, (because the write-back partition is MOLAP), the statement got written to our table and the write-back partition got processed. If we have another look at the Write Table we will notice that we have one row there. Also, we can see the value for the measure in BIDS:
ROLLBACK is less interesting – it just discards the UPDATEd values and the cube goes back to the same state it was in before we started manipulating it.
Another interesting thing which I already mentioned is that if we run UPDATE before we have issues a BEGIN TRANSACTION (note I am using TRAN and TRANSACTION interchangeably in my script and text), a transaction is implicitly started but not committed. Therefore, we cannot just call UDPATE and hope to see the changes in the Write Table. We must call COMMIT first.
This behaviour explains how Excel 2010 does write-back. When we change values it issues UDPATE statements and once we “Publish” the changes it issues a COMMIT TRAN. This is where we have the largest wait overhead in MOLAP mode. The comparison between MOLAP and ROLAP write-back is really for another article, whcih I hope to have the time to write soon.
Boyan Penev on Microsoft BI 