Introduction

Analysis Services introduced the capability to define a writeback partition with a MOLAP storage (the only choice until now was ROLAP).

You can check this whitepapers for a full list of enhancements: http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_bi.mspx

This new feature should help getting performance gains, as described by Microsoft:

"MOLAP Performance (FITS): New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance."

Configuring MOLAP writeback

My tests were done using SQL Server 2008 November CTP virtual machine.

CTP can be downloaded from https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395.

VHD can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en.

Writeback can be enabled from both SSMS and SSBI Development Studio.

SSMS offers a straight forward way of configuring writeback via a wizard, while SSBI Development Studio allows defining partition properties (like error configuration).

Direct XMLA definition is also available for ASSL specialists.

From SSMS, writeback can be enabled by expanding cube measure group, and right-clicking on Writeback folder item.

This gives access to a wizard. We immediately notice the new storage mode option, giving access to MOLAP (default), or ROLAP choice.

Even if storage mode is MOLAP, writeback mechanism will store updates in a writeback table, which guarantees data persistence even if cube is unprocessed.

This results in the creation of a writeback partition:

Writeback table will be created during writeback partition process if it doesn't exit.

The interesting point here is that it seems it will be possible to change the storage mode of an existing writeback partition (by disabling/enabling writeback), thus allowing easy optimization of an existing cube with pre-filled writeback data.

How does it work?

I suppose SSAS 2008 will transparently keep synchronized an invisible duplicate MOLAP partition that stores writeback table data, therefore reducing the amount of SQL queries against relational data source (where writeback table is stored).

Using MOLAP writeback

Once our cube is write-enabled, let's run an UPDATE CUBE MDX command from SSMS.

Then, let's run a COMMIT TRAN statement to validate our transaction. SSAS will insert new records to the writeback table as part of the execution.

That's where things get complicated, as SSAS 2008 returns the following error:

"Executing the query ...

Server: The operation has been cancelled.

Errors in the metadata manager. The write enabled 'WriteTable_Budget_INCR_UPDATE_TEMP_kf5kj_' partition must have table binding.

Execution complete"

I tried different workarounds to make it work (i.e. commit), unfortunately with no success.

I checked on the Microsoft Connect web site, but nobody seems to have reported this issue yet.

What's next?

Hopefully, Analysis Services development team will correct this problem for the next CTP.

So I will be patient, and wait for it to continue my investigations:

• Performance tests (compared to ROLAP writeback).
• Cube cache behavior on commit
• Etc.

See you soon.