Introduction

In a recent post, I started investigating around a new Analysis Services 2008 feature: writeback partition with MOLAP storage.

Unfortunately, I got stopped by a bug in SQL Server 2008 CTP 5 which disallowed me from committing my updates.

SQL Server 2008 CTP 6 is now available. I can now continue from where I stopped last time.

Committing updates

After our UPDATE CUBE MDX command from SSMS, let's run a COMMIT TRAN statement to validate our transaction.

This step works perfectly with CTP 6. We can see records inserted by SSAS into the writeback table.

What's behind the scene?

Processing

When we fully process a MOLAP writeback partition, we notice an extra item (compared to when processing a ROLAP one) in the Process Progress dialog box. This item shows the SQL query to the writeback table used to feed MOLAP writeback partition.

Profiling

If we activate SQL Server Profiler during COMMIT TRAN operation, we can see the following actions.

With ROLAP writeback:

With MOLAP writeback:

Extra WriteData events are issued, notifying SSAS 2008 feed of the MOLAP partition with writeback table data. This incremental feeding seems to be done by processing a temporary partition (named here WriteTable_Budget_INCR_UPDATE_TEMP_epn1o_), then merging it with the writeback partition.

A very interesting point is that if we profile relational database during commit operation, we can see the INSERT BULK T-SQL statement into writeback table, but no SQL query for processing temporary incremental writeback partition. It seems that SSAS takes in memory session updates and "Direct data load" them in this partition, as SQL Server Integration Services (SSIS) does with Partition Processing Data Flow items.

Performance gain

What improvement can we expect from a MOLAP writeback partition?

Test 1 (query)

To apprehend performance gain, we can do a simple test: in both cases (ROLAP and MOLAP), query the same cell, and see what's being done in SQL Server Profiler.

Test sequence:

1. Clear cube cache.
2. Query cell.
3. Query cell again (to check cache effect).

First query: data is read from measure group partition. With the ROLAP writeback partition, an extra ExecuteSQL event is triggered that reads writeback table data for corresponding cell. Data is cached.

Second query reads data from cache

The need to issue a SQL statement at query time makes MDX query processing longer. This extra step will dramatically increase query execution in situations where cube cell reading relies on many other cells (in case of calculations for example).

Test 2 (update)

The second test intends to observe cube cache consistency after committing updates.

Test sequence:

1. Clear cache.
2. Query cell twice.
3. Update cell
4. Commit transaction.
5. Query cell.

In ROLAP mode, the UPDATE CUBE statement triggers 2 ExecuteSQL events to read writeback table data. The commit step doesn't show a big difference between to two modes (see Profiling section earlier in the article).

In both bases, the commit step clears cube cache. As a consequence, the next cell read won't be able to rely on cube cache.

That would be nice if SSAS could handle 2 caches: one for the fact data, and one for the writeback data. This would reduce the effect of committing updates.

Which method is faster?

I wrote a VB Script (see previous article on performance) that executes steps of update test (test 2).

I have stressed SSAS with this simple benchmark (running it 10 times), for both ROLAP and MOLAP writeback modes.

The results are shown in table below (times are in seconds):

Querying and updating is faster with MOLAP writeback (mainly because no ExecuteSQL event is required to query writeback partition).

Committing is much slower with MOLAP writeback (probably because of the time needed to process/merge temporary partition). This shouldn't be a major issue, as committing is usually done after a series of queries/updates, like saving a document.

Conclusion

MOLAP Writeback is a promising feature for a read/write OLAP context.

In a budgeting application, where users (sometimes called contributors) submit updates (i.e. commit changes) in a cycle way, query performance decreases, as SSAS permanently issues SQL statements to query writeback table. This won't be the case anymore with MOLAP Writeback. We can expect here a huge performance improvement.