SSAS 2008 MOLAP Writeback (2)


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?


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.


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.


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.

This entry was posted in Analysis Services 2008. Bookmark the permalink.

7 Responses to SSAS 2008 MOLAP Writeback (2)

  1. 旺立 says:


  2. ping says: Hp dv2200 Battery Hp dv4000 Battery Hp dv6000 Battery Ibm thinkpad t40 Battery Toshiba satellite a105 Battery Toshiba satellite m45 Battery Toshiba pa3285u-3bas Battery Toshiba pa3395u-1brs Battery Toshiba pa3399u-1bas Battery Toshiba pa3399u-1brs Battery Toshiba pa3399u-2bas Battery Toshiba pa3399u-2brs Battery laptop battery laptop batteries Toshiba pa3465u-1brs Battery Toshiba pa3534u-1brs Battery Toshiba pa3594u-1brs Battery Uniwill 255-3s4400-g1l1 Battery Uniwill 258-4s4400-s1p1 Battery HP pavilion dv9000 Battery HP pavilion dv9700 Battery HP pavilion dv9500 Battery HP pavilion dv9600 Battery Dell inspiron b130 Battery Dell inspiron mini 9 Battery Hp nc8230 Battery Hp nc8430 Battery Hp nw8000 Battery Hp nw8200 Battery Hp nc6200 Battery Hp nc6100 Battery Hp nc6120 Battery Hp nc6400 Battery Hp pavilion dv2000 Battery Hp pavilion dv6000 Battery Hp pavilion dv8000 Battery Hp pavilion dv9000 Battery Hp pavilion dv9500 Battery Hp pavilion dv9600 Battery Hp pavilion dv9700 Battery Acer aspire 5520 Adapter Acer aspire 5920 Adapter Dell kd476 Battery Dell kr-onx511 Battery Dell latitude d620 Battery Dell latitude d630 Battery

  3. ping says: Acer aspire 9300 series Battery laptop ac adapter laptop ac adapters Apple a1185 Battery Apple a1185 black Battery Apple a1185 white Battery Asus a42-a4 Battery Asus a42-v6 Battery Clevo d400 Battery Clevo d470w Battery laptop battery laptop batteries Compaq dv4000 Battery Compaq dv6000 Battery Compaq dv6500 Battery Compaq nc6000 Battery Compaq nc8000 Battery Compaq presario v4000 Battery Dell 1501 Battery Dell 6400 Battery Dell d620 Battery Dell d630 Battery Dell d820 Battery Dell d830 Battery Dell e1505 Battery Dell gd761 Battery Dell gk479 Battery Dell inspiron 1000 Battery Dell inspiron 6000 Battery Dell inspiron 6400 Battery Dell inspiron 9300 Battery Dell inspiron 9400 Battery Dell kd476 Battery Dell kr-onx511 Battery Dell latitude d620 Battery Dell latitude d630 Battery Dell latitude d810 Battery Dell latitude d820 Battery Dell latitude d830 Battery Dell pc764 Battery Dell vostro 1000 Battery Dell vostro 1500 Battery Dell vostro 1700 Battery Dell wr050 Battery Dell xps m1210 Battery

  4. ping says: Toshiba PA3467U PA3396U 19V 3.42A 65W 5.5MM*2.5MM Adapter Toshiba PA3467U PA3396U 19V 3.42A 65W 5.5MM*2.5MM Adapter Toshiba PA3589U 19V 4.74A 90W 5.5MM*2.5MM Adapter Asus A2 A3 19V 3.42A 65W 5.5MM*2.5MM Adapter Dell 6400 D620 19.5V 3.34A 65W 7.4MM*5.0MM Adapter Toshiba PA2521U PA3201U-1ACA 15V 6A 90W 6.3MM*3.0MM Adapter Toshiba PA2450U PA3241U-1ACA 15V 3A 45W 6.3MM*3.0MM Adapter Samsung GT8000 GT8600 19V 3.16A 60W 5.5MM*3.4MM Adapter Delta 1680 19V 3.42A 65W 5.5MM*2.5MM Adapter Hp DV2000 DV6000 DV1000 18.5V 3.5A 65W 4.8MM*1.7MM Adapter Hp ZT1000 XT1000 18.5V 4.9A 90W 5.5MM*2.5MM Adapter Hp NC6000 NC8230 19V 4.74A 90W 4.8MM*1.7MM Adapter Hp DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter Liteon M320 M680 19V 3.42A 65W 5.5MM*2.5MM Adapter Liteon 1200 1800 19V 3.95A 75W 5.5MM*2.5MM Adapter Liteon TravelMate 4200 19V 4.74A 90W 5.5MM*1.7MM Adapter Liteon Satellite 1100 19V 4.74A 90W 5.5MM*2.5MM Adapter Liteon M3 M5244 19V 6.3A 120W 4 PIN ROUND Adapter Liteon 2200 R3000 19V 6.3A 120W 5.5MM*2.5MM Adapter Compaq NC6000 NC8000 18.5V 2.7A 50W 4.8MM*1.7MM Adapter Compaq ZT1000 1200 19V 3.16A 60W 5.5MM*2.5MM Adapter Compaq NX9000 DV9000 19V 4.74A 90W 5.5MM*2.5MM Adapter sony vgp-bpl9 battery sony vgp-bps10 battery sony vgp-bps8 battery sony vgp-bps8a battery sony vgp-bps9 battery Acer aspire one zg5 battery Toshiba pa3399u-1brs Battery Toshiba pa3399u-2bas Battery Toshiba pa3399u-2brs Battery Toshiba pa3465u-1brs Battery

  5. 旺立 says:


  6. Sam Kane says:

    Here are this and some other articles on SSAS Write Back:

  7. SQL Lion says:

    All features of SQL Server IDENTITY property of columns. And a handy procedure to monitor all Identity columns.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s