Improve Clarity Write-Back Performance for Analysis Services

Back To Blog

Many Clarity clients running on Analysis Services have experienced performance issues when saving templates.  In many cases this is not the fault of either Clarity or the Analysis Services (SSAS) design, but simply a side effect of the way the SSAS save functionality works.

By default Clarity will perform a MDX UPDATE statement for each cell of data being pushed back into the database.  SSAS then determines the delta to the currently stored value in the cube and writes the difference into the write-back table.  This is the built-it SSAS functionality – it’s easy to implement and out-of-the-box, but unfortunately it can be very, very slow.

Clarity consultants implementing Clarity 6 quickly realized that this default write method would not meet all client expectations and another save method was required.  Working with the development team they developed what is known as the Clarity Quick-Save customization.  By creating a 100% ROLAP write-back partition in the cube, customizing some of the Clarity code and adding client specific SQL stored procedures they were able to by-pass the cube and update the write-back table directly – and thus significantly improving save performance.  With one client template saves were taking 2-3 minutes – a crazy long amount of time.  After the Fast-Save was implemented the save only took a few seconds!  It wastruly amazing the difference it made.  This one change took the implementation from the brink of failure into an immediate success!  The save performance issues were casting a dark shadow over what as otherwise a very successful implementation.

With version 7, Clarity realized the importance of this requirement and added functionality to support the Fast-Save out-of-the-box without modifying any code.  The 100% ROLAP partition and client specific SQL stored procedures are still required, but that’s it.  They also wrote a very detailed White-Paper on how to implement the functionality in Clarity 7, including some great samples of the SQL stored procedures.  The White-Paper is titled “Clarity 7 Write-Back Configuration” and can be found on the IBM Support site.  I have also included it in our OLAP Solutions Reference area for you convenience.

Many Clarity 6 clients don’t even realize this functionality is available to them.  The changes to the Clarity code are actually very minimal and have been implemented successfully in many clients without any problems.  If you are having save performance issues I highly recommend you review the white-paper and consider adding this enhancement to you Clarity implementation.  This is just one way you can make your Clarity implementation the best it can be!