SSAS 2008 MOLAP writeback

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.

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/SSAS2008MOLAPWriteback/Picture01.png

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

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/SSAS2008MOLAPWriteback/Picture02.png

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:

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/SSAS2008MOLAPWriteback/Picture03.png

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.

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

4 Responses to SSAS 2008 MOLAP writeback

  1. Unknown says:

    http://www.batteryfast.com/sony/pcga-bpz51.htm Sony pcga-bpz51 battery http://www.batteryfast.com/sony/pcga-bpz51a.htm Sony pcga-bpz51a battery http://www.batteryfast.com/sony/pcga-bpz52.htm Sony pcga-bpz52 battery http://www.batteryfast.com/sony/vgp-bpl5a.htm Sony vgp-bpl5a battery http://www.batteryfast.com/sony/vgp-bps5.htm Sony vgp-bps5 battery http://www.batteryfast.com/sony/vgp-bps5a.htm Sony vgp-bps5a battery http://www.batteryfast.com/toshiba/pa3123u-1brs.htm Toshiba pa3123u-1brs battery http://www.batteryfast.com/toshiba/pa3178u-1bas.htm Toshiba pa3178u-1bas battery http://www.batteryfast.com/toshiba/pa3178u-1brs.htm Toshiba pa3178u-1brs battery http://www.batteryfast.com/toshiba/pa3211u-1bas.htm Toshiba pa3211u-1bas battery http://www.batteryfast.com/toshiba/pa3211u-1brs.htm Toshiba pa3211u-1brs battery http://www.batteryfast.com/toshiba/pa3479u-1brs.htm Toshiba pa3479u-1brs battery http://www.batteryfast.com/toshiba/pa3480u-1brs.htm Toshiba pa3480u-1brs battery http://www.batteryfast.com/toshiba/pa3591u-1bas.htm Toshiba pa3591u-1bas battery http://www.batteryfast.com/toshiba/pa3634u-1bas.htm Toshiba pa3634u-1bas battery http://www.batteryfast.com/toshiba/pa3635u-1brm.htm Toshiba pa3635u-1brm battery http://www.batteryfast.com/toshiba/pa3638u-1bap.htm Toshiba pa3638u-1bap battery http://www.batteryfast.com/toshiba/pabas117.htm Toshiba pabas117 battery http://www.batteryfast.com/toshiba/pabas118.htm Toshiba pabas118 battery http://www.batteryfast.com/acer/batsqu410.htm acer batsqu410 battery http://www.batteryfast.com/acer/qc145.htm acer qc145 battery http://www.batteryfast.com/acer/sq-1100.htm acer sq-1100 battery http://www.batteryfast.com/acer/sq-2100.htm acer sq-2100 battery http://www.batteryfast.com/acer/squ-202.htm acer squ-202 battery http://www.batteryfast.com/acer/squ-405.htm acer squ-405 battery http://www.batteryfast.com/acer/squ-406.htm acer squ-406 battery http://www.batteryfast.com/acer/squ-410.htm acer squ-410 battery http://www.batteryfast.com/dell/inspiron-1420.htm dell inspiron 1420 battery http://www.batteryfast.com/dell/vostro-1400.htm dell vostro 1400 battery http://www.batteryfast.com/mitac/bp-8089.htm mitac bp-8089 battery http://www.batteryfast.com/mitac/bp-8389.htm mitac bp-8389 battery http://www.batteryfast.com/sony/vgp-bpl9.htm sony vgp-bpl9 battery http://www.batteryfast.com/sony/vgp-bps10.htm sony vgp-bps10 battery http://www.batteryfast.com/sony/vgp-bps8.htm sony vgp-bps8 battery http://www.batteryfast.com/sony/vgp-bps8a.htm sony vgp-bps8a battery http://www.batteryfast.com/sony/vgp-bps9.htm sony vgp-bps9 battery http://www.batteryfast.com/toshiba/pa3369u-1bas.htm toshiba pa3369u-1bas battery http://www.batteryfast.com/toshiba/pa3369u-1brs.htm toshiba pa3369u-1brs battery

  2. 旺立 says:

    http://www.batteries-shop.nethttp://www.batterieslaptop.nethttp://www.uk-laptopbattery.comhttp://www.wt-batteries.comhttp://www.cheapteastore.comhttp://www.laptopbatterystore.co.ukhttp://www.batterieslaptop.nethttp://www.batteries-supply.comhttp://www.powertoolsbatteries.co.ukhttp://www.batterygrip.orghttp://www.uk-batteries.co.ukhttp://www.ukbatterystore.co.ukhttp://www.topbatteries.co.ukhttp://www.batteries-shop.net/acer-laptop-battery-c-2.htmlhttp://www.batteries-shop.net/apple-laptop-battery-c-3.htmlhttp://www.batteries-shop.net/asus-laptop-battery-c-4.htmlhttp://www.batteries-shop.net/compaq-laptop-battery-c-5.htmlhttp://www.batteries-shop.net/dell-laptop-battery-c-6.htmlhttp://www.batteries-shop.net/fujitsu-laptop-battery-c-7.htmlhttp://www.batteries-shop.net/gateway-laptop-battery-c-8.htmlhttp://www.batteries-shop.net/hp-laptop-battery-c-9.htmlhttp://www.batteries-shop.net/hp-compaq-laptop-battery-c-10.htmlhttp://www.batteries-shop.net/ibm-laptop-battery-c-11.htmlhttp://www.batteries-shop.net/lenovo-laptop-battery-c-12.htmlhttp://www.batteries-shop.net/lg-laptop-battery-c-13.htmlhttp://www.batteries-shop.net/panasonic-laptop-battery-c-14.htmlhttp://www.batteries-shop.net/samsung-laptop-battery-c-15.htmlhttp://www.batteries-shop.net/sony-laptop-battery-c-16.htmlhttp://www.batteries-shop.net/toshiba-laptop-battery-c-17.htmlhttp://www.uk-laptopbattery.com/acer-laptop-battery-c-2.htmlhttp://www.uk-laptopbattery.com/apple-laptop-battery-c-3.htmlhttp://www.uk-laptopbattery.com/asus-laptop-battery-c-4.htmlhttp://www.uk-laptopbattery.com/compaq-laptop-battery-c-5.htmlhttp://www.uk-laptopbattery.com/dell-laptop-battery-c-6.htmlhttp://www.uk-laptopbattery.com/fujitsu-laptop-battery-c-7.htmlhttp://www.uk-laptopbattery.com/gateway-laptop-battery-c-8.htmlhttp://www.uk-laptopbattery.com/hp-laptop-battery-c-9.htmlhttp://www.uk-laptopbattery.com/hp-compaq-laptop-battery-c-10.htmlhttp://www.uk-laptopbattery.com/ibm-laptop-battery-c-11.htmlhttp://www.uk-laptopbattery.com/lenovo-laptop-battery-c-12.htmlhttp://www.uk-laptopbattery.com/lg-laptop-battery-c-13.htmlhttp://www.uk-laptopbattery.com/panasonic-laptop-battery-c-14.htmlhttp://www.uk-laptopbattery.com/samsung-laptop-battery-c-15.htmlhttp://www.uk-laptopbattery.com/sony-laptop-battery-c-16.htmlhttp://www.uk-laptopbattery.com/toshiba-laptop-battery-c-17.html

  3. Sam Kane says:

    Here are this and some other articles on SSAS Write Back: http://ssas-wiki.com/w/Articles#Write_Back

  4. This is very interesting, You are a very skilled blogger. I’ve joined your feed and look forward to seeking more of your wonderful post. Also, I’ve shared your web site in my web and social networks!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s